大家都知道,很多企业都想把页面的数据能以excel的形式导出来,尤其是erp之类的管理系统,特别需要这个功能,所以导出excel程序员的必备技能之一,下面来看例子。
- Java导出excel表格
@RequestMapping("receiptExport")
public void receiptExport(String groupCode,String statData,String endData,Integer aStatus){
// *收款单列表
Map<String,Object> mapCondition=new HashMap<String,Object>();
mapCondition.put("groupCode", groupCode);
mapCondition.put("billType", 1);
mapCondition.put("statData", statData);
mapCondition.put("endData", endData);
mapCondition.put("aStatus", aStatus);
List<Cost> inslist =costDao.costList(mapCondition);
Cost mCostSum=costDao.selectSumCost(groupCode, 1,statData, endData);
Map<Integer, List<Cost>> rmap = new HashMap<Integer, List<Cost>>();
// 查询数据
if (null != inslist && inslist.size() > 0) {
for (int i = 0; i < inslist.size(); i++) {
rmap.put(i + 2, inslist);// 把查询出来的对象放入map集合中
}
}
ExcelWriter writer = new ExcelWriter("sheet");
HSSFWorkbook workbook = writer.getWorkbook();
// 字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFFont.SS_NONE);
// 表头字体
HSSFFont fontHead = workbook.createFont();
fontHead.setColor(HSSFFont.SS_NONE);
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 表头单元格样式
HSSFCellStyle headStyle = workbook.createCellStyle();
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont fontNo = workbook.createFont();
fontNo.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headStyle.setFont(fontNo);
// 普通单元格样式
HSSFCellStyle styleColor = workbook.createCellStyle();
styleColor.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 取消数字过长自动转换成科学计数法的样式
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
cellStyle2.setDataFormat(format.getFormat("@"));// 此样式代表文本
HSSFSheet sheet = workbook.getSheetAt(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 16));
// 设置字体大小
HSSFFont celltbNameFont = workbook.createFont();
celltbNameFont.setFontHeightInPoints((short) 15);
celltbNameFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置顶部样式
HSSFCellStyle styleCell = workbook.createCellStyle();
styleCell.setFont(celltbNameFont);
styleCell.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFRow rows = sheet.createRow((short) 0);// 创建一行
rows.setHeight((short) 600);
HSSFCell cells = rows.createCell(0);
styleCell.setWrapText(true);
String title = "收款单列表";
cells.setCellValue(new HSSFRichTextString(title));
cells.setCellStyle(styleCell);
// 第一行,表头
writer.createCellNumeric(1, 0, "序号", headStyle, (int) (256 * 6));
writer.createCellNumeric(1, 1, "状态", headStyle, (int) (256 * 6));
writer.createCellNumeric(1, 2, "类型", headStyle, (int) (256 * 6));
writer.createCellNumeric(1, 3, "单据编号", headStyle, (int) (256 * 6));
writer.createCellNumeric(1, 4, "单据日期", headStyle, (int) (256 * 6));
writer.createCellNumeric(1, 5, "客户", headStyle, (int) (256 * 6));
writer.createCellNumeric(1, 6, "收款合计", headStyle, (int) (256 * 6));
// 写入数据
for (Entry<Integer, List<Cost>> map : rmap.entrySet()) {
int a = map.getKey();
writer.createCellNumeric(a, 0, "" + (map.getKey() - 1), styleColor,(int) (256 * 6));// 里面的几个值以此代表:行数、列数、内容、样式、宽度 。 下面方法参数跟这个相同
if (inslist.get(a - 2).getState()==0) {
writer.createCellNumeric(a, 1,"已保存", styleColor, (int) (256 * 30));
}else if (inslist.get(a - 2).getState()==1) {
writer.createCellNumeric(a, 1,"一审已审", styleColor, (int) (256 * 30));
}else if (inslist.get(a - 2).getState()==2) {
if(inslist.get(a - 2).getBill_type()==0 || inslist.get(a - 2).getBill_type()==1){
writer.createCellNumeric(a, 1,"已二审", styleColor, (int) (256 * 30));
}else if(inslist.get(a - 2).getBill_type()==2 || inslist.get(a - 2).getBill_type()==3){
writer.createCellNumeric(a, 1,"已审核", styleColor, (int) (256 * 30));
}
}
if (inslist.get(a - 2).getBill_type()==0) {
writer.createCellNumeric(a, 2,"付款单" , styleColor,(int) (256 * 30));
}else if(inslist.get(a - 2).getBill_type()==1){
writer.createCellNumeric(a, 2,"收款单" , styleColor,(int) (256 * 30));
}else if(inslist.get(a - 2).getBill_type()==2){
writer.createCellNumeric(a, 2,"其他收款单" , styleColor,(int) (256 * 30));
}else{
writer.createCellNumeric(a, 2,"其他付款单" , styleColor,(int) (256 * 30));
}
writer.createCell(a, 3,
inslist.get(a - 2).getBill_code() == null ? "" : inslist
.get(a - 2).getBill_code()
+ "", cellStyle2, (int) (256 * 30));
writer.createCell(a, 4,
inslist.get(a - 2).getReceive_date() == null ? "" : inslist
.get(a - 2).getReceive_date()
+ "", cellStyle2, (int) (256 * 30));
writer.createCellNumeric(a, 5,
inslist.get(a - 2).getOrgan_name() == null ? "" : inslist.get(
a - 2).getOrgan_name(), styleColor, (int) (256 * 30));
writer.createCellNumeric(a, 6,
inslist.get(a - 2).getMoney_sum() == null ? "" : inslist.get(
a - 2).getMoney_sum()
+ "", styleColor, (int) (256 * 30));
}
int num=inslist.size()+2;
writer.createCellNumeric(num, 5, "(已二审)总计:", headStyle, (int) (256 * 30));
writer.createCellNumeric(num, 6, String.valueOf(mCostSum.getMoney_sum()), headStyle, (int) (256 * 30));
writer.createCellNumeric(num, 7, String.valueOf(mCostSum.getWriteoff_sum()), headStyle, (int) (256 * 30));
writer.createCellNumeric(num, 8, String.valueOf(mCostSum.getZhekou()), headStyle, (int) (256 * 30));
writer.createCellNumeric(num, 9, String.valueOf(mCostSum.getThis_payment()), headStyle, (int) (256 * 30));
try {
response().setContentType("application/vnd.ms-excel");
response().setHeader("Content-Disposition",
"attachment;filename=\""
+ URLEncoder.encode(title + ".xls", "UTF-8")
+ "\"");
response().setHeader("Cache-Control",
"must-revalidate,post-check=0,pre-check=0");
response().setHeader("Pragma", "public");
response().setDateHeader("Expires", 0);
OutputStream os = response().getOutputStream();
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
注解:稍微解释一下,第一步,就是一个查询列表,该怎么查就怎么查,然后把查到的数据,按格式放入到下面的excel代码中,excel代码中也有注释,就不一点点解释了。
除了HSSFWorkbook这种导出excel方法以外,其实还有其他的类也能导出excel,感兴趣的可以百度一下,有什么问题,也欢迎大家给我留言。