读取excel文件.xls格式 转为html格式在页面中显示
使用poi的jar包，读取excel文件内容
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
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 {
@SuppressWarnings("rawtypes")
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("<table class='excel_table'><tbody>");
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;
}
int height = row.getHeight()/15;
sb.append("<tr>");
int lastColNum = row.getLastCellNum();//这一行有多少列
int[] widthArray = new int[lastColNum];
for (int colNum = 0; colNum < lastColNum; colNum++) {
widthArray[colNum] = sheet.getColumnWidth(colNum)/32;
}
int width = 0;
for (int colNum = 0; colNum < lastColNum; colNum++) {//遍历这一行的每一列
cell = row.getCell(colNum);
if (cell == null) {
sb.append(" ");
continue;
}
int bottomeCol = 0;
int colSpan = 0;
String stringValue = getCellValue(cell);//读取这行这列的内容
if (map[0].containsKey(rowNum + "," + colNum)) {//如果这是合并单元格的第一个就进这里
String pointString = (String) map[0].get(rowNum + "," + colNum);
map[0].remove(rowNum + "," + colNum);
//int bottomeRow = Integer.valueOf(pointString.split(",")[0]);
bottomeCol = Integer.valueOf(pointString.split(",")[1]);
//int rowSpan = bottomeRow - rowNum + 1;//占了多少行
colSpan = bottomeCol - colNum + 1;//占了多少列
//width = single.multiply(new BigDecimal(colSpan));
for (int i = colNum; i <= bottomeCol; i++) {
width = widthArray[i] + width;
}
sb.append(" ");
} else if (map[1].containsKey(rowNum + "," + colNum)) {//如果这是合并单元格的非第一个就进这里
map[1].remove(rowNum + "," + colNum);
continue;
} else {
colSpan = 1;
sb.append(" ");//如果这个格不是合并单元格就进这里
width = widthArray[colNum];
}
HSSFCellStyle cellStyle = cell.getCellStyle();
if (cellStyle != null) {//给这行的这列添加样式
HSSFFont hf = cellStyle.getFont(wb);
short fontColor = hf.getColor();
HSSFPalette palette = wb.getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式
HSSFColor hc = palette.getColor(fontColor);
sb.append("<td");
sb.append(" colspan='"+colSpan+"'");//占多少列
sb.append(" style='");//style开始
sb.append("width:"+width+"px;height:"+height+"px;");//列宽
short boldWeight = hf.getBoldweight();//font-weight属性
sb.append("font-weight:"+boldWeight+";");
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 + ";"); // 背景颜色
}
//border-top: 5px solid #000;
short borderTop = cellStyle.getBorderTop();
short borderRight = cellStyle.getBorderRight();
short borderBottom = cellStyle.getBorderBottom();
short borderLeft = cellStyle.getBorderLeft();
if (borderTop > 0) {
sb.append("border-top: 1px solid #000;"); // 上边框
}
if (borderRight > 0) {
sb.append("border-right: 1px solid #000;"); // 右边框
}
if (borderBottom > 0) {
sb.append("border-bottom: 1px solid #000;"); // 下边框
}
if (borderLeft > 0) {
sb.append("border-left: 1px solid #000;"); // 左边框
}
sb.append("'>");//style终止
short alignment = cellStyle.getAlignment();//对齐方式
sb.append("<p style='text-align:"+convertAlignToHtml(alignment)+";'>");
sb.append("<span style='font-size:"+hf.getFontHeight() / 20+"pt;'>");// 字体大小
if (stringValue == null || "".equals(stringValue.trim())) {//值为空
sb.append("");
} else {//值不为空
sb.append(rightTrim(stringValue));
}
sb.append("</span>");
sb.append("</p>");
sb.append("</td>");
width = 0;
}
sb.append(" ");
}
sb.append("</tr>");
}
sb.append("</tbody></table>");
return sb.toString();
}
@SuppressWarnings({ "unchecked", "rawtypes" })
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;
}
/**
* 去掉字符串右边的空格
* @param str 要处理的字符串
* @return 处理后的字符串
*/
public static String rightTrim(String str) {
if (str == null) {
return "";
}
int length = str.length();
for (int i = length - 1; i >= 0; i--) {
if (str.charAt(i) != 0x20) {
break;
}
length--;
}
return str.substring(0, length);
}
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;
}
public 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 "";
}
}
public static void main(String[] args) {
try {
POIReadExcel poire = new POIReadExcel();
String path = "E:\\gaofendengji2015.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();
}
}
}
