SSM之poi导出
先导入poi包
实体类
导出的公共类ExcelUtil
import java.beans.IntrospectionException;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
/**
* 导出Excel表
* @param clazz 数据源model类型
* @param objs excel标题以及对应的model字段
* @param map 标题行数以及cell字体样式
* @param sheetName 工作簿名称
* @return
* @throws IntrospectionException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public static XSSFWorkbook createExcelFile(
Class<?> clazz,
List<Map<String,Object>> objs,
Map<Integer,List<ExcelBean>> map,
String sheetName) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, IntrospectionException{
//创建新的工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = workbook.createSheet(sheetName);
//设置excel的字体样式以及标题与内容的创建
createFont(workbook);//字体样式
createTableHeader(sheet,map);//创建标题
createTableRows(sheet,map,objs,clazz);//创建内容
System.out.println(workbook);
return workbook;
}
private static XSSFCellStyle fontStyle;
private static XSSFCellStyle fontStyle2;
private static void createFont(XSSFWorkbook workbook) {
//表头
fontStyle = workbook.createCellStyle();
XSSFFont font1 = workbook.createFont();
font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font1.setFontName("黑体");
font1.setFontHeightInPoints((short) 12);//字体大小
fontStyle.setFont(font1);
fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);//下边框
fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//右边框
fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//居中
//内容
fontStyle2 = workbook.createCellStyle();
XSSFFont font2 = workbook.createFont();
font2.setFontName("宋体");
font2.setFontHeightInPoints((short)10);
fontStyle2.setFont(font2);
fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN);//下边框
fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);//右边框
fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER);//居中
}
/**
* 根据ExcelMapping 生成列头(多行列头)
* @param sheet 工作簿
* @param map 每行每个单元格对应的列头信息
*/
private static void createTableHeader(
XSSFSheet sheet,
Map<Integer, List<ExcelBean>> map) {
int startIndex = 0;//cell起始位置
int endIndex = 0;//cell终止位置
for(Map.Entry<Integer,List<ExcelBean>> entry: map.entrySet()){
XSSFRow row = sheet.createRow(entry.getKey()); //创建行
List<ExcelBean> excels = entry.getValue();
for(int x=0;x<excels.size();x++){
//合并单元格
if(excels.get(x).getCols()>1){
if(x==0){
endIndex += excels.get(x).getCols()-1;
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, startIndex, endIndex));
startIndex += excels.get(x).getCols();
}else{
endIndex += excels.get(x).getCols();
sheet.addMergedRegion(new CellRangeAddress(0, 0, startIndex, endIndex));
startIndex += excels.get(x).getCols();
}
XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
//设置内容
cell.setCellValue(excels.get(x).getHeadTextName());
if(excels.get(x).getCellStyle() != null){
//设置格式
cell.setCellStyle(excels.get(x).getCellStyle());
}
cell.setCellStyle(fontStyle);
}else{
XSSFCell cell = row.createCell(x);
//设置内容
cell.setCellValue(excels.get(x).getHeadTextName());
if(excels.get(x).getCellStyle() != null){
//设置格式
cell.setCellStyle(excels.get(x).getCellStyle());
}
cell.setCellStyle(fontStyle);
}
}
}
}
/**
* 为excel表中循环添加数据
* @param sheet
* @param map 字段名
* @param objs 查询的数据
* @param clazz 无用
*/
private static void createTableRows(
XSSFSheet sheet,
Map<Integer,List<ExcelBean>> map,
List<Map<String,Object>> objs,
Class<?> clazz)
throws IntrospectionException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
int rowindex = map.size();
int maxkey = 0;
List<ExcelBean> ems = new ArrayList<>();
for(Map.Entry<Integer,List<ExcelBean>> entry : map.entrySet()){
if(entry.getKey() > maxkey){
maxkey = entry.getKey();
}
}
ems = map.get(maxkey);
List<Integer> widths = new ArrayList<Integer>(ems.size());
for(Map<String,Object> obj : objs){
XSSFRow row = sheet.createRow(rowindex);
for(int i=0;i<ems.size();i++){
ExcelBean em = (ExcelBean)ems.get(i);
String propertyName = em.getPropertyName();
Object value = obj.get(propertyName);
XSSFCell cell = row.createCell(i);
String cellValue = "";
if("valid".equals(propertyName)){
cellValue = value.equals(1)?"启用":"禁用";
}else if(value==null){
cellValue = "";
}else if(value instanceof Date){
cellValue = new SimpleDateFormat("yyyy-MM-dd").format(value);
}else{
cellValue = value.toString();
}
cell.setCellValue(cellValue);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(fontStyle2);
sheet.autoSizeColumn(i);
}
rowindex++;
}
//设置列宽
for(int index=0;index<widths.size();index++){
Integer width = widths.get(index);
width = width<2500?2500:width+300;
width = width>10000?10000+300:width+300;
sheet.setColumnWidth(index, width);
}
}
}
Dao层
//导出
List<Map<String,Object>> selectYyscInfoBybids(@Param("bids")String bids)throws Exception;
.xml映射
Biz层
/**
* 导出
*/
XSSFWorkbook exportExcelInfoYyscInfo(@Param("bids")String bids) throws Exception;
Service层
/**
* 导出
*/
@Override
public XSSFWorkbook exportExcelInfoYyscInfo(String bids) throws Exception {
// 根据条件查询数据
List<Map<String, Object>> list = yblrDao.selectYyscInfoBybids(bids);
for (Map<String, Object> m : list)
{
for (String k : m.keySet())
{
//判断材料类型
if(k.equals("yblx1")){
if(m.get(k).equals("1")){
m.put("yblx1", "血清");
}
if(m.get(k).equals("2")){
m.put("yblx1", "血细胞");
}
if(m.get(k).equals("3")){
m.put("yblx1", "血浆");
}
if(m.get(k).equals("4")){
m.put("yblx1", "血凝块");
}
}else if(k.equals("yblx2")){
if(m.get(k).equals("1")){
m.put("yblx2", "血清");
}
if(m.get(k).equals("2")){
m.put("yblx2", "血细胞");
}
if(m.get(k).equals("3")){
m.put("yblx2", "血浆");
}
if(m.get(k).equals("4")){
m.put("yblx2", "血凝块");
}
}else if(k.equals("yblx3")){
if(m.get(k).equals("1")){
m.put("yblx3", "血清");
}
if(m.get(k).equals("2")){
m.put("yblx3", "血细胞");
}
if(m.get(k).equals("3")){
m.put("yblx3", "血浆");
}
if(m.get(k).equals("4")){
m.put("yblx3", "血凝块");
}
}else if(k.equals("yblx4")){
if(m.get(k).equals("1")){
m.put("yblx4", "血清");
}
if(m.get(k).equals("2")){
m.put("yblx4", "血细胞");
}
if(m.get(k).equals("3")){
m.put("yblx4", "血浆");
}
if(m.get(k).equals("4")){
m.put("yblx4", "血凝块");
}
}else if(k.equals("yblx5")){
if(m.get(k).equals("1")){
m.put("yblx5", "血清");
}
if(m.get(k).equals("2")){
m.put("yblx5", "血细胞");
}
if(m.get(k).equals("3")){
m.put("yblx5", "血浆");
}
if(m.get(k).equals("4")){
m.put("yblx5", "血凝块");
}
}
//System.out.println(k + " : " + m.get(k));
}
}
// System.out.println(list);
List<ExcelBean> excel = new ArrayList<>();
Map<Integer, List<ExcelBean>> map = new LinkedHashMap<>();
// 设置标题栏
excel.add(new ExcelBean("筛查编号", "bids", 0));
excel.add(new ExcelBean("姓名", "name", 0));
excel.add(new ExcelBean("身份证号", "card_id", 0));
excel.add(new ExcelBean("建档机构", "jg_name", 0));
excel.add(new ExcelBean("检查医院", "hosp_name", 0));
excel.add(new ExcelBean("检查日期", "sc_date", 0));
excel.add(new ExcelBean("01样本编号", "ybbh01", 0));
excel.add(new ExcelBean("01样本类型【血清/血细胞/血浆/血凝块】", "yblx1", 0));
excel.add(new ExcelBean("01样本量", "ybsl01", 0));
excel.add(new ExcelBean("保存地点", "bcdd1", 0));
excel.add(new ExcelBean("保存温度", "bcwd1", 0));
excel.add(new ExcelBean("02样本编号", "ybbh02", 0));
excel.add(new ExcelBean("02样本类型【血清/血细胞/血浆/血凝块】", "yblx2", 0));
excel.add(new ExcelBean("02样本量", "ybsl02", 0));
excel.add(new ExcelBean("保存地点", "bcdd2", 0));
excel.add(new ExcelBean("保存温度", "bcwd2", 0));
excel.add(new ExcelBean("03样本编号", "ybbh03", 0));
excel.add(new ExcelBean("03样本类型【血清/血细胞/血浆/血凝块】", "yblx3", 0));
excel.add(new ExcelBean("03样本量", "ybsl03", 0));
excel.add(new ExcelBean("保存地点", "bcdd3", 0));
excel.add(new ExcelBean("保存温度", "bcwd3", 0));
excel.add(new ExcelBean("04样本编号", "ybbh04", 0));
excel.add(new ExcelBean("04样本类型【血清/血细胞/血浆/血凝块】", "yblx4", 0));
excel.add(new ExcelBean("04样本量", "ybsl04", 0));
excel.add(new ExcelBean("保存地点", "bcdd4", 0));
excel.add(new ExcelBean("保存温度", "bcwd4", 0));
excel.add(new ExcelBean("05样本编号", "ybbh05", 0));
excel.add(new ExcelBean("05样本类型【血清/血细胞/血浆/血凝块】", "yblx5", 0));
excel.add(new ExcelBean("05样本量", "ybsl05", 0));
excel.add(new ExcelBean("保存地点", "bcdd5", 0));
excel.add(new ExcelBean("保存温度", "bcwd5", 0));
map.put(0, excel);
String sheetName = "样本录入表";
// 调用ExcelUtil方法
XSSFWorkbook xssfWorkbook = ExcelUtil.createExcelFile(YyscInfo.class, list, map, sheetName);
System.out.println(xssfWorkbook);
return xssfWorkbook;
}
Controller层
// 导出样本录入表
@RequestMapping("dcyblr")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response,
@RequestParam(value = "bids", required = false) String bids
,Logs logs
,HttpSession session
) throws Exception {
// 连接Zlpt数据库
DynamicDataSourceHolder.setDataSources("dataSourceZlpt");
response.res
et(); // 清除buffer缓存
String filename = "样本录入表.xlsx";
// Map<String,Object> map=new HashMap<String,Object>();
// 指定下载的文件名
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(filename.getBytes(), "iso-8859-1"));
// 导出Excel对象
XSSFWorkbook workbook;
// acId=acId.substring(0,acId.length()-1);
if (bids == null || bids == "") {
workbook = this.ybkrBiz.exportExcelInfoYyscInfo(bids);//appconsumablesBiz.exportExcelInfo(acId);
} else {
workbook = this.ybkrBiz.exportExcelInfoYyscInfo(bids.substring(0, bids.length()-1));//appconsumablesBiz.exportExcelInfo(acId.substring(0, acId.length() - 1));
}
// System.out.println("888888888888888888888888888888888888888"+acId);
OutputStream output;
try {
output = response.getOutputStream();
BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);
bufferedOutput.flush();
workbook.write(bufferedOutput);
bufferedOutput.close();
// 连接MM数据库
DynamicDataSourceHolder.setDataSources("dataSourceMm");
//日志
UserInfo users = (UserInfo) session.getAttribute("users");
logs.setUsername(users.getUserName());
logs.setDwmc(users.getCnName());
logs.setContent("导出了样本信息");
logs.setOperationType("样本信息登记——导出");
this.logsBiz.addLog(logs);
} catch (IOException e) {
e.printStackTrace();
}
}
poi包
链接：https://pan.baidu.com/s/1PAYJtAB3t98QPeuwENl22g
提取码：jn8n