Java后端pom 引入 poi-ooxml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
前端js代码实例
将内容上传然后转换为Excel后下载=(url,content)=>{
let xhr = new XMLHttpRequest();
xhr.open("post",url);
xhr.responseType="blob"; // 要设置responseType为blob
xhr.onload=(ev)=>{
let blob = xhr.response; //这步只是为了说明,设置responseType为blob后,response就是blob
let a=document.createElement("a");
a.href = window.URL.createObjectURL(blob);
a.download="excle.xls";
a.click();
window.URL.revokeObjectURL(a.href); //释放内存
};
xhr.send(content);
};
~function fexe010(){
let 上传内容=JSON.stringify(
[
["第一列","第二列","第三列"]
,
[1,2,3]
,
[4,5,6]
,
[7,8,9]
]
);
将内容上传然后转换为Excel后下载("http://localhost:8080/excel/ar2dToXls", 上传内容);
}();
主要方法提炼
将内容上传然后转换为Excel后下载=function(url,content,fileName="excel.xls"){
let r=new XMLHttpRequest();
r.open("post",url);
r.responseType="blob"; // 要设置responseType为blob
r.onload=(ev)=>{
let a=document.createElement("a");
a.href=URL.createObjectURL(r.response);
a.download=fileName;
a.click();
window.URL.revokeObjectURL(a.href); //释放内存
};
r.send(content);
};
URL.createObjectURL(接收Blob或File或MediaSource对象作为参数)
href = urlObject = URL.createObjectURL(接收Blob或File或MediaSource对象作为参数)
MDN对window.URL的说明 , 有两个静态方法: URL.createObjectURL 和 URL.revokeObjectURL
XMLHttpRequest.responseType 默认""与"text"都是text , 还有 “blob”,“arraybuffer”,“document”,"json"等
后端控制器代码
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.servlet.http.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;
@RestController
@RequestMapping("excel")
public class ExcelCtl {
ObjectMapper om = new ObjectMapper();
@GetMapping()
public Object defGet() {
return null;
}
@PostMapping("a2ToExcel")
public Object a2ToExcel(HttpServletRequest req, HttpServletResponse resp) {
// System.out.println("a2ToExcel");
String [][] ar2d=null;
resp.setHeader("Content-Type", "application/x-xls");
try {
ar2d=om.readValue(req.getInputStream(), String[][].class);
} catch (JsonParseException e) {
e.printStackTrace();
} catch (JsonMappingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
OutputStream os = resp.getOutputStream();
a2ToExcel(ar2d, os, req.getParameter("扩展名")); //指定扩展名为 xlsx 得到xlsx 否则为 xls
} catch (IOException e) {
System.err.println("response.getOutputStream()发生异常");
e.printStackTrace();
}
return null;
}
public static Workbook a2ToExcel(String[][] ar2d, OutputStream os, final String exName){
Workbook wb = null;
if("xlsx".equals(exName))wb = new XSSFWorkbook(); //指定扩展名为 xlsx 得到xlsx 否则为 xls
else wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("sheet0");
for(int r=0;r<ar2d.length;r++) {
Row row = sheet.createRow(r); String ar[] = ar2d[r];
for(int c=0;c<ar.length;c++) {
String str = ar[c];
Cell cell = row.createCell(c);
cell.setCellValue(str);
// System.out.append(str).append(" ");
}
// System.out.println();
}
// try {
// FileOutputStream fos = new FileOutputStream("excelTest010.xls"); //生成在项目文件夹或jar包文件夹
// wb.write(fos); //wb.write()可以多次调用输出的多个OutputStream
// fos.close();
// }catch(Exception ex) {ex.printStackTrace();}
if(os!=null) {
try{wb.write(os);}catch(IOException e) {e.printStackTrace();}
try{wb.close();}catch(IOException e) {e.printStackTrace();}
try{os.close();}catch(IOException e) {e.printStackTrace();}
}
return wb;
}
@SuppressWarnings("unchecked")
public static Workbook a2ToExcel(ArrayList<?> ar2d, OutputStream os, final String ExtensionName){
String [][] sar2d = new String[ar2d.size()][];
for(int r=0;r<sar2d.length;r++) {
Object ro = ar2d.get(r);
if(ro instanceof String[])sar2d[r]=(String[]) ro;
else if(ro instanceof ArrayList) {
ArrayList<String> sal = (ArrayList<String>) ro;
String [] sr = new String[sal.size()];
sal.toArray(sr);
sar2d[r]=sr;
}
}
return a2ToExcel(sar2d, os, ExtensionName);
}
public static Workbook ajToExcel(LinkedHashMap<String,String>[] arj, OutputStream os, final String ExtensionName) {
ArrayList<ArrayList<String>> ar2d = new ArrayList<ArrayList<String>>();
LinkedHashMap<String,String> m0 = arj[0];
ArrayList<String> headAl = new ArrayList<String>(m0.size()); ar2d.add(headAl);
m0.forEach((k,v)->{headAl.add(k);});
for(int r=0; r<arj.length; r++){
ArrayList<String> rAl = new ArrayList<>(); ar2d.add(rAl);
LinkedHashMap<String,String> mr = arj[r];
mr.forEach((k,v)->{rAl.add(v);});
}
// System.out.println(ar2d);
return a2ToExcel(ar2d, os, ExtensionName);
}
@SuppressWarnings("unchecked")
public static Workbook ajToExcel(ArrayList<LinkedHashMap<String, String>> arj, OutputStream os, final String ExtensionName) {
LinkedHashMap<String,String>[] arj1 = new LinkedHashMap[arj.size()];
arj.toArray(arj1);
return ajToExcel(arj1, os, ExtensionName);
}
static class Arj extends ArrayList<LinkedHashMap<String,String>>{
/**
*
*/
private static final long serialVersionUID = 1L;
}
@PostMapping("ajToExcel")
public Object ajToExcel(HttpServletRequest req, HttpServletResponse resp) {
Arj arj=null;
resp.setHeader("Content-Type", "application/x-xls");
try {
arj=om.readValue(req.getInputStream(), Arj.class);
// System.out.println(arj);
} catch (JsonParseException e) {
e.printStackTrace();
} catch (JsonMappingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
OutputStream os = resp.getOutputStream();
ajToExcel(arj, os, req.getParameter("扩展名")); //指定扩展名为 xlsx 得到xlsx 否则为 xls
} catch (IOException e) {
System.err.println("response.getOutputStream()发生异常");
e.printStackTrace();
}
return null;
}
@SuppressWarnings("deprecation")
public static String[][] sheet2Sar2d(Sheet sheet){
String[][] result = null; Row row = null; Cell cell = null;
try {
result = new String[sheet.getLastRowNum()+1][]; //getLastRowNum()要+1 , getLastCellNum()不用
for(int r=0; r<result.length; r++) {
row = sheet.getRow(r);
result[r] = row==null ? new String[0] : new String[row.getLastCellNum()];
for(int c=0; c<result[r].length ; c++) {
cell = row.getCell(c);
if(cell==null) {
result[r][c]="";
}else {
if(cell.getCellType()==CellType.STRING) {result[r][c]=cell.getStringCellValue();}
else if(cell.getCellType()==CellType.NUMERIC) {
if(DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
String dtStr;
if(date.getSeconds()!=0 && date.getMinutes()!=0 && date.getHours()!=0)
{dtStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}
else{dtStr = new SimpleDateFormat("yyyy-MM-dd").format(date);}
result[r][c]=dtStr;
}else{
String str = cell.toString();
if(str.endsWith(".0"))str=str.substring(0, str.length()-2);
result[r][c]=str;
};
}else {
result[r][c]=cell.toString();
}
}
}
}
}catch(Exception exception) {exception.printStackTrace();}
return result;
}
public static Arj a2ToAm(String[][] ar2d) {
String heads[] = ar2d[0];
Arj arj = new Arj();
for(int r=1; r<ar2d.length; r++) {
String rsar[] = ar2d[r];
LinkedHashMap<String, String> lh = new LinkedHashMap<String, String>();
for(int c=0;c<heads.length;c++) {
lh.put(heads[c], rsar[c]);
}
arj.add(lh);
}
return arj;
}
public static String[][] excelSheet0ToSar2d(InputStream is){
String sar2d[][] = null; Workbook workbook=null;
try {
workbook = WorkbookFactory.create(is);
sar2d = sheet2Sar2d(workbook.getSheetAt(0));
is.close(); workbook.close();
}catch(Exception exception) {exception.printStackTrace();}
finally {
try {is.close();}catch(Exception e) {}
try {workbook.close();}catch(Exception e) {}
}
return sar2d;
}
public static Arj excelSheet0ToArj(InputStream is) {
String ar2d[][] = excelSheet0ToSar2d(is);
return a2ToAm(ar2d);
}
@PostMapping("testExcelToArj")
public Object testExcelToArj(HttpServletRequest req) throws Exception{
String r="";
Arj arj = excelSheet0ToArj(req.getInputStream());
System.out.println(arj);
r = om.writeValueAsString(arj);
System.out.println(r);
return r;
}
public static void main(String[] arguments) throws Exception{
InputStream is = null;
is = new FileInputStream("d:/temp/temp.xls");
printExcel(is);
}
public static void printExcel(InputStream is) {
String[][] sar2d = excelSheet0ToSar2d(is);
for(int r=0; r<sar2d.length; r++) {
for(int c=0; c<sar2d[r].length; c++) {
System.out.append(sar2d[r][c]).append(',');
}
System.out.println();
}
}
}
完整Html示例代码
<!DOCTYPE html><html lang="zh-CN"><head><meta charset="utf-8"/><title></title><style>
#BodyH{display:flex; align-items:center; justify-content:center;}
button{padding:10px;}
button:hover:not(:active){cursor:pointer; transform:scale(1.05); }
button:active{transform:scale(0.95);}
legend{font-size:36px;}
</style><script>const ge=window.dgebi=function(e,s){if(e&&e.constructor===String){e=document.getElementById(e);e=e||document.querySelector(e);}if(e&&s)e.appendChild(s);if(!e){console.log("dgebi没能获取到元素");}return e;},
dcept=(p,tn="DIV")=>{let e=document.createElement(tn);e.idfine=(i)=>{e.id=i;return window[i]=e;};e.htm=(h)=>{e.innerHTML=h;return e;};dgebi(p,e);return e;} ;
if(!document.title.length)(function(){var str=document.URL; str=str.substring( str.lastIndexOf("/")+1 , str.length-5); str=decodeURI(str); document.title=str;})();
</script><script>
</script></head><body style="margin:0; font-size:16px; "><header id="BodyH">
<label style="font-size:3rem; "></label><script>{let s=document.currentScript; let l=s.previousElementSibling; if(!l.innerHTML.length)l.textContent=document.title}</script>
</header><div id="BodyB">
<div style="display:grid; grid-template-columns:1fr 1fr;">
<button type="button" style="padding:40px; font-size:20px; border-radius:6px; background-color:#0099ff;"
onclick="eval(this.textContent+'();')">将a2内容上传然后转换为xls后下载方法演示</button>
<button type="button" style="padding:40px; font-size:20px; border-radius:6px; background-color:#0099ff;"
onclick="eval(this.textContent+'();')">将a2内容上传然后转换为xlsx后下载方法演示</button>
<button type="button" style="padding:40px; font-size:20px; border-radius:6px; background-color:#0099ff;"
onclick="eval(this.textContent+'();')">将aj内容上传然后转换为xls后下载方法演示</button>
<button type="button" style="padding:40px; font-size:20px; border-radius:6px; background-color:#0099ff;"
onclick="eval(this.textContent+'();')">将aj内容上传然后转换为xlsx后下载方法演示</button>
</div>
<fieldset><legend>前端js代码<button type="button" onclick="copyTextContentToClipboardBy(this.parentNode.nextElementSibling.children[0])">点击复制👇</button></legend><pre><code id="前端js代码"></code></pre></fieldset>
<fieldset><legend>后端控制器代码<button type="button" onclick="copyTextContentToClipboardBy(this.parentNode.nextElementSibling.children[0])">点击复制👇</button></legend><pre><code id="后端控制器代码"></code></pre></fieldset>
<script>
{
let code = dgebi("后端控制器代码");
code.textContent=`
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.servlet.http.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;
@RestController
@RequestMapping("excel")
public class ExcelCtl {
ObjectMapper om = new ObjectMapper();
@GetMapping()
public Object defGet() {
return null;
}
@PostMapping("a2ToExcel")
public Object a2ToExcel(HttpServletRequest req, HttpServletResponse resp) {
// System.out.println("a2ToExcel");
String [][] ar2d=null;
resp.setHeader("Content-Type", "application/x-xls");
try {
ar2d=om.readValue(req.getInputStream(), String[][].class);
} catch (JsonParseException e) {
e.printStackTrace();
} catch (JsonMappingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
OutputStream os = resp.getOutputStream();
a2ToExcel(ar2d, os, req.getParameter("扩展名")); //指定扩展名为 xlsx 得到xlsx 否则为 xls
} catch (IOException e) {
System.err.println("response.getOutputStream()发生异常");
e.printStackTrace();
}
return null;
}
public static Workbook a2ToExcel(String[][] ar2d, OutputStream os, final String exName){
Workbook wb = null;
if("xlsx".equals(exName))wb = new XSSFWorkbook(); //指定扩展名为 xlsx 得到xlsx 否则为 xls
else wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("sheet0");
for(int r=0;r<ar2d.length;r++) {
Row row = sheet.createRow(r); String ar[] = ar2d[r];
for(int c=0;c<ar.length;c++) {
String str = ar[c];
Cell cell = row.createCell(c);
cell.setCellValue(str);
// System.out.append(str).append(" ");
}
// System.out.println();
}
// try {
// FileOutputStream fos = new FileOutputStream("excelTest010.xls"); //生成在项目文件夹或jar包文件夹
// wb.write(fos); //wb.write()可以多次调用输出的多个OutputStream
// fos.close();
// }catch(Exception ex) {ex.printStackTrace();}
if(os!=null) {
try{wb.write(os);}catch(IOException e) {e.printStackTrace();}
try{wb.close();}catch(IOException e) {e.printStackTrace();}
try{os.close();}catch(IOException e) {e.printStackTrace();}
}
return wb;
}
@SuppressWarnings("unchecked")
public static Workbook a2ToExcel(ArrayList<?> ar2d, OutputStream os, final String ExtensionName){
String [][] sar2d = new String[ar2d.size()][];
for(int r=0;r<sar2d.length;r++) {
Object ro = ar2d.get(r);
if(ro instanceof String[])sar2d[r]=(String[]) ro;
else if(ro instanceof ArrayList) {
ArrayList<String> sal = (ArrayList<String>) ro;
String [] sr = new String[sal.size()];
sal.toArray(sr);
sar2d[r]=sr;
}
}
return a2ToExcel(sar2d, os, ExtensionName);
}
public static Workbook ajToExcel(LinkedHashMap<String,String>[] arj, OutputStream os, final String ExtensionName) {
ArrayList<ArrayList<String>> ar2d = new ArrayList<ArrayList<String>>();
LinkedHashMap<String,String> m0 = arj[0];
ArrayList<String> headAl = new ArrayList<String>(m0.size()); ar2d.add(headAl);
m0.forEach((k,v)->{headAl.add(k);});
for(int r=0; r<arj.length; r++){
ArrayList<String> rAl = new ArrayList<>(); ar2d.add(rAl);
LinkedHashMap<String,String> mr = arj[r];
mr.forEach((k,v)->{rAl.add(v);});
}
// System.out.println(ar2d);
return a2ToExcel(ar2d, os, ExtensionName);
}
@SuppressWarnings("unchecked")
public static Workbook ajToExcel(ArrayList<LinkedHashMap<String, String>> arj, OutputStream os, final String ExtensionName) {
LinkedHashMap<String,String>[] arj1 = new LinkedHashMap[arj.size()];
arj.toArray(arj1);
return ajToExcel(arj1, os, ExtensionName);
}
static class Arj extends ArrayList<LinkedHashMap<String,String>>{
/**
*
*/
private static final long serialVersionUID = 1L;
}
@PostMapping("ajToExcel")
public Object ajToExcel(HttpServletRequest req, HttpServletResponse resp) {
Arj arj=null;
resp.setHeader("Content-Type", "application/x-xls");
try {
arj=om.readValue(req.getInputStream(), Arj.class);
// System.out.println(arj);
} catch (JsonParseException e) {
e.printStackTrace();
} catch (JsonMappingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
OutputStream os = resp.getOutputStream();
ajToExcel(arj, os, req.getParameter("扩展名")); //指定扩展名为 xlsx 得到xlsx 否则为 xls
} catch (IOException e) {
System.err.println("response.getOutputStream()发生异常");
e.printStackTrace();
}
return null;
}
@SuppressWarnings("deprecation")
public static String[][] sheet2Sar2d(Sheet sheet){
String[][] result = null; Row row = null; Cell cell = null;
try {
result = new String[sheet.getLastRowNum()+1][]; //getLastRowNum()要+1 , getLastCellNum()不用
for(int r=0; r<result.length; r++) {
row = sheet.getRow(r);
result[r] = row==null ? new String[0] : new String[row.getLastCellNum()];
for(int c=0; c<result[r].length ; c++) {
cell = row.getCell(c);
if(cell==null) {
result[r][c]="";
}else {
if(cell.getCellType()==CellType.STRING) {result[r][c]=cell.getStringCellValue();}
else if(cell.getCellType()==CellType.NUMERIC) {
if(DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
String dtStr;
if(date.getSeconds()!=0 && date.getMinutes()!=0 && date.getHours()!=0)
{dtStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}
else{dtStr = new SimpleDateFormat("yyyy-MM-dd").format(date);}
result[r][c]=dtStr;
}else{
String str = cell.toString();
if(str.endsWith(".0"))str=str.substring(0, str.length()-2);
result[r][c]=str;
};
}else {
result[r][c]=cell.toString();
}
}
}
}
}catch(Exception exception) {exception.printStackTrace();}
return result;
}
public static Arj a2ToAm(String[][] ar2d) {
String heads[] = ar2d[0];
Arj arj = new Arj();
for(int r=1; r<ar2d.length; r++) {
String rsar[] = ar2d[r];
LinkedHashMap<String, String> lh = new LinkedHashMap<String, String>();
for(int c=0;c<heads.length;c++) {
lh.put(heads[c], rsar[c]);
}
arj.add(lh);
}
return arj;
}
public static String[][] excelSheet0ToSar2d(InputStream is){
String sar2d[][] = null; Workbook workbook=null;
try {
workbook = WorkbookFactory.create(is);
sar2d = sheet2Sar2d(workbook.getSheetAt(0));
is.close(); workbook.close();
}catch(Exception exception) {exception.printStackTrace();}
finally {
try {is.close();}catch(Exception e) {}
try {workbook.close();}catch(Exception e) {}
}
return sar2d;
}
public static Arj excelSheet0ToArj(InputStream is) {
String ar2d[][] = excelSheet0ToSar2d(is);
return a2ToAm(ar2d);
}
@PostMapping("testExcelToArj")
public Object testExcelToArj(HttpServletRequest req) throws Exception{
String r="";
Arj arj = excelSheet0ToArj(req.getInputStream());
System.out.println(arj);
r = om.writeValueAsString(arj);
System.out.println(r);
return r;
}
public static void main(String[] arguments) throws Exception{
InputStream is = null;
is = new FileInputStream("d:/temp/temp.xls");
printExcel(is);
}
public static void printExcel(InputStream is) {
String[][] sar2d = excelSheet0ToSar2d(is);
for(int r=0; r<sar2d.length; r++) {
for(int c=0; c<sar2d[r].length; c++) {
System.out.append(sar2d[r][c]).append(',');
}
System.out.println();
}
}
}
`;
}
</script>
</div><footer id="BodyF"></footer><style id="LastStyleTag"></style><script>const BodyH=dgebi("BodyH") , BodyB=dgebi("BodyB") , BodyF=dgebi("BodyF") , LastStyleTag=dgebi("LastStyleTag") , LastStyleSheet=LastStyleTag.sheet;
{//复制text到剪切板 要用textarea, input没有换行
const t=dcept(document.body,"textarea"); t.style.cssText="position:fixed; top:-100px; width:0; height:0;";
function copyTextContentToClipboardBy(e){e=dgebi(e); let h=t; h.value=e.textContent; h.select(); document.execCommand('copy');}
}
</script>
<script id="script168">
将内容上传然后转换为Excel后下载=(url,content,fileName="excel.xls")=>{
let xhr = new XMLHttpRequest();
xhr.open("post",url);
xhr.responseType="blob"; // 要设置responseType为blob
xhr.onload=(ev)=>{
let blob = xhr.response; //这步只是为了说明,设置responseType为blob后,response就是blob
console.log(xhr);
let a=document.createElement("a");
a.href = window.URL.createObjectURL(blob);
a.download=fileName;
a.click();
window.URL.revokeObjectURL(a.href); //释放内存
};
xhr.send(content);
};
function a2aj(ax){let rlt=[], x=ax[0];
if(x.constructor===Array){
for(let r=1;r<ax.length;r++){let ar=ax[r],rj={};rlt.push(rj);
for(let c=0;c<x.length;c++){rj[x[c]]=ar[c];}
}
}
else if(x.constructor===Object){let hs=[]; for(let h of Object.keys(x)){hs.push(h);}rlt.push(hs);
for(let r=0;r<ax.length;r++){let ar=[],rj=ax[r];rlt.push(ar);
for(let v of Object.values(rj))ar.push(v);
}
}
return rlt;}
let a2=
[
["第一列","第二列","第三列"]
,
[1,2,3]
,
[4,5,6]
,
[7,8,9]
];
let aj=a2aj(a2);
console.log(a2aj(aj));
let 上传a2内容=JSON.stringify(a2);
let 上传aj内容=JSON.stringify(aj);
function 将a2内容上传然后转换为xls后下载方法演示(){ 将内容上传然后转换为Excel后下载("http://localhost:8080/excel/a2ToExcel", 上传a2内容); }
function 将a2内容上传然后转换为xlsx后下载方法演示(){ 将内容上传然后转换为Excel后下载("http://localhost:8080/excel/a2ToExcel?扩展名=xlsx", 上传a2内容); }
function 将aj内容上传然后转换为xls后下载方法演示(){ 将内容上传然后转换为Excel后下载("http://localhost:8080/excel/ajToExcel", 上传aj内容); }
function 将aj内容上传然后转换为xlsx后下载方法演示(){ 将内容上传然后转换为Excel后下载("http://localhost:8080/excel/ajToExcel?扩展名=xlsx", 上传aj内容); }
</script>
<script>
{
let code = dgebi("前端js代码");
code.textContent=dgebi("script168").innerHTML;
}
</script>
</body></html>