最近做了个项目需要对excel进行发送邮件。但是有个需求是需要将excel里面的sheet0的内容原本复制为邮件的正文。样式都要。所以只能操作excel转为html然后嵌套再邮件中发送了。
1.导入pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
2.操作
package statement;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import statement.base.StringUtil;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @ClassName: PoiExcelToHtmlUtil
* @Description: TODO(poi转excel为html)
*/
public class ExcelToHtml {
private static Map<String, Object> map[];
public static void main(String [] args){
String html=excelWriteToHtml(filepath);
System.out.print(html);
}
/**
* 程序入口方法(读取指定位置的excel,将其转换成html形式的字符串,
* @return <table>...</table> 字符串
*/
public static String excelWriteToHtml(String sourcePath){
File sourceFile = new File(sourcePath);
try {
InputStream fis = new FileInputStream(sourceFile);
String excelHtml = ExcelToHtml.readExcelToHtml(fis, true);
return excelHtml;
} catch (FileNotFoundException e) {
e.printStackTrace();
return null;
}
}
/**
* 程序入口方法(将指定路径的excel文件读取成字符串)
* @param filePath 文件的路径
* @param isWithStyle 是否需要表格样式 包含 字体 颜色 边框 对齐方式
* @return <table>...</table> 字符串
*/
public static String readExcelToHtml(String filePath , boolean isWithStyle){
InputStream is = null;
String htmlExcel = null;
try {
File sourcefile = new File(filePath);
is = new FileInputStream(sourcefile);
Workbook wb = WorkbookFactory.create(is);
htmlExcel = readWorkbook(wb, isWithStyle);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return htmlExcel;
}
/**
* 程序入口方法(将指定路径的excel文件读取成字符串)
* @param is excel转换成的输入流
* @param isWithStyle 是否需要表格样式 包含 字体 颜色 边框 对齐方式
* @return <table>...</table> 字符串
*/
public static String readExcelToHtml(InputStream is,boolean isWithStyle){
String htmlExcel = null;
try {
Workbook wb = WorkbookFactory.create(is);
htmlExcel = readWorkbook(wb,isWithStyle);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return htmlExcel;
}
/**
* 根据excel的版本分配不同的读取方法进行处理
* @param wb
* @param isWithStyle
* @return
*/
private static String readWorkbook(Workbook wb,boolean isWithStyle){
String htmlExcel = "";
if (wb instanceof XSSFWorkbook) {
XSSFWorkbook xWb = (XSSFWorkbook) wb;
htmlExcel = getExcelInfo(xWb, isWithStyle);
}else if(wb instanceof HSSFWorkbook){
HSSFWorkbook hWb = (HSSFWorkbook) wb;
htmlExcel = getExcelInfo(hWb, isWithStyle);
}
return htmlExcel;
}
/**
* 读取excel成string
* @param wb
* @param isWithStyle
* @return
*/
public static String getExcelInfo(Workbook wb, boolean isWithStyle){
StringBuffer sb = new StringBuffer();
//获取第一个Sheet的内容
Sheet sheet = wb.getSheetAt(0);
// map等待存储excel图片
Map<String, PictureData> sheetIndexPicMap = getSheetPictrues(0, sheet, wb);
//临时保存位置,正式环境根据部署环境存放其他位置
try {
if(sheetIndexPicMap != null)
printImg(sheetIndexPicMap);
} catch (IOException e) {
e.printStackTrace();
}
//读取excel拼装html
int lastRowNum = sheet.getLastRowNum();
map = getRowSpanColSpanMap(sheet);
sb.append("<table style='border-collapse:collapse;width:100%;'>");
Row row = null; //兼容
Cell cell = null; //兼容
for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum ++) {
if(rowNum > 1000) break;
row = sheet.getRow(rowNum);
int lastColNum = ExcelToHtml.getColsOfTable(sheet)[0];
int rowHeight = ExcelToHtml.getColsOfTable(sheet)[1];
if(null != row) {
lastColNum = row.getLastCellNum();
rowHeight = row.getHeight();
}
if (null == row) {
sb.append("<tr><td > </td></tr>");
continue;
}else if(row.getZeroHeight()){
continue;
}else if(0 == rowHeight){
continue; //针对jxl的隐藏行(此类隐藏行只是把高度设置为0,单getZeroHeight无法识别)
}
sb.append("<tr>");
for (int colNum = 0; colNum < lastColNum; colNum ++) {
if(sheet.isColumnHidden(colNum)) continue;
String imageRowNum = "0_" + rowNum + "_" + colNum;
String imageHtml = "";
cell = row.getCell(colNum);
if ((sheetIndexPicMap != null && !sheetIndexPicMap.containsKey(imageRowNum) || sheetIndexPicMap == null) && cell == null) { //特殊情况 空白的单元格会返回null+//判断该单元格是否包含图