• java 解析word、excel转换为html、pdf


    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.hwpf.HWPFDocument;
    import org.apache.poi.hwpf.converter.PicturesManager;
    import org.apache.poi.hwpf.converter.WordToHtmlConverter;
    import org.apache.poi.hwpf.usermodel.PictureType;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    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.XSSFWorkbook;
    import org.apache.poi.xwpf.converter.core.BasicURIResolver;
    import org.apache.poi.xwpf.converter.core.FileImageExtractor;
    import org.apache.poi.xwpf.converter.xhtml.XHTMLConverter;
    import org.apache.poi.xwpf.converter.xhtml.XHTMLOptions;
    import org.apache.poi.xwpf.usermodel.XWPFDocument;
    import org.docx4j.Docx4J;
    import org.docx4j.fonts.IdentityPlusMapper;
    import org.docx4j.fonts.Mapper;
    import org.docx4j.fonts.PhysicalFonts;
    import org.docx4j.openpackaging.packages.WordprocessingMLPackage;
    import org.docx4j.org.apache.poi.util.IOUtils;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.w3c.dom.Document;

    import javax.xml.parsers.DocumentBuilderFactory;
    import javax.xml.parsers.ParserConfigurationException;
    import javax.xml.transform.OutputKeys;
    import javax.xml.transform.Transformer;
    import javax.xml.transform.TransformerException;
    import javax.xml.transform.TransformerFactory;
    import javax.xml.transform.dom.DOMSource;
    import javax.xml.transform.stream.StreamResult;
    import java.io.*;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Map;

    /**
    * ------------------
    * User: tianwf
    * Date: 2021/6/1
    * Time: 9:29
    * ------------------
    * Description: office文档转换html
    */
    public class OfficeToHtml {

    private static final Logger logger = LoggerFactory.getLogger(OfficeToHtml.class);
    private static String[] bordesr = { "border-top:", "border-right:", "border-bottom:", "border-left:" };

    /**
    * word2003转换为html文件
    * @param wordPath word文件路径
    * @param wordName word文件名称无后缀
    * @param suffix word文件后缀
    * @throws IOException
    * @throws TransformerException
    * @throws ParserConfigurationException
    */
    public static String word2003ToHtml(String wordPath,String wordName,String suffix) throws IOException, TransformerException, ParserConfigurationException {
    String htmlPath = wordPath + File.separator + wordName + "_show" + File.separator;
    String htmlName = wordName + ".html";
    final String imagePath = htmlPath + "image" + File.separator;
    // 判断html文件是否存在
    File htmlFile = new File(htmlPath + htmlName);
    if(htmlFile.exists()) {
    return htmlFile.getAbsolutePath();
    }
    // word文档
    final String file = wordPath + File.separator + wordName + suffix;
    InputStream input = new FileInputStream(new File(file));
    HWPFDocument wordDocument = new HWPFDocument(input);
    WordToHtmlConverter wordToHtmlConverter = new WordToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
    // 设置图片存放的位置
    wordToHtmlConverter.setPicturesManager(new PicturesManager() {
    @Override
    public String savePicture(byte[] content, PictureType pictureType, String suggestedName, float widthInches, float heightInches) {
    File imgPath = new File(imagePath);
    if(!imgPath.exists()){//图片目录不存在则创建
    imgPath.mkdirs();
    }
    File file = new File(imagePath + suggestedName);
    try {
    OutputStream os = new FileOutputStream(file);
    os.write(content);
    os.close();
    } catch (FileNotFoundException e) {
    e.printStackTrace();
    } catch (IOException e) {
    e.printStackTrace();
    }
    return "image/" + suggestedName;
    }
    });
    // 解析word文档
    wordToHtmlConverter.processDocument(wordDocument);
    Document htmlDocument = wordToHtmlConverter.getDocument();
    // 生成html文件上级文件夹
    File folder = new File(htmlPath);
    if(!folder.exists()) {
    folder.mkdirs();
    }
    // 生成html文件地址
    OutputStream outStream = new FileOutputStream(htmlFile);
    DOMSource domSource = new DOMSource(htmlDocument);
    StreamResult streamResult = new StreamResult(outStream);
    TransformerFactory factory = TransformerFactory.newInstance();
    Transformer serializer = factory.newTransformer();
    serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8");
    serializer.setOutputProperty(OutputKeys.INDENT, "yes");
    serializer.setOutputProperty(OutputKeys.METHOD, "html");
    serializer.transform(domSource, streamResult);
    outStream.close();
    return htmlFile.getAbsolutePath();
    }

    /**
    * 2007版本word转换成html
    * @param wordPath word文件路径
    * @param wordName word文件名称无后缀
    * @param suffix word文件后缀
    * @return
    * @throws IOException
    */
    public static String word2007ToHtml(String wordPath, String wordName, String suffix) throws IOException {
    String htmlPath = wordPath + File.separator + wordName + "_show" + File.separator;
    String htmlName = wordName + ".html";
    String imagePath = htmlPath + "image" + File.separator;
    //判断html文件是否存在
    File htmlFile = new File(htmlPath + htmlName);
    if(htmlFile.exists()) {
    return htmlFile.getAbsolutePath();
    }
    //word文件
    File wordFile = new File(wordPath + File.separator + wordName + suffix);
    // 1) 加载word文档生成 XWPFDocument对象
    InputStream in = new FileInputStream(wordFile);
    XWPFDocument document = new XWPFDocument(in);
    // 2) 解析 XHTML配置 (这里设置IURIResolver来设置图片存放的目录)
    File imgFolder = new File(imagePath);
    XHTMLOptions options = XHTMLOptions.create();
    options.setExtractor(new FileImageExtractor(imgFolder));
    //html中图片的路径 相对路径
    options.URIResolver(new BasicURIResolver("image"));
    options.setIgnoreStylesIfUnused(false);
    options.setFragment(true);
    // 3) XWPFDocument转换成XHTML
    //生成html文件上级文件夹
    File folder = new File(htmlPath);
    if(!folder.exists()){
    folder.mkdirs();
    }
    OutputStream out = new FileOutputStream(htmlFile);
    XHTMLConverter.getInstance().convert(document, out, options);
    return htmlFile.getAbsolutePath();
    }

    /**
    * excel转换成html
    * @param filePath word文件路径
    * @param fileName word文件名称无后缀
    * @param suffix word文件后缀
    * @return
    * @throws IOException
    */
    public static String excelToHtml(String filePath, String fileName, String suffix) throws IOException {
    InputStream is = null;
    String htmlExcel = null;
    Writer or = null;
    BufferedWriter bor = null;

    File sourceFile = new File(filePath + File.separator + fileName + suffix);
    File outFile = new File(filePath + File.separator + fileName + ".html");
    if(outFile.exists()) {
    return outFile.getAbsolutePath();
    }
    try {
    is = new FileInputStream(sourceFile);
    or = new FileWriter(outFile);
    bor = new BufferedWriter(or);
    Workbook wb = WorkbookFactory.create(is);// WorkbookFactoryPOI-3.10版本中使用需要添加dom4j
    if (wb instanceof XSSFWorkbook) { XSSFWorkbook xWb = (XSSFWorkbook) wb
    ;
    htmlExcel = getExcelInfo(xWb, true);
    } else if (wb instanceof HSSFWorkbook) { HSSFWorkbook hWb = (HSSFWorkbook) wb
    ;
    htmlExcel = getExcelInfo(hWb, true);
    }
    logger.debug("ExcelToHtml:"+htmlExcel);
    bor.write(htmlExcel);
    bor.flush();
    } catch (Exception e) { e.printStackTrace()
    ;
    } finally {
    try { is.close()
    ;
    } catch (IOException e) {
    logger.debug("ExcelToHtml IOException:"+e);
    }
    try { bor.close()
    ;
    } catch (IOException e) {
    logger.debug("ExcelToHtml IOException:"+e);
    }
    try { or.close()
    ;
    } catch (IOException e) {
    logger.debug("ExcelToHtml IOException:"+e);
    } }


    return outFile.getAbsolutePath();
    }

    /**
    * 获取表格数据转为html元素
    * @param wb
    * @param isWithStyle
    * @return
    */
    public static String getExcelInfo(Workbook wb, boolean isWithStyle) { StringBuffer sb =
    new StringBuffer();
    Sheet sheet = wb.getSheetAt(0);// 获取第一个Sheet的内容
    // 去掉表格中没有值的行,获取表格的实际的行数
    int lastRowNum = ExcelUtil.filterNullRow(sheet);
    Map<String, String> map[] = getRowSpanColSpanMap(sheet);
    sb.append("<table style='border-collapse:collapse;' width='100%'>");
    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 > &nbsp;</td></tr>");
    continue;
    } sb.append(
    "<tr>");
    int lastColNum = row.getLastCellNum();
    for (int colNum = 0; colNum < lastColNum; colNum++) { cell = row.getCell(colNum)
    ;
    if (cell == null) { // 特殊情况 空白的单元格会返回null
    sb.append("<td>&nbsp;</td>");
    continue;
    } String stringValue =
    getCellValue(cell);
    // 如果是空值要进行计算判断是否有计算的结果值。
    if (stringValue==null||"".equals(stringValue.trim())){ FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator()
    ;
    if(evaluator.evaluate(cell) != null) { stringValue = evaluator.evaluate(cell).getNumberValue()+
    "";
    } }

    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 (isWithStyle) {
    dealExcelStyle(wb, sheet, cell, sb);// 处理单元格样式
    } sb.append(
    ">");
    if (stringValue == null || "".equals(stringValue.trim())) { sb.append(
    " &nbsp; ");
    } else {
    // ascii码为160的空格转换为html下的空格(&nbsp;
    sb.append(stringValue.replace(String.valueOf((char) 160), "&nbsp;"));
    } sb.append(
    "</td>");
    } sb.append(
    "</tr>");
    } sb.append(
    "</table>");

    return sb.toString();
    }

    @SuppressWarnings("unchecked")
    private static 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;
    }

    /**
    * 获取表格单元格Cell内容
    *
    * @param cell
    * @return
    */
    private static String getCellValue(Cell cell) { String result =
    new String();
    switch (cell.getCellType()) {
    case NUMERIC:// 数字类型
    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
    SimpleDateFormat sdf = null;
    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { sdf =
    new SimpleDateFormat("HH:mm");
    } else {// 日期
    sdf = new SimpleDateFormat("yyyy-MM-dd");
    } Date date = cell.getDateCellValue()
    ;
    result = sdf.format(date);
    } else if (cell.getCellStyle().getDataFormat() == 58) {
    // 处理自定义日期格式:md(通过判断单元格的格式id解决,id的值是58)
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    double value = cell.getNumericCellValue();
    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
    result = sdf.format(date);
    } else {
    double value = cell.getNumericCellValue();
    CellStyle style = cell.getCellStyle();
    DecimalFormat format = new DecimalFormat();
    String temp = style.getDataFormatString();
    // 单元格设置成常规
    if (temp.equals("General")) { format.applyPattern(
    "#");
    } result = format.format(value)
    ;
    }
    break;
    case STRING:// String类型
    result = cell.getRichStringCellValue().toString();
    break;
    case BLANK: result =
    "";
    break;
    default: result =
    "";
    break;
    }
    return result;
    }

    /**
    * 处理表格样式
    *
    * @param wb
    * @param sheet
    * @param cell
    * @param sb
    */
    private static void dealExcelStyle(Workbook wb, Sheet sheet, Cell cell, StringBuffer sb) { CellStyle cellStyle = cell.getCellStyle()
    ;
    if (cellStyle != null) { HorizontalAlignment alignment = cellStyle.getAlignment()
    ;
    sb.append("align='" + convertAlignToHtml(alignment) + "' ");// 单元格内容的水平对齐方式
    VerticalAlignment verticalAlignment = cellStyle.getVerticalAlignment();
    sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' ");// 单元格中内容的垂直排列方式
    if (wb instanceof XSSFWorkbook) { XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont()
    ;
    // short boldWeight = xf.getBoldweight();
    boolean boldWeight = xf.getBold();
    sb.append("style='");
    sb.append("font-weight:" + (boldWeight?"Blod":"") + ";"); // 字体加粗
    sb.append("font-size: " + xf.getFontHeight() / 2 + "%;"); // 字体大小
    int columnWidth = sheet.getColumnWidth(cell.getColumnIndex());
    sb.append("" + columnWidth + "px;");
    XSSFColor xc = xf.getXSSFColor();
    if (xc != null && !"".equals(xc)) { sb.append(
    "color:#" + xc.getARGBHex().substring(2) + ";"); // 字体颜色
    } XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor()

    ;
    if (bgColor != null && !"".equals(bgColor)) { sb.append(
    "">2) + ";"); // 背景颜色
    }
    // sb.append(getBorderStyle(null,0, cellStyle.getTopBorderColor(),
    // ((XSSFCellStyle) cellStyle).getTopBorderColor()));
    // sb.append(getBorderStyle(null,1, cellStyle.getRightBorderColor(),
    // ((XSSFCellStyle) cellStyle).getRightBorderColor()));
    // sb.append(getBorderStyle(null,2, cellStyle.getBottomBorderColor(),
    // ((XSSFCellStyle) cellStyle).getBottomBorderColor()));
    // sb.append(getBorderStyle(null,3, cellStyle.getLeftBorderColor(),
    // ((XSSFCellStyle) cellStyle).getLeftBorderColor()));

    } else if (wb instanceof HSSFWorkbook) { HSSFFont hf = ((HSSFCellStyle) cellStyle).getFont(wb)

    ;
    //short boldWeight = hf.getBoldweight();
    boolean boldWeight = hf.getBold();
    short fontColor = hf.getColor();
    sb.append("style='");
    HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); // HSSFPalette用于求的颜色的国际标准形式
    HSSFColor hc = palette.getColor(fontColor);
    sb.append("font-weight:" + (boldWeight?"Blod":"") + ";"); // 字体加粗
    sb.append("font-size: " + hf.getFontHeight() / 2 + "%;"); // 字体大小
    String fontColorStr = convertToStardColor(hc);
    if (fontColorStr != null && !"".equals(fontColorStr.trim())) { sb.append(
    "color:" + fontColorStr + ";"); // 字体颜色
    }
    int columnWidth = sheet.getColumnWidth(cell.getColumnIndex());
    sb.append("" + columnWidth + "px;");
    short bgColor = cellStyle.getFillForegroundColor();
    hc = palette.getColor(bgColor);
    String bgColorStr = convertToStardColor(hc);
    if (bgColorStr != null && !"".equals(bgColorStr.trim())) { sb.append(
    "">";"); // 背景颜色
    } sb.append(
    getBorderStyle(palette, 0, cellStyle.getTopBorderColor(), cellStyle.getTopBorderColor()));
    sb.append(getBorderStyle(palette, 1, cellStyle.getRightBorderColor(), cellStyle.getRightBorderColor()));
    sb.append(getBorderStyle(palette, 3, cellStyle.getLeftBorderColor(), cellStyle.getLeftBorderColor()));
    sb.append(getBorderStyle(palette, 2, cellStyle.getBottomBorderColor(), cellStyle.getBottomBorderColor()));
    } sb.append(

    "' ");
    } }


    /**
    * 单元格内容的水平对齐方式
    * @param alignment
    * @return
    */
    private static String convertAlignToHtml(HorizontalAlignment alignment) { String align =
    "left";
    if(HorizontalAlignment.LEFT == alignment) { align =
    "left";
    } else if(HorizontalAlignment.CENTER == alignment) { align =
    "center";
    } else if(HorizontalAlignment.RIGHT == alignment) { align =
    "right";
    }
    return align;
    }

    /**
    * 单元格中内容的垂直排列方式
    * @param verticalAlignment
    * @return
    */
    private static String convertVerticalAlignToHtml(VerticalAlignment verticalAlignment) { String valign =
    "middle";
    if(VerticalAlignment.TOP == verticalAlignment) { valign =
    "top";
    } else if(VerticalAlignment.CENTER == verticalAlignment) { valign =
    "center";
    } else if(VerticalAlignment.BOTTOM == verticalAlignment) { valign =
    "bottom";
    }
    return valign;
    }

    private static String getBorderStyle(HSSFPalette palette, int b, short s, short t) {
    if (s == 0)
    return bordesr[b] + "solid #d0d7e5 1px;";
    String borderColorStr = convertToStardColor(palette.getColor(t));
    borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" : borderColorStr;

    return bordesr[b] + "solid " + borderColorStr + " 1px;";
    }

    private static String convertToStardColor(HSSFColor hc) { StringBuffer sb =
    new StringBuffer("");

    if (hc != null) {
    if (HSSFColor.HSSFColorPredefined.AUTOMATIC.getIndex() == 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;
    }

    /**
    * 2007版本word转换成pdf
    * @param filePath word文件路径
    * @param fileName word文件名称无后缀
    * @param suffix word文件后缀
    * @return
    * @throws IOException
    */
    public static String wordToPdf(String filePath, String fileName, String suffix) throws Exception { FileOutputStream fileOutputStream =
    null;
    String docxPath = filePath + File.separator + fileName + suffix;
    String pdfPath = filePath + File.separator + fileName + ".pdf";
    // 判断html文件是否存在
    File pdfFile = new File(pdfPath);
    if(pdfFile.exists()) { pdfPath = pdfFile.getAbsolutePath()
    ;
    } else { File file =
    new File(docxPath);
    fileOutputStream = new FileOutputStream(new File(pdfPath));
    WordprocessingMLPackage mlPackage = WordprocessingMLPackage.load(file);
    setFontMapper(mlPackage);
    Docx4J.toPDF(mlPackage, new FileOutputStream(new File(pdfPath)));
    IOUtils.closeQuietly(fileOutputStream);
    }

    return pdfPath;
    }

    private static void setFontMapper(WordprocessingMLPackage mlPackage) throws Exception { Mapper fontMapper =
    new IdentityPlusMapper();
    fontMapper.put("隶书", PhysicalFonts.get("LiSu"));
    fontMapper.put("宋体", PhysicalFonts.get("SimSun"));
    fontMapper.put("微软雅黑", PhysicalFonts.get("Microsoft Yahei"));
    fontMapper.put("黑体", PhysicalFonts.get("SimHei"));
    fontMapper.put("楷体", PhysicalFonts.get("KaiTi"));
    fontMapper.put("新宋体", PhysicalFonts.get("NSimSun"));
    fontMapper.put("华文行楷", PhysicalFonts.get("STXingkai"));
    fontMapper.put("华文仿宋", PhysicalFonts.get("STFangsong"));
    fontMapper.put("宋体扩展", PhysicalFonts.get("simsun-extB"));
    fontMapper.put("仿宋", PhysicalFonts.get("FangSong"));
    fontMapper.put("仿宋_GB2312", PhysicalFonts.get("FangSong_GB2312"));
    fontMapper.put("幼圆", PhysicalFonts.get("YouYuan"));
    fontMapper.put("华文宋体", PhysicalFonts.get("STSong"));
    fontMapper.put("华文中宋", PhysicalFonts.get("STZhongsong"));

    mlPackage.setFontMapper(fontMapper);
    }

    public static void main(String[] args) { OfficeToHtml otm =
    new OfficeToHtml();
    /*try {
    String returnMsg = otm.word2003ToHtml("E:\tempFiles", "99228b35-0efd-4185-a2dd-776287e3d158", ".doc");
    System.out.println(returnMsg);
    } catch (ParserConfigurationException pce) {
    pce.printStackTrace();
    } catch (TransformerException te) {
    te.printStackTrace();
    } catch (IOException ioe) {
    ioe.printStackTrace();
    }*/

    /*try {
    String returnMsg = otm.word2007ToHtml("E:\tempFiles", "preview", ".docx");
    System.out.println(returnMsg);
    } catch (IOException ioe) {
    ioe.printStackTrace();
    }*/

    /*try {
    String returnMsg = otm.excelToHtml("E:\tempFiles", "政府采购询价报价表", ".xlsx");
    System.out.println(returnMsg);
    } catch (IOException ioe) {
    ioe.printStackTrace();
    }*/

    try { String returnMsg = otm.
    wordToPdf("E:\tempFiles", "99228b35-0efd-4185-a2dd-776287e3d158", ".docx");
    System.out.println(returnMsg);
    } catch (Exception e) { e.printStackTrace()
    ;
    }   }}






    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    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 java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;

    /**
    * ------------------
    * User: tianwf
    * Date: 2021/6/1
    * Time: 17:04
    * ------------------
    * Description: No Description
    */
    public class ExcelUtil {

    private static final DecimalFormat decimalFormat = new DecimalFormat("#.###");

    /**
    * 功能描述:将sheet末位为空的行去掉,得到实际有数据的行数 输入参数:<按照参数定义顺序>
    * @param childSheet
    * :Excel表单封装对象 返回值: 类型 <说明>
    * @return
    * @throws Exception
    */
    public static int filterNullRow(Sheet childSheet) {
    int rowNum = childSheet.getLastRowNum();
    int j = 1;
    // 判断末行,如果不为空,直接返回行数
    Row lastRow = childSheet.getRow(rowNum);
    if (!isNullRow(lastRow))
    return rowNum;
    // 如果末行为空,则进入循环,直到遇到不为空的为止
    for (int i = rowNum - 1; i > 0; i--) {
    Row row = childSheet.getRow(i);
    if (row == null || isNullRow(row)) {
    j++;
    } else {
    break;
    }
    }
    return rowNum - j;
    }

    /**
    * 功能描述:判断一行是否是空行,true false 不是 输入参数:<按照参数定义顺序>
    * @param row
    * :Excel表单行封装对象 返回值: 类型 <说明>
    * @return boolean
    * @throws Exception
    */
    public static boolean isNullRow(Row row) {
    if (row == null)
    return true;
    boolean nullFlag = true;
    for (int k = 0; k < row.getLastCellNum(); k++) {
    Cell cell = row.getCell((short) k);
    if (!"".equals(transferToString(cell))) {
    nullFlag = false;
    break;
    }
    }
    return nullFlag;
    }

    /**
    * 功能描述:处理cell中的值 输入参数:<按照参数定义顺序>
    *
    * @param cell
    * :Excel表单单元格封装对象 返回值: 类型 <说明>
    * @return String
    * @throws Exception
    */
    public static String transferToString(Cell cell) {
    String transferedStr = "";
    if (cell == null) {
    return "";
    }
    switch (cell.getCellType()) {
    case NUMERIC: // 数字
    if (HSSFDateUtil.isCellDateFormatted(cell)) {
    // 如果是date类型则 ,获取该celldate
    transferedStr = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
    } else { // 纯数字
    double cellValue = cell.getNumericCellValue();
    transferedStr = decimalFormat.format(cellValue);
    }
    break;
    case STRING: // 字符串
    transferedStr = cell.getStringCellValue() + "";
    break;
    default:
    transferedStr = "";
    break;
    }
    return transferedStr;
    }

    /**
    * 获取表格单元格Cell内容
    *
    * @param cell
    * @return
    */
    public static String getCellValue(Cell cell) {
    String result = new String();
    switch (cell.getCellType()) {
    case NUMERIC:// 数字类型
    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
    SimpleDateFormat sdf = null;
    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
    sdf = new SimpleDateFormat("HH:mm");
    } else {// 日期
    sdf = new SimpleDateFormat("yyyy-MM-dd");
    }
    Date date = cell.getDateCellValue();
    result = sdf.format(date);
    } else if (cell.getCellStyle().getDataFormat() == 58) {
    // 处理自定义日期格式:md(通过判断单元格的格式id解决,id的值是58)
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    double value = cell.getNumericCellValue();
    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
    result = sdf.format(date);
    } else {
    double value = cell.getNumericCellValue();
    CellStyle style = cell.getCellStyle();
    DecimalFormat format = new DecimalFormat();
    String temp = style.getDataFormatString();
    // 单元格设置成常规
    if (temp.equals("General")) {
    format.applyPattern("#");
    }
    result = format.format(value);
    }
    break;
    case STRING:// String类型
    result = cell.getRichStringCellValue().toString();
    break;
    case BLANK:
    result = "";
    break;
    default:
    result = "";
    break;
    }
    return result;
    }

    }

    所用到的jar
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.4</version>
    </dependency>
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
    </dependency>
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
    </dependency>
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
    </dependency>
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>4.1.2</version>
    </dependency>
    <dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.7.30</version>
    </dependency>
    <dependency>
    <groupId>fr.opensagres.xdocreport</groupId>
    <artifactId>xdocreport</artifactId>
    <version>1.0.6</version>
    </dependency>


    <dependency>
    <groupId>org.docx4j</groupId>
    <artifactId>docx4j</artifactId>
    <version>6.1.1</version>
    </dependency>
    <dependency>
    <groupId>org.docx4j</groupId>
    <artifactId>docx4j-export-fo</artifactId>
    <version>8.1.1</version>
    </dependency>



  • 相关阅读:
    C++——string转char[]
    Ackerman的非递归算法(未解决)
    单链表——递归求最大整数、节点个数、平均值
    队列——以数组Q[m]存放循环队列元素,设置一个标志tag,以tag=0和tag=1来区别在头指针和尾指针相等时,队列为空或满
    队列——假设以带头结点的循环链表表示队列,并且只设一个指针指向队尾元素结点(注意:不设头指针), * 试编写相应的置空队列、判断队列是否为空、入队和出队等算法。
    栈——判断回文
    栈——表达式求值
    栈——匹配()[]
    栈——十进制转八进制
    动态获取导航栏
  • 原文地址:https://www.cnblogs.com/vofill/p/14841803.html
Copyright © 2020-2023  润新知