eip_manage/src/main/java/com/yihu/hos/standard/util/ExcelUtil.java

package com.yihu.hos.standard.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.IndexedColors;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.UUID;

/**
 * Created by Administrator on 2016/6/24.
 */
public class ExcelUtil {

    public static void downFileByHttpResponse(HttpServletResponse response, List<ExcelModel> excelModels) throws Exception {
        response.setHeader("Content-disposition", "attachment; filename= adapt.xlsx");// 设定输出文件头
        response.setContentType("application/x-msdownload");// 定义输出类型
        OutputStream os = response.getOutputStream();// 取得输出流

        HSSFWorkbook xssfWorkbook = new HSSFWorkbook();

        HSSFCellStyle style=xssfWorkbook.createCellStyle();
        for (ExcelModel excelModel : excelModels) {
            HSSFSheet sheet = xssfWorkbook.createSheet(excelModel.getSheetName());
            //设置表头
            HSSFRow row = sheet.createRow(0);

            for (int i = 0; i < excelModel.getHead().size(); i++) {
                sheet.setColumnWidth(i, 5000);

                HSSFCell cell= row.createCell(i);

                style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());//設置背景色
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

                HSSFFont font = xssfWorkbook.createFont();
                font.setFontName("黑体");
                font.setFontHeightInPoints((short) 12);
                style.setFont(font);
                cell.setCellStyle(style);


                cell.setCellValue(excelModel.getHead().get(i));
            }

            for (int i = 0; i <excelModel.getContext().size(); i++) {
                List<String> rowData = excelModel.getContext().get(i);
                row = sheet.createRow(i + 1);
                for (int j = 0; j < rowData.size(); j++) {
                    HSSFCell cell= row.createCell(j);
                    cell.setCellValue(rowData.get(j));
                }
            }
        }
        //主体内容生成结束
        xssfWorkbook.write(os); // 写入文件
        xssfWorkbook.close();
        os.flush();
        os.close();
    }


    public static File saveAsFile(List<ExcelModel> excelModels,String path) throws Exception {
        File newPath=new File(path);
        newPath.mkdir();
        File file=new File(path+ UUID.randomUUID().toString().replaceAll("-", "")+".xls");
        OutputStream outputStream=new FileOutputStream(file);
        HSSFWorkbook xssfWorkbook = new HSSFWorkbook();

        HSSFCellStyle style=xssfWorkbook.createCellStyle();
        for (ExcelModel excelModel : excelModels) {
            HSSFSheet sheet = xssfWorkbook.createSheet(excelModel.getSheetName());
            //设置表头
            HSSFRow row = sheet.createRow(0);

            for (int i = 0; i < excelModel.getHead().size(); i++) {
                sheet.setColumnWidth(i, 5000);

                HSSFCell cell= row.createCell(i);

                style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());//設置背景色
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

                HSSFFont font = xssfWorkbook.createFont();
                font.setFontName("黑体");
                font.setFontHeightInPoints((short) 12);
                style.setFont(font);
                cell.setCellStyle(style);


                cell.setCellValue(excelModel.getHead().get(i));
            }

            for (int i = 0; i <excelModel.getContext().size(); i++) {
                List<String> rowData = excelModel.getContext().get(i);
                row = sheet.createRow(i + 1);
                for (int j = 0; j < rowData.size(); j++) {
                    HSSFCell cell= row.createCell(j);
                    cell.setCellValue(rowData.get(j));
                }
            }
        }
        //主体内容生成结束
         xssfWorkbook.write(outputStream); // 写入文件
         xssfWorkbook.close();
         outputStream.flush();
         outputStream.close();

        return file;
    }
}