SpringMVC 实现POI读取Excle文件中数据导入数据库（上传）、导出数据库中数据到Excle文件中（下载）
读取Excale表返回一个集合：
package com.shiliu.game.utils; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; 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; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.commons.CommonsMultipartFile; /** * SpringMVC 读取Excle工具类 * @author otowa * @Date 2016-12-04 14:57 * * @param <T> */ public class LeadingInExcel<T> { //log4j输出 private Logger logger = Logger.getLogger(this.getClass()); // 时间的格式 private String format="yyyy-MM-dd"; /** * 无参构造 */ public LeadingInExcel() { super(); } /** * 构造设置显示时间的格式 * @param format 例："yyyy-MM-dd" */ public LeadingInExcel(String format) { super(); this.format = format; } /** * 设置显示时间的格式 * @param format 例："yyyy-MM-dd" */ public void setFormat(String format) { this.format = format; } /** * 上传Excle文件、并读取其中数据、返回list数据集合 * @param multipart * @param propertiesFileName properties文件名称 * @param kyeName properties文件中上传存储文件的路径 * @param sheetIndex 读取Excle中的第几页中的数据 * @param titleAndAttribute 标题名与实体类属性名对应的Map集合 * @param clazz 实体类.class * @return 返回读取出的List集合 * @throws Exception */ public List<T> uploadAndRead(MultipartFile multipart,String propertiesFileName, String kyeName,int sheetIndex, Map<String, String> titleAndAttribute,Class<T> clazz) throws Exception{ String originalFilename=null; int i = 0; boolean isExcel2003 = false; //取出文件名称 originalFilename = multipart.getOriginalFilename(); //判断Excel是什么版本 i = isExcleVersion(originalFilename); if(i==0)return null; else if(i==1)isExcel2003=true; String filePath = readPropertiesFilePathMethod( propertiesFileName, kyeName); File filePathname = this.upload(multipart, filePath, isExcel2003); List<T> judgementVersion = judgementVersion(filePathname, sheetIndex, titleAndAttribute, clazz, isExcel2003); return judgementVersion; } /** * @描述：判断Excel是什么版本 * @param originalFilename * @return * 1 ：2003 * 2 ：2007 * 0 ：不是Excle版本 */ public int isExcleVersion(String originalFilename){ int i = 0; if(originalFilename.matches("^.+\\.(?i)(xls)$"))i = 1; else if(originalFilename.matches("^.+\\.(?i)(xlsx)$"))i = 2; return i; } /** * 读取properties文件中对应键的值 * @param propertiesFileName * @param kyeName * @return value值 */ public String readPropertiesFilePathMethod(String propertiesFileName, String kyeName){ //读取properties文件 InputStream inputStream=null; Properties properties=null; String filePath=null;//读取出的文件路径 try { inputStream= new FileInputStream(this.getClass().getClassLoader().getResource("/"+propertiesFileName+".properties").getPath()); properties=new Properties(); properties.load(inputStream); filePath = properties.getProperty(kyeName); } catch (FileNotFoundException e1) { logger.error("未找到properties文件！", e1); } catch (IOException e1) { logger.error("打开文件流异常！", e1); } finally{ //关闭流 if(inputStream!=null){ try { inputStream.close(); } catch (IOException e) { logger.error("关闭文件流异常！", e); } } } return filePath; } /** * SpringMVC 上传Excle文件至本地 * @param multipart * @param filePath 上传至本地的文件路径 例：D:\\fileupload * @param isExcel2003 是否是2003版本的Excle文件 * @return 返回上传文件的全路径 * @throws Exception */ public File upload(MultipartFile multipart,String filePath,boolean isExcel2003) throws Exception{ //文件后缀 String extension=".xlsx"; if(isExcel2003)extension=".xls"; //指定上传文件的存储路径 File file=new File(filePath); //接口强转实现类 CommonsMultipartFile commons=(CommonsMultipartFile) multipart; //判断所属路径是否存在、不存在新建 if(file.exists())file.mkdirs(); /* * 新建一个文件 * LongIdWorker longID工具类 */ File filePathname=new File(file+File.separator+LongIdWorker.getDataId()+extension); //将上传的Excel写入新建的文件中 try { commons.getFileItem().write(filePathname); } catch (Exception e) { logger.error("写入文件异常", e); } return filePathname; } /** * 读取本地Excel文件返回List集合 * @param filePathname * @param sheetIndex * @param titleAndAttribute * @param clazz * @param isExcel2003 * @return * @throws Exception */ public List<T> judgementVersion(File filePathname,int sheetIndex,Map<String, String> titleAndAttribute,Class<T> clazz,boolean isExcel2003) throws Exception{ FileInputStream is=null; POIFSFileSystem fs=null; Workbook workbook=null; try { //打开流 is=new FileInputStream(filePathname); if(isExcel2003){ //把excel文件作为数据流来进行传入传出 fs=new POIFSFileSystem(is); //解析Excel 2003版 workbook = new HSSFWorkbook(fs); }else{ //解析Excel 2007版 workbook=new XSSFWorkbook(is); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } return readExcelTitle(workbook,sheetIndex,titleAndAttribute,clazz); } /** * 判断接收的Map集合中的标题是否于Excle中标题对应 * @param workbook * @param sheetIndex * @param titleAndAttribute * @param clazz * @return * @throws Exception */ private List<T> readExcelTitle(Workbook workbook,int sheetIndex,Map<String, String> titleAndAttribute,Class<T> clazz) throws Exception{ //得到第一个shell Sheet sheet = workbook.getSheetAt(sheetIndex); // 获取标题 Row titelRow = sheet.getRow(0); Map<Integer, String> attribute = new HashMap<Integer, String>(); if (titleAndAttribute != null) { for (int columnIndex = 0; columnIndex < titelRow.getLastCellNum(); columnIndex++) { Cell cell = titelRow.getCell(columnIndex); if (cell != null) { String key = cell.getStringCellValue(); String value = titleAndAttribute.get(key); if (value == null) { value = key; } attribute.put(Integer.valueOf(columnIndex), value); } } } else { for (int columnIndex = 0; columnIndex < titelRow.getLastCellNum(); columnIndex++) { Cell cell = titelRow.getCell(columnIndex); if (cell != null) { String key = cell.getStringCellValue(); attribute.put(Integer.valueOf(columnIndex), key); } } } return readExcelValue(workbook,sheet,attribute,clazz); } /** * 获取Excle中的值 * @param workbook * @param sheet * @param attribute * @param clazz * @return * @throws Exception */ private List<T> readExcelValue(Workbook workbook,Sheet sheet,Map<Integer, String> attribute,Class<T> clazz) throws Exception{ List<T> info=new ArrayList<T>(); //获取标题行列数 int titleCellNum = sheet.getRow(0).getLastCellNum(); // 获取值 for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { Row row = sheet.getRow(rowIndex); // logger.debug("第--" + rowIndex); // 1.若当前行的列数不等于标题行列数就放弃整行数据(若想放弃此功能注释4个步骤即可) int lastCellNum = row.getLastCellNum(); if(titleCellNum != lastCellNum){ continue; } // 2.标记 boolean judge = true; T obj = clazz.newInstance(); for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {//这里小于等于变成小于 Cell cell = row.getCell(columnIndex); //处理单元格中值得类型 String value = getCellValue(cell); // 3.单元格中的值等于null或等于"" 就放弃整行数据 if(value == null || "".equals(value)){ judge = false; break; } /* * 测试：查看自定义的title Map集合中定义的Excle标题和实体类中属性对应情况！ System.out.println("c:"+columnIndex+"\t"+attribute.get(Integer.valueOf(columnIndex))); */ Field field = clazz.getDeclaredField(attribute.get(Integer .valueOf(columnIndex))); Class<?> fieldType = field.getType(); Object agge = null; if (fieldType.isAssignableFrom(Integer.class)) { agge = Integer.valueOf(value); } else if (fieldType.isAssignableFrom(Double.class)) { agge = Double.valueOf(value); } else if (fieldType.isAssignableFrom(Float.class)) { agge = Float.valueOf(value); } else if (fieldType.isAssignableFrom(Long.class)) { agge = Long.valueOf(value); } else if (fieldType.isAssignableFrom(Date.class)) { agge = new SimpleDateFormat(format).parse(value); } else if (fieldType.isAssignableFrom(Boolean.class)) { agge = "Y".equals(value) || "1".equals(value); } else if (fieldType.isAssignableFrom(String.class)) { agge = value; } // 个人感觉char跟byte就不用判断了 用这两个类型的很少如果是从数据库用IDE生成的话就不会出现了 Method method = clazz.getMethod("set" + toUpperFirstCase(attribute.get(Integer .valueOf(columnIndex))), fieldType); method.invoke(obj, agge); } // 4. if if(judge)info.add(obj); } return info; } /** * @ 首字母大写 */ private String toUpperFirstCase(String str) { return str.replaceFirst(str.substring(0, 1), str.substring(0, 1) .toUpperCase()); } /** * 功能:处理单元格中值得类型 * @param cell * @return */ private String getCellValue(Cell cell) { Object result = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: //判断是是日期型，转换日期格式，否则转换数字格式。 if(DateUtil.isCellDateFormatted(cell)){ Date dateCellValue = cell.getDateCellValue(); if(dateCellValue != null){ result = new SimpleDateFormat(this.format).format(dateCellValue); }else{ result=""; } }else{ result = new DecimalFormat("0").format(cell.getNumericCellValue()); }; break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: /* * 导入时如果为公式生成的数据则无值 * if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } else { value = cell.getNumericCellValue() + ""; } */ result = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: result = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_BLANK: break; default: break; } } return result.toString(); } }
使用例子（项目代码只能模仿）：
/** * 读取Excel中的用户信息插入数据库 * @param multipart * @param session * @return */ @RequestMapping(value="/batchimport") @ResponseBody public String batchImportMethod( @RequestParam(value="gameId") String gameId, @RequestParam(value="filename") MultipartFile multipart ){ //局部变量 LeadingInExcel<UserWhiteList> testExcel=null; List<UserWhiteList> uploadAndRead=null; boolean judgement = false; String Msg =null; String error = ""; //定义需要读取的数据 String formart = "yyyy-MM-dd"; String propertiesFileName = "config"; String kyeName = "file_path"; int sheetIndex = 0; Map<String, String> titleAndAttribute=null; Class<UserWhiteList> clazz=UserWhiteList.class; //定义对应的标题名与对应属性名 titleAndAttribute=new HashMap<String, String>(); titleAndAttribute.put("手机号码", "phone"); titleAndAttribute.put("总抽奖次数", "playtimes"); //调用解析工具包 testExcel=new LeadingInExcel<UserWhiteList>(formart); //解析excel，获取客户信息集合 try { uploadAndRead = testExcel.uploadAndRead(multipart, propertiesFileName, kyeName, sheetIndex, titleAndAttribute, clazz); } catch (Exception e) { log.error("读取Excel文件错误！",e); } if(uploadAndRead != null && !"[]".equals(uploadAndRead.toString()) && uploadAndRead.size()>=1){ judgement = true; } if(judgement){ //把客户信息分为没100条数据为一组迭代添加客户信息（注：将customerList集合作为参数，在Mybatis的相应映射文件中使用foreach标签进行批量添加。） //int count=0; int listSize=uploadAndRead.size(); int toIndex=100; for (int i = 0; i < listSize; i+=100) { if(i+100>listSize){ toIndex=listSize-i; } List<UserWhiteList> subList = uploadAndRead.subList(i, i+toIndex); /* * 测试数据： count=count+subList.size(); System.out.println("subList长度："+subList.size()+"\t总长度："+count); * for (UserJHDX userJHDX : subList) { System.out.println("手机号："+userJHDX.getPhone()+"截止日期:"+userJHDX.getUptodate()+"流量值"+userJHDX.getFlux()+"总次数"+userJHDX.getTotal()); } */ /** 此处执行集合添加 */ userWhiteListService.batchInport(subList, gameId); } Msg ="批量导入EXCEL成功！"; }else{ Msg ="批量导入EXCEL失败！"; } String res = "{ error:'" + error + "', msg:'" + Msg + "'}"; return res; }
读取一个集合输出Excle:
package com.shiliu.game.utils; import java.io.OutputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; /** * 导出Excel公共方法 * * @author wkr * */ public class LeadingOutExcel { //导出文件的名字 private String fileName; //显示的导出表的标题 private String title; //导出表的列名 private String[] rowName; private List<Object[]> dataList = new ArrayList<Object[]>(); private HttpServletResponse response = null; private HSSFWorkbook workbook = null; private OutputStream out = null; //构造方法，传入要导出的数据 public LeadingOutExcel(String fileName,String title,String[] rowName,List<Object[]> dataList,HttpServletResponse response){ this.fileName = fileName; this.dataList = dataList; this.rowName = rowName; this.title = title; this.response = response; } public void export () throws Exception{ HSSFWorkbook createExcel = this.createExcel(); this.writeInOutputStream(createExcel); } /* * 导出数据 * */ public HSSFWorkbook createExcel() throws Exception{ try{ workbook = new HSSFWorkbook(); // 创建工作簿对象 HSSFSheet sheet = workbook.createSheet(title); // 创建工作表 /* * 产生表格标题行 HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0);//设置开头两行 * 使用条件： * HSSFRow rowRowName = sheet.createRow(0);设置索引2的位置创建行 * HSSFRow row = sheet.createRow(i+1);//创建所需的行数 改为i+3 * */ //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象 HSSFCellStyle style = this.getStyle(workbook); //单元格样式对象 /* * 产生表格标题行 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1))); //设置开头两行合并 cellTiltle.setCellStyle(columnTopStyle); //设置列头单元格样式 cellTiltle.setCellValue("");//设置空单元格的内容。比如传入List的值是空的。 */ // 定义所需列数 int columnNum = rowName.length; HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行) // 将列头设置到sheet的单元格中 for(int n=0;n<columnNum;n++){ HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型 HSSFRichTextString text = new HSSFRichTextString(rowName[n]); cellRowName.setCellValue(text); //设置列头单元格的值 cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式 } //将查询出的数据设置到sheet对应的单元格中 for(int i=0;i<dataList.size();i++){ Object[] obj = dataList.get(i);//遍历每个对象 HSSFRow row = sheet.createRow(i+1);//创建所需的行数 for(int j=0; j<obj.length; j++){ HSSFCell cell = null; //设置单元格的数据类型 if(j == 0){ cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(i+1); }else{ cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING); if(!"".equals(obj[j]) && obj[j] != null){ cell.setCellValue(obj[j].toString()); //设置单元格的值 } } cell.setCellStyle(style); //设置单元格样式 } } //让列宽随着导出的列长自动适应 for (int colNum = 0; colNum < columnNum; colNum++) { int columnWidth = sheet.getColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { HSSFRow currentRow; //当前行未被使用过 if (sheet.getRow(rowNum) == null) { currentRow = sheet.createRow(rowNum); } else { currentRow = sheet.getRow(rowNum); } if (currentRow.getCell(colNum) != null) { HSSFCell currentCell = currentRow.getCell(colNum); if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { //int length = currentCell.getStringCellValue().getBytes().length; String stringCellValue = null; try { stringCellValue = currentCell.getStringCellValue(); } catch (Exception e) { continue; } int length = stringCellValue==null?50:stringCellValue.getBytes().length; if (columnWidth < length) { columnWidth = length; } } } } if(colNum == 0){ sheet.setColumnWidth(colNum, (columnWidth-2) * 256); }else{ sheet.setColumnWidth(colNum, (columnWidth+4) * 256); } } }catch(Exception e){ e.printStackTrace(); } return workbook; } public void writeInOutputStream(HSSFWorkbook workbook) throws Exception{ //设置响应类型、与头信息 response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName+".xls", "UTF-8")); out = response.getOutputStream(); workbook.write(out); //清除资源 out.close(); } /* * 列头单元格样式 */ public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short)11); //字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体名字 font.setFontName("Courier New"); //设置样式; HSSFCellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } /* * 列数据信息单元格样式 */ public HSSFCellStyle getStyle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); //设置字体大小 //font.setFontHeightInPoints((short)10); //字体加粗 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体名字 font.setFontName("Courier New"); //设置样式; HSSFCellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } } package com.shiliu.game.utils; import java.io.OutputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; /** * 导出Excel公共方法 * * @author wkr * */ public class LeadingOutExcel { //导出文件的名字 private String fileName; //显示的导出表的标题 private String title; //导出表的列名 private String[] rowName; private List<Object[]> dataList = new ArrayList<Object[]>(); private HttpServletResponse response = null; private HSSFWorkbook workbook = null; private OutputStream out = null; //构造方法，传入要导出的数据 public LeadingOutExcel(String fileName,String title,String[] rowName,List<Object[]> dataList,HttpServletResponse response){ this.fileName = fileName; this.dataList = dataList; this.rowName = rowName; this.title = title; this.response = response; } public void export () throws Exception{ HSSFWorkbook createExcel = this.createExcel(); this.writeInOutputStream(createExcel); } /* * 导出数据 * */ public HSSFWorkbook createExcel() throws Exception{ try{ workbook = new HSSFWorkbook(); // 创建工作簿对象 HSSFSheet sheet = workbook.createSheet(title); // 创建工作表 /* * 产生表格标题行 HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0);//设置开头两行 * 使用条件： * HSSFRow rowRowName = sheet.createRow(0);设置索引2的位置创建行 * HSSFRow row = sheet.createRow(i+1);//创建所需的行数 改为i+3 * */ //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象 HSSFCellStyle style = this.getStyle(workbook); //单元格样式对象 /* * 产生表格标题行 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1))); //设置开头两行合并 cellTiltle.setCellStyle(columnTopStyle); //设置列头单元格样式 cellTiltle.setCellValue("");//设置空单元格的内容。比如传入List的值是空的。 */ // 定义所需列数 int columnNum = rowName.length; HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行) // 将列头设置到sheet的单元格中 for(int n=0;n<columnNum;n++){ HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型 HSSFRichTextString text = new HSSFRichTextString(rowName[n]); cellRowName.setCellValue(text); //设置列头单元格的值 cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式 } //将查询出的数据设置到sheet对应的单元格中 for(int i=0;i<dataList.size();i++){ Object[] obj = dataList.get(i);//遍历每个对象 HSSFRow row = sheet.createRow(i+1);//创建所需的行数 for(int j=0; j<obj.length; j++){ HSSFCell cell = null; //设置单元格的数据类型 if(j == 0){ cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(i+1); }else{ cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING); if(!"".equals(obj[j]) && obj[j] != null){ cell.setCellValue(obj[j].toString()); //设置单元格的值 } } cell.setCellStyle(style); //设置单元格样式 } } //让列宽随着导出的列长自动适应 for (int colNum = 0; colNum < columnNum; colNum++) { int columnWidth = sheet.getColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { HSSFRow currentRow; //当前行未被使用过 if (sheet.getRow(rowNum) == null) { currentRow = sheet.createRow(rowNum); } else { currentRow = sheet.getRow(rowNum); } if (currentRow.getCell(colNum) != null) { HSSFCell currentCell = currentRow.getCell(colNum); if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { //int length = currentCell.getStringCellValue().getBytes().length; String stringCellValue = null; try { stringCellValue = currentCell.getStringCellValue(); } catch (Exception e) { continue; } int length = stringCellValue==null?50:stringCellValue.getBytes().length; if (columnWidth < length) { columnWidth = length; } } } } if(colNum == 0){ sheet.setColumnWidth(colNum, (columnWidth-2) * 256); }else{ sheet.setColumnWidth(colNum, (columnWidth+4) * 256); } } }catch(Exception e){ e.printStackTrace(); } return workbook; } public void writeInOutputStream(HSSFWorkbook workbook) throws Exception{ //设置响应类型、与头信息 response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName+".xls", "UTF-8")); out = response.getOutputStream(); workbook.write(out); //清除资源 out.close(); } /* * 列头单元格样式 */ public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short)11); //字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体名字 font.setFontName("Courier New"); //设置样式; HSSFCellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } /* * 列数据信息单元格样式 */ public HSSFCellStyle getStyle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); //设置字体大小 //font.setFontHeightInPoints((short)10); //字体加粗 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体名字 font.setFontName("Courier New"); //设置样式; HSSFCellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } }
使用例子（项目代码只能模仿）：
/** * 条件导出 * @param gameId * @param startDate * @param endDate * @param level * @param response */ @RequestMapping(value="/conditionalExportDate") public void conditionalExportDateMethod( @RequestParam(value="gameId") String gameId, @RequestParam(value="startDate") String startDate, @RequestParam(value="endDate") String endDate, @RequestParam(value="level") String level, HttpServletResponse response ){ Map<String, Object> conditionMap = null; List<PlayRecord> dataSet = null; LeadingOutExcel leadingOutExcel = null; //工具类 //配置信息 String fileName = "client"; String format = "yyyy-MM-dd hh:mm:ss"; String title = "用户信息"; String[] rowName = { "编号","微信号","微信名称", "手机号", "参与活动时间","中奖等级", "获得奖品" }; //查询条件 conditionMap = new HashMap<String, Object>(); conditionMap.put("gameId", gameId); //导出全部 if(!"请选择日期".equals(startDate) && !"请选择日期".equals(endDate)){ conditionMap.put("startDate", startDate); conditionMap.put("endDate", endDate); } if(!"请输入中奖等级".equals(level)){ conditionMap.put("level", level); } dataSet = playRecordService.conditionQuery(conditionMap); List<Object[]> dataList = new ArrayList<Object[]>(); Object[] objs = null; for (int i = 0; i < dataSet.size(); i++) { PlayRecord man = dataSet.get(i); objs = new Object[rowName.length]; objs[0] = i; objs[1] = man.getOpenid(); objs[2] = man.getNickName(); objs[3] = man.getPhoneNumber(); //日期类型处理 Date date = man.getPalyTime(); String dateStr = ""; if(date!=null){ SimpleDateFormat df = new SimpleDateFormat(format); dateStr = df.format(date); } objs[4] = dateStr; objs[5] = man.getLevel(); objs[6] = man.getAwardName(); dataList.add(objs); } leadingOutExcel = new LeadingOutExcel(fileName,title, rowName, dataList,response); try { leadingOutExcel.export(); } catch (Exception e) { log.error("写入Excle出错！", e); } } }
原创：意艺
