/**
* 该方法是将导出的文件转成64位编码,由前端解析成文件
**/
public void export(HttpServletResponse response,List<T> dataList ){
File file = new File("excel路径");
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
int rowNum = 0;//excel行数,从0开始
Row row = sheet.createRow(rowNum);//创建行
String[] billAcctNames = {"名称1","名称2"};//每列的名称
for (int i = 0; i < billAcctNames.length; i++) {
Cell cell = row.createCell(i);//创建每行的格子,从0开始
if(i % 2 == 0){
Font font = workbook.createFont();//设置字体类型
font.setBold(true);//是否加粗,true为是
font.setFontName("黑体");//导出字体类型
CellStyle style = workbook.createCellStyle();//设置单元格类型
style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());//设置背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色
style.setFont(font);
cell.setCellStyle(getCellStyle(workbook));
cell.setCellValue(billAcctNames[i]);
}
else {
writeRow(cell,object,new String[]{"code1","code2"});
int startRowNum = rowNum;
int startRowNum = rowNum + 1;
sheet.addMergedRegion(new CellRangeAddress(startRowNum, endRowNum, i, i));//合并单元格
rowNum++;
}
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
byte[] barray = bos.toByteArray();
Base64.Encoder encoder = Base64.getEncoder();
return encoder.encodeToString(barray);
}
/**
* 该方法是直接导出文件,并下载
**/
public void export(HttpServletResponse response,List<T> dataList ){
File file = new File("excel路径");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("文件名.xlsx", "UTF-8"));
response.setCharacterEncoding("UTF-8");
response.flushBuffer();
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
int rowNum = 0;//excel行数,从0开始
Row row = sheet.createRow(rowNum);//创建行
String[] billAcctNames = {"名称1","名称2"};//每列的名称
for (int i = 0; i < billAcctNames.length; i++) {
Cell cell = row.createCell(i);//创建每行的格子,从0开始
if(i % 2 == 0){
Font font = workbook.createFont();//设置字体类型
font.setBold(true);//是否加粗,true为是
font.setFontName("黑体");//导出字体类型
CellStyle style = workbook.createCellStyle();//设置单元格类型
style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());//设置背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色
style.setFont(font);
cell.setCellStyle(getCellStyle(workbook));
cell.setCellValue(billAcctNames[i]);
}
else {
writeRow(cell,object,new String[]{"code1","code2"});
int startRowNum = rowNum;
int startRowNum = rowNum + 1;
sheet.addMergedRegion(new CellRangeAddress(startRowNum, endRowNum, i, i));//合并单元格
rowNum++;
}
}
workbook.write(response.getOutputStream());
response.getOutputStream().close();
return "";
}
private void writeRow(Row row, Object data, String[] sortFiledList) {
Class<?> dataClass = data.getClass();
String filedName = null;
String type = null;
for (int i = 0; i < sortFiledList.length; i++) {
Cell cell = row.createCell(i);
try {
Field targetFiled = dataClass.getDeclaredField(sortFiledList[i]);
targetFiled.setAccessible(true);
type = targetFiled.getType().getName();
filedName = targetFiled.getName();
Object filedValue = targetFiled.get(data);
if ("java.lang.String".equals(type)) {
cell.setCellType(CellType.STRING);
cell.setCellValue(DictUtil.getEnum(dictMap, filedValue.toString(), filedName));
} else if ("java.lang.Integer".equals(type) || "int".equals(type)) {
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(Double.parseDouble(filedValue.toString()));
} else {
cell.setCellType(CellType.BLANK);
cell.setCellValue(filedValue.toString());
System.out.println(targetFiled + ":" + filedValue.toString());
}
} catch (NoSuchFieldException e) {
throw new RuntimeException("未在对象类型" + dataClass.getName() + "中找到字段" + filedName);
} catch (IllegalAccessException e) {
throw new RuntimeException("在对象类型" + dataClass.getName() + " 中对字段" + filedName + " 赋值有误 ,字段类型 " + type);
} catch (NullPointerException e) {
cell.setCellType(CellType.STRING);
cell.setCellValue("");
}
}
}
Excel导出功能
最新推荐文章于 2023-12-27 00:28:12 发布