POI读取EXCEL，转换HTML，图片，附件
来源：互联网 发布：网络手机卡对比 编辑：程序博客网 时间：2024/05/05 07:42
使用POI对excel文件进行读取
- Excel转换为HTML表格（包括样式）
- Excel读取图片
- Excel读取附件
- 使用POI对excel文件进行读取
- excel转换HTML
- 代码块
- ReadExcel2Html 类
- ColorInfo 类
- ColorUtil 类
- OperaColor 类
- 代码块
- Excel读取图片
- 代码块
- Picture 类主要用于保存图片所在位置x和y分别是左上角的点
- ReadExcelPicture 类读取图片
- 代码块
- excel读取附件
- 其他附件实现方法
- ReadExcelOle 类该类仅支持单个sheet进行读取导入附件方式必须为ole package方式才可以读取
- 其他附件实现方法
- excel转换HTML
excel转换HTML
代码块
ReadExcel2Html 类 ：
import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.EncryptedDocumentException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;import org.apache.poi.hssf.usermodel.HSSFPalette;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CellValue;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.FormulaEvaluator;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.ss.usermodel.WorkbookFactory;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.XSSFColor;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ReadExcel2Html { static String[] bordesr = { "border-top:", "border-right:", "border-bottom:", "border-left:" }; static String[] borderStyles = { "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid", "solid", "solid", "solid", "solid" }; /** * 转换xls中的颜色代码 * @param hc * @return */ private static String convertToStardColor(HSSFColor hc) { StringBuffer sb = new StringBuffer(""); if (hc != null) { if (HSSFColor.AUTOMATIC.index == hc.getIndex()) { return null; } sb.append("#"); for (int i = 0; i < hc.getTriplet().length; i++) { sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i]))); } } return sb.toString(); } private static String fillWithZero(String str) { if (str != null && str.length() < 2) { return "0" + str; } return str; } /** * 获取xls里面的边框 * @param palette * @param b * @param s * @param t * @return */ private String getBorderStyle(HSSFPalette palette, int b, short s, short t) { if (s == 0) return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;"; ; String borderColorStr = convertToStardColor(palette.getColor(t)); borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" : borderColorStr; return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;"; } /** * 获取xlsx里面的边框 * @param b * @param s * @param t * @return */ private String getBorderStyle(int b, short s, XSSFColor t) { if (s == 0) { return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;"; } String borderColorStr = ColorUtil.convertColorToHex(t); borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" : borderColorStr; return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;"; } /** * 转换单元格中上中下对齐 * * @param verticalAlignment * @return */ private String convertVerticalAlignToHtml(short verticalAlignment) { String valign = "middle"; switch (verticalAlignment) { case XSSFCellStyle.VERTICAL_BOTTOM: valign = "bottom"; break; case XSSFCellStyle.VERTICAL_CENTER: valign = "center"; break; case XSSFCellStyle.VERTICAL_TOP: valign = "top"; break; default: break; } return valign; } /** * 转换单元格中左中右对齐 * * @param alignment * @return */ private static String convertAlignToHtml(short alignment) { String align = "left"; switch (alignment) { case XSSFCellStyle.ALIGN_LEFT: align = "left"; break; case XSSFCellStyle.ALIGN_CENTER: align = "center"; break; case XSSFCellStyle.ALIGN_RIGHT: align = "right"; break; default: break; } return align; } /** * 空值样式 * * @return */ private String getNullCellBorderStyle() { return "border: #d0d7e5 1px 1px 1px 1px;"; } private Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) { Map<String, String> map0 = new HashMap<String, String>(); Map<String, String> map1 = new HashMap<String, String>(); int mergedNum = sheet.getNumMergedRegions(); CellRangeAddress range = null; for (int i = 0; i < mergedNum; i++) { range = sheet.getMergedRegion(i); int topRow = range.getFirstRow(); int topCol = range.getFirstColumn(); int bottomRow = range.getLastRow(); int bottomCol = range.getLastColumn(); map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol); int tempRow = topRow; while (tempRow <= bottomRow) { int tempCol = topCol; while (tempCol <= bottomCol) { map1.put(tempRow + "," + tempCol, ""); tempCol++; } tempRow++; } map1.remove(topRow + "," + topCol); } @SuppressWarnings("rawtypes") Map[] map = { map0, map1 }; return map; } /** * 获取不同工作簿的函数式方法 * * @param wb * @return */ public FormulaEvaluator getFormulaEvaluator(Workbook wb) { FormulaEvaluator evaluator = null; if (wb instanceof XSSFWorkbook) { XSSFWorkbook xWb = (XSSFWorkbook) wb; evaluator = new XSSFFormulaEvaluator(xWb); } else if (wb instanceof HSSFWorkbook) { HSSFWorkbook hWb = (HSSFWorkbook) wb; evaluator = new HSSFFormulaEvaluator(hWb); } return evaluator; } /** * 详细转换方法 * * @param wb * @return * @throws Exception */ private List<String> getExcelInfo(Workbook wb) throws Exception { List<String> list=new ArrayList<String>(); FormulaEvaluator evaluator = getFormulaEvaluator(wb); int sheets = wb.getNumberOfSheets(); for (int i = 0; i < sheets; i++) { list.add(Sheet2Html(wb, evaluator, wb.getSheetAt(i))); } return list; } private String Sheet2Html(Workbook wb, FormulaEvaluator evaluator, Sheet sheet) { StringBuffer sb = new StringBuffer(); int lastRowNum = sheet.getLastRowNum(); Map<String, String> map[] = getRowSpanColSpanMap(sheet); sb.append("<table style='border-collapse:collapse;' >"); Row row = null; Cell cell = null; for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { row = sheet.getRow(rowNum); if (row == null) { sb.append("<tr><td style='" + getNullCellBorderStyle() + "' > </td></tr>"); continue; } sb.append("<tr>"); int lastColNum = row.getLastCellNum(); for (int colNum = 0; colNum < lastColNum; colNum++) { cell = row.getCell(colNum); if (cell == null) { sb.append("<td style='" + getNullCellBorderStyle() + ";white-space: nowrap;'> </td>"); continue; } String stringValue = null; // switch (cell.getCellType()) { //获取单元格的值 // case HSSFCell.CELL_TYPE_BLANK: // stringValue = ""; // break; // case HSSFCell.CELL_TYPE_BOOLEAN: // stringValue = String // .valueOf(cell.getBooleanCellValue()); // break; // case HSSFCell.CELL_TYPE_ERROR: // stringValue = cell.getErrorCellString(); // break; // case HSSFCell.CELL_TYPE_FORMULA: // stringValue = cell.getCTCell().getV(); // break; // case HSSFCell.CELL_TYPE_NUMERIC: // stringValue = String // .valueOf(cell.getNumericCellValue()); // break; // case HSSFCell.CELL_TYPE_STRING: // stringValue = cell.getStringCellValue(); // break; // default: // break; // } // String stringValue = null; // long longVal; // double doubleVal; // int intvalue; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: // 数值型 if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是时间类型 SimpleDateFormat sdf = new SimpleDateFormat( "yyyy/MM/dd"); stringValue = sdf.format(cell.getDateCellValue()); } else { // 纯数字 double value = cell.getNumericCellValue(); CellStyle style1 = cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String temp = style1.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); } stringValue = format.format(value); // doubleVal = cell.getNumericCellValue(); // intvalue = (int) cell.getNumericCellValue(); // if(doubleVal == intvalue) // stringValue = String.valueOf(intvalue); // else // // cell.setCellType(HSSFCell.CELL_TYPE_STRING); // // stringValue = cell.getStringCellValue(); // stringValue = // String.valueOf(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: // 字符串型 stringValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔 stringValue = " " + cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // 空值 stringValue = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 stringValue = ""; break; case HSSFCell.CELL_TYPE_FORMULA: // 公式型 try { CellValue cellValue; cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { // 判断公式类型 case Cell.CELL_TYPE_BOOLEAN: stringValue = String.valueOf(cellValue .getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: // 处理日期 if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat( "yyyy/MM/dd"); stringValue = sdf.format(cell .getDateCellValue()); } else { // longVal = // Math.round(cell.getNumericCellValue()); // doubleVal = // Math.round(cell.getNumericCellValue()); // if(Double.parseDouble(longVal + ".0") == // doubleVal) // stringValue = String.valueOf(longVal); // else // stringValue = String.valueOf(doubleVal); double value = cell.getNumericCellValue(); CellStyle style1 = cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String temp = style1.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); } stringValue = format.format(value); } break; case Cell.CELL_TYPE_STRING: stringValue = cellValue.getStringValue(); break; case Cell.CELL_TYPE_BLANK: stringValue = ""; break; case Cell.CELL_TYPE_ERROR: stringValue = ""; break; case Cell.CELL_TYPE_FORMULA: stringValue = ""; break; } } catch (Exception e) { // stringValue = cell.; cell.getCellFormula(); } break; default: stringValue = cell.getStringCellValue().toString(); break; } // switch (cell.getCellType()) { // case HSSFCell.CELL_TYPE_FORMULA: // // cell.getCellFormula(); // try { // stringValue = String.valueOf(cell.getNumericCellValue()); // } catch (IllegalStateException e) { // stringValue = // String.valueOf(cell.getRichStringCellValue()); // } // break; // case HSSFCell.CELL_TYPE_NUMERIC: // stringValue = String.valueOf(cell.getNumericCellValue()); // break; // case HSSFCell.CELL_TYPE_STRING: // stringValue = // String.valueOf(cell.getRichStringCellValue()); // break; // } // if (map[0].containsKey(rowNum + "," + colNum)) { String pointString = map[0].get(rowNum + "," + colNum); map[0].remove(rowNum + "," + colNum); int bottomeRow = Integer .valueOf(pointString.split(",")[0]); int bottomeCol = Integer .valueOf(pointString.split(",")[1]); int rowSpan = bottomeRow - rowNum + 1; int colSpan = bottomeCol - colNum + 1; sb.append("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' "); } else if (map[1].containsKey(rowNum + "," + colNum)) { map[1].remove(rowNum + "," + colNum); continue; } else { sb.append("<td "); } // 获取样式的内容 if (wb instanceof XSSFWorkbook) { XSSFCellStyle xcellStyle = ((XSSFCell) cell) .getCellStyle(); if (xcellStyle != null) { short alignment = xcellStyle.getAlignment(); sb.append("align='" + convertAlignToHtml(alignment) + "' "); short verticalAlignment = xcellStyle .getVerticalAlignment(); sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' "); sb.append("style='"); XSSFFont xf = xcellStyle.getFont(); short boldWeight = xf.getBoldweight(); XSSFColor xc = xf.getXSSFColor(); String fontColorStr = ColorUtil .convertColorToHex(xc); String fontName=xf.getFontName(); int fontsize=xf.getFontHeightInPoints(); int columnWidth = (int)sheet.getColumnWidthInPixels(cell .getColumnIndex()); int rowHeight=(int) row.getHeightInPoints(); sb.append("width:" + columnWidth + "px;"); sb.append("height:" + rowHeight + "px;"); if (fontColorStr != null && !"".equals(fontColorStr.trim())) { sb.append("color:" + fontColorStr + ";"); // 字体颜色 } if(fontName!=null&& !"".equals(fontName.trim())){ sb.append("font-family:\"" + fontName + "\";"); // 字体 } if(fontsize!=0){ sb.append("font-size:" + fontsize + "px;"); // 字体大小 } XSSFColor xbgColor = null; if (xcellStyle.getFillPattern() == CellStyle.SOLID_FOREGROUND) { xbgColor = xcellStyle .getFillForegroundXSSFColor(); } xbgColor = xcellStyle.getFillForegroundXSSFColor(); String bgColorStr = ColorUtil .convertColorToHex(xbgColor); if (bgColorStr != null && !"".equals(bgColorStr.trim())) { sb.append("background-color:" + bgColorStr + ";"); // 背景颜色 } sb.append(getBorderStyle(0, xcellStyle.getBorderTop(), xcellStyle.getTopBorderXSSFColor())); sb.append(getBorderStyle(1, xcellStyle.getBorderRight(), xcellStyle.getRightBorderXSSFColor())); sb.append(getBorderStyle(2, xcellStyle.getBorderBottom(), xcellStyle.getBottomBorderXSSFColor())); sb.append(getBorderStyle(3, xcellStyle.getBorderLeft(), xcellStyle.getLeftBorderXSSFColor())); sb.append("font-weight:" + boldWeight + ";"); // 字体加粗 sb.append("font-size: " + xf.getFontHeight() / 2.5 + "%;"); // 字体大小 sb.append("white-space: nowrap;"); } } else if (wb instanceof HSSFWorkbook) { HSSFCellStyle hcellStyle = ((HSSFCell) cell) .getCellStyle(); if (hcellStyle != null) { short alignment = hcellStyle.getAlignment(); sb.append("align='" + convertAlignToHtml(alignment) + "' "); short verticalAlignment = hcellStyle .getVerticalAlignment(); sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' "); sb.append("style='"); HSSFFont hf = hcellStyle.getFont(wb); short boldWeight = hf.getBoldweight(); short fontColor = hf.getColor(); String fontName=hf.getFontName(); int fontsize=hf.getFontHeightInPoints(); HSSFPalette palette = ((HSSFWorkbook) wb) .getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式 HSSFColor hc = palette.getColor(fontColor); String fontColorStr = ColorUtil .convertColorToHex(hc); int columnWidth = (int)sheet.getColumnWidthInPixels(cell .getColumnIndex()); int rowHeight=(int) row.getHeightInPoints(); sb.append("width:" + columnWidth + "px;"); sb.append("height:" + rowHeight + "px;"); if (fontColorStr != null && !"".equals(fontColorStr.trim())) { sb.append("color:" + fontColorStr + ";"); // 字体颜色 } if(fontName!=null&& !"".equals(fontName.trim())){ sb.append("font-family:\"" + fontName + "\";"); // 字体 } if(fontsize!=0){ sb.append("font-size:" + fontsize + "px;"); // 字体大小 } short bgColor = hcellStyle.getFillForegroundColor(); hc = palette.getColor(bgColor); String bgColorStr = convertToStardColor(hc); if (bgColorStr != null && !"".equals(bgColorStr.trim())) { sb.append("background-color:" + bgColorStr + ";"); // 背景颜色 } sb.append(getBorderStyle(palette, 0, hcellStyle.getBorderTop(), hcellStyle.getTopBorderColor())); sb.append(getBorderStyle(palette, 1, hcellStyle.getBorderRight(), hcellStyle.getRightBorderColor())); sb.append(getBorderStyle(palette, 3, hcellStyle.getBorderLeft(), hcellStyle.getLeftBorderColor())); sb.append(getBorderStyle(palette, 2, hcellStyle.getBorderBottom(), hcellStyle.getBottomBorderColor())); sb.append("font-weight:" + boldWeight + ";"); // 字体加粗 sb.append("font-size: " + hf.getFontHeight() / 2.5 + "%;"); // 字体大小 sb.append("white-space: nowrap;"); } } sb.append("' "); sb.append(">"); if (stringValue == null || "".equals(stringValue.trim())) { sb.append(" "); } else { // 将ascii码为160的空格转换为html下的空格（ ） sb.append(stringValue.replace( String.valueOf((char) 160), " ")); } sb.append("</td>"); } sb.append("</tr>"); } sb.append("</table>"); return sb.toString(); } /** * 转换excel2html方法 * * @param wb * 工作簿 * @return map key:sheet1 value: * <table> * ... * </table> * 字符串 */ public List<String> getExcelToHtml(Workbook wb) { try { List<String> htmlPage = getExcelInfo(wb); return htmlPage; } catch (Exception e) { e.printStackTrace(); } return null; } /** * 程序入口方法 * * @param filePath * 文件的路径 * @return <table> * ... * </table> * 字符串 */ public List<String> readExcelToHtml(String filePath) { List<String> htmlExcel=null; try { File sourcefile = new File(filePath); InputStream is = new FileInputStream(sourcefile); Workbook wb = WorkbookFactory.create(is); htmlExcel = getExcelToHtml(wb); } catch (EncryptedDocumentException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return htmlExcel; }}ColorInfo 类
public class ColorInfo{ /** * 颜色的alpha值，此值控制了颜色的透明度 */ public int A; /** * 颜色的红分量值，Red */ public int R; /** * 颜色的绿分量值，Green */ public int G; /** * 颜色的蓝分量值，Blue */ public int B; public int toRGB() { return this.R << 16 | this.G << 8 | this.B; } public java.awt.Color toAWTColor(){ return new java.awt.Color(this.R,this.G,this.B,this.A); } public static ColorInfo fromARGB(int red, int green, int blue) { return new ColorInfo((int) 0xff, (int) red, (int) green, (int) blue); } public static ColorInfo fromARGB(int alpha, int red, int green, int blue) { return new ColorInfo(alpha, red, green, blue); } public ColorInfo(int a,int r, int g , int b ) { this.A = a; this.B = b; this.R = r; this.G = g; }}ColorUtil 类
import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Color;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFColor;public class ColorUtil{ /** * excel97中颜色转化为uof颜色 * * @param color * 颜色序号 * @return 颜色或者null */ public static ColorInfo excel97Color2UOF(Workbook book, short color) { if (book instanceof HSSFWorkbook) { HSSFWorkbook hb = (HSSFWorkbook) book; HSSFColor hc = hb.getCustomPalette().getColor(color); ColorInfo ci = excelColor2UOF(hc); return ci; } return null; } /** * excel(包含97和2007)中颜色转化为uof颜色 * * @param color * 颜色序号 * @return 颜色或者null */ public static ColorInfo excelColor2UOF(Color color) { if (color == null) { return null; } ColorInfo ci = null; if (color instanceof XSSFColor) {// .xlsx XSSFColor xc = (XSSFColor) color; byte[] b = xc.getRgb(); if (b != null) {// 一定是argb if(b.length==4) ci = ColorInfo.fromARGB(b[0], b[1], b[2], b[3]); else ci = ColorInfo.fromARGB(b[0], b[1], b[2]); } } else if (color instanceof HSSFColor) {// .xls HSSFColor hc = (HSSFColor) color; short[] s = hc.getTriplet();// 一定是rgb if (s != null) { ci = ColorInfo.fromARGB(s[0], s[1], s[2]); } } return ci; } //将颜色转换为16进制的数 public static String convertColorToHex(Color c){ ColorInfo ci = ColorUtil.excelColor2UOF(c); String colorStr = null; if(ci != null){ colorStr = OperaColor.toHex(ci.R, ci.G, ci.B); } return colorStr; }}OperaColor 类
import java.awt.Color;public class OperaColor extends Color { public OperaColor(int r, int g, int b) { super(r, g, b); } /** * * Returns the HEX value representing the colour in the default sRGB * ColorModel. * * * * @return the HEX value of the colour in the default sRGB ColorModel */ public String getHex() { return toHex(getRed(), getGreen(), getBlue()); } /** * * Returns a web browser-friendly HEX value representing the colour in the * default sRGB * ColorModel. * * * * @param r * red * * @param g * green * * @param b * blue * * @return a browser-friendly HEX value */ public static String toHex(int r, int g, int b) { return "#" + toBrowserHexValue(r) + toBrowserHexValue(g) + toBrowserHexValue(b); } private static String toBrowserHexValue(int number) { StringBuilder builder = new StringBuilder(Integer .toHexString(number & 0xff)); while (builder.length() < 2) { builder.append("0"); } return builder.toString().toUpperCase(); }}Excel读取图片
代码块
Picture 类（主要用于保存图片所在位置x和y分别是左上角的点）
public class Picture { private int x; private int y; private String pictureData; public int getX() { return x; } public void setX(int x) { this.x = x; } public int getY() { return y; } public void setY(int y) { this.y = y; } public String getPictureData() { return pictureData; } public void setPictureData(String pictureDate) { this.pictureData = pictureDate; }}ReadExcelPicture 类（读取图片）
import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.util.ArrayList;import java.util.List;import org.apache.poi.POIXMLDocumentPart;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFPicture;import org.apache.poi.hssf.usermodel.HSSFShape;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFDrawing;import org.apache.poi.xssf.usermodel.XSSFPicture;import org.apache.poi.xssf.usermodel.XSSFShape;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import sun.misc.BASE64Encoder;public class ReadExcelPicture { /** * 获取图片 * * @param workbook * @return */ public static List<Picture> getPic(Workbook workbook, int sheetNum) { List<Picture> picList = new ArrayList<Picture>(); if (workbook instanceof XSSFWorkbook) { XSSFWorkbook xwb = (XSSFWorkbook) workbook; XSSFSheet xsheet = xwb.getSheetAt(sheetNum); for (POIXMLDocumentPart dr : xsheet.getRelations()) { if (dr instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) dr; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { Picture picture = new Picture(); XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor anchor = pic.getPreferredSize(); picture.setX(anchor.getRow1());// 左上角坐标 picture.setY(anchor.getCol1()); byte[] data = pic.getPictureData().getData();// 图片数据 picture.setPictureData(new BASE64Encoder().encode(data)); picList.add(picture); picture = null; } } } } else if (workbook instanceof HSSFWorkbook) { HSSFWorkbook hwb = (HSSFWorkbook) workbook; HSSFSheet hsheet = hwb.getSheetAt(sheetNum); HSSFPatriarch hssfPatriarch= hsheet.getDrawingPatriarch(); if(hssfPatriarch!=null) for (HSSFShape shape : hssfPatriarch.getChildren()) { HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor(); if (shape instanceof HSSFPicture) { Picture picture = new Picture(); picture.setX(anchor.getRow1());// 左上角坐标 picture.setY(anchor.getCol1()); HSSFPicture pic = (HSSFPicture) shape; byte[] data = pic.getPictureData().getData();// 图片数据 picture.setPictureData(new BASE64Encoder().encode(data)); picList.add(picture); } } } return picList; } /** * 获取图片 * * @param filePath * @return */ public static List<List<Picture> > readExcelPicture(String filePath) { List<List<Picture> > allpic = new ArrayList<List<Picture>>(); try { File file = new File(filePath); FileInputStream fis = new FileInputStream(file); Workbook workbook = WorkbookFactory.create(fis); int sheetNum = workbook.getNumberOfSheets();// sheet的页数 for (int i = 0; i < sheetNum; i++) { allpic.add(getPic(workbook, i)); } } catch (InvalidFormatException | IOException e) { e.printStackTrace(); } return allpic; }这里将图片转换为base64编码字符串，需要用 Base64进行解码，有需要可以自行修改输出的方法
excel读取附件
附件中读取excel,word,powerPoint 相关文档可以通过POI的示例实现，均在POI示例代码包（poi-example）
03版xls(EmeddedObjects )
/* ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.==================================================================== */package org.apache.poi.hssf.usermodel.examples;import java.io.FileInputStream;import java.util.Iterator;import org.apache.poi.hslf.usermodel.HSLFSlideShow;import org.apache.poi.hslf.usermodel.HSLFSlideShowImpl;import org.apache.poi.hssf.usermodel.HSSFObjectData;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hwpf.HWPFDocument;import org.apache.poi.poifs.filesystem.DirectoryNode;import org.apache.poi.poifs.filesystem.Entry;import org.apache.poi.poifs.filesystem.POIFSFileSystem;/** * Demonstrates how you can extract embedded data from a .xls file */public class EmeddedObjects { @SuppressWarnings("unused") public static void main(String[] args) throws Exception { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(args[0])); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (HSSFObjectData obj : workbook.getAllEmbeddedObjects()) { //the OLE2 Class Name of the object String oleName = obj.getOLE2ClassName(); if (oleName.equals("Worksheet")) { DirectoryNode dn = (DirectoryNode) obj.getDirectory(); HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(dn, fs, false); //System.out.println(entry.getName() + ": " + embeddedWorkbook.getNumberOfSheets()); embeddedWorkbook.close(); } else if (oleName.equals("Document")) { DirectoryNode dn = (DirectoryNode) obj.getDirectory(); HWPFDocument embeddedWordDocument = new HWPFDocument(dn); //System.out.println(entry.getName() + ": " + embeddedWordDocument.getRange().text()); } else if (oleName.equals("Presentation")) { DirectoryNode dn = (DirectoryNode) obj.getDirectory(); HSLFSlideShow embeddedPowerPointDocument = new HSLFSlideShow(new HSLFSlideShowImpl(dn)); //System.out.println(entry.getName() + ": " + embeddedPowerPointDocument.getSlides().length); } else { if(obj.hasDirectoryEntry()){ // The DirectoryEntry is a DocumentNode. Examine its entries to find out what it is DirectoryNode dn = (DirectoryNode) obj.getDirectory(); for (Iterator<Entry> entries = dn.getEntries(); entries.hasNext();) { Entry entry = entries.next(); //System.out.println(oleName + "." + entry.getName()); } } else { // There is no DirectoryEntry // Recover the object's data from the HSSFObjectData instance. byte[] objectData = obj.getObjectData(); } } } workbook.close(); }}07版xlsx（）
/* ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.==================================================================== */package org.apache.poi.xssf.usermodel.examples;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.openxml4j.opc.PackagePart;import org.apache.poi.xslf.usermodel.XSLFSlideShow;import org.apache.poi.xwpf.usermodel.XWPFDocument;import org.apache.poi.hslf.usermodel.HSLFSlideShowImpl;import org.apache.poi.hwpf.HWPFDocument;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import java.io.InputStream;/** * Demonstrates how you can extract embedded data from a .xlsx file */public class EmbeddedObjects { public static void main(String[] args) throws Exception { OPCPackage pkg = OPCPackage.open(args[0]); XSSFWorkbook workbook = new XSSFWorkbook(pkg); for (PackagePart pPart : workbook.getAllEmbedds()) { String contentType = pPart.getContentType(); // Excel Workbook - either binary or OpenXML if (contentType.equals("application/vnd.ms-excel")) { HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream()); } // Excel Workbook - OpenXML file format else if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) { XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(pPart.getInputStream()); } // Word Document - binary (OLE2CDF) file format else if (contentType.equals("application/msword")) { HWPFDocument document = new HWPFDocument(pPart.getInputStream()); } // Word Document - OpenXML file format else if (contentType.equals("application/vnd.openxmlformats-officedocument.wordprocessingml.document")) { XWPFDocument document = new XWPFDocument(pPart.getInputStream()); } // PowerPoint Document - binary file format else if (contentType.equals("application/vnd.ms-powerpoint")) { HSLFSlideShowImpl slideShow = new HSLFSlideShowImpl(pPart.getInputStream()); } // PowerPoint Document - OpenXML file format else if (contentType.equals("application/vnd.openxmlformats-officedocument.presentationml.presentation")) { OPCPackage docPackage = OPCPackage.open(pPart.getInputStream()); XSLFSlideShow slideShow = new XSLFSlideShow(docPackage); } // Any other type of embedded object. else { System.out.println("Unknown Embedded Document: " + contentType); InputStream inputStream = pPart.getInputStream(); } } pkg.close(); }}其他附件实现方法
ReadExcelOle 类（该类仅支持单个sheet进行读取，导入附件方式必须为ole package方式才可以读取）
package cn.gov.customs.xshg.base.exceltool;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.nio.charset.Charset;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFObjectData;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFShape;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.openxml4j.opc.PackagePart;import org.apache.poi.openxml4j.opc.PackageRelationship;import org.apache.poi.poifs.filesystem.DirectoryNode;import org.apache.poi.poifs.filesystem.Ole10Native;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.apache.poi.xssf.usermodel.XSSFRelation;import org.apache.poi.xssf.usermodel.XSSFSheet;public class ReadExcelOle { /** * 获取附件并生成附件到与excel同一目录下面 * * @param path * excel文件所在文件夹路径 * @param s * 工作簿 * @return List<文件名称> */ public static List<String> getOleBySheet(String path, Sheet s) { List<String> oleNames = new ArrayList<String>(); try { if (s instanceof XSSFSheet) { XSSFSheet sheet = (XSSFSheet) s; for (PackageRelationship rel : sheet.getPackagePart() .getRelationshipsByType( XSSFRelation.OLEEMBEDDINGS.getRelation())) { PackagePart pPart = sheet.getPackagePart().getRelatedPart( rel); InputStream inputStream = pPart.getInputStream(); POIFSFileSystem poifsFileSystem = new POIFSFileSystem( inputStream); Ole10Native ole = Ole10Native .createFromEmbeddedOleObject(poifsFileSystem); oleNames.add(write(ole, path)); } } else if (s instanceof HSSFSheet) { HSSFSheet sheet = (HSSFSheet) s; HSSFPatriarch patriarch = sheet.getDrawingPatriarch(); if (patriarch != null) for (HSSFShape shape : patriarch.getChildren()) { if (shape instanceof HSSFObjectData) { HSSFObjectData obj = (HSSFObjectData) shape; String oleName = obj.getOLE2ClassName(); if (!(oleName.equals("Worksheet") || oleName.equals("Document") || oleName .equals("Presentation"))) { if (obj.hasDirectoryEntry()) { DirectoryNode dn = (DirectoryNode) obj .getDirectory(); Ole10Native ole = Ole10Native .createFromEmbeddedOleObject(dn); oleNames.add(write(ole, path)); } } } } }else{ throw new RuntimeException("没找到对应工作簿解析方法"); } } catch (IOException e) { e.printStackTrace(); return null; } catch (Exception e) { e.printStackTrace(); return null; } return oleNames; } //此处文件名的编码可能会有问题，POI实现自动将编码设置为ISO-8859-1，我们需要将他转换成中文才可以正常显示 static String write(Ole10Native ole, String path) throws IOException { String string = new String(ole.getLabel().getBytes( Charset.forName("ISO-8859-1")), "GBK"); FileOutputStream os = new FileOutputStream( new File(path + "/" + string)); os.write(ole.getDataBuffer()); os.close(); return string; } //示例调用 public static void main(String[] args) throws InvalidFormatException, IOException { File file=new File("C:/Users/Administrator/Desktop/test.xls"); Workbook wb=WorkbookFactory.create(file); List<String> string=getOleBySheet(file.getParent(),wb.getSheetAt(0)); wb.close(); for (String string2 : string) { System.out.println(string2); } }} 0 0
- POI读取EXCEL，转换HTML，图片，附件
- 读取excel（POI）【转换为html】
- 读取 Excel(POI) 转 HTML
- POI读取word转换html
- POI读取word转换html
- POI读取excel转换成XML
- POI-----POI读取Excel实例
- POI读取Excel 各种特殊数字和类型的转换
- POI读取Excel 各种特殊数字和类型的转换
- POI读取Excel 各种特殊数字和类型的转换。
- POI读取Excel 各种特殊数字和类型的转换
- 使用 apache poi 读取excel 把 数字转换成 字符
- POI读取Excel 各种特殊数字和类型的转换。
- jacob读取word excel，转换成html
- POI读取EXCEL
- POI读取EXCEL教程
- POI读取EXCEL
- poi 读取excel
- hadoop yarn中常见问题以及解决方案
- csdn如何转载别人的文章
- TYPESDK手游聚合SDK服务端设计思路与架构之一：应用场景分析
- IOS Dev Intro - App Life Cycle
- 接受json格式数据
- POI读取EXCEL，转换HTML，图片，附件
- 那些看似小并非小的小事
- 买新电脑要考虑的一些问题
- 欢迎使用CSDN-markdown编辑器
- mybatis快速入门
- Java中JNI的使用详解第一篇:HelloWorld
- JSONExport Model生成工具
- Hadoop日志说明
- java通过JDBC链接SQLServer2012
