• 导出数据为Excel文档


    导出数据为Excel文档

    工具类代码1:
    package cn.xyt.util;
    
    import com.alibaba.fastjson.JSON;
    import com.alibaba.fastjson.JSONArray;
    import com.alibaba.fastjson.JSONObject;
    import org.apache.poi.hpsf.SummaryInformation;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.math.BigDecimal;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * 2019.05.31
     * 支持大数据导出
     */
    public class ExcelUtilNew {
    
        private final static int MAX_ROW = 65535;//定义最大列数. excel 2003版最大值为MAX_ROW
    
        public static String NO_DEFINE = "no_define";//未定义的字段
        public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";//默认日期格式
        public static int DEFAULT_COLOUMN_WIDTH = 17;
    
    
        public static void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray, int colWidth, OutputStream out) {
            Object o = jsonArray.get(0);
            JSONObject json = (JSONObject) JSONObject.toJSON(o);
            for (String key : json.keySet()) {
    
            }
    
            exportExcel(title, headMap, jsonArray, null, colWidth, out);
        }
    
        /**
         * 导出Excel 97(.xls)格式 ,少量数据
         *
         * @param title       标题行
         * @param headMap     属性-列名
         * @param jsonArray   数据集
         * @param datePattern 日期格式,null则用默认日期格式
         * @param colWidth    列宽 默认 至少17个字节
         * @param out         输出流
         */
        public static void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
            if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            workbook.createInformationProperties();
            workbook.getDocumentSummaryInformation().setCompany("*****公司");
            SummaryInformation si = workbook.getSummaryInformation();
            si.setAuthor("JACK");  //填加xls文件作者信息
            si.setApplicationName("导出程序"); //填加xls文件创建程序信息
            si.setLastAuthor("最后保存者信息"); //填加xls文件最后保存者信息
            si.setComments("JACK is a programmer!"); //填加xls文件作者信息
            si.setTitle("POI导出Excel"); //填加xls文件标题信息
            si.setSubject("POI导出Excel");//填加文件主题信息
            si.setCreateDateTime(new Date());
            //表头样式
            HSSFCellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 700);
            titleStyle.setFont(titleFont);
            // 列头样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            // 单元格样式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(cellFont);
            // 生成一个(带标题)表格
            HSSFSheet sheet = workbook.createSheet();
            // 声明一个画图的顶级管理器
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            // 定义注释的大小和位置,详见文档
            HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                    0, 0, 0, (short) 4, 2, (short) 6, 5));
            // 设置注释内容
            comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));
            // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
            comment.setAuthor("JACK");
            //设置列宽
            int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
            int[] arrColWidth = new int[headMap.size()];
            // 产生表格标题行,以及设置列宽
            String[] properties = new String[headMap.size()];
            String[] headers = new String[headMap.size()];
            int ii = 0;
            for (Iterator<String> iter = headMap.keySet().iterator(); iter
                    .hasNext(); ) {
                String fieldName = iter.next();
    
                properties[ii] = fieldName;
                headers[ii] = fieldName;
    
                int bytes = fieldName.getBytes().length;
                arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
                sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
                ii++;
            }
            // 遍历集合数据,产生数据行
            int rowIndex = 0;
            for (Object obj : jsonArray) {
                if (rowIndex == MAX_ROW || rowIndex == 0) {//此处定义数据最大存储量
                    if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了,则在第二页显示
    
                    HSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                    titleRow.createCell(0).setCellValue(title);
                    titleRow.getCell(0).setCellStyle(titleStyle);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
    
                    HSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
                    for (int i = 0; i < headers.length; i++) {
                        headerRow.createCell(i).setCellValue(headers[i]);
                        headerRow.getCell(i).setCellStyle(headerStyle);
    
                    }
                    rowIndex = 2;//数据内容从 rowIndex=2开始
                }
                JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
                HSSFRow dataRow = sheet.createRow(rowIndex);
                for (int i = 0; i < properties.length; i++) {
                    HSSFCell newCell = dataRow.createCell(i);
    
                    Object o = jo.get(properties[i]);
                    String cellValue = "";
                    if (o == null) cellValue = "";
                    else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                    else cellValue = o.toString();
    
                    newCell.setCellValue(cellValue);
                    newCell.setCellStyle(cellStyle);
                }
                rowIndex++;
            }
            // 自动调整宽度
            /*for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }*/
            try {
                workbook.write(out);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 导出Excel 2007 OOXML (.xlsx)格式
         *
         * @param title       标题行
         * @param headMap     属性-列头
         * @param jsonArray   数据集
         * @param datePattern 日期格式,传null值则默认 年月日
         * @param colWidth    列宽 默认 至少17个字节
         * @param out         输出流
         */
        public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
            if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
            // 声明一个工作薄
            SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
            workbook.setCompressTempFiles(true);
            //表头样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 700);
            titleStyle.setFont(titleFont);
            // 列头样式
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            headerStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            // 单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(cellFont);
            // 生成一个(带标题)表格
            Sheet sheet = workbook.createSheet();
            //设置列宽
            int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
            int[] arrColWidth = new int[headMap.size()];
            // 产生表格标题行,以及设置列宽
            String[] properties = new String[headMap.size()];
            String[] headers = new String[headMap.size()];
            int ii = 0;
            for (Iterator<String> iter = headMap.keySet().iterator(); iter
                    .hasNext(); ) {
                String fieldName = iter.next();
    
                properties[ii] = fieldName;
                headers[ii] = headMap.get(fieldName);
    
                int bytes = fieldName.getBytes().length;
                arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
                sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
                ii++;
            }
            // 遍历集合数据,产生数据行
            int rowIndex = 0;
            for (Object obj : jsonArray) {
                if (rowIndex == MAX_ROW || rowIndex == 0) {
                    if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了,则在第二页显示
    
                    Row titleRow = sheet.createRow(0);//表头 rowIndex=0
                    titleRow.createCell(0).setCellValue(title);
                    titleRow.getCell(0).setCellStyle(titleStyle);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
    
                    Row headerRow = sheet.createRow(1); //列头 rowIndex =1
                    for (int i = 0; i < headers.length; i++) {
                        headerRow.createCell(i).setCellValue(headers[i]);
                        headerRow.getCell(i).setCellStyle(headerStyle);
                    }
                    rowIndex = 2;//数据内容从 rowIndex=2开始
                }
                JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
                Row dataRow = sheet.createRow(rowIndex);
                for (int i = 0; i < properties.length; i++) {
                    Cell newCell = dataRow.createCell(i);
    
                    Object o = jo.get(properties[i]);
                    String cellValue = "";
                    if (o == null) cellValue = "";
                    else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                    else if (o instanceof Float || o instanceof Double)
                        cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                    else cellValue = o.toString();
    
                    newCell.setCellValue(cellValue);
                    newCell.setCellStyle(cellStyle);
                }
                rowIndex++;
            }
            // 自动调整宽度
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }
            try {
                workbook.write(out);
                workbook.dispose();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * Web 导出excel
         *
         * @param fileName 文件名
         * @param title    首行标题
         * @param headMap  行头
         * @param ja       数据
         * @param response
         * @throws IOException
         */
        public static void downloadExcelFile(String fileName, String title, Map<String, String> headMap, JSONArray ja, HttpServletResponse response) throws IOException {
            try {
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                ExcelUtilNew.exportExcelX(title, headMap, ja, null, 0, os);
                byte[] content = os.toByteArray();
                InputStream is = new ByteArrayInputStream(content);
                // 设置response参数,可以打开下载页面
                response.reset();
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
                response.setContentLength(content.length);
                ServletOutputStream outputStream = response.getOutputStream();
                BufferedInputStream bis = new BufferedInputStream(is);
                BufferedOutputStream bos = new BufferedOutputStream(outputStream);
                byte[] buff = new byte[8192];
                int bytesRead;
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
                bis.close();
                bos.close();
                outputStream.flush();
                outputStream.close();
                is.close();
                os.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        /**
         * web导出excel
         *
         * @param fileName 文件名
         * @param title    标题
         * @param list     数据
         * @param res
         */
        public static void downloadExcelFile(String fileName, String title, List<Map<String, Object>> list, HttpServletResponse res) throws IOException {
            Map<String, String> headMap = new LinkedHashMap<>(1);
            for (String head : list.get(0).keySet()) {
                headMap.put(head, head);
            }
            JSONArray jsonList = JSONArray.parseArray(JSON.toJSONString(list));
            downloadExcelFile(fileName + "_" + getTodayStr(), title, headMap, jsonList, res);
        }
    
        public static String getTodayStr() {
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            return df.format(new Date());
        }
    
    
    
    
    
        //测试2
        public static void main(String[] args) throws IOException {
            List<Map<String, Object>> list = new ArrayList<>();
            for (int i = 0; i < 10; i++) {
                Map<String, Object> map = new LinkedHashMap<>();
                map.put("1", 6);
                map.put("2", 6);
                map.put("3", 6);
                list.add(map);
            }
            JSONArray objects = JSONArray.parseArray(JSON.toJSONString(list));
            System.out.println(objects.toJSONString());
    
    
            Map<String, String> headMap = new LinkedHashMap<String, String>();
            headMap.put("1", "1");
            headMap.put("2", "2");
            headMap.put("3", "3");
    
    
            String title = "测试2";
            OutputStream outXlsx = new FileOutputStream("E://b.xlsx");
            System.out.println("正在导出xlsx....");
            Date d2 = new Date();
            ExcelUtilNew.exportExcelX(title, headMap, objects, null, 0, outXlsx);
            System.out.println("执行完毕" + (new Date().getTime() - d2.getTime()) + "ms");
            outXlsx.close();
    
    
        }
    
    
        //测试
        public static void main1(String[] args) throws IOException {
            int count = 100000;
            JSONArray ja = new JSONArray();
            for (int i = 0; i < 100000; i++) {
                Student s = new Student();
                s.setName("POI" + i);
                s.setAge(i);
                s.setBirthday(new Date());
                s.setHeight(i);
                s.setWeight(i);
                s.setSex(i / 2 == 0 ? false : true);
                ja.add(s);
            }
            Map<String, String> headMap = new LinkedHashMap<String, String>();
            headMap.put("name", "姓名");
            headMap.put("age", "年龄");
            headMap.put("birthday", "生日");
            headMap.put("height", "身高");
            headMap.put("weight", "体重");
            headMap.put("sex", "性别");
    
            String title = "测试";
            /*
            OutputStream outXls = new FileOutputStream("E://a.xls");
            System.out.println("正在导出xls....");
            Date d = new Date();
            ExcelUtil.exportExcel(title,headMap,ja,null,outXls);
            System.out.println("共"+count+"条数据,执行"+(new Date().getTime()-d.getTime())+"ms");
            outXls.close();*/
            //
            OutputStream outXlsx = new FileOutputStream("E://b.xlsx");
            System.out.println("正在导出xlsx....");
            Date d2 = new Date();
            ExcelUtilNew.exportExcelX(title, headMap, ja, null, 0, outXlsx);
            System.out.println("共" + count + "条数据,执行" + (new Date().getTime() - d2.getTime()) + "ms");
            outXlsx.close();
        }
    }
    
    class Student {
        private String name;
        private int age;
        private Date birthday;
        private float height;
        private double weight;
        private boolean sex;
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    
        public float getHeight() {
            return height;
        }
    
        public void setHeight(float height) {
            this.height = height;
        }
    
        public double getWeight() {
            return weight;
        }
    
        public void setWeight(double weight) {
            this.weight = weight;
        }
    
        public boolean isSex() {
            return sex;
        }
    
        public void setSex(boolean sex) {
            this.sex = sex;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    }
    
    
    

    此代码可用,运行main方法出现的异常:java.lang.NoClassDefFoundError: javax/servlet/ServletOutputStream引入下面包即可,在启动tomcat部署运行程序时注解此包

    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>3.1.0</version>
        <scope>provided</scope>
    </dependency>
    
    工具类代码2:

    此代码作为参考学习,因其不再符合导出大数据的需求,以后不再使用.

    package cn.xyt.util;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.UnsupportedEncodingException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.Region;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.IndexedColors;
    //import org.apache.poi.xssf.usermodel.XSSFRow;
    //import org.apache.poi.xssf.usermodel.XSSFSheet;
    //import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    
    @SuppressWarnings("deprecation")
    public class ExcelUtil{
    
    	private static final Log log = LogFactory.getLog(ExcelUtil.class);
    
    	/**
    	 * 创建excel
    	 * @param res
    	 * @param list
    	 * @param fileName
    	 * @throws IOException
    	 */
    	public static void createExcel(HttpServletResponse res,List<Map<String, Object>> list,String fileName) throws IOException {
    		try {
    			fileName = new String(fileName.getBytes(), "ISO-8859-1");
    		} catch (UnsupportedEncodingException e) {
    			log.error(e);
    		}
    		res.setCharacterEncoding("utf-8");
    		res.reset(); // 清空数据
    		res.setContentType("applicatoin/octet-stream;charset=utf-8");
    		res.addHeader("Content-Disposition", "attachment; filename=""+ fileName + "";");
    
    		// 创建Excel的工作书册 Workbook,对应到一个excel文档
    	    HSSFWorkbook wb = new HSSFWorkbook();
    	    // 创建Excel的工作sheet,对应到一个excel文档的tab
    	    HSSFSheet sheet = wb.createSheet("sheet1");
    
    	    HSSFCellStyle titleStyle = wb.createCellStyle();
    	    HSSFCellStyle contentStyle = wb.createCellStyle();
    
    	    // 居中
    	    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	    titleStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    	    titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    	    contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    
    	    //设置字体大小
    	    HSSFFont font = wb.createFont();
    	    font.setFontName("楷书");
    	    font.setFontHeightInPoints((short) 15);
    	    font.setBoldweight((short)3);
    	    titleStyle.setFont(font);
    
    	    HSSFFont contentfont = wb.createFont();
    	    contentfont.setFontHeightInPoints((short) 11);
    	    contentStyle.setFont(contentfont);
    
    	    //设置自动换行
    //	    titleStyle.setWrapText(true);
    //	    contentStyle.setWrapText(true);
    
    	    // 建立新行
    	    HSSFRow titlerow = sheet.createRow(0);
    	    titlerow.setHeightInPoints(30);
    	    Map<String, Object> title = list.get(0);
    	    int i=0;
    	    for(String key: title.keySet()){
    	    	HSSFCell cell = titlerow.createCell(i);
    	    	sheet.setColumnWidth(i, 60*100);
    	    	cell.setCellType(Cell.CELL_TYPE_STRING);
    	    	cell.setCellValue(key);
    	    	cell.setCellStyle(titleStyle);
    	    	i++;
    	    }
    	    int j = 1;
    	    for (Map<String, Object> map: list) {
    	    	HSSFRow contentrow = sheet.createRow(j);
    	    	j++;
    	    	contentrow.setHeightInPoints(20);
    	    	int jj =0;
    	    	for(String key: map.keySet()){
    	    		HSSFCell cell = contentrow.createCell(jj);
    	    		jj++;
    	    		cell.setCellType(Cell.CELL_TYPE_STRING);
    	    		String content = ""+map.get(key);
    		    	cell.setCellValue(content);
    		    	cell.setCellStyle(contentStyle);
    	    	}
    	    }
    		wb.write(res.getOutputStream());
    	}
    
    
    
    	/**
    	 * 生成并下载Excel
    	 * @param res HTTP响应
    	 * @param list Excel内数据
    	 * @param titleArray 标题数组
    	 * @param fileName 文件名
    	 */
    	public static void createExcel(HttpServletResponse res,List<List<String>> list,String[] titleArray,String fileName) {
    		try {
    			fileName = new String(fileName.getBytes(), "ISO-8859-1");
    		} catch (UnsupportedEncodingException e) {
    			log.error(e);
    		}
    		res.setCharacterEncoding("utf-8");
    		res.reset(); // 清空数据
    		res.setContentType("applicatoin/octet-stream;charset=utf-8");
    		res.addHeader("Content-Disposition", "attachment; filename=""+ fileName + "";");
    
    		// 创建Excel的工作书册 Workbook,对应到一个excel文档
    	    HSSFWorkbook wb = new HSSFWorkbook();
    	    // 创建Excel的工作sheet,对应到一个excel文档的tab
    	    HSSFSheet sheet = wb.createSheet("sheet1");
    
    	    HSSFCellStyle titleStyle = wb.createCellStyle();
    	    HSSFCellStyle contentStyle = wb.createCellStyle();
    
    	    // 居中
    	    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	    contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    
    	    //设置字体大小
    	    HSSFFont font = wb.createFont();
    	    font.setFontName("楷书");
    	    font.setFontHeightInPoints((short) 15);
    	    font.setBoldweight((short)3);
    	    titleStyle.setFont(font);
    
    	    HSSFFont contentfont = wb.createFont();
    	    contentfont.setFontHeightInPoints((short) 11);
    	    contentStyle.setFont(contentfont);
    
    	    //设置自动换行
    //	    titleStyle.setWrapText(true);
    //	    contentStyle.setWrapText(true);
    
    	    // 建立新行
    	    HSSFRow titlerow = sheet.createRow(0);
    	    titlerow.setHeightInPoints(30);
    	    for(int i=0;i<titleArray.length;i++){
    	    	HSSFCell cell = titlerow.createCell(i);
    	    	sheet.setColumnWidth(i, 60 * 256);
    	    	cell.setCellType(Cell.CELL_TYPE_STRING);
    	    	cell.setCellValue(titleArray[i]);
    	    	cell.setCellStyle(titleStyle);
            }
    	    for (int i=0;i<list.size();i++) {
    	    	HSSFRow contentrow = sheet.createRow(i+1);
    	    	contentrow.setHeightInPoints(20);
    	    	List<String> li=list.get(i);
    	    	for(int j=0;j<li.size();j++){
    	    		HSSFCell cell = contentrow.createCell(j);
    	    		cell.setCellType(Cell.CELL_TYPE_STRING);
    	    		String content=li.get(j);
    //				try {
    //					content = new String(li.get(j).getBytes("UTF-8"), "GBK");
    //				} catch (UnsupportedEncodingException e) {
    //					// TODO Auto-generated catch block
    //					e.printStackTrace();
    //				}
    		    	cell.setCellValue(content);
    		    	cell.setCellStyle(contentStyle);
    	    	}
    	    }
    	    try {
    			wb.write(res.getOutputStream());
    		} catch (IOException e) {
    			log.error(e);
    		}
    	}
    
    
    	/**
    	 * 读取office2007之前的Excel文件(后缀为.xls)
    	 * @param is
    	 * @return
    	 */
    	public static List<Map<String, Object>> getxls(InputStream is) {
    		List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
    		try {
    			// 构造 XSSFWorkbook 对象,strPath 传入文件路径
    			HSSFWorkbook xwb = new HSSFWorkbook(is);
    			// 读取第一章表格内容
    			HSSFSheet sheet = xwb.getSheetAt(0);
    			// 定义 row、cell
    			HSSFRow row;
    			String cell;
    			List<String> title=new ArrayList<String>();
    			//输出表头信息
    			row = sheet.getRow(0);
    			if(row==null){
    				return list;
    			}
    			for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
    				// 通过 row.getCell(j).toString() 获取单元格内容,
    				cell =row.getCell(j).toString();
    				title.add(cell);
    			}
    			Map<String, Object> titlemap=new HashMap<String, Object>();
    			titlemap.put("title", title);
    			list.add(titlemap);
    			// 循环输出表格中的内容
    			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
    				row = sheet.getRow(i);
    				Map<String, Object> map=new HashMap<String, Object>();
    				map.put("row", (i+1));
    				for (int j = row.getFirstCellNum(); j < title.size(); j++) {
    					// 通过 row.getCell(j).toString() 获取单元格内容,
    					if(row.getCell(j)==null){
    						continue;
    					}
    					row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
    					cell=row.getCell(j).toString();
    					map.put(title.get(j), cell);
    					map.put(title.get(j), cell);
    				}
    				list.add(map);
    			}
    		} catch (Exception e) {
    			log.error(e);
    		}
    		return list;
    	}
    
    	/**
    	 * 读取office2007以后(包括)的Excel文件(后缀为.xlsx)
    	 * @param is
    	 * @return
    	 */
    //	public static List<Map<String, Object>> getxlsx(InputStream is) {
    //		List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
    //		try {
    //			// 构造 XSSFWorkbook 对象,strPath 传入文件路径
    //			XSSFWorkbook xwb = new XSSFWorkbook(is);
    //			// 读取第一章表格内容
    //			XSSFSheet sheet = xwb.getSheetAt(0);
    //			// 定义 row、cell
    //			XSSFRow row;
    //			String cell;
    //			List<String> title=new ArrayList<String>();
    //			//输出表头信息
    //			row = sheet.getRow(0);
    //			if(row==null){
    //				return list;
    //			}
    //			for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
    //				// 通过 row.getCell(j).toString() 获取单元格内容,
    //				cell = row.getCell(j).toString();
    //				title.add(cell);
    //			}
    //			Map<String, Object> titlemap=new HashMap<String, Object>();
    //			titlemap.put("title", title);
    //			list.add(titlemap);
    //			
    //			// 循环输出表格中的内容
    //			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
    //				row = sheet.getRow(i);
    //				Map<String, Object> map=new HashMap<String, Object>();
    //				map.put("row", (i+1));
    //				for (int j = row.getFirstCellNum(); j < title.size(); j++) {
    //					// 通过 row.getCell(j).toString() 获取单元格内容,
    //					if(row.getCell(j)==null){
    //						continue;
    //					}
    //					row.getCell(j).setCellType(Cell.CELL_TYPE_STRING); 
    //					cell=row.getCell(j).toString();
    //					map.put(title.get(j), cell);
    //				}
    //				list.add(map);
    //			}
    //		} catch (Exception e) {
    //			e.printStackTrace();
    //		}
    //		return list;
    //	}
    
    
    
    
    	/**
    	 * 生成并下载Excel(带合并单元格)
    	 * @param res HTTP响应
    	 * @param list Excel内数据
    	 * @param titleArray 标题数组
    	 * @param fileName 文件名
    	 */
    	public static void createMergeExcel(HttpServletResponse res,List<List<String>> list,String[] titleArray,String fileName) {
    		try {
    			fileName = new String(fileName.getBytes(), "ISO-8859-1");
    		} catch (UnsupportedEncodingException e) {
    			log.error(e);
    		}
    		res.setCharacterEncoding("utf-8");
    		res.reset(); // 清空数据
    		res.setContentType("applicatoin/octet-stream;charset=utf-8");
    		res.addHeader("Content-Disposition", "attachment; filename=""+ fileName + "";");
    
    		// 创建Excel的工作书册 Workbook,对应到一个excel文档
    	    HSSFWorkbook wb = new HSSFWorkbook();
    	    // 创建Excel的工作sheet,对应到一个excel文档的tab
    	    HSSFSheet sheet = wb.createSheet("sheet1");
    
    	    HSSFCellStyle titleStyle = wb.createCellStyle();
    	    HSSFCellStyle contentStyle = wb.createCellStyle();
    
    	    // 居中
    	    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	    contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    
    	    //设置字体大小
    	    HSSFFont font = wb.createFont();
    	    font.setFontName("楷书");
    	    font.setFontHeightInPoints((short) 15);
    	    font.setBoldweight((short)3);
    	    titleStyle.setFont(font);
    
    	    HSSFFont contentfont = wb.createFont();
    	    contentfont.setFontHeightInPoints((short) 11);
    	    contentStyle.setFont(contentfont);
    
    	    //设置自动换行
    //	    titleStyle.setWrapText(true);
    //	    contentStyle.setWrapText(true);
    
    	    // 建立新行
    	    HSSFRow titlerow = sheet.createRow(0);
    	    titlerow.setHeightInPoints(30);
    	    for(int i=0;i<titleArray.length;i++){
    	    	HSSFCell cell = titlerow.createCell(i);
    	    	sheet.setColumnWidth(i, 50 * 256);
    	    	cell.setCellType(Cell.CELL_TYPE_STRING);
    	    	cell.setCellValue(titleArray[i]);
    	    	cell.setCellStyle(titleStyle);
            }
    
    	    int begin=1;
    	    int end=1;
    	    for (int i=0;i<list.size();i++) {
    	    	HSSFRow contentrow = sheet.createRow(begin);
    	    	contentrow.setHeightInPoints(20);
    	    	List<String> li=list.get(i);
    
    	    	//TODO 优化
    	    	for(int j=0;j<=3;j++){
    	    		int cnt=Integer.parseInt(li.get(3));
    	    		end=begin+(cnt-1);
    	    		//合并单元格(new Region(从第几行,(short)第几列,到第几行,(short)第几列))行列下标都从0开始
    	    		sheet.addMergedRegion(new Region(begin,(short)j,end,(short)j));
    	    		HSSFCell cell = contentrow.createCell(j);
    	    		cell.setCellType(Cell.CELL_TYPE_STRING);
    	    		String content=li.get(j);
    		    	cell.setCellValue(content);
    		    	cell.setCellStyle(contentStyle);
    	    	}
    	    	String[] u=li.get(4).split("
    ");
    	    	for(int j=0;j<u.length;j++){
    	    		String[] contentarr=u[j].split(",");
    	    		for(int z=0;z<contentarr.length;z++){
    	    			HSSFCell cell = contentrow.createCell(z+4);
    		    		cell.setCellType(Cell.CELL_TYPE_STRING);
    	    			String content=contentarr[z];
    	    			cell.setCellValue(content);
    			    	cell.setCellStyle(contentStyle);
    	    		}
    	    		contentrow = sheet.createRow(begin+j+1);
    	    		contentrow.setHeightInPoints(20);
    	    	}
    	    	begin=end+1;
    	    }
    	    try {
    			wb.write(res.getOutputStream());
    		} catch (IOException e) {
    			log.error(e);
    		}
    	}
    }
    
    
  • 相关阅读:
    STM32 Timer : Base Timer, Input Capture, PWM, Output Compare
    Bus Blaster v4 design overview
    JTAG Communications model
    ARM JTAG 信号 RTCK 应该如何处理?
    HappyJTAG2
    用SWD调试接口测量代码运行时间 ( SWO )
    ARM Cortex Design Considerations for Debug
    Technical Information ARM-related JTAG / SWD / SWV / ETM Target Interfaces
    Keil debugging techniques and alternative printf (SWO function)
    Serial Wire Viewer (SWV)
  • 原文地址:https://www.cnblogs.com/zongJianKun/p/10956223.html
Copyright © 2020-2023  润新知