导入/导出Excel利用apache.poi

一、生成Excel

1、导入jar包

maven地址

       <dependency>              <groupId>org.apache.poi</groupId>              <artifactId>poi</artifactId>              <version>3.16</version>          </dependency>  

2、编写ExcelUtil工具类

package com.qn.yun.utils;import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook;public class ExcelUtil {private Workbook wb = null;private Sheet sheet = null;public ExcelUtil(Workbook wb, Sheet sheet) {super();this.wb = wb;this.sheet = sheet;}/** * 设置表头样式 *  * @return */public CellStyle getHeadStyle() {// 创建单元格样式CellStyle cellStyle = wb.createCellStyle();// 设置单元格的背景颜色为淡蓝色cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 设置单元格居中对齐cellStyle.setAlignment(HorizontalAlignment.CENTER);// 设置单元格垂直居中对齐cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 创建单元格内容显示不下时自动换行cellStyle.setWrapText(true);// 设置单元格字体样式Font font = wb.createFont();// 设置字体加粗font.setBold(true);font.setFontName("宋体");font.setFontHeight((short) 200);cellStyle.setFont(font);// 设置单元格边框为细线条cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);return cellStyle;}public void buildTitle(Sheet sheet, String[] titles) {Row headRow = sheet.createRow(0);Cell cell = null;for (int i = 0; i < titles.length; i++) {cell = headRow.createCell(i);cell.setCellStyle(getHeadStyle());cell.setCellValue(titles[i]);}}/** * 设置表体的单元格样式 *  * @return */public CellStyle getBodyStyle() {// 创建单元格样式CellStyle cellStyle = wb.createCellStyle();// 设置单元格居中对齐cellStyle.setAlignment(HorizontalAlignment.CENTER);// 设置单元格垂直居中对齐cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 创建单元格内容显示不下时自动换行cellStyle.setWrapText(true);// 设置单元格字体样式Font font = wb.createFont();// 设置字体加粗font.setBold(true);font.setFontName("宋体");font.setFontHeight((short) 200);cellStyle.setFont(font);// 设置单元格边框为细线条cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);return cellStyle;}public void autoSizeColumnSize(Sheet sheet) {if (sheet == null) {return;}if (sheet.getRow(0) == null) {return;}int colCount = sheet.getRow(0).getPhysicalNumberOfCells();for (int column = 0; column < colCount; column++) {int columnWidth = sheet.getColumnWidth(column) / 256;for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {Row currentRow;if (sheet.getRow(rowNum) == null) {currentRow = sheet.createRow(rowNum);} else {currentRow = sheet.getRow(rowNum);}if (currentRow.getCell(column) != null) {Cell currentCell = currentRow.getCell(column);int length = currentCell.toString().length();if (columnWidth < length) {columnWidth = length;}}}sheet.setColumnWidth(column, (columnWidth * 2) * 256);}}}

3、编写数据内容service

package com.qn.demo;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.junit.Test;import com.qn.yun.utils.ExcelUtil;public class ExcelYunDemo {@Testpublic void outputExcel(){SXSSFWorkbook workbook = new SXSSFWorkbook(100);Sheet sheet = workbook.createSheet("学生内容表");ExcelUtil excelUtil = new ExcelUtil(workbook, sheet);//设计单元格样式CellStyle bodyStyle = excelUtil.getBodyStyle();// 构建表头String[] titles={"姓名","性别","年龄"};excelUtil.buildTitle(sheet, titles);Row bodyRow=sheet.createRow(1);Cell cell=bodyRow.createCell(0);cell.setCellValue("小明");cell=bodyRow.createCell(1);cell.setCellValue("男");cell=bodyRow.createCell(2);cell.setCellValue(12);excelUtil.autoSizeColumnSize(sheet);ByteArrayOutputStream out=new ByteArrayOutputStream();try {workbook.write(out);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();System.err.println("导出失败");}finally{try {out.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();System.err.println("关闭流失败");}}workbook.dispose();byte[] byteArray = out.toByteArray();OutputStream outputStream=null;try {outputStream=new FileOutputStream(new File("f://学生信息.xlsx"));outputStream.write(byteArray);System.out.println("输出ok");} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();System.err.println("输出失败");}finally {try {outputStream.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

4、导出的表格中要合并单元格

/**  *   * @Project:Report  * @Title:MergeCell.java  * @Package:com.you.excel  * @Description:  * @Author:YouHaiDong  * @Date:2015年11月4日 下午2:36:46  * @Version:  */  package com.you.excel;    import java.io.FileOutputStream;    import org.apache.poi.hssf.usermodel.HSSFCell;  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.Region;    /**  * <p>合并单元格</p>  * @ClassName:MergeCell  * @Description:  * @Author:YouHaiDong  * @Date:2015年11月4日 下午2:36:46  *   */  public class MergeCell   {      /**      * 合并单元格      * @Title:MergeCell      * @Description:      * @param args      * @Date:2015年11月4日 下午2:36:46      * @return: void       * @throws Exception      */      @SuppressWarnings({ "resource", "deprecation" })      public static void main(String[] args) throws Exception       {          //创建workbook           HSSFWorkbook workbook = new HSSFWorkbook();           //创建sheet页          HSSFSheet sheet = workbook.createSheet("学生表");           //创建单元格          HSSFRow row = sheet.createRow(0);           HSSFCell c0 = row.createCell(0);           c0.setCellValue(new HSSFRichTextString("学号"));           HSSFCell c1 = row.createCell(1);           c1.setCellValue(new HSSFRichTextString("姓名"));           HSSFCell c2 = row.createCell(2);           c2.setCellValue(new HSSFRichTextString("性别"));           HSSFCell c3 = row.createCell(3);           c3.setCellValue(new HSSFRichTextString("年龄"));           HSSFCell c4 = row.createCell(4);           c4.setCellValue(new HSSFRichTextString("2015年分数"));           HSSFCell c5 = row.createCell(7);           c5.setCellValue(new HSSFRichTextString("2014年分数"));           HSSFRow row1 = sheet.createRow(1);           HSSFCell c6 = row1.createCell(4);           c6.setCellValue(new HSSFRichTextString("语文"));           HSSFCell c7 = row1.createCell(5);           c7.setCellValue(new HSSFRichTextString("数学"));           HSSFCell c8 = row1.createCell(6);           c8.setCellValue(new HSSFRichTextString("外语"));          HSSFCell c9 = row1.createCell(7);           c9.setCellValue(new HSSFRichTextString("语文"));           HSSFCell c10 = row1.createCell(8);           c10.setCellValue(new HSSFRichTextString("数学"));           HSSFCell c11 = row1.createCell(9);           c11.setCellValue(new HSSFRichTextString("外语"));                    Region region1 = new Region(0, (short)0, 1, (short)0);           Region region2 = new Region(0, (short)1, 1, (short)1);           Region region3 = new Region(0, (short)2, 1, (short)2);           Region region4 = new Region(0, (short)3, 1, (short)3);           Region region5 = new Region(0, (short)4, 0, (short)6);           Region region6 = new Region(0, (short)7, 0, (short)9);           sheet.addMergedRegion(region1);           sheet.addMergedRegion(region2);           sheet.addMergedRegion(region3);           sheet.addMergedRegion(region4);           sheet.addMergedRegion(region5);           sheet.addMergedRegion(region6);     
//此方法在POI3.8中已经被废弃，建议使用下面一个    或者用      CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11);         //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列      但应注意两个构造方法的参数不是一样的，具体使用哪个取决于POI的不同版本。       sheet.addMergedRegion(region1);   

FileOutputStream stream = new FileOutputStream("d:/student.xls"); workbook.write(stream); } }
实现之后为：

5、实际工程中，要用controller写入接口Spring-core
/** * 导出凭证列表 *  */@PostMapping("/exportVoucher")public void exportVoucher(@RequestBody VouQueryVO vouQueryVO) {response.setContentType("application/vnd.ms-excel");String fileName = "凭证列表";try {response.setCharacterEncoding("UTF-8");fileName = URLEncoder.encode(fileName, "UTF-8");response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");// 组装附件名称和格式// byte[] bytes = new byte[1024];byte[] bytes = bsVoucherService.exportVoucherList(vouQueryVO);response.getOutputStream().write(bytes);} catch (Exception e) {e.printStackTrace();}}
6、设置单元格可以用以下方法
创建sheet什么的就不多说了，直接进入正题      HSSFCellStyle cellStyle = wb.createCellStyle();     一、设置背景色:      cellStyle.setFillForegroundColor((short) 13);// 设置背景色    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);    二、设置边框:      cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框    三、设置居中:      cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中    四、设置字体:      HSSFFont font = wb.createFont();    font.setFontName("黑体");    font.setFontHeightInPoints((short) 16);//设置字体大小        HSSFFont font2 = wb.createFont();    font2.setFontName("仿宋_GB2312");    font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示    font2.setFontHeightInPoints((short) 12);        cellStyle.setFont(font);//选择需要用到的字体格式    五、设置列宽:    sheet.setColumnWidth(0, 3766);   //第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2012-08-10"的宽度为2500    六、设置自动换行:    cellStyle.setWrapText(true);//设置自动换行    七、合并单元格:    Region region1 = new Region(0, (short) 0, 0, (short) 6);//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号        //此方法在POI3.8中已经被废弃，建议使用下面一个    或者用      CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11);         //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列      但应注意两个构造方法的参数不是一样的，具体使用哪个取决于POI的不同版本。       sheet.addMergedRegion(region1);    


二、读取Excel表格
1、controller接口
@PostMapping("/inputExcel")public String inputExcel(@RequestParam(value = "uploadFile", required = true) MultipartFile uploadFile){return excelService.uplodeExcelService(uploadFile);}

2、service层
public String uplodeExcelService(MultipartFile uploadFile){String msg="";if(uploadFile==null)  return msg="上传文件为空";if(uploadFile.getSize()>1024*1024*10)//单位为字节return msg="上传的文件大于10M";// 1.xls类型的为:application/vnd.ms-excel// 2、xlsx的类型为:application/vnd.openxmlformats-officedocument.spreadsheetml.sheetString contentType = uploadFile.getContentType();if(!"application/vnd.ms-excel".equals(contentType))return msg="上传的文件不是已xls结尾的Excel文件";Workbook workbook=null;try {workbook=new HSSFWorkbook(uploadFile.getInputStream());} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();return msg="上传失败1";}Sheet sheet = workbook.getSheetAt(0);List<User> userList=new ArrayList<User>();int lastRowNum = sheet.getLastRowNum();for(int startRowNum=2;startRowNum<=lastRowNum;startRowNum++){User user = new User();Row row = sheet.getRow(startRowNum);if(row==null) continue;short lastCellNum = row.getLastCellNum();for(int cellNum=0;cellNum<=lastCellNum;cellNum++){Cell cell = row.getCell(cellNum);String value="";if(cell!=null&&cell.getCellType()!=HSSFCell.CELL_TYPE_BLANK&&cell.getCellType()!=HSSFCell.CELL_TYPE_ERROR){switch (cell.getCellType()) {// 数字case HSSFCell.CELL_TYPE_NUMERIC:// 数字// 处理日期格式、时间格式if(HSSFDateUtil.isCellDateFormatted(cell)){Date date = cell.getDateCellValue();value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}else{value = NumberToTextConverter.toText(cell.getNumericCellValue());}break;// 字符串 case HSSFCell.CELL_TYPE_STRING: value=cell.getStringCellValue(); // 公式 case HSSFCell.CELL_TYPE_FORMULA: try { DecimalFormat df = new DecimalFormat("0.00");//设置小数点的位数 value = String.valueOf(df.format(cell.getNumericCellValue()));} catch (Exception e) {value = String.valueOf(cell.getRichStringCellValue());}}}if(!"".equals(value)){switch (cellNum) {case 0:user.setName(value);break;case 1:user.setAge((int)Double.parseDouble(value));break;case 2:user.setPassword(value);break;}}}userList.add(user);}System.out.println(userList);return msg="上传成功";}