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("width:" + 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("width:" + 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>



posted on 2021-06-02 16:29  vofill-work  阅读(809)  评论(0编辑  收藏  举报