- 前段时间,做一个项目,其中有一个导出功能,需要将查询出来的数据导出 excel 表格,给出的模板如下:
- 导出excel不难,这里难在如何取合并单元格,并且第一列和第七列,第二列和第六列是相同的合并方式。我想写一个共通的方法类,增加代码的可复用性。
- 下面是共通的Java类,直接贴出代码。
package com.fable.insightview.app.assess.common.util;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
/**
* 导出Excel公共方法
*
* @author zhaods
*
*/
public class ExportExcel {
//显示的导出表的标题
private String title;
//导出表的列名
private String[] rowName ;
//数据
private List<Object[]> dataList = new ArrayList<Object[]>();
//其他参数
private Map<String,Object> params = new HashMap<String, Object>();
//构造方法,传入要导出的数据
public ExportExcel(String title,String[] rowName,List<Object[]> dataList,Map<String,Object> params){
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
this.params = params;
}
/**
* 导出数据
* @param response
* @throws Exception
*/
public void export(HttpServletResponse response) throws Exception{
try{
// 创建工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作表
HSSFSheet sheet = workbook.createSheet(title);
// 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);
//获取列头样式对象
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
//单元格样式对象
HSSFCellStyle style = this.getStyle(workbook);
HSSFCell currentCell = null;
//设置标题
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
// 定义所需列数
int columnNum = rowName.length;
// 在索引2的位置创建行(最顶端的行开始的第二行)
HSSFRow rowRowName = sheet.createRow(2);
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
//创建列头对应个数的单元格
HSSFCell cellRowName = rowRowName.createCell(n);
//设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
//设置列头单元格的值
cellRowName.setCellValue(text);
//设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
//遍历每个对象
Object[] obj = dataList.get(i);
//创建所需的行数
HSSFRow row = sheet.createRow(i+3);
for(int j=0; j<obj.length; j++){
//设置单元格的数据类型
HSSFCell cell = null;
if(j == 0){
cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(i+1);
}else{
if(!"".equals(obj[j]) && obj[j] != null){
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
//设置单元格的值
cell.setCellValue(obj[j].toString());
}else {
cell = row.createCell(j,HSSFCell.CELL_TYPE_BLANK);
cell.setCellValue("");
}
}
//设置单元格样式
cell.setCellStyle(style);
}
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (null != currentRow.getCell(colNum)) {
currentCell = currentRow.getCell(colNum);
if(null != currentCell){
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
if(null != currentCell.getStringCellValue()){
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
//合并第一考核项,合并第一列和第七列,从第三行开始。
mergeCell(sheet, 1, 3, sheet.getLastRowNum(), workbook,6);
//合并第二考核项,合并第二列和第六列,从第三行开始。
mergeCell(sheet, 2, 3, sheet.getLastRowNum(), workbook,4);
//合计列
HSSFRow rowRowN = sheet.createRow(sheet.getLastRowNum()+1);
for(int i = 0 ; i < rowName.length; i ++ ){
if(i == 0){
HSSFCell cellRow= rowRowN.createCell(i);
cellRow.setCellType(HSSFCell.CELL_TYPE_STRING);
cellRow.setCellValue(dataList.size() + 1);
cellRow.setCellStyle(style);
}else if(i == 1){
HSSFCell cellRow= rowRowN.createCell(i);
cellRow.setCellType(HSSFCell.CELL_TYPE_STRING);
cellRow.setCellValue("合计");
cellRow.setCellStyle(columnTopStyle);
}else if(i == rowName.length-1){
HSSFCell cellRow= rowRowN.createCell(i);
cellRow.setCellType(HSSFCell.CELL_TYPE_STRING);
if(null != params.get("yearScore")){
cellRow.setCellValue(params.get("yearScore").toString());
}
cellRow.setCellStyle(columnTopStyle);
}else {
HSSFCell cellRow= rowRowN.createCell(i);
cellRow.setCellType(HSSFCell.CELL_TYPE_STRING);
cellRow.setCellValue(" ");
cellRow.setCellStyle(columnTopStyle);
}
}
//导出excel,并下载
if(workbook !=null){
FileOutputStream fout = null;
OutputStream out = null;
String cityName = null;
String year = null;
try
{
if(null != params.get("cityName")){
cityName = params.get("cityName").toString();
}
if(null != params.get("year")){
year = params.get("year").toString();
}
String fileName = cityName + "市"+ year +"年成绩清单.xls";
// String fileName = "地市成绩清单_" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
String headStr = "attachment; filename=\"" + java.net.URLEncoder.encode(fileName, "UTF-8") + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
out = response.getOutputStream();
// fout = new FileOutputStream("E:/cityScore.xls");
workbook.write(out);
} catch (IOException e){
e.printStackTrace();
} finally {
// fout.close();
out.close();
}
}
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/**
* 列数据信息单元格样式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/**
* 合并单元格
* @param sheet 要合并单元格的excel 的sheet
* @param cellLine 要合并的列
* @param startRow 要合并列的开始行
* @param endRow 要合并列的结束行
* @param offset 与合并的列相同合并方式的列的偏移量
*/
private static void mergeCell(HSSFSheet sheet, int cellLine, int startRow, int endRow,HSSFWorkbook workBook,int offset){
// 样式对象
HSSFCellStyle style = workBook.createCellStyle();
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//获取开始行的数据,以便后面进行比较
String s_will = sheet.getRow(startRow).getCell(cellLine).getStringCellValue();
int count = 0;
boolean flag = false;
for (int i = startRow+1; i <= endRow; i++) {
sheet.getRow(i).getCell(cellLine).setCellType(Cell.CELL_TYPE_STRING);
String s_current = sheet.getRow(i).getCell(cellLine).getStringCellValue();
if(s_will.equals(s_current))
{
s_will = s_current;
if(flag)
{
sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine,cellLine));
HSSFRow row = sheet.getRow(startRow-count);
if(null != sheet.getRow(startRow-count).getCell(cellLine)){
sheet.getRow(i).getCell(cellLine).setCellType(Cell.CELL_TYPE_STRING);
String cellValueTemp = sheet.getRow(startRow-count).getCell(cellLine).getStringCellValue();
HSSFCell cell = row.createCell(cellLine);
cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
cell.setCellStyle(style); // 样式
}
//合并与当前列相同合并方式的列
sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine + offset,cellLine + offset));
HSSFRow row1 = sheet.getRow(startRow-count);
if(null != sheet.getRow(startRow-count).getCell(cellLine + offset)){
sheet.getRow(i).getCell(cellLine + offset).setCellType(Cell.CELL_TYPE_STRING);
String cellValueTemp = sheet.getRow(startRow-count).getCell(cellLine + offset).getStringCellValue();
HSSFCell cell = row1.createCell(cellLine + offset);
cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
cell.setCellStyle(style); // 样式
}
count = 0;
flag = false;
}
startRow=i;
count++;
}else{
flag = true;
s_will = s_current;
}
if(i==endRow&&count>0)
{
/** 防止前面的漏合单元格,这里重新再合一次 start ***/
sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine,cellLine));
HSSFRow row = sheet.getRow(startRow-count);
if(null != sheet.getRow(startRow-count).getCell(cellLine)){
sheet.getRow(i).getCell(cellLine).setCellType(Cell.CELL_TYPE_STRING);
String cellValueTemp = sheet.getRow(startRow-count).getCell(cellLine).getStringCellValue();
HSSFCell cell = row.createCell(cellLine);
cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
cell.setCellStyle(style); // 样式
}
sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine + offset,cellLine + offset));
HSSFRow row1 = sheet.getRow(startRow-count);
if(null != sheet.getRow(startRow-count).getCell(cellLine + offset)){
sheet.getRow(i).getCell(cellLine + offset).setCellType(Cell.CELL_TYPE_STRING);
String cellValueTemp = sheet.getRow(startRow-count).getCell(cellLine + offset).getStringCellValue();
HSSFCell cell = row1.createCell(cellLine + offset);
cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
cell.setCellStyle(style); // 样式
}
/** 防止前面的漏合单元格,这里重新再合一次 end ***/
//最后两条相同时,再合并
if(sheet.getRow(i).getCell(cellLine).getStringCellValue().equals(sheet.getRow(i-1).getCell(cellLine).getStringCellValue())){
sheet.addMergedRegion(new CellRangeAddress(endRow-count,endRow,cellLine,cellLine));
String cellValueTemp2 = sheet.getRow(startRow-count).getCell(cellLine).getStringCellValue();
HSSFRow row2 = sheet.getRow(startRow-count);
HSSFCell cell2 = row2.createCell(cellLine);
cell2.setCellValue(cellValueTemp2); // 跨单元格显示的数据
cell2.setCellStyle(style); // 样式
//合并与当前列相同合并方式的列
sheet.addMergedRegion(new CellRangeAddress(endRow-count,endRow,cellLine + offset,cellLine + offset));
String cellValueTemp3 = sheet.getRow(startRow-count).getCell(cellLine + offset).getStringCellValue();
HSSFRow row3 = sheet.getRow(startRow-count);
HSSFCell cell3 = row3.createCell(cellLine + offset);
cell3.setCellValue(cellValueTemp3); // 跨单元格显示的数据
cell3.setCellStyle(style); // 样式
}
}
}
}
}
- 那如何调用呢?还是直接上代码。
package com.fable.insightview.app.assess.controller.cityassess;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import com.fable.insightview.app.assess.api.model.cityassess.City;
import com.fable.insightview.app.assess.api.model.cityassess.CityAssess;
import com.fable.insightview.app.assess.api.model.cityassess.YearCityAssess;
import com.fable.insightview.app.assess.api.model.common.Data;
import com.fable.insightview.app.assess.api.model.common.MessageBean;
import com.fable.insightview.app.assess.api.service.cityassess.CityAssessService;
import com.fable.insightview.app.assess.common.util.ExportExcel;
import com.wordnik.swagger.annotations.Api;
import com.wordnik.swagger.annotations.ApiOperation;
import com.wordnik.swagger.annotations.ApiParam;
/**
* 地市成绩清单/考核项列表控制层
* @author zhaods
*
*/
@Controller
@RequestMapping("/cityAssess")
@Api(value="/cityAssess",description="地市成绩清单/考核项列表控制层")
public class CityAssessController {
@Autowired
private CityAssessService cityAssessService;
private static String title;
private static String[] rowsName;
@RequestMapping(value="/exportExcel", method = RequestMethod.GET)
@ApiOperation(value = "导出excel表格", httpMethod = "GET", response = MessageBean.class, notes = "导出地市考核清单")
@ResponseBody
public MessageBean exportExcel(
@ApiParam(required = true, name = "cityAssess", value = "城市成绩清单")
// @RequestBody CityAssess cityAssess,
@RequestParam String year,
@RequestParam String cityId,
HttpServletResponse response){
MessageBean msg = new MessageBean();
Map<String,Object> params = new HashMap<String, Object>();
List<Object[]> dataList = new ArrayList<Object[]>();
Object[] objs = null;
//excel标题表头设置
createExcel(objs, dataList, params, cityId, year);
//excel导出
ExportExcel ex = new ExportExcel(title, rowsName, dataList,params);
try {
ex.export(response);
} catch (Exception e) {
e.printStackTrace();
}
return msg;
}
/**
* 设置excel标题及表头
* @param objs
* @param dataList
* @param params
* @param cityId
* @param year
*/
public void createExcel(Object[] objs,List<Object[]> dataList,Map<String,Object> params,String cityId,String year){
City city = cityAssessService.getCityById(cityId);
if(null != city){
title = city.getCityName() + "市"+ year +"年成绩清单";
params.put("cityName", city.getCityName());
}
rowsName = new String[]{"序号","项目","分项","考核内容","考核内容得分","评分人","分项得分","大项得分"};
List<CityAssess> cityScoreList = cityAssessService.getCityScoreList(cityId,year);
for(int i = 0 ; i < cityScoreList.size(); i ++){
CityAssess assess = cityScoreList.get(i);
objs = new Object[rowsName.length];
objs[0] = i;
objs[1] = assess.getFirstItemName();
objs[2] = assess.getSecondItemName();
objs[3] = assess.getThirdItemName();
if(null == String.valueOf(assess.getScore())){
objs[4] = 0 + "/" + assess.getTotal();
}else{
objs[4] = assess.getScore() + "/" + assess.getTotal();
}
objs[5] = assess.getMarkerName();
if(null == String.valueOf(assess.getSecondItemScore())){
objs[6] = 0;
}else {
objs[6] = assess.getSecondItemScore();
}
if(null == String.valueOf(assess.getFirstItemScore())){
objs[7] = 0;
}else {
objs[7] = assess.getFirstItemScore();
}
dataList.add(objs);
}
//年度总分
String yearScore = null;
YearCityAssess yearAssessInfo = cityAssessService.getYearAssessInfo(cityId,year);
if(null != yearAssessInfo){
yearScore = String.valueOf(yearAssessInfo.getScore());
}
params.put("yearScore", yearScore);
params.put("year", year);
}
}
- controller层使用了swagger注释便于测试,可以不用理会。(关于swagger与spingmvc整合等相关知识,会在下篇文章中介绍)。
- 最后生成的表格如下: