import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
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.struts2.ServletActionContext;
public class TestPoiExcelToHtml {
public static void excelToHtml(String fileName) throws Exception{
String path=ServletActionContext.getServletContext().getRealPath("/");
String targetPath=fileName.substring(fileName.lastIndexOf("\\upload"),fileName.length());
File excelFile = new File(path+targetPath); //创建文件对象
FileInputStream is = new FileInputStream(excelFile); //文件流
Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
Sheet sheet = workbook.getSheetAt(0);
int lastRowIndex = sheet.getLastRowNum();
String result="";
result+="<html>";
result+="<head>";
result+="<meta charset='UTF-8'/>";
result+="<link rel='shortcut icon' href='../systemicon/favico.ico' type='image/x-icon'/>";
result+="<title>模板预览</title>";
result+="<style type='text/css'>";
result+="body{width:10000px;}";
result+="table{border-collapse:collapse; border:1px solid black;}";
result+="table tr:first-child{display:none;}";
result+="table td{border:1px solid #000;}";
result+=".rownumber{display:none;}";
result+="</style>";
result+="</head>";
result+="<body>";
result+="<table>";
for (int rowIndex = 0; rowIndex <= lastRowIndex; rowIndex++){
Row currentRow = sheet.getRow(rowIndex);
result+="<tr>";
if(currentRow!=null){
//int firstColumnIndex = currentRow.getFirstCellNum();
int lastColumnIndex = currentRow.getLastCellNum();
for (int columnIndex = 0; columnIndex <= lastColumnIndex-1; columnIndex++) {
Cell cell = currentRow.getCell(columnIndex);
String currentCellValue = getCellValue(cell, true);
if(currentCellValue.equals("")||currentCellValue==null){
result+="<td></td>";
}else{
result+="<td>"+currentCellValue + "</td>";
}
}
}
result+="</tr>";
}
result+="</table>";
result+="</body>";
result+="</html>";
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
DOMSource domSource = new DOMSource ();
StreamResult streamResult = new StreamResult (outStream);
TransformerFactory tf = TransformerFactory.newInstance();
Transformer serializer = tf.newTransformer();
serializer.setOutputProperty (OutputKeys.ENCODING, "utf-8");
serializer.setOutputProperty (OutputKeys.INDENT, "yes");
serializer.setOutputProperty (OutputKeys.METHOD, "html");
serializer.transform (domSource, streamResult);
outStream.close();
FileUtils.writeStringToFile(new File (path, "publishbusiness\\fastpublish\\previewexcel.html"), result, "UTF-8");
}
private static String getCellValue(Cell cell, boolean treatAsStr) {
if (cell == null) {
return "";
}
if (treatAsStr) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
}
import java.io.File;
import java.io.FileInputStream;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
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.struts2.ServletActionContext;
public class TestPoiExcelToHtml {
public static void excelToHtml(String fileName) throws Exception{
String path=ServletActionContext.getServletContext().getRealPath("/");
String targetPath=fileName.substring(fileName.lastIndexOf("\\upload"),fileName.length());
File excelFile = new File(path+targetPath); //创建文件对象
FileInputStream is = new FileInputStream(excelFile); //文件流
Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
Sheet sheet = workbook.getSheetAt(0);
int lastRowIndex = sheet.getLastRowNum();
String result="";
result+="<html>";
result+="<head>";
result+="<meta charset='UTF-8'/>";
result+="<link rel='shortcut icon' href='../systemicon/favico.ico' type='image/x-icon'/>";
result+="<title>模板预览</title>";
result+="<style type='text/css'>";
result+="body{width:10000px;}";
result+="table{border-collapse:collapse; border:1px solid black;}";
result+="table tr:first-child{display:none;}";
result+="table td{border:1px solid #000;}";
result+=".rownumber{display:none;}";
result+="</style>";
result+="</head>";
result+="<body>";
result+="<table>";
for (int rowIndex = 0; rowIndex <= lastRowIndex; rowIndex++){
Row currentRow = sheet.getRow(rowIndex);
result+="<tr>";
if(currentRow!=null){
//int firstColumnIndex = currentRow.getFirstCellNum();
int lastColumnIndex = currentRow.getLastCellNum();
for (int columnIndex = 0; columnIndex <= lastColumnIndex-1; columnIndex++) {
Cell cell = currentRow.getCell(columnIndex);
String currentCellValue = getCellValue(cell, true);
if(currentCellValue.equals("")||currentCellValue==null){
result+="<td></td>";
}else{
result+="<td>"+currentCellValue + "</td>";
}
}
}
result+="</tr>";
}
result+="</table>";
result+="</body>";
result+="</html>";
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
DOMSource domSource = new DOMSource ();
StreamResult streamResult = new StreamResult (outStream);
TransformerFactory tf = TransformerFactory.newInstance();
Transformer serializer = tf.newTransformer();
serializer.setOutputProperty (OutputKeys.ENCODING, "utf-8");
serializer.setOutputProperty (OutputKeys.INDENT, "yes");
serializer.setOutputProperty (OutputKeys.METHOD, "html");
serializer.transform (domSource, streamResult);
outStream.close();
FileUtils.writeStringToFile(new File (path, "publishbusiness\\fastpublish\\previewexcel.html"), result, "UTF-8");
}
private static String getCellValue(Cell cell, boolean treatAsStr) {
if (cell == null) {
return "";
}
if (treatAsStr) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
}