package com.example.demo.job.util;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class BeanUtils {
//******************导出*********************
public static String generateExcel(List<Map<String,Object>> list, String title, String key){
try {
//创建工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
//创建表单
XSSFSheet sheet = genSheet(workbook,"testExcel");
//创建表单样式
XSSFCellStyle titleStyle = genTitleStyle(workbook);//创建标题样式
XSSFCellStyle contextStyle = genContextStyle(workbook);//创建文本样式
//创建Excel
genExcel(sheet,titleStyle,contextStyle,list,title,key);
//将生成的Excel文件保存到本地
FileOutputStream out = new FileOutputStream(new File("D://"+new Date().getTime()+"test.xlsx"));
//将工作薄写入文件输出流中
workbook.write(out);
//文本文件输出流,释放资源
out.close();
return "200";
} catch (IOException e) {
e.printStackTrace();
return "501";
}
}
public static void genExcel(XSSFSheet sheet,XSSFCellStyle titleStyle,XSSFCellStyle contextStyle
,List<Map<String,Object>> list,String title,String key) {
//根据Excel列名长度,指定列名宽度 Excel总共10列
for (int i = 0; i < list.size(); i++) {
if (i == 0) {
sheet.setColumnWidth(i, 1500);
} else if (i == 2 || i == 3 || i == 4) {
sheet.setColumnWidth(i, 4000);
} else if (i == 9) {
sheet.setColumnWidth(i, 3000);
} else {
sheet.setColumnWidth(i, 2000);
}
}
//设置标题位置
sheet.addMergedRegion(new CellRangeAddress(
0, //first row
0, //last row
0, //first column
list.get(0).size()-1 //last column(标题占几列的位置)
));
XSSFRow row = sheet.createRow(0);//创建第一行,为标题,index从0开始
XSSFCell cell;
cell = row.createCell(0);//创建一列
cell.setCellValue("方案详情");//标题
cell.setCellStyle(titleStyle);//设置标题样式
String[] titles = title.split(",");
row = sheet.createRow(1);//创建第二行(列名展示)
for(int i=0;i<titles.length;i++){
cell = row.createCell(i);//创建第二行第一列
cell.setCellValue(titles[i]);//第二行第一列内容
cell.setCellStyle(contextStyle);//设置样式
}
String[] keys = key.split(",");
//从数据库取数据填充到Excel,这步省略,添加模拟数据
for(int i = 0 ; i<list.size();i++){//
row=sheet.createRow(i+2);//创建第三行,上边已经创建了两行
for(int j=0;j<titles.length;j++){
cell = row.createCell(j);//创建第二行第一列
cell.setCellValue(list.get(i).get(keys[j]).toString());//第二行第一列内容
cell.setCellStyle(contextStyle);//设置样式
}
}
}
//设置表单,并生成表单
public static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName){
//生成表单
XSSFSheet sheet = workbook.createSheet(sheetName);
//设置表单文本居中
sheet.setHorizontallyCenter(true);
sheet.setFitToPage(false);
//打印时在底部右边显示文本页信息
Footer footer = sheet.getFooter();
footer.setRight( "Page " + HeaderFooter.numPages()+ " Of "+ HeaderFooter.page());
//打印时在头部右边显示Excel创建日期信息
Header header = sheet.getHeader();
header.setRight("Create Date " + HeaderFooter.date() + " " + HeaderFooter.time());
//设置打印方式
XSSFPrintSetup ps = sheet.getPrintSetup();
ps.setLandscape(true); // true:横向打印,false:竖向打印 ,因为列数较多,推荐在打印时横向打印
ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //打印尺寸大小设置为A4纸大小
return sheet;
}
//创建文本样式
public static XSSFCellStyle genContextStyle(XSSFWorkbook workbook){
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//文本水平居中显示
style.setVerticalAlignment(VerticalAlignment.CENTER);//文本竖直居中显示
style.setWrapText(true);//文本自动换行
//生成Excel表单,需要给文本添加边框样式和颜色
/*
CellStyle.BORDER_DOUBLE 双边线
CellStyle.BORDER_THIN 细边线
CellStyle.BORDER_MEDIUM 中等边线
CellStyle.BORDER_DASHED 虚线边线
CellStyle.BORDER_HAIR 小圆点虚线边线
CellStyle.BORDER_THICK 粗边线
*/
style.setBorderBottom(BorderStyle.THIN);//设置文本边框
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
//set border color
style.setTopBorderColor(new XSSFColor(java.awt.Color.BLACK));//设置文本边框颜色
style.setBottomBorderColor(new XSSFColor(java.awt.Color.BLACK));
style.setLeftBorderColor(new XSSFColor(java.awt.Color.BLACK));
style.setRightBorderColor(new XSSFColor(java.awt.Color.BLACK));
return style;
}
//生成标题样式
public static XSSFCellStyle genTitleStyle(XSSFWorkbook workbook){
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setWrapText(true);
//标题居中,没有边框,所以这里没有设置边框,设置标题文字样式
XSSFFont titleFont = workbook.createFont();
titleFont.setBold(true);//加粗
titleFont.setFontHeight((short)15);//文字尺寸
titleFont.setFontHeightInPoints((short)15);
style.setFont(titleFont);
return style;
}
//*********************************导出结束***********************************
}
//调用
package com.example.demo.job.controller;
import com.alibaba.fastjson.JSONObject;
import com.example.demo.entity.ReceivePageParams;
import com.example.demo.job.service.PlanService;
import com.example.demo.job.util.BeanUtils;
import com.example.demo.mapper.CellinfoMapper;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletResponse;
import javax.swing.filechooser.FileSystemView;
import java.awt.*;
import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
*
*/
@Controller
@RequestMapping("plan")
public class PlanController {
/**
* 导出
*/
@RequestMapping(value = "exportPlanExcel", method = RequestMethod.GET)
@ResponseBody
public String exportPlanExcel(){
Map<String,Object> map = new HashMap<>();
//准备数据
List<Map<String,Object>> list=cellinfoMapper.selectDataByPage();//这个是自己数据库查询回来的数据.根据自己情况查询即可
String title = "城市,数量"; //
String key = "city,cellNum"; //
String code = BeanUtils.generateExcel(list,title,key);
if("200".equals(code)){
map.put("code",200);
map.put("msg","导出成功");
return JSONObject.toJSONString(map);
}else{
map.put("msg","系统错误,请联系管理员");
map.put("code",501);
return JSONObject.toJSONString(map);
}
}
}
//所需jar
<!-- 导出需要用的两个包-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>