- package yys.hpsz.cview;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.IOException;
- import java.math.BigDecimal;
- import java.text.DecimalFormat;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- import java.util.Map;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFDateUtil;
- 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.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.hssf.util.HSSFColor;
- import org.apache.poi.hssf.util.Region;
- import org.apache.poi.ss.usermodel.CellStyle;
- import org.apache.poi.ss.util.CellRangeAddress;
- import soja.base.SojaProperties;
- import soja.transfer.DataErrorException;
- import yys.common.mvc.datamgr.DataManager;
- public class ExcelShower extends DataManager {
- /**
- * SID.
- */
- private static final long serialVersionUID = -8344971443770122206L;
- /**
- * 读取 Excel 显示页面.
- * @param properties
- * @return
- * @throws Exception
- */
- public StringBuffer read(Map properties) throws Exception {
- HSSFSheet sheet = null;
- StringBuffer lsb = new StringBuffer();
- String excelFileName = SojaProperties.getSojaRoot() + "/page/readExcel/sale.xls";
- try {
- HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelFileName)); // 获整个Excel
- for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
- sheet = workbook.getSheetAt(sheetIndex);// 获所有的sheet
- String sheetName = workbook.getSheetName(sheetIndex); // sheetName
- if (workbook.getSheetAt(sheetIndex) != null) {
- sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet
- if (sheet != null) {
- int firstRowNum = sheet.getFirstRowNum(); // 第一行
- int lastRowNum = sheet.getLastRowNum(); // 最后一行
- // 构造Table
- lsb.append("<table width=\\"100%\\" style=\\"border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;border-collapse:collapse;\\">");
- for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
- if (sheet.getRow(rowNum) != null) {// 如果行不为空,
- HSSFRow row = sheet.getRow(rowNum);
- short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格
- short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
- int height = (int) (row.getHeight()/15.625); // 行的高度
- lsb.append("<tr height=\\""+height+"\\" style=\\"border:1px solid #000;border-width:0 1px 1px 0;margin:2px 0 2px 0;\\">");
- for (short cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格
- HSSFCell cell = row.getCell(cellNum);
- if (cell != null) {
- if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
- continue;
- }else {
- StringBuffer tdStyle =new StringBuffer("<td style=\\"border:1px solid #000; border-width:0 1px 1px 0;margin:2px 0 2px 0; ");
- HSSFCellStyle cellStyle = cell.getCellStyle();
- HSSFPalette palette = workbook.getCustomPalette(); //类HSSFPalette用于求颜色的国际标准形式
- HSSFColor hColor = palette.getColor(cellStyle.getFillForegroundColor());
- HSSFColor hColor2 = palette.getColor(cellStyle.getFont(workbook).getColor());
- String bgColor = convertToStardColor(hColor);//背景颜色
- short boldWeight = cellStyle.getFont(workbook).getBoldweight(); // 字体粗细
- short fontHeight = (short) (cellStyle.getFont(workbook).getFontHeight() / 2); // 字体大小
- String fontColor = convertToStardColor(hColor2); // 字体颜色
- if(bgColor != null && !"".equals(bgColor.trim())){
- tdStyle.append(" background-color:" + bgColor + "; ");
- }
- if(fontColor != null && !"".equals(fontColor.trim())) {
- tdStyle.append(" color:" + fontColor + "; ");
- }
- tdStyle.append(" font-weight:" + boldWeight + "; ");
- tdStyle.append(" font-size: " + fontHeight + "%;");
- lsb.append(tdStyle + "\\"");
- int width = (int) (sheet.getColumnWidth(cellNum)/ 35.7); //
- int cellReginCol = getMergerCellRegionCol(sheet,rowNum, cellNum); // 合并的列(solspan)
- int cellReginRow = getMergerCellRegionRow(sheet, rowNum, cellNum);// 合并的行(rowspan)
- String align = convertAlignToHtml(cellStyle.getAlignment()); //
- String vAlign = convertVerticalAlignToHtml(cellStyle.getVerticalAlignment());
- lsb.append(" align=\\"" + align + "\\" valign=\\""+vAlign+"\\" width=\\"" + width + "\\" ");
- lsb.append(" colspan=\\"" + cellReginCol + "\\" rowspan=\\""+cellReginRow+"\\"");
- lsb.append(">"+getCellValue(cell)+"</td>");
- }
- }
- }
- lsb.append("</tr>");
- }
- }
- }
- }
- }
- } catch (FileNotFoundException e) {
- throw new DataErrorException("文件 " + excelFileName + " 没有找到!");
- } catch (IOException e) {
- throw new DataErrorException("文件 " + excelFileName + " 处理错误("
- + e.getMessage() + ")!");
- }
- return lsb;
- }
- /**
- * 取得单元格的值
- * @param cell
- * @return
- * @throws IOException
- */
- private static Object getCellValue(HSSFCell cell) throws IOException {
- Object value = "";
- if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
- value = cell.getRichStringCellValue().toString();
- } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- Date date = cell.getDateCellValue();
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
- value = sdf.format(date);
- } else {
- double value_temp = (double) cell.getNumericCellValue();
- BigDecimal bd = new BigDecimal(value_temp);
- BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP);
- value = bd1.doubleValue();
- /*
- DecimalFormat format = new DecimalFormat("#0.###");
- value = format.format(cell.getNumericCellValue());
- */
- }
- }
- if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
- value = "";
- }
- return value;
- }
- /**
- * 判断单元格在不在合并单元格范围内,如果是,获取其合并的列数。
- * @param sheet 工作表
- * @param cellRow 被判断的单元格的行号
- * @param cellCol 被判断的单元格的列号
- * @return
- * @throws IOException
- */
- private static int getMergerCellRegionCol(HSSFSheet sheet, int cellRow,int cellCol) throws IOException {
- int retVal = 0;
- int sheetMergerCount = sheet.getNumMergedRegions();
- for (int i = 0; i < sheetMergerCount; i++) {
- CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
- int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
- int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
- int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
- int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
- if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
- if (cellCol >= firstCol && cellCol <= lastCol) {
- retVal = lastCol - firstCol+1; // 得到合并的列数
- break;
- }
- }
- }
- return retVal;
- }
- /**
- * 判断单元格是否是合并的单格,如果是,获取其合并的行数。
- * @param sheet 表单
- * @param cellRow 被判断的单元格的行号
- * @param cellCol 被判断的单元格的列号
- * @return
- * @throws IOException
- */
- private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow,int cellCol) throws IOException {
- int retVal = 0;
- int sheetMergerCount = sheet.getNumMergedRegions();
- for (int i = 0; i < sheetMergerCount; i++) {
- CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
- int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
- int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
- int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
- int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
- if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
- if (cellCol >= firstCol && cellCol <= lastCol) {
- retVal = lastRow - firstRow + 1; // 得到合并的行数
- break;
- }
- }
- }
- return retVal;
- }
- /**
- * 单元格背景色转换
- * @param hc
- * @return
- */
- private String convertToStardColor(HSSFColor hc) {
- StringBuffer sb = new StringBuffer("");
- if (hc != null) {
- int a = HSSFColor.AUTOMATIC.index;
- int b = hc.getIndex();
- if (a == b) {
- return null;
- }
- sb.append("#");
- for (int i = 0; i < hc.getTriplet().length; i++) {
- String str ;
- String str_tmp = Integer.toHexString(hc.getTriplet()[i]);
- if (str_tmp != null && str_tmp.length() < 2) {
- str = "0" + str_tmp;
- }else {
- str = str_tmp;
- }
- sb.append(str);
- }
- }
- return sb.toString();
- }
- /**
- * 单元格小平对齐
- * @param alignment
- * @return
- */
- 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;
- }
- /**
- * 单元格垂直对齐
- * @param verticalAlignment
- * @return
- */
- 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;
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/270820135354.html
来源: http://www.codesnippet.cn/detail/270820135354.html