Excel导入导出工具类

• Excel导出工具类

如果数据条数大于单张sheet的最大条数，会自动创建第二张sheet表单，如果有不懂的可以留言问我。

package com.itl.framework.utils;

import com.itl.pojo.tms.TmDemandExportDto;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.List;

/**
 * 导出Excel工具类
 *
 * @author xiaoshuaishuai
 * @date 2018/12/27 17:56
 */
public class ExcelUtils {

    private static final int MAX = 65535;

    public static void exportNoTitleExcel(HttpServletResponse response, List<TmDemandExportDto> list,
                                          String[] fields, String fileName) throws Exception {
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook excelDocument = new HSSFWorkbook();
        //sheet表单个数
        int sheetNumber = 1;
        if (list != null) {
            //数据总条数
            int total = list.size();
            if (total > MAX) {
                if ((total % MAX) > 0) {
                    sheetNumber = (total / MAX) + 1;
                }
            }
            for (int i = 0; i < sheetNumber; i++) {
                getSheet(list, fields, total, excelDocument, i);
            }
        }
        exportExcel(response, excelDocument, fileName);
    }

    /**
     * 输出Excel文档
     *
     * @param response      响应
     * @param excelDocument Excel文档对象
     * @throws Exception 异常信息
     */
    private static void exportExcel(HttpServletResponse response, HSSFWorkbook excelDocument, String fileName) throws Exception {
        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        fileName = new String(("attachment;filename=" + fileName + ".xls").getBytes("GBK"), "ISO_8859_1");
        response.setHeader("Content-disposition", fileName);
        excelDocument.write(output);
        output.close();
    }

    /**
     * 获取单张sheet表单
     *
     * @param list          需要导出的数据集合
     * @param fields        导出的字段
     * @param total         导出数据的总条数
     * @param excelDocument excel文档对象
     * @param i             第几张表单
     * @throws Exception 异常信息
     */
    private static void getSheet(List<TmDemandExportDto> list, String[] fields, int total, HSSFWorkbook excelDocument, int i) throws Exception {
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = excelDocument.createSheet("sheet" + i);
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow titleRow = sheet.createRow(0);
        for (int j = 0; j < fields.length; j++) {
            //创建单元格 excel的单元格，参数为列索引，可以是0～255之间的任何一个
            HSSFCell cell = titleRow.createCell(j);
            cell.setCellValue(fields[j]);
        }
        for (int j = 0; j < total; j++) {
            TmDemandExportDto record = list.get(j);
            //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row = sheet.createRow(j + 1);
            getCellContent(fields, record, row);
        }
    }

    /**
     * 获取单元格内容
     *
     * @param fields 导出的字段
     * @param record 填充的行对象
     * @param row    行对象
     * @throws Exception 异常信息
     */
    private static void getCellContent(String[] fields, TmDemandExportDto record, HSSFRow row) throws Exception {
        for (int k = 0; k < fields.length; k++) {
            HSSFCell contentCell = row.createCell(k);
            switch (k) {
                case 0:
                    contentCell.setCellValue(record.getDemandStatus());
                    continue;
                case 1:
                    contentCell.setCellValue(record.getDemandNo());
                    continue;
                case 2:
                    contentCell.setCellValue(record.getConsignerName());
                    continue;
                case 3:
                    contentCell.setCellValue(record.getWarehouseName());
                    continue;
                case 4:
                    contentCell.setCellValue(record.getCreateTime());
                    continue;
                case 5:
                    contentCell.setCellValue(record.getCarrierName());
                    continue;
                case 6:
                    contentCell.setCellValue(record.getConfirmTime());
                    continue;
                case 7:
                    contentCell.setCellValue(record.getEstimateNumber());
                    continue;
                case 8:
                    contentCell.setCellValue(record.getEstimateVolume());
                    continue;
                case 9:
                    contentCell.setCellValue(record.getEstimateWeight());
                    continue;
                case 10:
                    contentCell.setCellValue(record.getConsignerPlaceName());
                    continue;
                case 11:
                    contentCell.setCellValue(record.getConsigneePlaceName());
                    continue;
                case 12:
                    contentCell.setCellValue(record.getConsigneeAddress());
                    continue;
                case 13:
                    contentCell.setCellValue(record.getAccountsPrice());
                    continue;
                case 14:
                    contentCell.setCellValue(record.getFreeType());
                    continue;
                case 15:
                    BigDecimal estimateValue = record.getEstimateValue();
                    if(estimateValue != null){
                        contentCell.setCellValue(record.getEstimateValue().toString());
                    }else {
                        contentCell.setCellValue("");
                    }
                    continue;
                case 16:
                    contentCell.setCellValue(record.getProjectName());
                    continue;
                default:
                    break;
            }
        }
    }
}