packagecom.sd.sdcj.util.excel;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileWriter;importjava.io.IOException;importjava.io.InputStream;importjava.io.Writer;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.HashMap;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFDataFormat;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFPalette;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.ss.usermodel.WorkbookFactory;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.xssf.usermodel.XSSFCellStyle;importorg.apache.poi.xssf.usermodel.XSSFColor;importorg.apache.poi.xssf.usermodel.XSSFFont;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;/*** @功能描述 POI 读取 Excel 转 HTML 支持 03xls 和 07xlsx 版本 包含样式*/
public classPOIReadExcelToHtml {/*** 测试
*@paramargs*/
public static voidpdf2html(String path) {//String path = "C:\\Users\\yugb\\Desktop\\tohtml.xlsx";
InputStream is = null;
String htmlExcel= null;
Writer wr=null;try{
File sourcefile= newFile(path);
is= newFileInputStream(sourcefile);
Workbook wb= WorkbookFactory.create(is);//此WorkbookFactory在POI-3.10版本中使用需要添加dom4j
if (wb instanceofXSSFWorkbook) {
XSSFWorkbook xWb=(XSSFWorkbook) wb;
htmlExcel= POIReadExcelToHtml.getExcelInfo(xWb,true);
}else if(wb instanceofHSSFWorkbook){
HSSFWorkbook hWb=(HSSFWorkbook) wb;
htmlExcel= POIReadExcelToHtml.getExcelInfo(hWb,true);
}
System.out.println(htmlExcel);
File file=new File(path.substring(0,path.lastIndexOf("."))+".html");if(!file.exists()){
file.createNewFile();
}
wr=newFileWriter(file);
wr.write(htmlExcel);
}catch(Exception e) {
e.printStackTrace();
}finally{try{if(is!=null){
is.close();
}if(wr!=null){
wr.close();
}
}catch(IOException e) {
e.printStackTrace();
}
}
}/*** 程序入口方法
*@paramfilePath 文件的路径
*@paramisWithStyle 是否需要表格样式 包含 字体 颜色 边框 对齐方式
*@return
字符串*/public String readExcelToHtml(String filePath , booleanisWithStyle){
InputStream is= null;
String htmlExcel= null;try{
File sourcefile= newFile(filePath);
is= newFileInputStream(sourcefile);
Workbook wb=WorkbookFactory.create(is);if (wb instanceofXSSFWorkbook) {
XSSFWorkbook xWb=(XSSFWorkbook) wb;
htmlExcel=POIReadExcelToHtml.getExcelInfo(xWb,isWithStyle);
}else if(wb instanceofHSSFWorkbook){
HSSFWorkbook hWb=(HSSFWorkbook) wb;
htmlExcel=POIReadExcelToHtml.getExcelInfo(hWb,isWithStyle);
}
}catch(Exception e) {
e.printStackTrace();
}finally{try{
is.close();
}catch(IOException e) {
e.printStackTrace();
}
}returnhtmlExcel;
}public static String getExcelInfo(Workbook wb,booleanisWithStyle){
StringBuffer sb= newStringBuffer();
Sheet sheet= wb.getSheetAt(0);//获取第一个Sheet的内容
int lastRowNum =sheet.getLastRowNum();
Map map[] =getRowSpanColSpanMap(sheet);
sb.append("
Row row= null; //兼容
Cell cell = null; //兼容
for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
row=sheet.getRow(rowNum);if (row == null) {
sb.append("
");continue;}
sb.append("
");int lastColNum =row.getLastCellNum();for (int colNum = 0; colNum < lastColNum; colNum++) {cell=row.getCell(colNum);if (cell == null) { //特殊情况 空白的单元格会返回null
sb.append("
");continue;}
String stringValue=getCellValue(cell);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("
}else if (map[1].containsKey(rowNum + "," +colNum)) {
map[1].remove(rowNum + "," +colNum);continue;
}else{
sb.append("
}//判断是否需要样式
if(isWithStyle){
dealExcelStyle(wb, sheet, cell, sb);//处理单元格样式
}
sb.append(">");if (stringValue == null || "".equals(stringValue.trim())) {
sb.append(" ");
}else{//将ascii码为160的空格转换为html下的空格( )
sb.append(stringValue.replace(String.valueOf((char) 160)," "));
}
sb.append("
");}
sb.append("
");}
sb.append("
");returnsb.toString();}private static Map[] getRowSpanColSpanMap(Sheet sheet) {
Map map0 = new HashMap();
Map map1 = new HashMap();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);//System.out.println(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);
}
Map[] map={ map0, map1 };returnmap;
}/*** 获取表格单元格Cell内容
*@paramcell
*@return
*/
private staticString getCellValue(Cell cell) {
String result= newString();switch(cell.getCellType()) {case Cell.CELL_TYPE_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) {//处理自定义日期格式:m月d日(通过判断单元格的格式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= newDecimalFormat();
String temp=style.getDataFormatString();//单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result=format.format(value);
}break;case Cell.CELL_TYPE_STRING://String类型
result =cell.getRichStringCellValue().toString();break;caseCell.CELL_TYPE_BLANK:
result= "";break;default:
result= "";break;
}returnresult;
}/*** 处理表格样式
*@paramwb
*@paramsheet
*@paramcell
*@paramsb*/
private static voiddealExcelStyle(Workbook wb,Sheet sheet,Cell cell,StringBuffer sb){
CellStyle cellStyle=cell.getCellStyle();if (cellStyle != null) {short alignment =cellStyle.getAlignment();
sb.append("align='" + convertAlignToHtml(alignment) + "' ");//单元格内容的水平对齐方式
short verticalAlignment =cellStyle.getVerticalAlignment();
sb.append("valign='"+ convertVerticalAlignToHtml(verticalAlignment)+ "' ");//单元格中内容的垂直排列方式
if (wb instanceofXSSFWorkbook) {
XSSFFont xf=((XSSFCellStyle) cellStyle).getFont();short boldWeight =xf.getBoldweight();
sb.append("style='");
sb.append("font-weight:" + boldWeight + ";"); //字体加粗
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();//System.out.println("************************************");//System.out.println("BackgroundColorColor: "+cellStyle.getFillBackgroundColorColor());//System.out.println("ForegroundColor: "+cellStyle.getFillForegroundColor());//0//System.out.println("BackgroundColorColor: "+cellStyle.getFillBackgroundColorColor());//System.out.println("ForegroundColorColor: "+cellStyle.getFillForegroundColorColor());//String bgColorStr = bgColor.getARGBHex();//System.out.println("bgColorStr: "+bgColorStr);
if (bgColor != null && !"".equals(bgColor)) {
sb.append("background-color:#" + bgColor.getARGBHex().substring(2) + ";"); //鑳屾櫙棰滆壊
}
sb.append(getBorderStyle(0,cellStyle.getBorderTop(), ((XSSFCellStyle) cellStyle).getTopBorderXSSFColor()));
sb.append(getBorderStyle(1,cellStyle.getBorderRight(), ((XSSFCellStyle) cellStyle).getRightBorderXSSFColor()));
sb.append(getBorderStyle(2,cellStyle.getBorderBottom(), ((XSSFCellStyle) cellStyle).getBottomBorderXSSFColor()));
sb.append(getBorderStyle(3,cellStyle.getBorderLeft(), ((XSSFCellStyle) cellStyle).getLeftBorderXSSFColor()));
}else if(wb instanceofHSSFWorkbook){
HSSFFont hf=((HSSFCellStyle) cellStyle).getFont(wb);short boldWeight =hf.getBoldweight();short fontColor =hf.getColor();
sb.append("style='");
HSSFPalette palette= ((HSSFWorkbook) wb).getCustomPalette(); //绫籋SSFPalette鐢ㄤ簬姹傜殑棰滆壊鐨勫浗闄呮爣鍑嗗舰寮�
HSSFColor hc =palette.getColor(fontColor);
sb.append("font-weight:" + boldWeight + ";"); //瀛椾綋鍔犵矖
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("background-color:" + bgColorStr + ";"); //鑳屾櫙棰滆壊
}
sb.append( getBorderStyle(palette,0,cellStyle.getBorderTop(),cellStyle.getTopBorderColor()));
sb.append( getBorderStyle(palette,1,cellStyle.getBorderRight(),cellStyle.getRightBorderColor()));
sb.append( getBorderStyle(palette,3,cellStyle.getBorderLeft(),cellStyle.getLeftBorderColor()));
sb.append( getBorderStyle(palette,2,cellStyle.getBorderBottom(),cellStyle.getBottomBorderColor()));
}
sb.append("' ");
}
}/*** 单元格内容的水平对齐方式
*@paramalignment
*@return
*/
private static String convertAlignToHtml(shortalignment) {
String align= "left";switch(alignment) {caseCellStyle.ALIGN_LEFT:
align= "left";break;caseCellStyle.ALIGN_CENTER:
align= "center";break;caseCellStyle.ALIGN_RIGHT:
align= "right";break;default:break;
}returnalign;
}/*** 单元格中内容的垂直排列方式
*@paramverticalAlignment
*@return
*/
private static String convertVerticalAlignToHtml(shortverticalAlignment) {
String valign= "middle";switch(verticalAlignment) {caseCellStyle.VERTICAL_BOTTOM:
valign= "bottom";break;caseCellStyle.VERTICAL_CENTER:
valign= "center";break;caseCellStyle.VERTICAL_TOP:
valign= "top";break;default:break;
}returnvalign;
}private staticString 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])));
}
}returnsb.toString();
}private staticString fillWithZero(String str) {if (str != null && str.length() < 2) {return "0" +str;
}returnstr;
}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"};private static String getBorderStyle( HSSFPalette palette ,int b,short s, shortt){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;";
}private static String getBorderStyle(int b,shorts, XSSFColor xc){if(s==0)return bordesr[b]+borderStyles[s]+"#d0d7e5 1px;";;if (xc != null && !"".equals(xc)) {
String borderColorStr= xc.getARGBHex();//t.getARGBHex();
borderColorStr=borderColorStr==null|| borderColorStr.length()<1?"#000000":borderColorStr.substring(2);return bordesr[b]+borderStyles[s]+borderColorStr+" 1px;";
}return "";
}
}