java excel预览_excel使用java在线预览

该代码实现了一个ExcelUtil工具类,用于读取Excel文件并获取标题、工作表名称,以及将Excel转换为HTML。方法包括getTitltByXSLExcel从输入流中获取标题,getSheets获取文件中的所有工作表名,excelToHtml将Excel文件转换为HTML格式。类中包含了处理不同类型的Excel文件(POIFS和OOXML)以及处理单元格合并、样式、日期格式等功能。
摘要由CSDN通过智能技术生成

package com.xucj.test;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.io.PushbackInputStream;

import java.text.NumberFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.regex.Pattern;

import org.apache.poi.POIXMLDocument;

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

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

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

import

org.apache.poi.openxml4j.exceptions.InvalidFormatException;

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

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

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

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

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

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

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

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

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFColor;

import org.apache.poi.xssf.usermodel.XSSFFont;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class ExcelUtil {

private static final Logger log =

LoggerFactory.getLogger(ExcelUtil.class);

public static List getTitltByXSLExcel(InputStream input)

{

ArrayList list = new ArrayList();

Workbook workBook = null;

Sheet sheet = null;

if (!((InputStream) input).markSupported()) {

input = new PushbackInputStream((InputStream) input, 8);

}

try {

if (!POIFSFileSystem.hasPOIFSHeader((InputStream) input)

&& !POIXMLDocument.hasOOXMLHeader((InputStream) input))

{

log.error("非法的输入流:当前输入流非OLE2流或OOXML流!");

} else {

workBook = WorkbookFactory.create((InputStream) input);

}

} catch (IOException arg17) {

log.error("创建表格工作簿对象发生IO异常!原因:" + arg17.getMessage(),

arg17);

} catch (InvalidFormatException arg18) {

log.error("非法的输入流:当前输入流非OLE2流或OOXML流!", arg18);

}

try {

if (workBook != null) {

int numberSheet = workBook.getNumberOfSheets();

if (numberSheet > 0) {

sheet = workBook.getSheetAt(0);

int rowCount = sheet.getPhysicalNumberOfRows();

if (rowCount > 1) {

Row titleRow = sheet.getRow(0);

int colNum = titleRow.getPhysicalNumberOfCells();

for (int i = 0; i < colNum; ++i) {

list.add(titleRow.getCell(i).getStringCellValue());

}

}

} else {

log.warn("目标表格工作簿(Sheet)数目为0!");

}

}

} finally {

try {

if (input != null) {

((InputStream) input).close();

}

} catch (IOException arg15) {

;

}

}

return list;

}

public static List getSheets(File file) {

ArrayList list = new ArrayList();

Workbook workBook = null;

Sheet sheet = null;

Object input = null;

try {

input = new FileInputStream(file);

} catch (FileNotFoundException arg16) {

log.error("未找到指定的文件!", arg16);

} catch (Exception arg17) {

log.error("读取Excel文件发生异常!", arg17);

}

if (!((InputStream) input).markSupported()) {

input = new PushbackInputStream((InputStream) input, 8);

}

try {

if (!POIFSFileSystem.hasPOIFSHeader((InputStream) input)

&& !POIXMLDocument.hasOOXMLHeader((InputStream) input))

{

log.error("非法的输入流:当前输入流非OLE2流或OOXML流!");

} else {

workBook = WorkbookFactory.create((InputStream) input);

}

} catch (IOException arg19) {

log.error("创建表格工作簿对象发生IO异常!原因:" + arg19.getMessage(),

arg19);

} catch (InvalidFormatException arg20) {

log.error("非法的输入流:当前输入流非OLE2流或OOXML流!", arg20);

}

try {

if (workBook != null) {

int numberSheet = workBook.getNumberOfSheets();

if (numberSheet > 0) {

for (int i = 0; i < numberSheet; ++i) {

sheet = workBook.getSheetAt(i);

list.add(sheet.getSheetName());

}

} else {

log.warn("目标表格工作簿(Sheet)数目为0!");

}

}

} finally {

try {

if (input != null) {

((InputStream) input).close();

}

} catch (IOException arg15) {

;

}

}

return list;

}

public static Map excelToHtml(File file) {

HashMap map = new HashMap();

Workbook workBook = null;

Sheet sheet = null;

Object input = null;

try {

input = new FileInputStream(file);

} catch (FileNotFoundException arg16) {

log.error("未找到指定的文件!", arg16);

} catch (Exception arg17) {

log.error("读取Excel文件发生异常!", arg17);

}

if (!((InputStream) input).markSupported()) {

input = new PushbackInputStream((InputStream) input, 8);

}

try {

if (!POIFSFileSystem.hasPOIFSHeader((InputStream) input)

&& !POIXMLDocument.hasOOXMLHeader((InputStream) input))

{

log.error("非法的输入流:当前输入流非OLE2流或OOXML流!");

} else {

workBook = WorkbookFactory.create((InputStream) input);

}

} catch (IOException arg19) {

log.error("创建表格工作簿对象发生IO异常!原因:" + arg19.getMessage(),

arg19);

} catch (InvalidFormatException arg20) {

log.error("非法的输入流:当前输入流非OLE2流或OOXML流!", arg20);

}

try {

if (workBook != null) {

int numberSheet = workBook.getNumberOfSheets();

if (numberSheet > 0) {

for (int i = 0; i < numberSheet; ++i) {

sheet = workBook.getSheetAt(i);

map.put(sheet.getSheetName(), sheetToHtml(sheet));

}

} else {

log.warn("目标表格工作簿(Sheet)数目为0!");

}

}

} finally {

try {

if (input != null) {

((InputStream) input).close();

}

} catch (IOException arg15) {

;

}

}

return map;

}

public static String sheetToHtml(Sheet sheet) {

StringBuffer html = new StringBuffer();

boolean tempCellNum = false;

short lastCellNum = 0;

Workbook workbook = sheet.getWorkbook();

boolean isCellNull = true;

int firstRowNum = sheet.getFirstRowNum();

int lastRowNum = sheet.getLastRowNum();

sheet.getPhysicalNumberOfRows();

if (lastRowNum == 0) {

return "";

} else {

int rowNum;

Row row;

for (rowNum = firstRowNum; rowNum <= lastRowNum; ++rowNum)

{

row = sheet.getRow(rowNum);

if (row != null) {

short arg28 = row.getLastCellNum();

if (arg28 > lastCellNum) {

lastCellNum = arg28;

}

}

}

html.append("");

for (rowNum = firstRowNum; rowNum <= lastRowNum; ++rowNum)

{

row = sheet.getRow(rowNum);

for (short height = 0; height < lastCellNum; ++height)

{

Cell td_rowSpanCount = null;

if (row != null) {

td_rowSpanCount = row.getCell(height);

}

if (td_rowSpanCount != null &&

td_rowSpanCount.getCellType() != 3) {

isCellNull = false;

break;

}

}

if (row != null && !row.getZeroHeight()) {

if (isCellNull) {

;

}

int arg29 = (int) ((double) row.getHeight() / 15.625D);

html.append("");

int arg30 = 0;

int td_count = 0;

for (short cellNum = 0; cellNum < lastCellNum; ++cellNum)

{

Cell cell = row.getCell(cellNum);

boolean isHidden = sheet.isColumnHidden(cellNum);

if (!isHidden) {

if (cell == null) {

html.append("");

} else {

CellStyle cellStyle = cell.getCellStyle();

String bgColor = null;

String fontColor = null;

if (workbook instanceof HSSFWorkbook) {

HSSFPalette boldWeight = ((HSSFWorkbook)

workbook).getCustomPalette();

HSSFColor fontName =

boldWeight.getColor(cellStyle.getFillForegroundColor());

HSSFColor fontHeight =

boldWeight.getColor(workbook.getFontAt(cellStyle.getFontIndex()).getColor());

bgColor = convertToStardColor(fontName);

fontColor = convertToStardColor(fontHeight);

} else if (workbook instanceof XSSFWorkbook) {

XSSFColor arg31 = (XSSFColor)

cellStyle.getFillForegroundColorColor();

if (arg31 != null && arg31.getARGBHex() != null)

{

bgColor = "#" + arg31.getARGBHex().substring(2);

}

XSSFFont arg33 = ((XSSFCellStyle) cellStyle).getFont();

if (arg33 != null) {

arg31 = arg33.getXSSFColor();

if (arg31 != null && arg31.getARGBHex() != null)

{

fontColor = "#" + arg31.getARGBHex().substring(2);

}

}

}

short arg32 =

workbook.getFontAt(cellStyle.getFontIndex()).getBoldweight();

String arg34 =

workbook.getFontAt(cellStyle.getFontIndex()).getFontName();

short arg35 = (short)

(workbook.getFontAt(cellStyle.getFontIndex()).getFontHeight() /

20);

int cellReginCol = getMergerCellRegionCol(sheet, rowNum,

cellNum);

int cellReginRow = getMergerCellRegionRow(sheet, rowNum,

cellNum);

String align =

convertAlignToHtml(cellStyle.getAlignment());

String vAlign =

convertVerticalAlignToHtml(cellStyle.getVerticalAlignment());

StringBuffer tdHtml = new StringBuffer("

StringBuffer tdStyle = new StringBuffer();

if (bgColor != null && !"".equals(bgColor.trim()))

{

tdStyle.append(" background-color:" + bgColor + "; ");

}

if (fontColor != null && !"".equals(fontColor.trim()))

{

tdStyle.append(" color:" + fontColor + "; ");

}

if (arg34 != null && !"".equals(arg34.trim())

&& !"宋体".equals(arg34.trim())) {

tdStyle.append(" font-family:" + arg34 + "; ");

}

if (arg32 != 400) {

tdStyle.append("

font-weight:").append(arg32).append(";");

}

if (arg35 > 13) {

tdStyle.append(" font-size: " + arg35 + "px;");

} else {

tdStyle.append(" font-size: 13px;");

}

if (tdStyle != null && tdStyle.length() > 0)

{

tdHtml.append(" style="" + tdStyle + "" ");

}

if (!"left".equals(align)) {

tdHtml.append(" align="" + align + "" ");

}

if (!"center".equals(vAlign)) {

tdHtml.append(" valign="" + vAlign + "" ");

}

if (cellReginCol > 1) {

tdHtml.append(" colspan="" + cellReginCol + "" ");

}

if (cellReginRow > 1) {

tdHtml.append(" rowspan="" + cellReginRow + "" ");

++arg30;

}

tdHtml.append(">");

if (cell.getCellType() == 3) {

int cellReginRowTemp = getMergerCellRegionRow(sheet, rowNum -

1, cellNum);

if (cellReginRowTemp == 1) {

tdHtml.append(" ");

tdHtml.append("");

++td_count;

html.append(tdHtml);

}

} else {

tdHtml.append(getCellValue(cell).toString());

tdHtml.append("");

++td_count;

html.append(tdHtml);

}

cellNum = (short) (cellNum + cellReginCol - 1);

}

}

}

html.append("

");

isCellNull = true;

if (arg30 == td_count) {

html.append("");

}

}

}

html.append("");

return html.toString();

}

}

private static Object getCellValue(Cell cell) {

Object value = "";

if (cell.getCellType() == 1) {

value = cell.getRichStringCellValue().toString();

} else if (cell.getCellType() == 0) {

String forString =

cell.getCellStyle().getDataFormatString();

short dataFormat = cell.getCellStyle().getDataFormat();

String regExNum = "0+_";

String regExDateY = "y+";

String regExDateM = "m+";

String regExDateD = "d+";

String regExDateH = "h+";

boolean result = false;

boolean resultDate = false;

if (forString != null) {

if (result =

Pattern.compile(regExNum).matcher(forString).find()) {

result = true;

} else if

(Pattern.compile(regExDateY).matcher(forString).find() ||

Pattern.compile(regExDateM).matcher(forString).find() ||

Pattern.compile(regExDateD).matcher(forString).find()

|| Pattern.compile(regExDateH).matcher(forString).find())

{

resultDate = true;

}

}

if (result) {

value = getCellNumValue(cell, value);

} else if (resultDate) {

Date date;

SimpleDateFormat sdf;

if (dataFormat != 14 && dataFormat != 176 &&

dataFormat != 178 && dataFormat != 180 &&

dataFormat != 181 && dataFormat != 182) {

if (dataFormat == 181) {

date = cell.getDateCellValue();

sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");

value = sdf.format(date);

} else if (dataFormat == 179) {

date = cell.getDateCellValue();

sdf = new SimpleDateFormat("yyyy年MM月dd日");

value = sdf.format(date);

} else if (dataFormat != 188 && dataFormat != 177)

{

if (dataFormat == 31) {

date = cell.getDateCellValue();

sdf = new SimpleDateFormat("MM月dd日");

value = sdf.format(date);

}

} else {

date = cell.getDateCellValue();

sdf = new SimpleDateFormat("yyyy年MM月");

value = sdf.format(date);

}

} else {

date = cell.getDateCellValue();

sdf = new SimpleDateFormat("yyyy-MM-dd");

value = sdf.format(date);

}

} else {

value = getCellNumValue(cell, value);

}

}

if (cell.getCellType() == 3) {

value = "";

}

return value;

}

private static Object getCellNumValue(Cell cell, Object value)

{

cell.setCellType(1);

String tempValue =

cell.getRichStringCellValue().toString();

String value1;

if (tempValue.indexOf(".") > -1) {

double value_temp = Double.parseDouble(tempValue);

if (value_temp <= 999999.0D) {

value1 = tempValue.trim();

} else {

NumberFormat nf = NumberFormat.getInstance();

nf.setGroupingUsed(false);

value1 = nf.format(value_temp);

}

} else {

value1 = tempValue.trim();

}

return value1;

}

private static int getMergerCellRegionCol(Sheet sheet, int

cellRow, int cellCol) {

int retVal = 1;

int sheetMergerCount = sheet.getNumMergedRegions();

for (int i = 0; i < sheetMergerCount; ++i) {

CellRangeAddress region = sheet.getMergedRegion(i);

int col = region.getLastColumn() - region.getFirstColumn() +

1;

int firstRow = region.getFirstRow();

int firstCol = region.getFirstColumn();

int lastRow = region.getLastRow();

int lastCol = region.getLastColumn();

if (cellRow >= firstRow && cellRow <= lastRow

&& cellCol >= firstCol && cellCol <= lastCol)

{

retVal = col;

break;

}

}

return retVal;

}

private static int getMergerCellRegionRow(Sheet sheet, int

cellRow, int cellCol) {

int retVal = 1;

int sheetMergerCount = sheet.getNumMergedRegions();

for (int i = 0; i < sheetMergerCount; ++i) {

CellRangeAddress region = sheet.getMergedRegion(i);

int row = region.getLastRow() - region.getFirstRow() +

1;

int firstRow = region.getFirstRow();

int firstCol = region.getFirstColumn();

int lastRow = region.getLastRow();

int lastCol = region.getLastColumn();

if (cellRow >= firstRow && cellRow <= lastRow

&& cellCol >= firstCol && cellCol <= lastCol)

{

retVal = row;

break;

}

}

return retVal;

}

private static String convertAlignToHtml(short alignment)

{

String align = "left";

switch (alignment) {

case 1:

align = "left";

break;

case 2:

align = "center";

break;

case 3:

align = "right";

}

return align;

}

private static String convertVerticalAlignToHtml(short

verticalAlignment) {

String valign = "middle";

switch (verticalAlignment) {

case 0:

valign = "top";

break;

case 1:

valign = "center";

break;

case 2:

valign = "bottom";

}

return valign;

}

private static String convertToStardColor(HSSFColor hc)

{

StringBuffer sb = new StringBuffer("");

if (hc != null) {

byte a = 64;

short b = hc.getIndex();

if (a == b) {

return null;

}

sb.append("#");

for (int i = 0; i < hc.getTriplet().length; ++i) {

String str_tmp =

Integer.toHexString(hc.getTriplet()[i]);

String str;

if (str_tmp != null && str_tmp.length() < 2)

{

str = "0" + str_tmp;

} else {

str = str_tmp;

}

sb.append(str);

}

}

return sb.toString();

}

}

到此搞定!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值