会python真的可以为所欲为0(回)
399天前
这里还有人吗1(回)
585天前这里还有人吗0(回)
585天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
809天前到处都是羊，不想上班0(回)
889天前
鸽子
0(回)
893天前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 ""; |
} |
} |
} |
