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"));