不知不觉一个多月没更新博客了,因为这段时间博主辞职了,过着醉生梦死般的生活。哈哈哈哈
今天开始正式入职新公司,翻看着公司以前写好的项目,准备熟悉一下业务以及代码风格,不知道怎么回事,越看越蛋疼,代码里充斥着大量重复的逻辑功能。还有就是莫名其妙就会有中文首字母缩写的变量名、方法名、文件名。崩溃。(谷歌翻译,百度翻译是个好东西)。然后最后重点关注了一下util工具类这个包,哎,工具类真的太少了,由此可见,这项目是很少用工具类的。可能是业务需要的少,但我觉得更大的可能还是开发人员懒得封装提取代码吧。(毕竟一顿复制粘贴可谓是神级操作,让我这个程序员放弃复制去写代码,不可能的,别想了 ~^o^~)
废话不多说,看代码,如何通过编写更少的代码去实现下载excel表功能
给你们看一下公司的一个controller层源代码,只贴了两个映射method功能(我没改动前):
@GetMapping("/UserExcelDownloads")
public void downloadAllClassmate(HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("信息表");
sheet.setColumnWidth(0, 3766);
sheet.setColumnWidth(1, 3766);
sheet.setColumnWidth(2, 3766);
sheet.setColumnWidth(3, 3766);
sheet.setColumnWidth(4, 3766);
sheet.setColumnWidth(5, 3766);
sheet.setColumnWidth(6, 3766);
sheet.setColumnWidth(7, 3766);
sheet.setColumnWidth(8, 3766);
List<Map> classmateList = saleService.teacherinfor();
String fileName = URLEncoder.encode("入库单Excel表");//设置要导出的文件的名字
//新增数据行,并且设置单元格数据
int rowNum = 1;
String[] headers = {"", "投售人姓名", "单据类型", "未验货数量", "已验货数量", "净重", "应付金额", "已付金额", "剩余金额"};
//headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
//在excel表中添加表头
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//在表中存放查询到的数据放入对应的列
for (Map teacher : classmateList) {
HSSFRow row1 = sheet.createRow(rowNum);
row1.createCell(0).setCellValue(rowNum);
row1.createCell(1).setCellValue(Util.Object2String(teacher.get("name")));
row1.createCell(2).setCellValue(Util.Object2String(teacher.get("bill_type")));
row1.createCell(3).setCellValue(Util.Object2String(teacher.get("not")));
row1.createCell(4).setCellValue(Util.Object2String(teacher.get("one")));
row1.createCell(5).setCellValue(Util.Object2String(teacher.get("check_weight")));
row1.createCell(6).setCellValue(Util.Object2String(teacher.get("money")));
row1.createCell(7).setCellValue(Util.Object2String(teacher.get("payment_amount")));
row1.createCell(8).setCellValue(Util.Object2String(teacher.get("alwaysmoney")));
rowNum++;
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String("入库单Excel表") + ".xls");//fileName
response.flushBuffer();
workbook.write(response.getOutputStream());
}
@GetMapping("/dispatchSurplusExcel")
public void download(HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("信息表");
sheet.setColumnWidth(0, 3766);
sheet.setColumnWidth(1, 3766);
sheet.setColumnWidth(2, 3766);
sheet.setColumnWidth(3, 3766);
sheet.setColumnWidth(4, 3766);
sheet.setColumnWidth(5, 3766);
List<Map> classmateList = dispatchService.dispatchSurplusExcelList();
//设置要导出的文件的名字
String fileName = "汇款单Excel表";
//新增数据行,并且设置单元格数据
int rowNum = 1;
String[] headers = {"", "投售人名称", "汇款单据号", "汇款时间", "投售人预付金额", "剩余金额"};
//headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
//在excel表中添加表头
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//在表中存放查询到的数据放入对应的列
for (Map teacher : classmateList) {
HSSFRow row1 = sheet.createRow(rowNum);
row1.createCell(0).setCellValue(rowNum);
row1.createCell(1).setCellValue(Util.Object2String(teacher.get("seller_name")));
row1.createCell(2).setCellValue(Util.Object2String(teacher.get("surpius_id")));
row1.createCell(3).setCellValue(Util.Object2String(teacher.get("surplus_time")));
row1.createCell(4).setCellValue(Util.Object2String(teacher.get("seller_prepaid_money")));
row1.createCell(5).setCellValue(Util.Object2String(teacher.get("surplus_noney")));
rowNum++;
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");
response.flushBuffer();
workbook.write(response.getOutputStream());
}
这样的代码可见是非常不美观的,代码耦合度高,重复代码多,对程序员开发来说,是非常耗时耗力的,于是我就花了一些功夫把他提取成一个工具类
/**
* @author hz
* @Description: 封装 excel文件导出
* @create 2019-07-05
*/
public class ExcelUtil {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
/**
*
* @param flag true 第一列数据为数字序号,false 第一列为正常数据显示
* @param workbook HSSFWorkbook对象
* @param fileName 下载后文件名称
* @param sheetName 工作簿sheet表名称 null为默认名称“sheet0”
* @param sheetCount 表列数总合
* @param headers 表第一列 标题名称数组
* @param list 表需要填入数据对象
* @param tableNames 根据标题名称一一对应数据库映射字段(为了从map.get(key)取值)
* @param response
* @return
*/
public static boolean downLoadExcel(boolean flag,HSSFWorkbook workbook,String fileName, String sheetName, int sheetCount, String headers[],List<Map> list,String tableNames[],HttpServletResponse response)
throws Exception{
//第一列为数字序号
if(flag){
if(sheetCount!=headers.length || sheetCount-1 != tableNames.length){
log.info("第一列为序号时,sheetCount参数和headers数组长度一致,且比tableNames数组长度大1,请核对代码");
System.out.println("请核对sheet长度和headers,tableNames");
return false;
}
}
//第一列为正常数据,不为数字
if(flag==false){
if(sheetCount!=headers.length || sheetCount != tableNames.length){
log.info("sheetCount参数和headers数组tableNames数组长度应一致,请核对代码");
System.out.println("请核对sheet长度,headers,tableNames");
return false;
}
}
int rowNum = 1;
HSSFSheet sheet = null;
if(sheetName==null){
sheet = workbook.createSheet();//默认名称 “sheet0”
}else{
sheet = workbook.createSheet(sheetName);
}
for(int i=0;i<sheetCount;i++){
sheet.setColumnWidth(i, 3766);
}
HSSFRow row = sheet.createRow(0);//设置第一行所有标题字段
//表格每列所有字段名称赋值
for (int i = 0; i < headers.length; i++) {
row.createCell(i).setCellValue(new HSSFRichTextString(headers[i]));
}
for (Map map : list) {
HSSFRow row1 = sheet.createRow(rowNum);
if(flag){
for(int i=0;i<headers.length;i++){
if(i==0){
row1.createCell(i).setCellValue(rowNum);
continue;
}
row1.createCell(i).setCellValue(Util.Object2String(map.get(tableNames[i-1])));
}
}else{
for(int i=0;i<headers.length;i++){
row1.createCell(i).setCellValue(Util.Object2String(map.get(tableNames[i])));
}
}
rowNum++;
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");
response.flushBuffer();
workbook.write(response.getOutputStream());
return true;
}
}
}
@Controller
public class TestController {
private static final Logger log = LoggerFactory.getLogger(TestController .class);
@RequestMapping("/downLoadExcel")
public void downLoadExcel(HttpServletResponse response){
HSSFWorkbook workbook = new HSSFWorkbook();
int rowNum = 1;
String headers[] = {"","姓名", "性别", "年龄"};
String tableNames[]={"name","sex","age"};
//在表中存放查询到的数据放入对应的列
List<Map> list = new ArrayList<Map>();
Map map = new HashMap();
map.put("name", "黄先生");
map.put("sex", "男");
map.put("age", "23");
list.add(map);
try{
Boolean flag = ExcelUtil.downLoadExcel(true,workbook, "用户表","信息表", 4, headers,list,tableNames,response);
}catch (Exception e){
e.printStackTrace();
log.info("downLoadExcel()方法中ExcelUtil.downLoadExcel()出现异常");
}
}
}
一个简单的excel下载工具类就这样封装好了。果然看起来舒服多了。哈哈哈哈