读取excel(POI)【转换为html】
来源:互联网 发布:淘大象软件下载 编辑:程序博客网 时间:2024/05/18 18:41
package test;
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;
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<String,String> map[] = getRowSpanColSpanMap(sheet);
sb.append("<table border='0' cellspacing='1' width='100%'>");
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("<tr><td > </td></tr>");
continue;
}
sb.append("<tr>");
int lastColNum = row.getLastCellNum();
for(int colNum = 0; colNum < lastColNum; colNum ++) {
cell = row.getCell(colNum);
if(cell == null){
sb.append("<td> </td>");
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("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' " );
} else if(map[1].containsKey(rowNum + "," + colNum)){
map[1].remove(rowNum + "," + colNum);
continue;
} else {
sb.append("<td ");
}
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("</td>");
}
sb.append("</tr>");
}
sb.append("</table>");
return sb.toString();
}
@SuppressWarnings("unchecked")
private 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);
//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 "";
}
}
}
- 读取excel(POI)【转换为html】
- POI读取EXCEL,转换HTML,图片,附件
- Java使用poi读取excel数据(excel可能很大,先转换为csv再读取)
- poi 将excel转换为html的java代码
- 利用POI将Excel文档转换为Html
- 读取 Excel(POI) 转 HTML
- POI读取word转换html
- POI读取word转换html
- POI读取excel转换成XML
- poi转excel,word文档为html
- Itext+POI将Excel转换为PDF
- POI解析DOC,转换为HTML
- 使用poi将word转换为html
- 读取excel(JXL)【输出为html格式】
- POI-----POI读取Excel实例
- POI读取Excel 各种特殊数字和类型的转换
- POI读取Excel 各种特殊数字和类型的转换
- POI读取Excel 各种特殊数字和类型的转换。
- 数据库-删除表中重复记录实现方法分析
- Count Primes
- UML用例图
- nmake编译QWT产生的几个错误
- SpringMVC-11 处理json
- 读取excel(POI)【转换为html】
- Qt程序在ARM板上无法显示JPG格式图片的解决方法
- 用chsh选择shell
- Spring项目的一些常见问题
- C++中sizeof与strlen的区别
- Struct和Class的区别
- C++实践参考——摩托车继承自行车和机动车
- 读取excel(JXL)【输出为html格式】
- FZU - 1683 纪念SlingShot 矩阵快速幂