package com.yqzx.common.util;
|
// @formatter:off
|
import cn.jiguang.common.utils.StringUtils;
|
import lombok.val;import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.util.CellRangeAddressList;
|
|
import javax.servlet.http.HttpServletResponse;import java.net.URLEncoder;import java.util.Date;import java.util.List;import java.util.Locale;
|
import java.util.Map;
|
|
/**
|
* .-~~~~~~~~~-._ _.-~~~~~~~~~-.
|
* __.' @Author ~. .~ 代码无Bug `.__
|
* .'// liu.q \./ (秘籍) \\`.
|
* .'// [916000612@qq.com] | 欲练神功 引刀自宫 \\`.
|
* .'// .-~"""""""~~~~-._ | _,-~~~~"""""""~-. \\`.
|
* .'//.-" 2019-07-17 `-. | .-' 16:54 "-.\\`.
|
* .'//______.============-.. \ | / ..-============.______\\`.
|
*.'______________________________\|/______________________________`.
|
*
|
* @Description :
|
*/
|
// @formatter:on
|
|
public class ExportExcel {
|
public static void exportExcel(String fileName, List<String> titles, List<List<Object>> rows, HttpServletResponse response) throws Exception {
|
|
// 声明一个工作薄
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
// 生成一个表格
|
HSSFSheet sheet = workbook.createSheet();
|
// 设置表格默认列宽度为15个字节
|
sheet.setDefaultColumnWidth((short) 18);
|
|
//设置标题
|
HSSFRow title = sheet.createRow(0);
|
for (int i = 0; i < titles.size(); i++) {
|
HSSFCell cell = title.createCell(i);
|
HSSFRichTextString text = new HSSFRichTextString(titles.get(i));
|
cell.setCellValue(text);
|
}
|
|
//设置数据
|
HSSFCellStyle cellStyle = workbook.createCellStyle();
|
for(int i=0;i<rows.size();i++){
|
HSSFRow row = sheet.createRow(i+1);
|
List<Object> clo = rows.get(i);
|
int index = 0;
|
for(Object o :clo){
|
HSSFCell cell = row.createCell(index);
|
if(o instanceof Date){
|
HSSFDataFormat format = workbook.createDataFormat();
|
cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd HH:mm:ss"));
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue((Date)o);
|
}else if(o instanceof Double){
|
HSSFDataFormat format = workbook.createDataFormat();
|
cellStyle.setDataFormat(format.getFormat("#,##0.00"));
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(Double.valueOf(o.toString()));
|
}else{
|
HSSFDataFormat format = workbook.createDataFormat();
|
cellStyle.setDataFormat(format.getFormat("@"));
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(o.toString());
|
}
|
index++;
|
|
}
|
}
|
// 告诉浏览器用什么软件可以打开此文件
|
response.setHeader("content-Type", "application/vnd.ms-excel");
|
|
//解决 苹果浏览器乱码问题
|
val ua = CommonUtil.getRequest().getHeader("User-Agent").toLowerCase(Locale.ENGLISH);
|
if (ua.indexOf("macintosh") > -1 && ua.indexOf("chrome") < 0) {
|
fileName = new String(fileName.getBytes(), "iso8859-1");
|
} else {
|
fileName = URLEncoder.encode(fileName, "utf-8");
|
}
|
|
// 下载文件的默认名称
|
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
|
response.flushBuffer();
|
workbook.write(response.getOutputStream());
|
}
|
public static void exportExcel(String fileName, List<String> titles, List<List<Object>> rows, Map<String,String> option, HttpServletResponse response) throws Exception {
|
// 声明一个工作薄
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
// 生成一个表格
|
HSSFSheet sheet = workbook.createSheet();
|
// 设置表格默认列宽度为15个字节
|
sheet.setDefaultColumnWidth((short) 18);
|
|
//设置标题
|
HSSFRow title = sheet.createRow(0);
|
for (int i = 0; i < titles.size(); i++) {
|
HSSFCell cell = title.createCell(i);
|
HSSFRichTextString text = new HSSFRichTextString(titles.get(i));
|
cell.setCellValue(text);
|
if(option!=null && !option.isEmpty()){
|
String op = option.get(titles.get(i));
|
if(StringUtils.isNotEmpty(op)){
|
// 设置第一列的1-10行为下拉列表
|
CellRangeAddressList regions = new CellRangeAddressList(1, rows.size(), i, i);
|
// 创建下拉列表数据
|
DVConstraint constraint = DVConstraint.createExplicitListConstraint(op.split(","));
|
// 绑定
|
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
|
sheet.addValidationData(dataValidation);
|
}
|
}
|
|
}
|
|
//设置数据
|
HSSFCellStyle cellStyle = workbook.createCellStyle();
|
for(int i=0;i<rows.size();i++){
|
HSSFRow row = sheet.createRow(i+1);
|
List<Object> clo = rows.get(i);
|
int index = 0;
|
for(Object o :clo){
|
HSSFCell cell = row.createCell(index);
|
if(o instanceof Date){
|
HSSFDataFormat format = workbook.createDataFormat();
|
cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd HH:mm:ss"));
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue((Date)o);
|
}else if(o instanceof Double){
|
HSSFDataFormat format = workbook.createDataFormat();
|
cellStyle.setDataFormat(format.getFormat("#,##0.00"));
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(Double.valueOf(o.toString()));
|
}else{
|
HSSFDataFormat format = workbook.createDataFormat();
|
cellStyle.setDataFormat(format.getFormat("@"));
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(o.toString());
|
}
|
index++;
|
|
}
|
}
|
// 告诉浏览器用什么软件可以打开此文件
|
response.setHeader("content-Type", "application/vnd.ms-excel");
|
|
//解决 苹果浏览器乱码问题
|
val ua = CommonUtil.getRequest().getHeader("User-Agent").toLowerCase(Locale.ENGLISH);
|
if (ua.indexOf("macintosh") > -1 && ua.indexOf("chrome") < 0) {
|
fileName = new String(fileName.getBytes(), "iso8859-1");
|
} else {
|
fileName = URLEncoder.encode(fileName, "utf-8");
|
}
|
|
// 下载文件的默认名称
|
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
|
response.flushBuffer();
|
workbook.write(response.getOutputStream());
|
}
|
|
|
/**
|
* @Description : Excel导出自适应宽度工具类
|
* @Author : wh
|
* @Date: 2021/10/20 - 11:43
|
*/
|
public static void setSizeColumn2(Sheet sheet, int size) {
|
for (int i = 0; i <= size; i++) {
|
//int orgWidth = sheet.getColumnWidth(i);
|
// sheet.autoSizeColumn(i, true);
|
sheet.setColumnWidth(i, 5000);
|
}
|
}
|
}
|
