909422229_基于JavaWeb的poi_Excel导出

最近在做统计，报表导出模块。下面记录一下Excel导出单元格的合并问题。

主要方法如下：

sheet.setColumnWidth(0, 8000);

这是创建单元格需要指定单元格的宽度的方法，有时候无需合并，只需要指定宽度是其他的多倍即可产生合并效果。

sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));

这四个参数分别代表：开始合并的行数、结束合并的行数、开始合并的列数、结束合并的列数。

下面贴图，方面演示：

注释：

前面几个需要注明的都需要指定单元格是否合并、名称等。

第一行标题：

第二行合并单元格：

第三行由于指定了前两列宽度是其他的两倍，所以看起来是合并了，其实不然。

      

Controller代码：

package module.supervise.modules.statistics.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
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.json.JSONArray;
import org.json.JSONObject;
import bjdczd.modules.interapproval.dao.JudgeStats;
import bjdczd.modules.interapproval.dao.impl.InterStatsSex;
import xsf.IContextDictionary;
import xsf.web.HttpContext;
import module.supervise.modules.statistics.interfaces.ExportExcelInfo;
import module.supervise.modules.statistics.util.QueryAllUtil;


public class ExportExcelInfoImpl implements ExportExcelInfo {


@Override
public String execute(HttpContext context, IContextDictionary dictionary) {


HSSFWorkbook workbook = new HSSFWorkbook();


HSSFSheet sheet = workbook.createSheet();


// 列头字体样式
HSSFFont font = this.getFont(workbook, "仿宋", (short) 16,
HSSFFont.BOLDWEIGHT_BOLD);


// 列头样式
HSSFCellStyle style = this.getTitleStyle(workbook, font);


// 第一行标题
HSSFRow row = sheet.createRow(0);


row.setHeightInPoints(30);


String fileName = "督办办理统计表";


HSSFCell cell = row.createCell(0);
cell.setCellValue(new HSSFRichTextString(fileName));
cell.setCellStyle(style);


sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));


sheet.setColumnWidth(0, 8000);
sheet.setColumnWidth(1, 8000);
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 4000);
sheet.setColumnWidth(4, 4000);
sheet.setColumnWidth(5, 4000);
sheet.setColumnWidth(6, 4000);


font = this.getFont(workbook, "仿宋", (short) 12,
HSSFFont.BOLDWEIGHT_BOLD);


style = this.getStyle(workbook, font);


// 第二行
row = sheet.createRow(1);


row.setHeightInPoints(30);


cell = row.createCell(0);
cell.setCellValue("月份");
cell.setCellStyle(style);


cell = row.createCell(1);
cell.setCellValue("立项数");
cell.setCellStyle(style);


cell = row.createCell(2);
cell.setCellValue("在办数");
cell.setCellStyle(style);


cell = row.createCell(3);
cell.setCellValue("在办数");
cell.setCellStyle(style);


cell = row.createCell(4);
cell.setCellValue("办结数");
cell.setCellStyle(style);


cell = row.createCell(5);
cell.setCellValue("办结数");
cell.setCellStyle(style);
//开始合并的行数，结束合并的行数，开始合并的列数，结束合并的列数。都是从0开始
sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));


// 第三行
row = sheet.createRow(2);


row.setHeightInPoints(30);


cell = row.createCell(0);
cell.setCellValue("月份");
cell.setCellStyle(style);


cell = row.createCell(1);
cell.setCellValue("立项数");
cell.setCellStyle(style);


cell = row.createCell(2);
cell.setCellValue("逾期办理数");
cell.setCellStyle(style);


cell = row.createCell(3);
cell.setCellValue("按期办理数");
cell.setCellStyle(style);


cell = row.createCell(4);
cell.setCellValue("逾期办结数");
cell.setCellStyle(style);


cell = row.createCell(5);
cell.setCellValue("按期办结数");
cell.setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));

this.getData(workbook, sheet, dictionary);

HttpServletResponse response = context.getResponse();


response.setContentType("application/x-download;charset=UTF-8");


try {
fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
} catch (Exception e) {
e.printStackTrace();
}


SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");


String day = sdf.format(new Date());


response.addHeader("Content-Disposition", "attachment;filename="
+ fileName + day + ".xls");


try {
OutputStream out = response.getOutputStream();


workbook.write(out);


out.flush();


out.close();


} catch (IOException e) {
e.printStackTrace();
}


return null;
}


private void getData(HSSFWorkbook workbook, HSSFSheet sheet,
IContextDictionary dictionary) {
HSSFFont font = this.getFont(workbook, "仿宋", (short) 12);


HSSFCellStyle style = this.getStyle(workbook, font);


HSSFRow row = null;


HSSFCell cell = null;


JudgeStats sex = new InterStatsSex();
//获得数据
JSONArray ja = QueryAllUtil.queryYueFen(dictionary.getString("year"), dictionary.getString("category"));


for (int i = 0; i < ja.length(); i++) {
// 获取每一个JsonObject对象
JSONObject jo = ja.getJSONObject(i);


// 第三行
row = sheet.createRow(i + 3);


row.setHeightInPoints(30);


cell = row.createCell(0);
cell.setCellValue(jo.getString("yue"));
cell.setCellStyle(style);


cell = row.createCell(1);
cell.setCellValue(jo.getString("sum"));
cell.setCellStyle(style);


cell = row.createCell(2);
cell.setCellValue(jo.getString("oyq"));
cell.setCellStyle(style);


cell = row.createCell(3);
cell.setCellValue(jo.getString("oaq"));
cell.setCellStyle(style);


cell = row.createCell(4);
cell.setCellValue(jo.getString("eyq"));
cell.setCellStyle(style);


cell = row.createCell(5);
cell.setCellValue(jo.getString("eaq"));
cell.setCellStyle(style);


}


}


private HSSFFont getFont(HSSFWorkbook workbook, String name, short size,
short bold) {
HSSFFont font = workbook.createFont();
font.setFontName(name);
font.setFontHeightInPoints(size);
font.setBoldweight(bold);


return font;
}


private HSSFFont getFont(HSSFWorkbook workbook, String name, short size) {
HSSFFont font = workbook.createFont();
font.setFontName(name);
font.setFontHeightInPoints(size);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);


return font;
}


private HSSFCellStyle getTitleStyle(HSSFWorkbook workbook, HSSFFont font) {
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setLocked(true);
style.setWrapText(true);


return style;
}


private HSSFCellStyle getStyle(HSSFWorkbook workbook, HSSFFont font) {
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// style.setLocked(true);
style.setWrapText(true);


style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);


return style;
}
}

这里的JSONArray是需要在查询数据之后返回json数组的，得到一个数据源，添加到Excel中，由于这次做的项目是公司的内部技术，就不发图了。例如：创建json对象，将数据封装到json对象中，然后放到json数组中集体返回。

JSONArray ja = QueryAllUtil.queryYueFen(dictionary.getString("year"), dictionary.getString("category"));