java生成excel(.xlsx)

java POI 生成excel
excel分 .xls .xlsx 格式
xls 使用HSSFWorkbook
xlsx 使用SXSSFWorkbook

依赖(此依赖中包含了POI的依赖,也可自行去查看POI依赖)

        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>2.0.0</version>
        </dependency>

 String  filePath = "D://test.xlsx";

import com.jdbc.DataImportJdbc;
import com.mapping.InitFields;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.util.ArrayList;

public class FieldsMappingExpoort {
   
    private DataImportJdbc dataImportJdbc = new DataImportJdbc();
    public void process(String filePath) throws Exception {


        FileOutputStream out=new FileOutputStream(filePath);
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("FieldsMapping");


        //列宽
        sheet.setColumnWidth(0,15*256);
        sheet.setColumnWidth(1,30*256);
        sheet.setColumnWidth(2,15*256);
        sheet.setColumnWidth(3,15*256);


        //表头cell格式
        CellStyle FirstRowCellStyle = workbook.createCellStyle();
        FirstRowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        FirstRowCellStyle.setFont(font);

       //生成第一行表头
        ArrayList<String> fieldsMappingFirstRow = InitFields.FieldsMappingFirstRow;
        SXSSFRow firstRow = sheet.createRow(0);
        for (int i = 0; i < fieldsMappingFirstRow.size(); i++) {
            SXSSFCell cell = firstRow.createCell(i);
            cell.setCellStyle(FirstRowCellStyle);
            cell.setCellValue(fieldsMappingFirstRow.get(i));
        }


        //表头以外的cell格式
        CellStyle rowCellStyle = workbook.createCellStyle();
        rowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //查询所有的字段映射数据
        int num = 1;
        ResultSet resultSet = dataImportJdbc.getAllFieldsMappingData();
        while(resultSet.next()){
            ArrayList<String> list = new ArrayList<>();
            SXSSFRow row = sheet.createRow(num);
            list.add(resultSet.getString("data_source"));
            list.add(resultSet.getString("origin_column_name"));
            list.add(resultSet.getString("table_name"));
            list.add(resultSet.getString("column_name"));
            for (int i = 0; i <4 ; i++) {
                SXSSFCell cell= row.createCell(i);
                cell.setCellStyle(rowCellStyle);
                cell.setCellValue(list.get(i));
            }
            num++;
        }
        workbook.write(out);
        workbook.close();
        //清理临时文件
        workbook.dispose（）;
    }
}

如果有date格式的内容要输出时:

            DataFormat dataFormat = workbook.createDataFormat();
            short format = dataFormat.getFormat("yyyy/m/d");
            CellStyle dataCellStyle = workbook.createCellStyle();
            dataCellStyle.setDataFormat(format);
            //date格式是通过cellstyle去格式化
            cell.setCellStyle(dataCellStyle);
            cell.setCellValue(date);