POI操作Excel（xls、xlsx）
阿帕奇官网：http://poi.apache.org/
POI3.17下载:http://poi.apache.org/download.html#POI-3.17
POI操作Excel教程（易百教程）：https://www.yiibai.com/apache_poi/
1.数据库连接：https://www.cnblogs.com/feipengting/p/7606042.html
1 package com.gdin.util; 2 3 import java.io.InputStream; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.PreparedStatement; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import java.sql.Statement; 10 import java.util.Properties; 11 12 public class DBUtil { 13 14 private static Connection con; 15 private static String url; 16 private static String user; 17 private static String pwd; 18 19 public DBUtil() { 20 21 } 22 static { 23 try { 24 Class.forName("oracle.jdbc.driver.OracleDriver");/*如果是MySQL就改为Class.forName("com.mysql.jdbc.Driver");*/ 25 InputStream is = DBUtil.class.getResourceAsStream("/db.properties");//db.properties 是一个用户配置文件传用户名密码 26 Properties prop=new Properties(); 27 prop.load(is); 28 url=prop.getProperty("url"); 29 user=prop.getProperty("user"); 30 pwd=prop.getProperty("password"); 31 con = DriverManager.getConnection(url, user, pwd); 32 }catch (Exception e){ 33 System.out.println("数据库连接失败！"); 34 } 35 } 36 public static ResultSet find(String sql){ 37 con=getCon(); 38 try { 39 Statement smt=con.createStatement(); 40 ResultSet rs=smt.executeQuery(sql); 41 return rs; 42 } catch (SQLException e) { 43 e.printStackTrace(); 44 return null; 45 } 46 } 47 public static ResultSet find(String sql,Object ...pram){//...pram数组 48 con=getCon(); 49 try { 50 PreparedStatement smt=con.prepareStatement(sql); 51 for (int i=0;i<pram.length;i++){ 52 smt.setObject(i+1,pram[i]); 53 } 54 ResultSet rs=smt.executeQuery(); 55 return rs; 56 } catch (SQLException e) { 57 e.printStackTrace(); 58 return null; 59 } 60 } 61 public static void insert(String sql,Object ...pram){//...pram数组 62 con=getCon(); 63 try { 64 PreparedStatement smt=con.prepareStatement(sql); 65 for (int i=0;i<pram.length;i++){ 66 smt.setObject(i+1,pram[i]); 67 } 68 smt.executeUpdate(); 69 } catch (SQLException e) { 70 e.printStackTrace(); 71 } 72 } 73 public static Connection getCon(){ 74 try { 75 if(con==null||con.isClosed()) 76 con = DriverManager.getConnection(url, user, pwd); 77 } catch (SQLException e) { 78 e.printStackTrace(); 79 } 80 return con; 81 } 82 }
重要的类以及接口：Workbook、Sheet、Row、Cell（对应HSSF和XSSF具体的类）
2.读取本地两种格式的Excel文件：
1 public static void main(String[] args) throws Exception, IOException { 2 File file=new File("C:\\Users\\Administrator\\Desktop\\rjl.xlsx"); 3 // File file=new File("C:\\Users\\Administrator\\Desktop\\123.xls"); 4 5 Workbook workBook=null; 6 System.out.println(file.getName()); 7 if(file.getName().endsWith("xlsx")){ 8 workBook = new XSSFWorkbook(file); 9 }else if(file.getName().endsWith("xls")){ 10 workBook=new HSSFWorkbook(new FileInputStream(file)); 11 } 12 13 Sheet sheet = workBook.getSheetAt(0); 14 int lastRowNum = sheet.getLastRowNum(); 15 System.out.println(lastRowNum); 16 for(int i=0;i<=lastRowNum;i++){ 17 Row row = sheet.getRow(i); 18 short lastCellNum = row.getLastCellNum(); 19 for(int j=0;j<lastCellNum;j++){ 20 if(row.getCell(j).getCellTypeEnum().equals(CellType.NUMERIC)){ 21 System.out.print(row.getCell(j).getNumericCellValue()+"###"); 22 }else if(row.getCell(j).getCellTypeEnum().equals(CellType.STRING)){ 23 System.out.print(row.getCell(j).getStringCellValue()+"###"); 24 } 25 } 26 System.out.println(); 27 } 28 29 }
3.poi操作Excel
poi执行Excel中的公式：https://blog.csdn.net/l362696422/article/details/41700057
1 public static void ExportExcel(){ 2 Workbook wb=new XSSFWorkbook(); 3 try { 4 FileOutputStream fos = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\信息.xlsx")); 5 //单元格样式 6 CellStyle cellStyle = wb.createCellStyle(); 7 //字体 8 Font font = wb.createFont(); 9 font.setItalic(true); 10 cellStyle.setBorderBottom(BorderStyle.DASHED); 11 cellStyle.setFont(font); 12 //设置旋转角度 13 cellStyle.setRotation((short) 270); 14 Sheet sheet = wb.createSheet("info"); 15 Row row = sheet.createRow(0); 16 //设置该行的行高 17 row.setHeight((short) 800); 18 Cell cell = row.createCell(0); 19 cell.setCellValue("myvalue"); 20 cell.setCellStyle(cellStyle); 21 //同上 22 CellStyle cellStyle2 = wb.createCellStyle(); 23 Row row2 = sheet.createRow(1); 24 row2.setHeight((short) 1000); 25 Cell cell2 = row2.createCell(1); 26 cellStyle2.setRotation((short) 180); 27 cell2.setCellValue(true); 28 cell2.setCellStyle(cellStyle2); 29 //row行和cell列都是从0开始的 30 Cell cell3 = row2.createCell(7); 31 Cell cell4 = row2.createCell(8); 32 Cell cell5 = row2.createCell(9); 33 Cell cell6 = row2.createCell(10); 34 cell3.setCellValue(11); 35 cell4.setCellValue(22); 36 cell5.setCellValue(33); 37 //设置单元格的类型为：公式类型FORMULA 38 cell6.setCellType(CellType.FORMULA); 39 cell6.setCellFormula("SUM(H2:J2)"); 40 //求出工作部所有的已设置使用公式的值 41 wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); 42 //在确定xls或xlsx对象类型时也可这样使用求公式值 43 //XSSFFormulaEvaluator.evaluateAllFormulaCells(wb); 44 //超链接 45 CellStyle style = wb.createCellStyle(); 46 Font font2 = wb.createFont(); 47 //设置字体格式为单下划线 48 font2.setUnderline(Font.U_SINGLE); 49 //font2.setColor(Font.COLOR_RED); 50 font2.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex()); 51 style.setFont(font2); 52 Cell cell12 = row2.createCell(12); 53 cell12.setCellValue("跳转网站"); 54 cell12.setCellStyle(style); 55 CreationHelper creationHelper = wb.getCreationHelper(); 56 Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL); 57 hyperlink.setAddress("http://www.baidu.com"); 58 //单元格设置超链接 59 cell12.setHyperlink(hyperlink); 60 //////////设置打印区域///////// 61 wb.setPrintArea(0, 0, 8, 0, 5); 62 sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE); 63 sheet.setDisplayGridlines(true); 64 sheet.setPrintGridlines(true); 65 66 wb.write(fos); 67 fos.close(); 68 wb.close(); 69 } catch (Exception e) { 70 // TODO Auto-generated catch block 71 e.printStackTrace(); 72 } 73 74 }
4.poi导入Excel文件的兼容性以及数字自动加小数点的问题解决方案
http://www.cnblogs.com/qiujiababy/p/9371786.html
https://www.jianshu.com/p/a7eca64237bd
/** * 处理导入小数点 */ public static String numOfImport(Cell cell) { String value = cell.toString(); int i = cell.getCellType(); if (i == 1) {//字符串类型 return value; } else { String[] str = value.split("\\."); if (str.length > 1) { String str1 = str[1]; int m = Integer.parseInt(str1); if (m == 0) { return str[0]; } else { return value; } }else{ return value; } } }
<form name="form1" action="handle.jsp" method="post" enctype="multipart/form-data"> <input type="file" name="excel"> <input type="submit" value="上传"> </form>
1 public static String ImportExcel(InputStream is){ 2 Workbook workBook=null; 3 /*try { 4 System.out.println("create1"); 5 workBook = new XSSFWorkbook(is); 6 7 8 } catch (Exception e) { 9 System.out.println("create2"); 10 try{ 11 System.out.println("create22222"); 12 workBook=new HSSFWorkbook(is); 13 System.out.println("nonono"); 14 }catch(Exception e2){ 15 System.out.println("无法处理！"); 16 } 17 18 }*/ 19 20 try { 21 workBook=WorkbookFactory.create(is); 22 } catch (EncryptedDocumentException e1) { 23 // TODO Auto-generated catch block 24 e1.printStackTrace(); 25 } catch (InvalidFormatException e1) { 26 // TODO Auto-generated catch block 27 e1.printStackTrace(); 28 } catch (IOException e1) { 29 // TODO Auto-generated catch block 30 e1.printStackTrace(); 31 } 32 33 Sheet sheet = workBook.getSheetAt(0); 34 int lastRowNum = sheet.getLastRowNum(); 35 System.out.println(lastRowNum); 36 for(int i=0;i<=lastRowNum;i++){ 37 Row row = sheet.getRow(i); 38 short lastCellNum = row.getLastCellNum(); 39 for(int j=0;j<lastCellNum;j++){ 40 if(row.getCell(j).getCellTypeEnum().equals(CellType.NUMERIC)){ 41 //System.out.print(row.getCell(j).getNumericCellValue()+"###"); 42 System.out.print(numOfImport(row.getCell(j))+"###"); 43 }else if(row.getCell(j).getCellTypeEnum().equals(CellType.STRING)){ 44 System.out.print(row.getCell(j).getStringCellValue()+"###"); 45 } 46 } 47 System.out.println(); 48 } 49 System.out.println("nono33333333333"); 50 try { 51 workBook.close(); 52 } catch (IOException e) { 53 // TODO Auto-generated catch block 54 e.printStackTrace(); 55 } 56 System.out.println("nono44444444444444"); 57 return "ok"; 58 }
使用apach的fileupload.jar包上传Excel，主要处理流（直接request.getInputStream()的流是没经过处理的，所以采用该jar包处理流问题）
<%
if(request.getMethod().equalsIgnoreCase("post")){
try {
FileItemFactory factory = new DiskFileItemFactory();
// 文件上传核心工具类
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setFileSizeMax(10 * 1024 * 1024); // 单个文件大小限制
upload.setSizeMax(50 * 1024 * 1024); // 总文件大小限制
upload.setHeaderEncoding("UTF-8"); // 对中文文件编码处理
if (ServletFileUpload.isMultipartContent(request)) {
List<FileItem> list = upload.parseRequest(request);
// 遍历
for (FileItem item : list) {
if (!item.isFormField()) {
TestExcel.ImportExcel(item.getInputStream());
}
}
}
out.print("成功");
} catch (Exception e) {
out.print("失败");
}
}
%>
5.导出（下载）Excel（jsp需要使用 response.reset() 来清除首部的空白行）
https://www.cnblogs.com/zml-java/p/6146421.html
https://blog.csdn.net/xingkong22star/article/details/39207015
<button><a href="exportExcel.jsp">下载</a></button>
<body> <% TestExcel.xiazai(response); %> </body>
1 public static void xiazai(HttpServletResponse response) throws IOException{ 2 Workbook wb = new XSSFWorkbook(); 3 Sheet sheet = wb.createSheet(); 4 Row row = sheet.createRow(0); 5 Cell cell = row.createCell(0); 6 cell.setCellValue("well down 我的测试结果"); 7 8 ServletOutputStream outputStream = response.getOutputStream(); 9 response.reset(); 10 response.setHeader("Content-disposition", "attachment; filename="+URLEncoder.encode("容杰龙测试", "UTF-8")+".xlsx"); 11 response.setContentType("application/msexcel"); 12 wb.write(outputStream); 13 14 outputStream.flush(); 15 outputStream.close(); 16 wb.close(); 17 18 }
6.XSSFWorkbook、HSSFWorkbook导出EXCEL数据和图片
https://blog.csdn.net/sun_cherish/article/details/79712975
https://blog.csdn.net/chenssy/article/details/20524563
https://blog.csdn.net/joyous/article/details/8780112
https://blog.csdn.net/joyous/article/details/9664739
1 public static void ExportPic() throws IOException{ 2 XSSFWorkbook wb = new XSSFWorkbook(); 3 String sheetName = "excel导出图片测试"; 4 XSSFSheet RZ_TXSheet = wb.createSheet(sheetName); 5 6 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); 7 //图片绝对路径 8 BufferedImage user_headImg = ImageIO.read(new File("C:\\Users\\Administrator\\Desktop\\boy.jpg")); 9 ImageIO.write(user_headImg, "jpg", byteArrayOut); 10 //sheet只能获取一个 11 XSSFDrawing patriarch = RZ_TXSheet.createDrawingPatriarch(); 12 //设置图片的属性 13 XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255,(short) 0, 0, (short) 5, 8); 14 anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); 15 //插入图片 16 patriarch.createPicture(anchor,wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG)); 17 18 FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\测试Excel.xlsx"); 19 // 写入excel文件 20 wb.write(fileOut); 21 wb.close(); 22 }
1 public static void ExportTest() throws FileNotFoundException, IOException{ 2 XSSFWorkbook workbook = new XSSFWorkbook(); 3 XSSFSheet xssfSheet = workbook.createSheet("abc"); 4 XSSFDrawing patriarch = xssfSheet.createDrawingPatriarch(); 5 XSSFClientAnchor anchor=new XSSFClientAnchor(0, 0, 255, 255, 2, 2, 8, 8); 6 byte[] by=IOUtils.toByteArray(new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\boy.jpg"))); 7 int pictureIndex = workbook.addPicture(by, Workbook.PICTURE_TYPE_JPEG); 8 patriarch.createPicture(anchor, pictureIndex); 9 10 FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\tupian.xlsx")); 11 workbook.write(fileOutputStream); 12 fileOutputStream.close(); 13 workbook.close(); 14 }
多张图片示例：
1 public static void ExportPic() throws IOException{ 2 XSSFWorkbook wb = new XSSFWorkbook(); 3 String sheetName = "excel导出图片测试"; 4 XSSFSheet RZ_TXSheet = wb.createSheet(sheetName); 5 6 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); 7 //图片绝对路径 8 BufferedImage user_headImg = ImageIO.read(new File("C:\\Users\\Administrator\\Desktop\\boy.jpg")); 9 ImageIO.write(user_headImg, "jpg", byteArrayOut); 10 //sheet只能获取一个 11 XSSFDrawing patriarch = RZ_TXSheet.createDrawingPatriarch(); 12 //设置图片的属性 13 XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255,(short) 0, 0, (short) 5, 8); 14 anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); 15 //插入图片 16 patriarch.createPicture(anchor,wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG)); 17 //插入第二张图片 18 int pictureIndex = wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_JPEG); 19 XSSFClientAnchor anchor2 = patriarch.createAnchor(0, 0, 255, 255, 1, 10, 10, 15); 20 patriarch.createPicture(anchor2, pictureIndex); 21 22 23 24 FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\测试Excel.xlsx"); 25 // 写入excel文件 26 wb.write(fileOut); 27 wb.close(); 28 }
7.poi获取excel中的图片
https://blog.csdn.net/delongcpp/article/details/8833995
https://www.cnblogs.com/colaclicken/p/8058719.html
public static void ReadPictureFromExcel() throws Exception{ FileInputStream fileInputStream = new FileInputStream("C:\\Users\\Administrator\\Desktop\\tupian.xlsx"); Workbook workbook = WorkbookFactory.create(fileInputStream); List<? extends PictureData> pictures = workbook.getAllPictures(); int i=1; for (PictureData pictureData : pictures) { byte[] bs = pictureData.getData(); FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\"+i+".jpg"); fileOutputStream.write(bs); fileOutputStream.close(); i++; } }
8.poi读取一个excel中的图片到另一个excel中
1 public static void main(String[] args) throws Exception, IOException { 2 3 WritePicToExcel(ReadPictureFromExcel2()); 4 } 5 6 public static Map<String, XSSFPictureData> ReadPictureFromExcel2() throws Exception { 7 Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>(); 8 FileInputStream fileInputStream = new FileInputStream("C:\\Users\\Administrator\\Desktop\\tupian.xlsx"); 9 XSSFWorkbook workbook = (XSSFWorkbook) new XSSFWorkbook(fileInputStream); 10 // List<? extends PictureData> pictures = workbook.getAllPictures(); 11 XSSFSheet sheet = workbook.getSheetAt(0); 12 List<POIXMLDocumentPart> list = sheet.getRelations(); 13 System.out.println(list.size()); 14 for (POIXMLDocumentPart poixmlDocumentPart : list) { 15 System.out.println(poixmlDocumentPart instanceof XSSFDrawing); 16 if (poixmlDocumentPart instanceof XSSFDrawing) { 17 XSSFDrawing drawing = (XSSFDrawing) poixmlDocumentPart; 18 List<XSSFShape> shapes = drawing.getShapes(); 19 System.out.println("图的数量："+shapes.size()); 20 for (XSSFShape shape : shapes) { 21 XSSFPicture picture = (XSSFPicture) shape; 22 XSSFClientAnchor anchor = picture.getPreferredSize(); 23 //CTMarker marker = anchor.getFrom(); 24 //String key = marker.getRow() + "-" + marker.getCol(); 25 String key =anchor.getDx1()+"-"+anchor.getDy1()+"-"+anchor.getDx2()+"-"+anchor.getDy2()+"-"+anchor.getCol1()+"-"+anchor.getRow1()+"-"+anchor.getCol2()+"-"+anchor.getRow2(); 26 System.out.println(key); 27 //System.out.println(anchor.getDx1()+"-"+anchor.getDy1()+"-"+anchor.getDx2()+"-"+anchor.getDy2()+"-"+anchor.getCol1()+"-"+anchor.getRow1()+"-"+anchor.getCol2()+"-"+anchor.getRow2()); 28 map.put(key, picture.getPictureData()); 29 } 30 } 31 } 32 workbook.close(); 33 return map; 34 } 35 36 public static void WritePicToExcel(Map<String, XSSFPictureData> map) throws Exception{ 37 FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\图片print.xlsx"); 38 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(); 39 XSSFSheet xssfSheet = xssfWorkbook.createSheet("print"); 40 Set<Entry<String, XSSFPictureData>> entrySet = map.entrySet(); 41 //注意！！！ 42 XSSFDrawing patriarch = xssfSheet.createDrawingPatriarch(); 43 for (Entry<String, XSSFPictureData> entry : entrySet) { 44 String key = entry.getKey(); 45 System.out.println(key); 46 String[] strings = key.split("-"); 47 //int row = Integer.parseInt(strings[0]); 48 //int column = Integer.parseInt(strings[1]); 49 //System.out.println("row:"+row+" column:"+column); 50 int dx1=Integer.parseInt(strings[0]); 51 int dy1=Integer.parseInt(strings[1]); 52 int dx2=Integer.parseInt(strings[2]); 53 int dy2=Integer.parseInt(strings[3]); 54 int col1=Integer.parseInt(strings[4]); 55 int row1=Integer.parseInt(strings[5]); 56 int col2=Integer.parseInt(strings[6]); 57 int row2=Integer.parseInt(strings[7]); 58 59 XSSFPictureData xssfPictureData = entry.getValue(); 60 //XSSFRow xssfRow = xssfSheet.createRow(row); 61 62 //XSSFClientAnchor anchor=new XSSFClientAnchor(); 63 XSSFClientAnchor anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2); 64 anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); 65 //anchor.setRow1(row); 66 //anchor.setCol1(column); 67 int pictureIndex=xssfWorkbook.addPicture(xssfPictureData.getData(), Workbook.PICTURE_TYPE_JPEG); 68 patriarch.createPicture(anchor, pictureIndex); 69 //FileOutputStream outputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\图片print"+row+".jpg"); 70 //outputStream.write(xssfPictureData.getData()); 71 //outputStream.close(); 72 } 73 xssfWorkbook.write(fileOutputStream); 74 fileOutputStream.close(); 75 xssfWorkbook.close(); 76 }
