excel html java_Excel转html工具类

packagecom.alphajuns.utils;import org.apache.poi.hssf.usermodel.*;importorg.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.*;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;importorg.junit.Test;importjava.io.File;importjava.io.FileInputStream;importjava.io.InputStream;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;import java.util.*;/*** excel转化为html工具类*/

public classPOIReadExcelToHtmlUtil {

@Testpublic voidrun() {

String filePath= "F:\\Test1.xlsx";boolean isWithStyle = true;

List> excelMapList =POIReadExcelToHtmlUtil.readExcelToHtml(filePath, isWithStyle);

System.out.println(excelMapList);

}/*** excel转html入口

*@paramfilePath

*@paramisWithStyle

*@return

*/

public static List> readExcelToHtml(String filePath, booleanisWithStyle) {

List> excelInfoMapList = null;//文件对象

File file = newFile(filePath);//文件流

InputStream inputStream = null;try{

inputStream= newFileInputStream(file);//创建工作簿

Workbook workbook =WorkbookFactory.create(inputStream);//Excel类型

if (workbook instanceofHSSFWorkbook) {//2003

HSSFWorkbook hssfWorkbook =(HSSFWorkbook) workbook;//获取Excel信息

excelInfoMapList =getExcelInfo(hssfWorkbook, isWithStyle);

}else if (workbook instanceofXSSFWorkbook) {//2007

XSSFWorkbook xssfWorkbook =(XSSFWorkbook) workbook;//获取Excel信息

excelInfoMapList =getExcelInfo(xssfWorkbook, isWithStyle);

}

}catch(Exception e) {

e.printStackTrace();

}returnexcelInfoMapList;

}/*** 获取Excel信息

*@paramworkbook

*@paramisWithStyle

*@return

*/

private static List> getExcelInfo(Workbook workbook, booleanisWithStyle) {

List> htmlMapList = new ArrayList>();//获取所有sheet

int sheets =workbook.getNumberOfSheets();//遍历sheets

for (int sheetIndex = 0; sheetIndex < sheets; sheetIndex++) {//用于保存sheet信息

Map sheetMap = new HashMap();//获取sheet名

String sheetName =workbook.getSheetName(sheetIndex);//存储sheet名

sheetMap.put("sheetName", sheetName);

StringBuffer stringBuffer= newStringBuffer();//获取第一个sheet信息

Sheet sheet =workbook.getSheetAt(sheetIndex);//行数

int lastRowNum =sheet.getLastRowNum();//获取合并后的单元格行列坐标

Map map[] =getRowSpanColSpan(sheet);

stringBuffer.append("

Row row= null;

Cell cell= null;for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {//获取行

row =sheet.getRow(rowNum);//空行

if (row == null) {

stringBuffer.append("

 ");continue;

}

stringBuffer.append("

");//列数

short lastCellNum =row.getLastCellNum();for (int colNum = 0; colNum <= lastCellNum; colNum++) {//获取列

cell =row.getCell(colNum);//空白单元格

if (cell == null) {

stringBuffer.append("

 ");continue;

}//获取列值

String cellValue =getCellValue(cell);if (map[0].containsKey(rowNum + "," +colNum)) {

String point= map[0].get(rowNum + "," +colNum);

map[0].remove(rowNum + "," +colNum);int bottomRow = Integer.valueOf(point.split(",")[0]);int bottomCol = Integer.valueOf(point.split(",")[1]);int rowSpan = bottomRow - rowNum + 1;int colSpan = bottomCol - colNum + 1;

stringBuffer.append("

}else if (map[1].containsKey(rowNum + "," +colNum)) {

map[1].remove(rowNum + "," +colNum);continue;

}else{

stringBuffer.append("

}//判断是否包含样式

if(isWithStyle) {//处理单元格样式

dealExcelStyle(workbook, sheet, cell, stringBuffer);

}

stringBuffer.append(">");if (cellValue == null || "".equals(cellValue.trim())) {

stringBuffer.append("   ");

}else{

stringBuffer.append(cellValue.replace(String.valueOf((char) 160), " "));

}

stringBuffer.append("

");

}

stringBuffer.append("

");if (rowNum > 500) {

stringBuffer.append("

数据量太大，请下载Excel查看更多数据");break;

}

}

stringBuffer.append("

");

sheetMap.put("content", stringBuffer.toString());

htmlMapList.add(sheetMap);

}returnhtmlMapList;

}/*** 获取列值

*@paramcell

*@return

*/

private staticString getCellValue(Cell cell) {

String result= newString();switch(cell.getCellType()) {case Cell.CELL_TYPE_NUMERIC: //数字类型

if(HSSFDateUtil.isCellDateFormatted(cell)) {

SimpleDateFormat simpleDateFormat= null;//时间

if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {

simpleDateFormat= new SimpleDateFormat("HH:mm");

}else{//日期

simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

}

Date date=cell.getDateCellValue();

result=simpleDateFormat.format(date);

}else if (cell.getCellStyle().getDataFormat() == 58) {//处理自定义日期格式：m月d日(通过判断单元格格式的id解决，id值为58)

SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");double value =cell.getNumericCellValue();

Date date=DateUtil.getJavaDate(value);

result=simpleDateFormat.format(date);

}else{double value =cell.getNumericCellValue();

CellStyle cellStyle=cell.getCellStyle();

DecimalFormat decimalFormat= newDecimalFormat();

String temp=cellStyle.getDataFormatString();//单元格设置成常规

if (temp.equals("General")) {

decimalFormat.applyPattern("#");

}

result=decimalFormat.format(value);

}break;case Cell.CELL_TYPE_STRING: //字符串

result =cell.getStringCellValue().toString();break;caseCell.CELL_TYPE_BLANK:

result= "";break;default:

result= "";break;

}returnresult;

}/*** 合并单元格

*@paramsheet

*@return

*/

private static Map[] getRowSpanColSpan(Sheet sheet) {

Map map0 = new HashMap();

Map map1 = new HashMap();//获取合并后的单元格数量

int mergeNum =sheet.getNumMergedRegions();

CellRangeAddress range= null;for (int i = 0; i < mergeNum; 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);

}

Map[] map={map0, map1};returnmap;

}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"};/*** 处理单元格样式

*@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();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(); //类HSSFPalette用于求的颜色的国际标准形式

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("' ");

}

}/*** 垂直对齐方式

*@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;

}/*** 水平对齐方式

*@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;

}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 "";

}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 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;

}

}