package com.element.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtils {
public static void exportBom(HttpServletRequest request, HttpServletResponse response, List<Map<String,Object>> list,String sjq,String sjz) throws IOException, ParseException {
//日期格式化
SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
//获取项目根目录
String path= request.getSession().getServletContext().getRealPath("/");
InputStream in = null;
HSSFWorkbook webBook = null;
//读取模板文件
in = new FileInputStream(path+"zzsb/ccdqdcmb.xls");
webBook = new HSSFWorkbook(in);
//得到sheet
Sheet sheet = webBook.getSheetAt(0);
//调用样式方法
sheet.setColumnWidth(0, 3766);//列宽
sheet.setColumnWidth(1, 4766);//列宽
//开始操作模板,找到某行某列(某个cell),需要注意的是这里有个坑,行和列的计数都是从0开始的
//一次数据插入的位置不对,别灰心,多试几次就好啦,你要是能看懂我下面的代码,数据插在了什么位置,你就明白了
//打印时间
//从第几行开始
int rows = 2;
Row row = sheet.getRow(rows);
for (int i = 0; i < list.size(); i++) {
//创建行
row = sheet.createRow(rows++);
//填充数据
row.createCell(0).setCellValue(list.get(i).get("num")==null?"":list.get(i).get("num").toString());
row.createCell(1).setCellValue(list.get(i).get("xh")==null?"":list.get(i).get("xh").toString());
row.createCell(2).setCellValue(list.get(i).get("qymc")==null?"":list.get(i).get("qymc").toString());
row.createCell(3).setCellValue(list.get(i).get("fr")==null?"":list.get(i).get("fr").toString());
row.createCell(4).setCellValue(list.get(i).get("zzdj")==null?"":list.get(i).get("zzdj").toString());
row.createCell(5).setCellValue(list.get(i).get("zzzsbh")==null?"":list.get(i).get("zzzsbh").toString());
row.createCell(6).setCellValue(list.get(i).get("zczb")==null?"":list.get(i).get("zczb").toString());
row.createCell(7).setCellValue(list.get(i).get("zsyxrq")==null?"":list.get(i).get("zsyxrq").toString());
row.createCell(8).setCellValue(list.get(i).get("bgdz")==null?"":list.get(i).get("bgdz").toString());
row.createCell(9).setCellValue(list.get(i).get("clsj")==null?"":list.get(i).get("clsj").toString());
row.createCell(10).setCellValue(list.get(i).get("qyfrdh")==null?"":list.get(i).get("qyfrdh").toString());
row.createCell(11).setCellValue(list.get(i).get("qylxrdh")==null?"":list.get(i).get("qylxrdh").toString());
row.createCell(12).setCellValue(list.get(i).get("qysbsx")==null?"":list.get(i).get("qysbsx").toString());
row.createCell(13).setCellValue(list.get(i).get("sbrq")==null?"":format1.format(format1.parse(list.get(i).get("sbrq").toString())));
row.createCell(14).setCellValue(list.get(i).get("dfzsrq")==null?"":format1.format(format1.parse(list.get(i).get("dfzsrq").toString())));
row.createCell(15).setCellValue(list.get(i).get("stbjrq")==null?"":format1.format(format1.parse(list.get(i).get("stbjrq").toString())));
}
System.out.println(format1.parse(sjq));
System.out.println(format.format(format1.parse(sjq)));
//最后一个参数是文件名
writeExcel(response, webBook, "长春地区"+format.format(format1.parse(sjq))+"_"+format.format(format1.parse(sjz)));
}
private static XSSFWorkbook readExcel(String filePath) {
InputStream in = null;
XSSFWorkbook work = null;
try {
in = new FileInputStream(filePath);
work = new XSSFWorkbook(in);
} catch (FileNotFoundException e) {
System.out.println("文件路径错误");
e.printStackTrace();
} catch (IOException e) {
System.out.println("文件输入流错误");
e.printStackTrace();
}
return work;
}
private static void writeExcel(HttpServletResponse response, Workbook work, String fileName) throws IOException {
OutputStream out = null;
try {
out = response.getOutputStream();
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(fileName + ".xls", "UTF-8"))));
work.write(out);
} catch (IOException e) {
System.out.println("输出流错误");
e.printStackTrace();
} finally {
out.close();
}
}
}
poi读取excel模板导出
最新推荐文章于 2024-09-03 20:52:56 发布