import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.springframework.util.ResourceUtils;
import com.smartdot.commons.model.AjaxValidate;
/**
* html导出excel
*
* 2017-11-9 wanghui
*/
public class HtmlToExcelUtil {
public static void main(String args[]) throws IOException{
///读取classpath目录下面的路径
String path=HtmlToExcelUtil.class.getResource("/").getPath();
path+="roster.html";
toExcelByFileName(path,"roster");
}
//得到Document并且设置编码格式 -根据文件
public static Document getDoc(String fileName) throws IOException{
File myFile=new File(fileName);
Document doc= Jsoup.parse(myFile, "UTF-8","");
return doc;
}
//得到Document并且设置编码格式-根据html
public static Document getDocByHtmlStr(String htmlStr) throws IOException{
Document doc= Jsoup.parse(htmlStr);
return doc;
}
///这个方法用于根据trs行数和sheet画出整个表格
public static void mergeColRow(Elements trs,WritableSheet sheet) throws RowsExceededException, WriteException{
int[][] rowhb=new int[300][300];
for(int i=0;i<trs.size();i++){
Element tr=trs.get(i);
Elements tds=tr.getElementsByTag("td");
if(i ==1){
setColWidthByThs(tds, sheet);
}
if(tds.size() ==0 ){
tds = tr.getElementsByTag("th");
setColWidthByThs(tds, sheet);
}
int realColNum=0;
for(int j=0;j<tds.size();j++){
Element td=tds.get(j);
if(rowhb[i][realColNum]!=0){
realColNum=getRealColNum(rowhb,i,realColNum);
}
int rowspan=1;
int colspan=1;
if(td.attr("rowspan")!=""){
rowspan = Integer.parseInt(td.attr("rowspan"));
}
if(td.attr("colspan")!=""){
colspan = Integer.parseInt(td.attr("colspan"));
}
String text=td.text();
drawMegerCell(rowspan,colspan,sheet,realColNum,i,text,rowhb);
realColNum=realColNum+colspan;
}
}
}
///这个方法用于根据样式画出单元格,并且根据rowpan和colspan合并单元格
public static void drawMegerCell(int rowspan,int colspan,WritableSheet sheet,int realColNum,int realRowNum,String text,int[][] rowhb) throws RowsExceededException, WriteException{
for(int i=0;i<rowspan;i++){
for(int j=0;j<colspan;j++){
if(i!=0||j!=0){
text="";
}
Label label = new Label(realColNum+j,realRowNum+i,text);
WritableFont countents = new WritableFont(WritableFont.TIMES,10); // 设置单元格内容,字号12
WritableCellFormat cellf = new WritableCellFormat(countents );
cellf.setAlignment(jxl.format.Alignment.CENTRE);//把水平对齐方式指定为居中
cellf.setWrap(true); //设置自动换行
cellf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//把垂直对齐方式指定为居
label.setCellFormat(cellf);
sheet.addCell(label);
rowhb[realRowNum+i][realColNum+j]=1;
}
}
sheet.mergeCells(realColNum,realRowNum, realColNum+colspan-1,realRowNum+rowspan-1);
}
public static int getRealColNum(int[][] rowhb,int i,int realColNum){
while(rowhb[i][realColNum]!=0){
realColNum++;
}
return realColNum;
}
///根据colgroups设置表格的列宽
public static void setColWidth(Elements colgroups,WritableSheet sheet){
if(colgroups.size()>0){
Element colgroup=colgroups.get(0);
Elements cols=colgroup.getElementsByTag("col");
for(int i=0;i<cols.size();i++){
Element col=cols.get(i);
String strwd=col.attr("width");
if(col.attr("width")!=""){
int wd=Integer.parseInt(strwd);
sheet.setColumnView(i,wd/8);
}
}
}
}
///根据获取到的th设置表格的列宽
public static void setColWidthByThs(Elements ths,WritableSheet sheet){
if(ths.size()>0){
for(int i=0;i<ths.size();i++){
Element th = ths.get(i);
String strwd= th.attr("width");
if(th.attr("width")!=""){
int wd=Integer.parseInt(strwd);
sheet.setColumnView(i,wd/8);
}
/*int colspanNum = 0;
if(th.attr("width")!=""){
int wd=Integer.parseInt(strwd);
if(th.attr("colspan")!=""){
int colspan = Integer.parseInt(th.attr("colspan"));
for(int j=0;j<colspan;j++){
sheet.setColumnView(i+j,wd/(8+colspan));
}
colspanNum += colspan;
}else{
sheet.setColumnView(i+colspanNum,wd/8);
}
}*/
}
}
}
//toExcel是根据html文件地址生成对应的xls
public static void toExcelByFileName(String fileName,String excelName)throws IOException{
Document doc=getDoc(fileName);
String title = doc.title();
///得到样式,以后可以根据正则表达式解析css,暂且没有找到cssparse
Elements style= doc.getElementsByTag("style");
///得到Table,demo只演示输入一个table,以后可以用循环遍历tables集合输入所有table
Elements tables= doc.getElementsByTag("TABLE");
if(tables.size()==0){
return;
}
Element table=tables.get(0);
//得到所有行
Elements trs = table.getElementsByTag("tr");
///得到列宽集合
Elements colgroups=table.getElementsByTag("colgroup");
try {
//文件保存到classpath目录下面
String path=HtmlToExcelUtil.class.getResource("/").getPath();
path+=excelName+".xls";
System.out.println(path);
WritableWorkbook book = Workbook.createWorkbook(new File(path));
WritableSheet sheet = book.createSheet("人事关系", 0);
setColWidth(colgroups,sheet);
mergeColRow(trs,sheet);
book.write();
book.close();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
/**
* 根据html内容生成对应的xls
* TODO
* @param htmlStr
* @param excelName
* @param sheetName
* @return
* @throws IOException
* 2017-11-8 wanghui
*/
public static AjaxValidate toExcelByHtmlStr(HttpServletResponse response,String htmlStr,String excelName,String sheetName)throws IOException{
OutputStream out = new BufferedOutputStream(response.getOutputStream());
Document doc= getDocByHtmlStr(htmlStr);
String title = doc.title();
///得到样式,以后可以根据正则表达式解析css,暂且没有找到cssparse
Elements style= doc.getElementsByTag("style");
///得到Table,demo只演示输入一个table,以后可以用循环遍历tables集合输入所有table
Elements tables= doc.getElementsByTag("TABLE");
if(tables.size()==0){
return new AjaxValidate("",false,"");
}
Element table=tables.get(0);
//得到所有行
Elements trs = table.getElementsByTag("tr");
///得到列宽集合
Elements colgroups=table.getElementsByTag("colgroup");
try {
//文件保存到classpath目录下面
String path = HtmlToExcelUtil.class.getResource("/").getPath()+excelName+".xls";
System.out.println(path);
WritableWorkbook book = Workbook.createWorkbook(new File(path));
WritableSheet sheet = book.createSheet(sheetName, 0);
setColWidth(colgroups,sheet);
mergeColRow(trs,sheet);
book.write();
book.close();
return new AjaxValidate("",true,path);
} catch (RowsExceededException e) {
e.printStackTrace();
return new AjaxValidate("",false,"");
} catch (WriteException e) {
e.printStackTrace();