poi读取excel 转换实体_利用POI读取excel文件,转换为html

[java]代码库import java.io.File;

import java.io.FileInputStream;

import java.io.InputStream;

import java.util.HashMap;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFPalette;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.util.CellRangeAddress;

/**

* 利用POI读取excel,转换为html

*

*/

public class POIReadExcel {

public static void main(String[] args) {

try {

POIReadExcel poire = new POIReadExcel();

String path = "D:\\test.xls";

File sourcefile = new File(path);

InputStream is = new FileInputStream(sourcefile);

POIFSFileSystem fs = new POIFSFileSystem(is);

HSSFWorkbook wb = new HSSFWorkbook(fs);

System.out.println(poire.getExcelInfo(wb));

is.close();

} catch (Exception e) {

e.printStackTrace();

}

}

public String getExcelInfo(HSSFWorkbook wb) throws Exception {

StringBuffer sb = new StringBuffer();

Sheet sheet = wb.getSheetAt(0);

int lastRowNum = sheet.getLastRowNum();

Map map[] = getRowSpanColSpanMap(sheet);

sb.append(" ");

HSSFRow row = null;

HSSFCell cell = null;

// System.out.println(sheet.getPhysicalNumberOfRows());

for (int rowNum = sheet.getFirstRowNum(); rowNum < lastRowNum; rowNum++) {

row = (HSSFRow) 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) {

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

}

HSSFCellStyle cellStyle = cell.getCellStyle();

if (cellStyle != null) {

short alignment = cellStyle.getAlignment();

sb.append("align='" + convertAlignToHtml(alignment) + "' ");

short verticalAlignment = cellStyle.getVerticalAlignment();

sb.append("valign='"

+ convertVerticalAlignToHtml(verticalAlignment)

+ "' ");

HSSFFont hf = cellStyle.getFont(wb);

short boldWeight = hf.getBoldweight();

short fontColor = hf.getColor();

sb.append("style='");

HSSFPalette palette = wb.getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式

HSSFColor hc = palette.getColor(fontColor);

sb.append("font-weight:" + boldWeight + ";"); // 字体加粗

// System.out.println(hf.getFontHeight());

sb.append("font-size: " + hf.getFontHeight() / 2 + "%;"); // 字体大小

String fontColorStr = convertToStardColor(hc);

if (fontColorStr != null && !"".equals(fontColorStr.trim())) {

sb.append("color:" + fontColorStr + ";"); // 字体颜色

}

short bgColor = cellStyle.getFillForegroundColor();

hc = palette.getColor(bgColor);

String bgColorStr = convertToStardColor(hc);

if (bgColorStr != null && !"".equals(bgColorStr.trim())) {

sb.append("background-color:" + bgColorStr + ";"); // 背景颜色

}

short borderColor = cellStyle.getBottomBorderColor();

hc = palette.getColor(borderColor);

String borderColorStr = convertToStardColor(hc);

if (borderColorStr != null

&& !"".equals(borderColorStr.trim())) {

sb.append("border-color:" + borderColorStr + ";"); // 边框颜色

}

// boolean borderBoolean = cellStyle.getWrapText();

//

// if(borderBoolean){

// sb.append("border-style: inset;");

// }

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

}

sb.append(" ");

}

sb.append(" ");

return sb.toString();

}

@SuppressWarnings("unchecked")

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

return map;

}

private String convertAlignToHtml(short alignment) {

String align = "left";

switch (alignment) {

case HSSFCellStyle.ALIGN_LEFT:

align = "left";

break;

case HSSFCellStyle.ALIGN_CENTER:

align = "center";

break;

case HSSFCellStyle.ALIGN_RIGHT:

align = "right";

break;

default:

break;

}

return align;

}

private String convertVerticalAlignToHtml(short verticalAlignment) {

String valign = "middle";

switch (verticalAlignment) {

case HSSFCellStyle.VERTICAL_BOTTOM:

valign = "bottom";

break;

case HSSFCellStyle.VERTICAL_CENTER:

valign = "center";

break;

case HSSFCellStyle.VERTICAL_TOP:

valign = "top";

break;

default:

break;

}

return valign;

}

private 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 String fillWithZero(String str) {

if (str != null && str.length() < 2) {

return "0" + str;

}

return str;

}

private String getCellValue(HSSFCell cell) {

switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_NUMERIC:

DecimalFormat format = new DecimalFormat("#0.##");

return format.format(cell.getNumericCellValue());

// return String.valueOf(cell.getNumericCellValue());

case HSSFCell.CELL_TYPE_STRING:

return cell.getStringCellValue();

// case HSSFCell.CELL_TYPE_FORMULA:

//

// return cell.getCellFormula();

default:

return "";

}

}

}