• poi导出excel


    Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。 

    此例子用的是poi3.9的版本,请在apache官网自行下载。

    一、相关api介绍

    //生成Workbook  (.xls)
    HSSFWorkbook wb = new HSSFWorkbook();  
    //生成Workbook  (.xlsx)
    XSSFWorkbook wb = new XSSFWorkbook();  
    
    //添加Worksheet(不添加sheet时生成的xls文件打开时会报错)  
    @SuppressWarnings("unused")  
    Sheet sheet1 = wb.createSheet();
      
    //保存为Excel文件  
    FileOutputStream out = null;  
    try {  
        out = new FileOutputStream("c:\text.xls");  
        wb.write(out);        
    } catch (IOException e) {  
        System.out.println(e.toString());  
    } finally {  
        try {  
            out.close();  
        } catch (IOException e) {  
            System.out.println(e.toString());  
        }  
    } 
    
    //读取Workbook
    FileInputStream in = null;  
    Workbook wb = null;  
    try {  
        in = new FileInputStream(TEST_WORKBOOK_NAME);  
        wb = WorkbookFactory.create(in);  
    } catch (IOException e) {  
        System.out.println(e.toString());  
    } catch (InvalidFormatException e) {  
        System.out.println(e.toString());  
    } finally {  
        try {  
            in.close();  
        } catch (IOException e) {  
            System.out.println(e.toString());  
        }  
    }
       
    //行操作
    //创建
    Row row1 = wb.getSheet("sheet1").createRow(1); 
    //删除
    wb.getSheet("sheet1").removeRow(row1);  
    //移动行(把第2行和第3行移到第6行之后)  
    wb.getSheet("sheetname5").shiftRows(1, 2, 6);  
    //设置默认行高  
    wb.getSheet("sheetname6").setDefaultRowHeight((short)100);  
    //设置行高  
    wb.getSheet("sheetname6").getRow(2).setHeight((short)(100 * 20));  
    //设置默认列宽  
    wb.getSheet("sheetname7").setDefaultColumnWidth(12);  
    //设置列宽  
    wb.getSheet("sheetname7").setColumnWidth(0, 5 * 256);  
    
    //单元格操作
    //追加行  
    for (int i = 0; i < 10; i++) {  
        Row row = wb.getSheet("sheetname10").createRow(i);  
        for (int j = 0; j < 10; j++) {  
            //添加单元格  
            Cell cell = row.createCell(j);  
            cell.setCellValue(i + 1);  
        }  
          
        //删除单元格  
        row.removeCell(row.getCell(5));  
    }          
    //合并单元格  
    //CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)  
    wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(1, 4, 2, 3));  
    
    //设置单元格值
    for (List<Object> dataRow : objects) {
        row = sheet.createRow(lastRowIndex);
        lastRowIndex++;
        for (int j = 0; j < dataRow.size(); j++) {
            Cell contentCell = row.createCell(j);
            Object dataObject = dataRow.get(j);
            if (dataObject != null) {
                if (dataObject instanceof Integer) {
                    contentCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                    contentCell.setCellStyle(contentIntegerStyle);
                    contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
                } else if (dataObject instanceof Double) {
                    contentCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                    contentCell.setCellStyle(contentDoubleStyle);
                    contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
                } else if (dataObject instanceof Long && dataObject.toString().length() == 13) {
                    contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    contentCell.setCellStyle(contentStyle);
                    contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString()))));
                } else if (dataObject instanceof Date) {
                    contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    contentCell.setCellStyle(contentStyle);
                    contentCell.setCellValue(getCnDate((Date) dataObject));
                } else {
                    contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    contentCell.setCellStyle(contentStyle);
                    contentCell.setCellValue(dataObject.toString());
                }
            } else {
                contentCell.setCellStyle(contentStyle);
                // 设置单元格内容为字符型
                contentCell.setCellValue("");
            }
        }
    }
    
    //设置标题头与标题列名
    // 合并单元格
    sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, columnNames.size() - 1));
    // 产生表格标题行
    Row rowMerged = sheet.createRow(lastRowIndex);
    lastRowIndex++;
    Cell mergedCell = rowMerged.createCell(0);
    mergedCell.setCellStyle(headStyle);
    mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
    // 产生表格表头列标题行
    Row row = sheet.createRow(lastRowIndex);
    lastRowIndex++;
    for (int i = 0; i < columnNames.size(); i++) {
        Cell cell = row.createCell(i);
        cell.setCellStyle(headStyle);
        RichTextString text = new XSSFRichTextString(columnNames.get(i));
        cell.setCellValue(text);
    }

    二、实例代码ExcelUtils

    package com.skin.webcat.util;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.util.CellRangeAddress;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.usermodel.RichTextString;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFFont;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class ExportExcelUtil2 {
        
        private static final int DEFAULT_COLUMN_SIZE = 30;
    
        /**
         * 导出字符串数据
         *
         * @param file        文件名
         * @param columnNames 表头
         * @param sheetTitle  sheet页Title
         * @param append      是否追加写文件
         * @return file
         * @throws ReportInternalException
         */
        public static void exportExcelTitle(File file,String sheetName, List<String> columnNames,
                                             String sheetTitle, boolean append,List<List<Object>> objects) throws  IOException {
            // 声明一个工作薄
            Workbook workBook = new XSSFWorkbook();
           if (file.exists() && append) {
                workBook = new XSSFWorkbook(new FileInputStream(file));
            } else {
                workBook = new XSSFWorkbook();
            }
            Map<String, CellStyle> cellStyleMap = styleMap(workBook);
            // 表头样式
            CellStyle headStyle = cellStyleMap.get("head");
            // 生成一个表格
            Sheet sheet = workBook.getSheet(sheetName);
            if (sheet == null) {
                sheet = workBook.createSheet(sheetName);
            }
            //最新Excel列索引,从0开始
            int lastRowIndex = sheet.getLastRowNum();
            if (lastRowIndex > 0) {
                lastRowIndex++;
            }
            // 设置表格默认列宽度
            sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, columnNames.size() - 1));
            // 产生表格标题行
             Row rowMerged = sheet.createRow(lastRowIndex);
            lastRowIndex++;
            Cell mergedCell = rowMerged.createCell(0);
            mergedCell.setCellStyle(headStyle);
            mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
            // 产生表格表头列标题行
            Row row1 = sheet.createRow(lastRowIndex);
            for (int i = 0; i < columnNames.size(); i++) {
                Cell cell = row1.createCell(i);
                cell.setCellStyle(headStyle);
                RichTextString text = new XSSFRichTextString(columnNames.get(i));
                cell.setCellValue(text);
            }
            
            
            CellStyle contentStyle = cellStyleMap.get("content");
            //正文整数样式
            CellStyle contentIntegerStyle = cellStyleMap.get("integer");
            //正文带小数整数样式
            CellStyle contentDoubleStyle = cellStyleMap.get("double");
            // 生成一个表格
            if (sheet == null) {
                sheet = workBook.createSheet(sheetName);
            }
            //最新Excel列索引,从0开始
           if (lastRowIndex > 0) {
                lastRowIndex++;
            }
            // 设置表格默认列宽度
            sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
            // 遍历集合数据,产生数据行,前两行为标题行与表头行
            for (List<Object> dataRow : objects) {
                Row row = sheet.createRow(lastRowIndex);
                lastRowIndex++;
                for (int j = 0; j < dataRow.size(); j++) {
                    Cell contentCell = row.createCell(j);
                    Object dataObject = dataRow.get(j);
                    if (dataObject != null) {
                        if (dataObject instanceof Integer) {
                            contentCell.setCellStyle(contentIntegerStyle);
                            contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
                        } else if (dataObject instanceof Double) {
                            contentCell.setCellStyle(contentDoubleStyle);
                            contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
                        } else if (dataObject instanceof Long && dataObject.toString().length() == 13) {
                            contentCell.setCellStyle(contentStyle);
                            contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString()))));
                        } else if (dataObject instanceof Date) {
                            contentCell.setCellStyle(contentStyle);
                            contentCell.setCellValue(getCnDate((Date) dataObject));
                        } else {
                            contentCell.setCellStyle(contentStyle);
                            contentCell.setCellValue(dataObject.toString());
                        }
                    } else {
                        contentCell.setCellStyle(contentStyle);
                        // 设置单元格内容为字符型
                        contentCell.setCellValue("");
                    }
                }
            }
            
            try {
                OutputStream ops = new FileOutputStream(file);
                workBook.write(ops);
                ops.flush();
                ops.close();
            } catch (IOException e) {
                throw new IOException(e);
            }
    //        return file;
        }
        /**
         * 日期转化为字符串,格式为yyyy-MM-dd HH:mm:ss
         */
        private static String getCnDate(Date date) {
            String format = "yyyy-MM-dd HH:mm:ss";
            SimpleDateFormat sdf = new SimpleDateFormat(format);
            return sdf.format(date);
        }
        
        /**
         * 创建单元格表头样式
         *
         * @param workbook 工作薄
         */
        private static CellStyle createCellHeadStyle(Workbook workbook) {
            CellStyle style = workbook.createCellStyle();
            // 设置边框样式
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);
            //设置对齐样式
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            // 生成字体
            Font font = workbook.createFont();
            // 表头样式
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            // 把字体应用到当前的样式
            style.setFont(font);
            return style;
        }
    
        /**
         * 创建单元格正文样式
         *
         * @param workbook 工作薄
         */
        private static CellStyle createCellContentStyle(Workbook workbook) {
            CellStyle style = workbook.createCellStyle();
            // 设置边框样式
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);
            //设置对齐样式
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            // 生成字体
            Font font = workbook.createFont();
            // 正文样式
            style.setFillPattern(XSSFCellStyle.NO_FILL);
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            // 把字体应用到当前的样式
            style.setFont(font);
            return style;
        }
    
        /**
         * 单元格样式(Integer)列表
         */
        private static CellStyle createCellContent4IntegerStyle(Workbook workbook) {
            CellStyle style = workbook.createCellStyle();
            // 设置边框样式
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);
            //设置对齐样式
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            // 生成字体
            Font font = workbook.createFont();
            // 正文样式
            style.setFillPattern(XSSFCellStyle.NO_FILL);
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            // 把字体应用到当前的样式
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));//数据格式只显示整数
            return style;
        }
    
        /**
         * 单元格样式(Double)列表
         */
        private static CellStyle createCellContent4DoubleStyle(Workbook workbook) {
            CellStyle style = workbook.createCellStyle();
            // 设置边框样式
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);
            //设置对齐样式
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            // 生成字体
            Font font = workbook.createFont();
            // 正文样式
            style.setFillPattern(XSSFCellStyle.NO_FILL);
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            // 把字体应用到当前的样式
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
            return style;
        }
    
        
        /**
         * 单元格样式列表
         */
        private static Map<String, CellStyle> styleMap(Workbook workbook) {
            Map<String, CellStyle> styleMap = new LinkedHashMap<>();
            styleMap.put("head", createCellHeadStyle(workbook));
            styleMap.put("content", createCellContentStyle(workbook));
            styleMap.put("integer", createCellContent4IntegerStyle(workbook));
            styleMap.put("double", createCellContent4DoubleStyle(workbook));
            return styleMap;
        }
        
        public static void main(String[] args) {
            File file = new File("d:/a.xlsx");
            List<List<Object>> objects = new ArrayList<List<Object>>();
            List<String> listn = new ArrayList<String>();
            listn.add("a");
            listn.add("b");
            listn.add("c");
            listn.add("d");
            for (int i = 0; i < 1000; i++) {
                List<Object> os = new ArrayList<Object>();
                os.add("a");
                os.add(new Date());
                os.add(1);
                os.add(1.33);
                objects.add(os);
            }
            try {
                exportExcelTitle(file, "sdf", listn, "213", false, objects);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

      

  • 相关阅读:
    LeeCode-Spiral Matrix II
    HDU1281(二分图最大匹配,棋盘建图,找关键点)
    HDU1083(二分图最大匹配vector实现)
    HDU2444(判断是否为二分图,求最大匹配)
    HDU1166(线段树单点更新区间查询)
    HDU1045(二分图经典建模)
    POJ1220(大数进制转换)
    POJ3466(01背包变形)
    POJ3180(有向图强连通分量结点数>=2的个数)
    POJ1236 (强连通分量缩点求入度为0和出度为0的分量个数)
  • 原文地址:https://www.cnblogs.com/TimeSay/p/9140057.html
Copyright © 2020-2023  润新知