Excel工具类

最新推荐文章于 2023-11-02 08:53:23 发布

Tg丶break

最新推荐文章于 2023-11-02 08:53:23 发布

阅读量147

点赞数

分类专栏： Java 文章标签： Excel导出 Java HttpResponse

本文链接：https://blog.csdn.net/m0_37589586/article/details/80308184

版权

Java 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

Excel导出工具类，BizException为自定义的RunTimeException业务异常，可按需求修改catch处理方法：

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.zm.business.common.DateUtil;
import com.zm.business.exception.BizException;

/**
 * Excel工具类
 * @author xiaowei 2018年5月14日 上午11:40:04
 */
public class ExcelUtil {

    private ExcelUtil() {

    }

    /**
     * 构建Excel并写入HttpResponse
     */
    public static void buildExcel(HttpServletResponse response, String fileName, String sheetName,
                                  List<String> head, List<List<Object>> list) {

        // 写入HttpResponse
        try {
            byte[] bytes = buildExcelToByte(sheetName, head, list);
            response.setContentType("application/x-excel");
            response.setContentLength(bytes.length);
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")));
            response.getOutputStream().write(bytes);
        } catch (Exception e) {
            throw new BizException("构建Excel失败！");
        }
    }

    /**
     * 构建Excel并写入HttpResponse
     */
    public static byte[] buildExcelToByte(String sheetName, List<String> head,
                                          List<List<Object>> list) {

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 设置表头
        if (head != null) {
            HSSFCell cell = null;
            for (int i = 0; i < head.size(); i++) {
                cell = row.createCell(i);
                cell.setCellValue(head.get(i));
                cell.setCellStyle(style);
            }
        }

        // 拼装excel内容
        List<Object> record = null;
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            record = list.get(i);
            for (int j = 0; j < record.size(); j++) {
                insertCell(row, j, record.get(j));
            }
        }

        // 设置列宽度自适应
        int rowNum = list.get(0).size();
        for (int i = 0; i < rowNum; i++)
            sheet.autoSizeColumn(i);

        try {
            wb.write(out);
            out.close();
        } catch (Exception e) {
            throw new BizException("构建Excel失败！");
        }
        return out.toByteArray();
    }

    /**
     * 插入数据
     */
    private static void insertCell(HSSFRow row, int i, Object obj) {

        String param;
        if (obj == null) {
            param = "";
        } else if (obj instanceof Date) {
            param = DateUtil.format((Date) obj, DateUtil.YYYYMMDD);
        } else {
            param = obj.toString();
        }
        row.createCell(i).setCellValue(param);
    }

    public static void main(String[] args) {

        String sheetName = "test";
        List<String> head = Arrays.asList("row1", "row2", "row3");
        List<Object> con = Arrays.asList(new Date(), "test", new BigDecimal("10.12"));
        List<Object> con1 = Arrays.asList(new Date(), "test1", new BigDecimal("10.22"));
        List<Object> con2 = Arrays.asList(new Date(), "test2", new BigDecimal("10.33"));
        List<List<Object>> list = Arrays.asList(con, con1, con2);
        try {
            byte[] bytes = buildExcelToByte(sheetName, head, list);
            File file = new File("d:/temp/test.xls");
            FileOutputStream outStream = new FileOutputStream(file);
            outStream.write(bytes);
            outStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}