怎么导出多个sheet的excel？
怎么导出多个sheet的excel？最后怎么弄的？ 最近刚完成一个多sheet导出；
工具类；
package com.jeecg.zpkjrz.util;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExportExcelUtils {
/**
* @Title: exportExcel
* @Description: 导出Excel的方法
* @param workbook
* @param sheetNum
* (sheet的位置，0表示第一个表格中的第一个sheet)
* @param sheetTitle
* （sheet的名称）
* @param headers
* （表格的标题）
* @param result
* （表格的数据）
* @param out
* （输出流）
* @throws Exception
*/
public void exportExcel(XSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers,
List<Map<String, Object>> result, OutputStream out) throws Exception {
// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置每列宽度
sheet.setColumnWidth(0, 10 * 256);
sheet.setColumnWidth(1, 30 * 256);
sheet.setColumnWidth(2, 40 * 256);
sheet.setColumnWidth(3, 15 * 256);
sheet.setColumnWidth(4, 15 * 256);
sheet.createFreezePane(0, 1, 0, 1);
// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 128, 128)));
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
// 字体样式
XSSFFont font = workbook.createFont();
font.setColor(new XSSFColor(new java.awt.Color(0, 0, 0)));
font.setFontHeightInPoints((short) 12);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers);
cell.setCellValue(text.toString());
}
// 遍历集合数据，产生数据行
if (result != null) {
int index = 1;
for (int i = 0; i < result.size(); i++) {
row = sheet.createRow(index);
int cellIndex = 0;
Map<String, Object> map = result.get(i);
for (Map.Entry<String, Object> entry : map.entrySet()) {
XSSFCell cell = row.createCell(cellIndex);
cell.setCellValue(entry.getValue().toString());
cellIndex++;
}
index++;
}
}
}
}
控制层实现；
/**
* 多sheet导出excel
*
* @param request
* @param response
* @throws IOException
*/
@RequestMapping(params = "exportXlsMultiple")
public void exportXlsMultiple(THllhZpkjrzEntity tHllhZpkjrz, HttpServletRequest request,
HttpServletResponse response, DataGrid dataGrid, ModelMap modelMap) {
String sql0 = "select ksrq,jzrq,username from t_h_export_set where username='"
+ ResourceUtil.getSessionUser().getRealName() + "'";
Map<String, Object> map = this.systemService.findOneForJdbc(sql0);
SimpleDateFormat sm = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
cal.add(Calendar.MONTH, -1);
cal.set(Calendar.DAY_OF_MONTH, 1);
String firstDay = sm.format(cal.getTime());
Calendar call = Calendar.getInstance();
call.set(Calendar.DAY_OF_MONTH, 0);
String lastDay = sm.format(call.getTime());
String ksrq = null;
String jzrq = null;
if (map != null) {
ksrq = (String) map.get("ksrq");
jzrq = (String) map.get("jzrq");
} else {
ksrq = firstDay;
jzrq = lastDay;
}
String sql1 = "select rownum xh,xf_nsrsbh, xf_qymc, je, se from sjff.cb_fpcgl_mx where kprq >= to_date('" + ksrq
+ "', 'yyyy-mm-dd') and kprq <= to_date('" + jzrq + "', 'yyyy-mm-dd') and zfbz = 'N' and sl = 0.16";
List<Map<String, Object>> data1 = this.systemService.findForJdbc(sql1);
String sql2 = "select rownum xh,xf_nsrsbh, xf_qymc, je, se from sjff.cb_fpcgl_mx where kprq >= to_date('" + ksrq
+ "', 'yyyy-mm-dd') and kprq <= to_date('" + jzrq + "', 'yyyy-mm-dd') and zfbz = 'N' and sl = 0.1";
List<Map<String, Object>> data2 = this.systemService.findForJdbc(sql2);
String sql3 = "select rownum xh,xf_nsrsbh, xf_qymc, je, se from sjff.cb_fdkfpcgl_mx where kprq >= to_date('"
+ ksrq + "', 'yyyy-mm-dd') and kprq <= to_date('" + jzrq
+ "', 'yyyy-mm-dd') and zfbz = 'N' and sl = 0.16";
List<Map<String, Object>> data3 = this.systemService.findForJdbc(sql3);
String sql4 = "select rownum xh,xf_nsrsbh, xf_qymc, je, se from sjff.cb_fdkfpcgl_mx where kprq >= to_date('"
+ ksrq + "', 'yyyy-mm-dd') and kprq <= to_date('" + jzrq
+ "', 'yyyy-mm-dd') and zfbz = 'N' and sl = 0.1";
List<Map<String, Object>> data4 = this.systemService.findForJdbc(sql4);
String sql5 = "select rownum xh,b.nsrsbh, b.nsrmc, je, se from sjff.rz_fpdkl_mx a, sjff.dj_nsrxx b where rz_sj >= to_date('"
+ ksrq + "', 'yyyy-mm-dd') and rz_sj <= to_date('" + jzrq
+ "', 'yyyy-mm-dd') and (a.gf_nsrsbh = b.nsrsbh or a.gf_nsrsbh = b.shxydm) and b.yxbz = 'Y' and b.kzztdjlx_dm <> '1500' and b.kqccsztdjbz = 'N' and a.sl = 16 and a.rz_jg = '0'";
List<Map<String, Object>> data5 = this.systemService.findForJdbc(sql5);
String sql6 = "select rownum xh,b.nsrsbh, b.nsrmc, je, se from sjff.rz_fpdkl_mx a, sjff.dj_nsrxx b where rz_sj >= to_date('"
+ ksrq + "', 'yyyy-mm-dd') and rz_sj <= to_date('" + jzrq
+ "', 'yyyy-mm-dd') and (a.gf_nsrsbh = b.nsrsbh or a.gf_nsrsbh = b.shxydm) and b.yxbz = 'Y' and b.kzztdjlx_dm <> '1500' and b.kqccsztdjbz = 'N' and a.sl = 10 and a.rz_jg = '0'";
List<Map<String, Object>> data6 = this.systemService.findForJdbc(sql6);
String sql7 = "select rownum xh,b.nsrsbh, b.nsrmc, je, se from sjff.rz_fpdkl_mx a, sjff.dj_nsrxx b where rz_sj >= to_date('"
+ ksrq + "', 'yyyy-mm-dd') and rz_sj <= to_date('" + jzrq
+ "', 'yyyy-mm-dd') and (a.gf_nsrsbh = b.nsrsbh or a.gf_nsrsbh = b.shxydm) and b.yxbz = 'Y' and b.kzztdjlx_dm <> '1500' and b.kqccsztdjbz = 'N' and a.sl is null and a.rz_jg = '0'";
List<Map<String, Object>> data7 = this.systemService.findForJdbc(sql7);
OutputStream out=null;
try {
out = response.getOutputStream();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
String[] headers1 = { "序号", "销方纳税人识别号", "销方企业名称", "金额", "税额" };
String[] headers2 = { "序号", "销方纳税人识别号", "销方企业名称", "金额", "税额" };
String[] headers3 = { "序号", "销方纳税人识别号", "销方企业名称", "金额", "税额" };
String[] headers4 = { "序号", "销方纳税人识别号", "销方企业名称", "金额", "税额" };
String[] headers5 = { "序号", "购方纳税人识别号", "购方企业名称", "金额", "税额" };
String[] headers6 = { "序号", "购方纳税人识别号", "购方企业名称", "金额", "税额" };
String[] headers7 = { "序号", "购方纳税人识别号", "购方企业名称", "金额", "税额" };
ExportExcelUtils eeu = new ExportExcelUtils();
XSSFWorkbook workbook = new XSSFWorkbook();
eeu.exportExcel(workbook, 0, "开具专票16%", headers1, data1, out);
eeu.exportExcel(workbook, 1, "开具专票10%", headers2, data2, out);
eeu.exportExcel(workbook, 2, "开具普票16%", headers3, data3, out);
eeu.exportExcel(workbook, 3, "开具普票10%", headers4, data4, out);
eeu.exportExcel(workbook, 4, "认证16%专票", headers5, data5, out);
eeu.exportExcel(workbook, 5, "认证10%专票", headers6, data6, out);
eeu.exportExcel(workbook, 6, "认证空税率专票", headers7, data7, out);
// 原理就是将所有的数据一起写入，然后再关闭输入流。
String filename="专普票开具认证16%和10%信息("+ksrq+"至"+jzrq+".xlsx";
response.reset();
response.setHeader("Content-Disposition", "attachment; filename="+ new String(filename.getBytes("gb2312"), "ISO8859-1" ));//要保存的文件名
response.setContentType("application/octet-stream; charset=utf-8");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
页:
[1]