普通方法
1、简单的表头与表体
1.1、 若是简单的表头与表体,不存在合并单元格的时候,可以使用简单的方法
ExcelViewObject 工具类:
package com.fh.util;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.servlet.view.document.AbstractXlsxView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @描述 Excel生成工具类
* @日期 2018年10月26日 上午10:03:55
* @author
*/
public class ExcelViewObject extends AbstractXlsxView {
private static String EXCEL_HIDE_SHEET_NAME = "hideSite";
private static Pattern pattern = Pattern.compile("[0-9].*");//一定记住加“.”
@Override
@SuppressWarnings("unchecked")
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
String filename = model.get("tableName") + "";
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "iso-8859-1") + ".xlsx");
XSSFSheet sheet = (XSSFSheet) workbook.createSheet("sheet1");
XSSFCell cell;
// 表头样式
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
XSSFCellStyle headerStyle = (XSSFCellStyle) workbook.createCellStyle(); // 标题样式
headerStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
// 设置字体
XSSFFont headerFont = (XSSFFont) workbook.createFont(); // 标题字体
headerFont.setBold(true);// 加粗
headerFont.setFontHeightInPoints((short) 11);// 设置字号
headerStyle.setFont(headerFont);
// 设置边框
headerStyle.setBorderBottom(BorderStyle.THIN);
headerStyle.setBorderLeft(BorderStyle.THIN);
headerStyle.setBorderTop(BorderStyle.THIN);
headerStyle.setBorderRight(BorderStyle.THIN);
// 设置宽度
int width = 26 * 256;
XSSFRow titleRow = sheet.createRow(0);
for (int i = 0; i < len; i++) {
// 设置标题宽度
width = 620 * titles.get(i).length();
sheet.setColumnWidth(i, width);
// 设置内容与样式
String title = titles.get(i);
cell = titleRow.createCell((short) i);
cell.setCellStyle(headerStyle);
cell.setCellValue(title);
}
if(model.get("options")!=null){
creatExcelHidePage(model,workbook);
}
// 内容样式
XSSFCellStyle contentStyle = (XSSFCellStyle) workbook.createCellStyle();
// 设置边框
contentStyle.setBorderBottom(BorderStyle.THIN);
contentStyle.setBorderLeft(BorderStyle.THIN);
contentStyle.setBorderTop(BorderStyle.THIN);
contentStyle.setBorderRight(BorderStyle.THIN);
// 水平居中
contentStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for (int i = 0; i < varCount; i++) {
PageData vpd = varList.get(i);
XSSFRow contentRow = sheet.createRow(i + 1);
for (int j = 0; j < len; j++) {
String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
cell = contentRow.createCell((short) j);
cell.setCellStyle(contentStyle);
cell.setCellValue(varstr);
}
}
}
private static void addValidationData(Workbook workbook, PageData pageData, int order, String[] nameList){
int sheetIndex = workbook.getNumberOfSheets();
if(sheetIndex>0){
for(int i=0;i<sheetIndex;i++){
Sheet sheet = workbook.getSheetAt(i);
if(!EXCEL_HIDE_SHEET_NAME.equals(sheet.getSheetName())){
getDataValidation(pageData,sheet);
}
}
}
}
private static String doHandle(final int num) {
String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
"W", "X", "Y", "Z" };
return charArr[num - 1];
}
//获取DataValidationConstraint
private static void getDataValidation(PageData pageData, Sheet sheet){
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
String hideSheetFlagName=pageData.getString("hideSheetFlagName");
// 指定关联那一列的数据
String cascadeRowStr=pageData.getString("cascadeRow");
int cascadeRow=Integer.parseInt(StringUtils.isNotEmpty(cascadeRowStr)?cascadeRowStr:"1");
String isCascade=pageData.getString("isCascade");
boolean cascadeFlag= "yes".equalsIgnoreCase(isCascade);
DataValidationConstraint provConstraint;
if(cascadeFlag){
//下拉选项添加验证数据
provConstraint=dvHelper.createFormulaListConstraint("INDIRECT(INDIRECT(\""+doHandle(cascadeRow)+"\"&ROW()))");
}else{
provConstraint=dvHelper.createFormulaListConstraint(hideSheetFlagName);
}
CellRangeAddressList rangeAddressList = getCellRangeAddressList(pageData,null);
DataValidation dataValidation = dvHelper.createValidation(provConstraint, rangeAddressList);
addValidation(pageData,dataValidation,sheet);
}
private static void addValidation(PageData pageData, DataValidation dataValidation, Sheet sheet){
String errorBox=pageData.getString("errorBox");
if(StringUtils.isNotEmpty(errorBox)){
dataValidation.createErrorBox("error", errorBox);
dataValidation.setShowErrorBox(true);
}
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);
}
//获取CellRangeAddressList
private static CellRangeAddressList getCellRangeAddressList(PageData pageData, String lastRowStr){
int firstCol=Integer.parseInt(pageData.getString("firstCol"));
int lastCol=Integer.parseInt(pageData.getString("lastCol"));
String firstRowStr=pageData.getString("firstRow");
int firstRow=Integer.parseInt(StringUtils.isNotEmpty(firstRowStr)?firstRowStr:"1");
int lastRow=Integer.parseInt(StringUtils.isNotEmpty(lastRowStr)?lastRowStr:"65536");
return new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
}
/**
* 隐藏一些信息--下拉框--列表数据同时创建下拉框
*/
private static void creatExcelHidePage(Map<String, Object> model,Workbook workbook){
Sheet hideInfoSheet = workbook.createSheet(EXCEL_HIDE_SHEET_NAME);//隐藏一些信息
//在隐藏页设置选择信息
// 准备下拉列表数据
List<PageData> options = CastUtil.castList(model.get("options"), PageData.class);
for (int i = 0; i < options.size(); i++) {
PageData pageData=options.get(i);
//第一行设置性别信息
Row row = hideInfoSheet.createRow(i);
String[] optionArr = (String[])pageData.get("list");
if(optionArr.length>0){
creatRow(row, optionArr);
String isCascade=pageData.getString("isCascade");
String hideSheetFlagName=pageData.getString("hideSheetFlagName");
boolean cascadeFlag= "yes".equalsIgnoreCase(isCascade);
int order=i+1;
if(cascadeFlag){
Matcher isNum = pattern.matcher(optionArr[0].charAt(0)+"");
hideSheetFlagName=optionArr[0];
if(isNum.matches()){
hideSheetFlagName="__"+optionArr[0];
}
}
try {
creatExcelNameList(workbook, hideSheetFlagName, order, optionArr.length, cascadeFlag);
addValidationData( workbook, pageData,order,optionArr);
}catch (Exception e){
System.out.println(e.getMessage());
}
}
}
//设置隐藏页标志
workbook.setSheetHidden(workbook.getSheetIndex(EXCEL_HIDE_SHEET_NAME), true);
}
/**
* 创建一列数据
* @param currentRow 当前行
* @param textList 数据
*/
private static void creatRow(Row currentRow,String[] textList){
if(textList!=null&&textList.length>0){
int i = 0;
for(String cellValue : textList){
Cell userNameLableCell = currentRow.createCell(i++);
Matcher isNum = pattern.matcher(cellValue.charAt(0)+"");
if(isNum.matches()){
cellValue="__"+cellValue;
}
userNameLableCell.setCellValue(cellValue);
}
}
}
/**
* 创建一个名称
* @param workbook 文档
*/
private static void creatExcelNameList(Workbook workbook,String nameCode,int order,int size,boolean cascadeFlag){
Name name;
name = workbook.createName();
name.setNameName(nameCode);
name.setRefersToFormula(EXCEL_HIDE_SHEET_NAME+"!"+getRange(order,size,cascadeFlag));
}
/**
* 名称数据行列计算表达式
* @param order 第几条
* @param size 大小
* @param cascadeFlag 是否级联
*/
private static String getRange(int order,int size,boolean cascadeFlag){
char start = 'A';
if(cascadeFlag){
start = 'B';
if(size<=25){
char end = (char)(start+size-1);
return "$"+start+"$"+order+":$"+end+"$"+order;
}else{
char endPrefix = 'A';
char endSuffix = 'A';
if((size-25)/26==0||size==51){//26-51之间,包括边界(仅两次字母表计算)
if((size-25)%26==0){//边界值
endSuffix = (char)('A'+25);
}else{
endSuffix = (char)('A'+(size-25)%26-1);
}
}else{//51以上
if((size-25)%26==0){
endSuffix = (char)('A'+25);
endPrefix = (char)(endPrefix + (size-25)/26 - 1);
}else{
endSuffix = (char)('A'+(size-25)%26-1);
endPrefix = (char)(endPrefix + (size-25)/26);
}
}
return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;
}
}else{
if(size<=26){
char end = (char)(start+size-1);
return "$"+start+"$"+order+":$"+end+"$"+order;
}else{
char endPrefix = 'A';
char endSuffix = 'A';
if(size%26==0){
endSuffix = (char)('A'+25);
if(size>52&&size/26>0){
endPrefix = (char)(endPrefix + size/26-2);
}
}else{
endSuffix = (char)('A'+size%26-1);
if(size>52&&size/26>0){
endPrefix = (char)(endPrefix + size/26-1);
}
}
return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;
}
}
}
}
controller层:
@RequestMapping(value = "/downExcelStuStatus")
public ModelAndView downExcelStuStatus() throws Exception {
ModelAndView modelAndView = this.getModelAndView();
PageData pageData = this.getPageData();
//获取基本信息数据
pageData.put("roleMaxLevel",this.getMaxRoleLevel());
pageData.put("stuGrade", URLDecoder.decode(pageData.get("stuGrade") + "", "UTF-8"));
pageData.put("stuName", URLDecoder.decode(pageData.get("stuName") + "", "UTF-8"));
pageData.put("stuCode", URLDecoder.decode(pageData.get("stuCode") + "", "UTF-8"));
pageData.put("stuCardCode", URLDecoder.decode(pageData.get("stuCardCode") + "", "UTF-8"));
pageData.put("classId", URLDecoder.decode(pageData.get("classId") + "", "UTF-8"));
Map<String,Object> dataMap = downExcelService.downExcelStuStatus(pageData);
ExcelViewObject erv = new ExcelViewObject();
modelAndView = new ModelAndView(erv,dataMap);
return modelAndView;
}
service层
业务层主实现表格头与表格体信息灌入
@Override
public Map<String, Object> downExcelStuStatus(PageData pageData) throws Exception {
// 1设置表格头
Map<String, Object> dataMap = new HashMap<String, Object>();
dataMap.put("titles", this.setStatusStuTitle());
// 2获取数据
List<PageData> stuList = new ArrayList<PageData>();
pageData.put("tchId", Jurisdiction.getUser().getDetailId());
String roleMaxLevel = pageData.get("roleMaxLevel") + "";
String userCode = Jurisdiction.getUserCode();
stuList = this.listStatusStuClassTch(pageData);
// 3设置表格体
dataMap.put("varList", this.setStatusStuBody(stuList));
// 设置名称
dataMap.put("tableName", "学生信息");
return dataMap;
}
表格头方法:
// [学籍信息]设置表头
private List<String> setStatusStuTitle() throws Exception {
List<String> titles = new ArrayList<String>();
titles.add(" 姓名 "); // 1
titles.add(" 性别 "); // 2
titles.add(" 出生日期 "); // 3
titles.add(" 身份证件类型 "); // 4
titles.add(" 身份证号 "); // 5
titles.add(" 姓名拼音 "); // 6
titles.add(" 班级名称 "); // 7
return titles;
}
表格体方法:
private List<PageData> setStatusStuBody(List<PageData> stuList) throws Exception {
List<PageData> varList = new ArrayList<PageData>();
for (int i = 0; i < stuList.size(); i++) {
PageData vpd = new PageData();
vpd.put("var1", stuList.get(i).get("STU_NAME"));
vpd.put("var2", stuList.get(i).get("STU_SEX"));
vpd.put("var3", stuList.get(i).get("STU_BIRTH"));
vpd.put("var4", stuList.get(i).get("STU_CARD_TYPE"));
vpd.put("var5", stuList.get(i).get("STU_CERT_CODE"));
vpd.put("var6", stuList.get(i).get("STU_NAME_PY"));
vpd.put("var7", stuList.get(i).get("STU_CLASS_NAME"));
varList.add(vpd);
}
return varList;
}
2、复杂表头
若是复杂的表格头,我们可以使用模板导出,需要往系统里添加EXCEL模板,用到JXI。
<jx:forEach items="${varList}" var="listItem" varStatus="dlStatus">
${listItem.ORG_NAME}
</jx:forEach>
其中varList为查到的数据集合,listItem为别名,具体每一项listItem.ORG_NAME 等等,名称不一样,根据自己的来设置
pom文件依赖
<!-- excel start -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!-- excel end -->
其中poi版本需要注意,我是用的是<poi.version>3.9</poi.version>
而我之前用的3.17不知道为什么会突然报错,报错为:找不到CellType方法,但是在/apache/poi/ss/usermodel/CellType中确实又存在,所以我使用的3.9版本
Controller层
@RequestMapping(value = "/detailExcel")
public ModelAndView detailExcel(HttpServletRequest request, HttpServletResponse response,Page page) throws Exception {
ModelAndView modelAndView = this.getModelAndView();
List<Role> roles = Jurisdiction.getUser().getRoles();
for (Role role:roles) {
String roleName = role.getRoleName();
//每次导入前,清除结束标记
request.getSession().removeAttribute("endflag");
PageData pageData = this.getPageData();
pageData.put("orderType", "sum");
// 获取基本信息数据
Map<String, Object> dataMap = downExcelService.downExcelStudentOffice(pageData);
//导出列表名
String fileName = "导出统计表";
//生成的导出文件
File destFile = File.createTempFile(fileName, ".xlsx");
//transformer转到Excel
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
String realPath = request.getServletContext().getRealPath("");
try {
String filePath = realPath + "/uploadFiles/template/excel/导出表.xlsx"; // 文件上传路径
System.out.println("---------------------- 导出统计表:realPath = " + realPath + " -----------------------------");
File file = new File(filePath);
InputStream resourceAsStream = new FileInputStream(file);
XLSTransformer xlsTransformer = new XLSTransformer();
Workbook workbook = xlsTransformer.transformXLS(resourceAsStream, dataMap);
OutputStream os = new BufferedOutputStream(new FileOutputStream(destFile));
workbook.write(os);
resourceAsStream.close();
os.flush();
os.close();
//将文件输入
InputStream inputStream = new FileInputStream(destFile);
// 设置response参数,可以打开下载页面
response.reset();
//设置响应文本格式
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
//将文件输出到页面
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(inputStream);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// 根据读取并写入
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
//使用完成后关闭流
try {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
} catch (IOException e) {
System.out.println(e.getMessage());
}
}
//设置结束标记
request.getSession().setAttribute("endflag", "1");
return modelAndView;
}
service层
@Override
public Map<String, Object> downExcelStudentOffice(PageData pageData) throws Exception {
Map<String, Object> dataMap = new HashMap<>();
List<PageData> huiZongList = this.regulateService.getClassListData(pageData);
dataMap.put("varList", huiZongList);
// 设置名称
dataMap.put("tableName", "统计信息");
return dataMap;
}
至于获取数据这里就不说了。。再更新