yunsu/cloud-model/src/main/java/com/hssx/cloudmodel/util/ExcelUtil.java

package com.hssx.cloudmodel.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.net.URLEncoder;
import java.util.List;

public class ExcelUtil {
    /**
     * 简单Excel导出
     * @param response
     * @param title     标题
     * @param list      数据
     * @return
     */
    public static String exportGeneralExcelByTitleAndList(HttpServletResponse response, String title, List<List<String>> list,String downloadPath) {
        String result="系统提示：Excel文件导出成功！";
        String fileName= title+".xls";
        try {
            response.reset();
            response.setHeader("Content-disposition",
                "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));
            //设置文件头编码格式
            response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//设置类型
            response.setHeader("Cache-Control","no-cache");//设置头
            response.setDateHeader("Expires", 0);//设置日期头
            // 创建工作簿
            HSSFWorkbook workBook = new HSSFWorkbook();
            // 创建工作类
            HSSFSheet sheet = workBook.createSheet();
            sheet.setDefaultColumnWidth(16);
            //设置字体样式
            HSSFFont headFont = workBook.createFont();
            headFont.setBold(true);
            headFont.setFontHeightInPoints((short) 10);
            headFont.setFontName("黑体");

            HSSFFont titleFont = workBook.createFont();
            titleFont.setBold(true);
            titleFont.setFontHeightInPoints((short) 10);
            titleFont.setFontName("黑体");

            HSSFFont font = workBook.createFont();
            font.setFontHeightInPoints((short) 10);
            font.setFontName("宋体");

            //设置单元格样式
            CellStyle headStyle = workBook.createCellStyle();
            headStyle.setFont(headFont);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
            headStyle.setWrapText(true);
            headStyle.setBorderBottom(BorderStyle.THIN); //下边框
            headStyle.setBorderLeft(BorderStyle.THIN);//左边框
            headStyle.setBorderTop(BorderStyle.THIN);//上边框
            headStyle.setBorderRight(BorderStyle.THIN);//右边框

            String color = "c0c0c0";    //此处得到的color为16进制的字符串
            //转为RGB码
            int r = Integer.parseInt((color.substring(0,2)),16);   //转为16进制
            int g = Integer.parseInt((color.substring(2,4)),16);
            int b = Integer.parseInt((color.substring(4,6)),16);

            //自定义cell颜色
            HSSFPalette palette = workBook.getCustomPalette();
            //这里的9是索引
            palette.setColorAtIndex((short)9, (byte) r, (byte) g, (byte) b);

            CellStyle titleStyle = workBook.createCellStyle();
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);  //填充单元格
            titleStyle.setFillForegroundColor((short)9);    //填色
            titleStyle.setWrapText(true);
            titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
            titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
            titleStyle.setBorderTop(BorderStyle.THIN);//上边框
            titleStyle.setBorderRight(BorderStyle.THIN);//右边框

            CellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
            cellStyle.setWrapText(true);
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框

            if(list.size() > 0) {
                //标题（如果需要在EXCEL内容最上面加标题，请打开下面的注释，修改start）
                /*
                HSSFRow titleRow = sheet.createRow(0);
                titleRow.setHeightInPoints(30);
                HSSFCell titleCell = titleRow.createCell(0);
                titleCell.setCellStyle(headStyle);
                titleCell.setCellValue(title);
                //合并单元格
                CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,0, list.get(0).size() - 1);
                //加入合并单元格对象
                sheet.addMergedRegion(cellRangeAddress);
                //使用RegionUtil类为合并后的单元格添加边框
			    RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet); // 下边框
                RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet); // 左边框
                RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet); // 有边框
                RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet); // 上边框
                */
                int start = 0;
                for(List<String> rowList : list) {
                    HSSFRow row = sheet.createRow(start);
                    row.setHeightInPoints(24);
                    for(int i = 0; i < rowList.size(); i++) {
                        HSSFCell cell = row.createCell(i);
                        if(start == 0) {
                            cell.setCellStyle(titleStyle);
                        }else {
                            cell.setCellStyle(cellStyle);
                        }
                        cell.setCellValue(rowList.get(i));
                    }
                    start++;
                }
            }
//            OutputStream os = response.getOutputStream();//在线下载
            File dir = null;
            dir = new File(downloadPath);
            // D://cloud/upload 文件上传后所存储的位置,部署到服务器上时配置服务器地址即可
            if (!dir.exists()) {
                dir.mkdirs();
            }
            FileOutputStream os = new FileOutputStream(downloadPath+fileName);//保存到本地
            workBook.write(os);
            os.flush();
            os.close();
        }catch(Exception e) {
            System.out.println(result);
            e.printStackTrace();
        }
        return "/upload/"+fileName;
    }
}