最近在做一个项目,需要导出excel,感觉好久不做只有个大概,细节又忘了。现在整理下。
直接先贴我的代码:
public void protocolExport() throws IOException{
String element ="";
String sql = "" ;
//下面是拼查询的字段。我做的有点区别是导出字段页面用户自己可以选择,这个this.selectBox集合里的值就是从页面传过来的用户选择的字段。
if(this.selectBox != null && this.selectBox.size()>0){
for (int i = 0; i < selectBox.size(); i++) {
if(i<selectBox.size()-1){
if(selectBox.get(i).equals("ID_CARD")){
element += "a."+(String) selectBox.get(i)+", ";
}else{
element += (String) selectBox.get(i)+", ";
}
}else{
if(selectBox.get(i).equals("ID_CARD")){
element += "a."+(String) selectBox.get(i);
}else{
element += (String) selectBox.get(i);
}
}
}
sql = "SELECT "+element+" "+this.sql.substring(this.sql.indexOf("FROM"), this.sql.length());//this.sql 是之前查询结果用的sql,因为我的方法在一个action,且scope是session,所以这里可以直接拿到。条件一样,替换查询字段就行。
List ptList = this.newHouseService.executeMySQLQuery(sql);
HSSFWorkbook workbook = ExportExcel(ptList,selectBox);
this.fileName = "协议信息表.xls";
fileName = new String(fileName.getBytes(), "ISO-8859-1");//导出excel文件名。汉字需要处理下。
//----------------------------------------------
ActionContext ctx = ActionContext.getContext();
HttpServletResponse response = (HttpServletResponse) ctx
.get(ServletActionContext.HTTP_RESPONSE);
response.setHeader("content-disposition", "attachment;filename=" + fileName);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream out = response.getOutputStream();
ByteArrayOutputStream output = new ByteArrayOutputStream();
workbook.write(output);
byte[] bt = output.toByteArray();
excelFile = new java.io.ByteArrayInputStream(bt);
int n;
while((n=excelFile.read(bt))!=-1){
out.write(bt, 0, n);
}
//------------------------------------------------
output.flush();
output.close();
excelFile.close();
out.close();
/*这里strtus2还有一种配置方式,就是action中不需要使用response.而是直接在strtus2.xml中进行配置
上面‘//-- ’里面代码保留一部分就行
ByteArrayOutputStream output = new ByteArrayOutputStream();
workbook.write(output);
byte[] bt = output.toByteArray();
excelFile = new java.io.ByteArrayInputStream(bt);
<action name="protocolExport" class="ProtocolInfoAction" method="protocolExport">
<result name="success" type="stream">
<param name="inputName">excelFile</param>
<param name="contentDisposition">attachment;filename="${fileName}"</param>
<param name="contentType">application/vnd.ms-excel;;charset=utf-8"</param>
<param name="bufferSize">1024</param>
</result>
</action>
*/
}
//return SUCCESS;
}
public HSSFWorkbook ExportExcel(List plist,List<String> choiceList){
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("协议信息");
// 第三步,创建单元格 样式
HSSFCellStyle style = this.createTitleStyle(wb);
if (choiceList != null && choiceList.size() > 0) {
HSSFRow row = sheet.createRow((short)0);
for (int i = 0; i < choiceList.size(); i++) {
if(choiceList.get(i).equals("PROTOCOL_NUM")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "协议编号");
}else if(choiceList.get(i).equals("PROTOCOL_NAME")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "协议名称");
}else if(choiceList.get(i).equals("REMARK")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "备注信息");
}else if(choiceList.get(i).equals("PROTOCOL_KIND")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "协议类型");
}else if(choiceList.get(i).equals("USER_NAME")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "被征收人");
}else if(choiceList.get(i).equals("ID_CARD")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "被征收人身份证");
}else if(choiceList.get(i).equals("PHONE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "被征收人电话");
}else if(choiceList.get(i).equals("ADDRESS")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "被征收房屋地址");
}else if(choiceList.get(i).equals("VILLAGE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "所属村委");
}else if(choiceList.get(i).equals("VGROUP")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "所属村组");
}else if(choiceList.get(i).equals("DEADLINE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "搬迁截止日期");
}else if(choiceList.get(i).equals("MONEY_AREA")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "货币置换面积");
}else if(choiceList.get(i).equals("M_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "货币置换计款");
}else if(choiceList.get(i).equals("TICKET_AREA")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "房票置换面积");
}else if(choiceList.get(i).equals("T_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "房票置换计款");
}else if(choiceList.get(i).equals("TICKET_NO")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "房票号");
}else if(choiceList.get(i).equals("OUT_ZJD_AREA")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "超出标准宅基地面积");
}else if(choiceList.get(i).equals("OUT_ZJD_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "超出标准宅基地面积计款");
}else if(choiceList.get(i).equals("OUT_FW_AREA")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "超出标准房屋面积");
}else if(choiceList.get(i).equals("OUT_FW_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "超出标准房屋面积计款");
}else if(choiceList.get(i).equals("OUT_JYF_AREA")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "超出标准简易房面积");
}else if(choiceList.get(i).equals("OUT_JYF_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "超出标准简易房计款");
}else if(choiceList.get(i).equals("TOTAL_AREA_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "面积补偿款合计");
}else if(choiceList.get(i).equals("BQ_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "搬迁补助费");
}else if(choiceList.get(i).equals("LSAZ_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "临时安置费");
}else if(choiceList.get(i).equals("PRIZE_DEADLINE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "有奖搬迁截止日(不含当日)");
}else if(choiceList.get(i).equals("PRIZE_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "奖励金额");
}else if(choiceList.get(i).equals("OTHER_FEES")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "其他费用合计");
}else if(choiceList.get(i).equals("DIFF_AREA")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "产权调换差额面积");
}else if(choiceList.get(i).equals("DIFF_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "产权调换差额");
}else if(choiceList.get(i).equals("ALL_FEE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "总合计款");
}else if(choiceList.get(i).equals("SIGN_DATE")){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, "协议签订日期");
}
}
}
if(plist.size()>0){
for (int j = 0; j < plist.size(); j++) {
HashMap pt =(HashMap)plist.get(j);
HSSFRow row1 = sheet.createRow((short) (j+1));
for (int k = 0; k < choiceList.size(); k++) {
if(choiceList.get(k).equals("PROTOCOL_NUM")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("PROTOCOL_NUM"));
}else if(choiceList.get(k).equals("PROTOCOL_NAME")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("PROTOCOL_NAME"));
}else if(choiceList.get(k).equals("REMARK")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("REMARK"));
}else if(choiceList.get(k).equals("PROTOCOL_KIND")){
if(pt.get("PROTOCOL_KIND").equals("1"))
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, "货币补偿");
else
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, "房屋置换");
}else if(choiceList.get(k).equals("USER_NAME")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("USER_NAME"));
}else if(choiceList.get(k).equals("ID_CARD")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("ID_CARD"));
}else if(choiceList.get(k).equals("PHONE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("PHONE"));
}else if(choiceList.get(k).equals("ADDRESS")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("ADDRESS"));
}else if(choiceList.get(k).equals("VILLAGE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("VILLAGE"));
}else if(choiceList.get(k).equals("VGROUP")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("VGROUP"));
}else if(choiceList.get(k).equals("DEADLINE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("DEADLINE"));
}else if(choiceList.get(k).equals("MONEY_AREA")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("MONEY_AREA"));
}else if(choiceList.get(k).equals("M_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("M_FEE"));
}else if(choiceList.get(k).equals("TICKET_AREA")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("TICKET_AREA"));
}else if(choiceList.get(k).equals("T_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("T_FEE"));
}else if(choiceList.get(k).equals("TICKET_NO")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("TICKET_NO"));
}else if(choiceList.get(k).equals("OUT_ZJD_AREA")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("OUT_ZJD_AREA"));
}else if(choiceList.get(k).equals("OUT_ZJD_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("OUT_ZJD_FEE"));
}else if(choiceList.get(k).equals("OUT_FW_AREA")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("OUT_FW_AREA"));
}else if(choiceList.get(k).equals("OUT_FW_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("OUT_FW_FEE"));
}else if(choiceList.get(k).equals("OUT_JYF_AREA")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("OUT_JYF_AREA"));
}else if(choiceList.get(k).equals("OUT_JYF_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("OUT_JYF_FEE"));
}else if(choiceList.get(k).equals("TOTAL_AREA_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("TOTAL_AREA_FEE"));
}else if(choiceList.get(k).equals("BQ_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("BQ_FEE"));
}else if(choiceList.get(k).equals("LSAZ_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("LSAZ_FEE"));
}else if(choiceList.get(k).equals("PRIZE_DEADLINE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("PRIZE_DEADLINE"));
}else if(choiceList.get(k).equals("PRIZE_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("PRIZE_FEE"));
}else if(choiceList.get(k).equals("OTHER_FEES")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("OTHER_FEES"));
}else if(choiceList.get(k).equals("DIFF_AREA")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("DIFF_AREA"));
}else if(choiceList.get(k).equals("DIFF_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("DIFF_FEE"));
}else if(choiceList.get(k).equals("ALL_FEE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("ALL_FEE"));
}else if(choiceList.get(k).equals("SIGN_DATE")){
this.createCell(row1, k, style, HSSFCell.CELL_TYPE_STRING, pt.get("SIGN_DATE"));
}
}
}
}else{
this.createCell(sheet.createRow(0), 0, style, HSSFCell.CELL_TYPE_STRING, "查无资料");
}
return wb;
}
@SuppressWarnings("deprecation")
private void createCell(HSSFRow row,int column,HSSFCellStyle style,int cellType,Object value){
//创建单元格
HSSFCell cell = row.createCell((short)column);
if(null!=style){
cell.setCellStyle(style);
}
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
cell.setCellValue(value.toString());
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(value.toString()));
default:
break;
}
}
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 200);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
return style;
贴一下我的查询页面和导出excel字段选择页面: