把页面Table表格内容导出为Excel-仅是当前页面上显示的table 分页未知(转载略修改)

最新推荐文章于 2022-07-06 11:35:20 发布

茻茻茻茻

最新推荐文章于 2022-07-06 11:35:20 发布

阅读量859

点赞数

原地址:http://zyn010101.iteye.com/blog/1751678

------JS---------------------------------------------------------------

------页面---------------------------------------------------------------
<form id="tableForm" action="url地址" method="post" style="margin-top: 100px;">
<input id="tableId" name="tableId" type="hidden" />
</form>

------Controller---------------------------------------------------------------

public String testExport(HttpServletRequest request, HttpServletResponse response, Model model, String tableId) {
response.setContentType("application/binary;charset=ISO8859_1");
try {
ServletOutputStream outputStream = response.getOutputStream();
String fileName = new String(("EXCEL文件名").getBytes(), "ISO8859_1") + DateUtils.formatDateTime(new Date());
String suffix = ".xlsx";
response.setHeader("Content-disposition", "attachment; filename=" + fileName + suffix);// 组装附件名称和格式
tableId = tableId.replaceAll("<tbody>", "").replaceAll("</tbody>", "");//因为原table中有<tbody>后台导出的时候不能正常显示数据, 故替换掉了
TableToExcelUtil.createExcelFormTable("sheet1", HtmlUtils.htmlUnescape(tableId),1, outputStream); //1.工作簿名 2.tableId 3.标题栏行数
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
------service---------------------------------------------------------------
import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;

public class TableToExcelUtil {
/**
*
* @param sheetName
* @param html
* @param headNum表头的行数
* @throws FileNotFoundException
*/
@SuppressWarnings({ "unchecked", "deprecation" })
public static void createExcelFormTable(String sheetName,String html,int headNum, ServletOutputStream outputStream) throws FileNotFoundException{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
CellStyle headStyle = createHeadStyle(wb);
CellStyle bodyStyle = createBodyStyle(wb);
SAXBuilder sb = new SAXBuilder();
try {
ByteArrayInputStream is = new ByteArrayInputStream(html.getBytes("UTF-8"));
org.jdom.Document document = sb.build(is);
//获取table节点
Element root = document.getRootElement();
//获取tr的list
List<Element> trList = root.getChildren("tr");
int[][] area = getCellArea(trList);
//循环创建行
for(int i=0;i<trList.size();i++){
HSSFRow row = sheet.createRow(i);
List<Element> tdList = trList.get(i).getChildren("td");
//该行td的序号
int tdIndex = 0;
for(int ii=0;ii<area[i].length;ii++){
row.createCell(ii);
HSSFCell cell = row.getCell(ii);
//判断是否为表头，使用对应的excel格式
if(i<headNum){
cell.setCellStyle(headStyle);
}else{
cell.setCellStyle(bodyStyle);
}
//如果对应的矩阵数字为1，则和横向前一单元格合并
if(area[i][ii]==1){
sheet.addMergedRegion(new CellRangeAddress(i,i,ii-1,ii));
}else if(area[i][ii]==2){//如果对应的矩阵数字为2，则和纵向的前一单元格合并
sheet.addMergedRegion(new CellRangeAddress(i-1,i,ii,ii));
}else{//如果为0，显示td中对应的文字,td序号加1
cell.setCellValue(getInnerText(tdList.get(tdIndex)));
tdIndex ++;
}
}
}
try {
wb.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (JDOMException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

@SuppressWarnings("unchecked")
private static int[][] getCellArea(List<Element> trList){
//获取table单元格矩阵
Element headtr = trList.get(0);
List<Element> headTdList = headtr.getChildren("td");
//每行的未经合并的单元格个数
int cols = 0;
for(Element e:headTdList){
//System.out.println("#"+e.getText());
int colspan = Integer.valueOf(null==e.getAttributeValue("colspan")?"0":e.getAttributeValue("colspan"));
if(colspan==0){
colspan =1;
}
cols += colspan;
}
//初始化单元格矩阵
int[][] area = new int[trList.size()][cols];
for(int i=0;i<trList.size();i++){
Element tr = trList.get(i);
List<Element> tdList = tr.getChildren("td");
for(int ii=0,tdIndex=0;ii<cols;ii++){
//如果大于0，表明已经处理过，不需再处理
if(area[i][ii]>0){
continue;
}
Element td = tdList.get(tdIndex);
int colspan = Integer.valueOf(null==td.getAttributeValue("colspan")?"0":td.getAttributeValue("colspan"));
colspan = colspan>1?colspan:1;
//单元格需要被横向合并声明为1
for(int m=1;m<colspan;m++){
area[i][ii+m]=1;
}
int rowspan = Integer.valueOf(null==td.getAttributeValue("rowspan")?"0":td.getAttributeValue("rowspan"));
rowspan = rowspan>1?rowspan:1;
//单元格需要被纵向向合并声明为2
for(int m=1;m<rowspan;m++){
area[m+i][ii] = 2;
}
//列和行都有跨度的区域，把第一行，第一列外的区域置为2
if(colspan>1 && rowspan>1){
for(int j=1;j<rowspan;j++){
for(int k=1;k<colspan;k++){
area[i+j][ii+k]=2;
}
}
}
tdIndex ++;
}
}
return area;
}

/**-
* 设置表头样式
* @param wb
* @return
*/
private static CellStyle createHeadStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
Font headerFont = wb.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);

style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}

/**-
* 设置表单记录样式
* @param wb
* @return
*/
private static CellStyle createBodyStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
Font headerFont = wb.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);

style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}

private static String getInnerText(Element td){
String txt = "";
if(td.getText()==null || td.getText().equals("")){
if(null != td.getChildren()){
for(int i=0;i<td.getChildren().size();i++){
Element e = (Element)td.getChildren().get(i);
txt += getInnerText(e);
}
}
}else{
txt = td.getText();
}
return txt;
}
}

页面显示图

导出Excel效果图

茻茻茻茻

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
把页面Table表格内容导出为Excel-仅是当前页面上显示的table 分页未知(转载略修改)

-----------------------------------------------------------------------------JS---------------------------------------------------------------------------function test(){var table = $("#tableD
复制链接

扫一扫