导出excel可以前台导出也可后台导出
后台想ajax下载的话
先ajax生成,再表单提交访问
一、前台导出(实质上已与java无关–js导出)
function loadxls(){
//获取下
var textCetent = $(".textCetent").text();
//console.log(textCetent);
//获取table的html内容了,里面包括标签的class或id等。
var oHtml = document.getElementById("table").outerHTML;
//alert(oHtml);
console.log(oHtml);
//样式
var cssfile=document.styleSheets[0].cssRules;
// 这里将table和style组成一个html,使用模板字符串
var excelHtml = "<html><head><meta charset='utf-8' /><style>table{border: 1px solid #ccc;border-collapse: collapse;border-spacing: 1px;}table tr td,table tr th{border: 1px solid #ccc;height: 40px;}.textCetent{text-align:center;}</style></head><body><h3 class=\"textCetent\">"+textCetent+"</h3>${oHtml}</body></html>";
// 生成xls文件并通过a标签下载到本地
excelHtml=excelHtml.replace("${oHtml}",oHtml);
console.log(excelHtml);
// 生成Excel
var excelBlob = new Blob([excelHtml], {
type: 'application/vnd.ms-excel'
})
if('msSaveOrOpenBlob' in navigator){
//我是ie
window.navigator.msSaveOrOpenBlob(excelBlob, textCetent+".xls");
return;
}
// 通过a标签下载到本地了,下载前可以利用a标签的download属性命名
// 创建一个a标签
var oA = document.createElement('a');
// 利用URL.createObjectURL()方法为a元素生成blob URL
oA.href = URL.createObjectURL(excelBlob);
// 给文件命名
oA.download =textCetent+'.xls';
//oA.innerHTML = "点击下载"
oA.click();
//document.getElementById('insert').appendChild(oA)
}
注意:
var oA = document.createElement('a');
// 利用URL.createObjectURL()方法为a元素生成blob URL
oA.href = URL.createObjectURL(excelBlob);
// 给文件命名
oA.download =textCetent+'.xls';
//oA.innerHTML = "点击下载"
oA.click();
上面的代码的意思是先用excelBlob把字符串内容转变成excelBlob链接,然后利用a标签自带的下载功能把内容下载下来。
以上代码在Chrome、Firefox、Safari、360、EdgeHtml浏览器中,均可以成功下载文件,但是在Edge中,会报错。
造成以上原因是在Edge中使用Blob生成的是不带域名的blob链接,
而chrome等浏览器生成的是带域名的,
所以在edge下通过a标签的href来下载是不行的。
二、后台导出
package com.bootdo.utils;
import com.bootdo.excelyk.entiy.Excelentiy;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class YKexcel {
public static void main(String[] args) throws IOException {
String ss="前台数据"; //在前台遍历了tr td封装成字符串了 下面解析 也可直接接收table 直接解析
String[] strs=ss.split("<");
List<List<Excelentiy>> listtr = new ArrayList();
for(int i=1;i<strs.length;i++){
List<Excelentiy> listtd= new ArrayList<>();
String aa=strs[i];
// System.out.println("行:"+aa);
String[] aastr=aa.split("-");
for(int j=0;j<aastr.length;j++){
String []kk=aastr[j].split(",");
System.out.println("td:"+kk[0]+" "+kk[1]+" "+kk[2]+" "+kk[3]);
Excelentiy exc=new Excelentiy();
exc.setTxt(kk[0]);
exc.setRow(kk[1]);
exc.setCol(kk[2]);
exc.setHrt(kk[3]);
listtd.add(exc);
}
listtr.add(listtd);
}
for(int i=0;i<listtr.size();i++){
for(int j=0;j<listtr.get(i).size();j++){
// System.out.println(listtr.get(i).get(j).toString());
}
}
//定义一个输出流
HSSFWorkbook wb = getsheet(listtr);
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)14);
FileOutputStream fos = new FileOutputStream(new File("D:/1.xls"));
wb.write(fos);
fos.flush();
fos.close();
}
public static HSSFWorkbook getsheet(List<List<Excelentiy>> listtr){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle contentStyle=wb.createCellStyle();
List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<CrossRangeCellMeta>();
int rowIndex = 2;
int roeIndexfinal=2;
for(List<Excelentiy> listtd:listtr){
HSSFRow row = sheet.createRow(rowIndex);
makeRowCell(listtd,rowIndex,row,0,contentStyle,crossRowEleMetaLs);
++rowIndex;
}
for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
sheet.addMergedRegion(new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()));
setRegionStyle(sheet, new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()),contentStyle);
}
//如果getRow()>1 则竖起来
for(int i=0;i<crossRowEleMetaLs.size();i++){
if(crossRowEleMetaLs.get(i).getRowSpan()>1) {
HSSFCell cell = HSSFCellUtil.getCell(sheet.getRow(crossRowEleMetaLs.get(i).getFirstRow()), crossRowEleMetaLs.get(i).getFirstCol());
//样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setRotation((short) 255);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell.setCellStyle(cellStyle);
}
}
//设置表头
/* HSSFCell cell = HSSFCellUtil.getCell(sheet.getRow(crossRowEleMetaLs.get(3).getFirstRow()), crossRowEleMetaLs.get(1).getFirstCol());
//样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setRotation((short) 255);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//cellStyle.
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell.setCellStyle(cellStyle);*/
contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
return wb;
}
private static int makeRowCell(List<Excelentiy> listtd, int rowIndex, HSSFRow row, int startCellIndex, HSSFCellStyle cellStyle,
List<CrossRangeCellMeta> crossRowEleMetaLs) {
int i = startCellIndex;
for (int eleIndex = 0; eleIndex < listtd.size(); i++, eleIndex++) {
int captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
while (captureCellSize > 0) {
for (int j = 0; j < captureCellSize; j++) {// 当前行跨列处理(补单元格)
row.createCell(i);
i++;
}
captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
}
String val = listtd.get(eleIndex).getTxt();
//System.out.println("val:"+val);
HSSFCell c = row.createCell(i);
c.setCellValue(val);
int rowSpan = NumberUtils.toInt(listtd.get(eleIndex).getRow(), 1);
int colSpan = NumberUtils.toInt(listtd.get(eleIndex).getCol(), 1);
c.setCellStyle(cellStyle);
if (rowSpan > 1 || colSpan > 1) { // 存在跨行或跨列
crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan));
}
if (colSpan > 1) {// 当前行跨列处理(补单元格)
for (int j = 1; j < colSpan; j++) {
i++;
row.createCell(i);
}
}
}
return i;
}
/**
* 获得因rowSpan占据的单元格
*
* @param rowIndex 行号
* @param colIndex 列号
* @param crossRowEleMetaLs 跨行列元数据
* @return 当前行在某列需要占据单元格
*/
private static int getCaptureCellSize(int rowIndex, int colIndex, List<CrossRangeCellMeta> crossRowEleMetaLs) {
int captureCellSize = 0;
for (CrossRangeCellMeta crossRangeCellMeta : crossRowEleMetaLs) {
if (crossRangeCellMeta.getFirstRow() < rowIndex && crossRangeCellMeta.getLastRow() >= rowIndex) {
if (crossRangeCellMeta.getFirstCol() <= colIndex && crossRangeCellMeta.getLastCol() >= colIndex) {
captureCellSize = crossRangeCellMeta.getLastCol() - colIndex + 1;
}
}
}
return captureCellSize;
}
/**
* 设置合并单元格的边框样式
*
* @param sheet
* @param region
* @param cs
*/
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle cs) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
HSSFRow row = HSSFCellUtil.getRow(i, sheet);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
cell.setCellStyle(cs);
}
}
}
}
实体
package com.bootdo.excelyk.entiy;
public class Excelentiy {
// txt+'||'+row+','+col+','+hrt+';;';
private String txt;
private String row;
private String col;
private String hrt; //横竖
public String getTxt() {
return txt;
}
public void setTxt(String txt) {
this.txt = txt;
}
public String getRow() {
return row;
}
public void setRow(String row) {
this.row = row;
}
public String getCol() {
return col;
}
public void setCol(String col) {
this.col = col;
}
public String getHrt() {
return hrt;
}
public void setHrt(String hrt) {
this.hrt = hrt;
}
@Override
public String toString() {
return "Excelentiy{" +
"txt='" + txt + '\'' +
", row='" + row + '\'' +
", col='" + col + '\'' +
", hrt='" + hrt + '\'' +
'}';
}
}
package com.bootdo.utils;
/**
* 跨行元素元数据
*
*/
public class CrossRangeCellMeta {
public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan) {
super();
this.firstRowIndex = firstRowIndex;
this.firstColIndex = firstColIndex;
this.rowSpan = rowSpan;
this.colSpan = colSpan;
}
private int firstRowIndex;
private int firstColIndex;
private int rowSpan;// 跨越行数
private int colSpan;// 跨越列数
public int getFirstRow() {
return firstRowIndex;
}
public int getLastRow() {
return firstRowIndex + rowSpan - 1;
}
public int getFirstCol() {
return firstColIndex;
}
public int getLastCol() {
return firstColIndex + colSpan - 1;
}
public int getColSpan(){
return colSpan;
}
public int getRowSpan() {
return rowSpan;
}
public void setRowSpan(int rowSpan) {
this.rowSpan = rowSpan;
}
public void setColSpan(int colSpan) {
this.colSpan = colSpan;
}
}