java web 在线编辑Excel -- x-spreadsheet
--- x-spreadsheet
--- 文档 https://hondrytravis.com/x-spreadsheet-doc/
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@page import="com.base.util.WebUtil"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; String id=WebUtil.getParam("id"); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <meta http-equiv="content-type" content="text/html; charset=UTF-8" /> <meta name="viewport" content="width=device-width"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> <link href="<%=basePath%>x-spreadsheet/xspreadsheet.css" rel="stylesheet"></head> <link rel="stylesheet" href="<%=basePath %>base/layuiadmin/layui/css/layui.css" media="all"> <link rel="stylesheet" href="<%=basePath %>base/layuiadmin/style/admin.css" media="all"> </head> <body onload="load()"> <div style="position: fixed; right: 0; top: .3em;"> <input type="text" id="id" name="id" value="<%=id %>" lay-verify="required" autocomplete="off" class="layui-input" style="display: none;" > <button id="save" name="save" class="layui-btn site-demo-active" data-type="sjglTabChange" lay-filter="demo" onclick="save()">保存</button> </div> <div id="x-spreadsheet-demo"></div> </body> <script src="<%=basePath %>base/layuiadmin/layui/layui.js"></script> <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script> <script type="text/javascript" > function save(){ // 保存 const d = xs.getData(); //console.log(d[0]); var merges = JSON.stringify(d[0].merges); var rows = JSON.stringify(d[0].rows); var styles = JSON.stringify(d[0].styles); //console.log( merges); //console.log( rows); //console.log( styles); var id = "<%=id%>"; $.ajax({ url:' ', data:{merges:merges,rows:rows,styles:styles,id:id}, type:'post', dataType: "json", success:function(obj){ console.log("---"); //console.log(obj); } }); } var htmlsourse = ""; var arr = ""; var excel_styles = ""; function getExcel(){ $.ajax({ url:' ', type:'post', dataType: "json", async:false, success:function(obj){ //console.log("---"); //console.log(obj); if(obj.length>0){ //rows={obj[0].htmlsourse}; htmlsourse = obj[0].excel_rows; htmlsourse = $.parseJSON( htmlsourse ); //jQuery.parseJSON(jsonstr),可以将json字符串转换成json对象 //JSON.parse(jsonstr); //可以将json字符串转换成json对象 //console.log( htmlsourse); arr = obj[0].excel_merges arr = arr.split(','); //console.log( arr); excel_styles = obj[0].excel_styles; excel_styles = excel_styles.replace(/\s+/g,""); // 去空格 excel_styles = $.parseJSON( excel_styles ); //console.log( excel_styles); } } }); } var xs = ""; function load(){ console.log('---'); getExcel(); const rows = htmlsourse; console.log('****'); /*const rows10 = { len: 1000 }; for (let i = 0; i < 1000; i += 1) { rows10[i] = { cells: { 0: { text: 'A-' + i }, 1: { text: 'B-' + i }, 2: { text: 'C-' + i }, 3: { text: 'D-' + i }, 4: { text: 'E-' + i }, 5: { text: 'F-' + i }, } }; }*/ //x_spreadsheet.locale('zh-cn'); var saveIcon = 'data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBzdGFuZGFsb25lPSJubyI/PjwhRE9DVFlQRSBzdmcgUFVCTElDICItLy9XM0MvL0RURCBTVkcgMS4xLy9FTiIgImh0dHA6Ly93d3cudzMub3JnL0dyYXBoaWNzL1NWRy8xLjEvRFREL3N2ZzExLmR0ZCI+PHN2ZyB0PSIxNTc3MTc3MDkyOTg4IiBjbGFzcz0iaWNvbiIgdmlld0JveD0iMCAwIDEwMjQgMTAyNCIgdmVyc2lvbj0iMS4xIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHAtaWQ9IjI2NzgiIHdpZHRoPSIxOCIgaGVpZ2h0PSIxOCIgeG1sbnM6eGxpbms9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkveGxpbmsiPjxkZWZzPjxzdHlsZSB0eXBlPSJ0ZXh0L2NzcyI+PC9zdHlsZT48L2RlZnM+PHBhdGggZD0iTTIxMy4zMzMzMzMgMTI4aDU5Ny4zMzMzMzRhODUuMzMzMzMzIDg1LjMzMzMzMyAwIDAgMSA4NS4zMzMzMzMgODUuMzMzMzMzdjU5Ny4zMzMzMzRhODUuMzMzMzMzIDg1LjMzMzMzMyAwIDAgMS04NS4zMzMzMzMgODUuMzMzMzMzSDIxMy4zMzMzMzNhODUuMzMzMzMzIDg1LjMzMzMzMyAwIDAgMS04NS4zMzMzMzMtODUuMzMzMzMzVjIxMy4zMzMzMzNhODUuMzMzMzMzIDg1LjMzMzMzMyAwIDAgMSA4NS4zMzMzMzMtODUuMzMzMzMzeiBtMzY2LjkzMzMzNCAxMjhoMzQuMTMzMzMzYTI1LjYgMjUuNiAwIDAgMSAyNS42IDI1LjZ2MTE5LjQ2NjY2N2EyNS42IDI1LjYgMCAwIDEtMjUuNiAyNS42aC0zNC4xMzMzMzNhMjUuNiAyNS42IDAgMCAxLTI1LjYtMjUuNlYyODEuNmEyNS42IDI1LjYgMCAwIDEgMjUuNi0yNS42ek0yMTMuMzMzMzMzIDIxMy4zMzMzMzN2NTk3LjMzMzMzNGg1OTcuMzMzMzM0VjIxMy4zMzMzMzNIMjEzLjMzMzMzM3ogbTEyOCAwdjI1NmgzNDEuMzMzMzM0VjIxMy4zMzMzMzNoODUuMzMzMzMzdjI5OC42NjY2NjdhNDIuNjY2NjY3IDQyLjY2NjY2NyAwIDAgMS00Mi42NjY2NjcgNDIuNjY2NjY3SDI5OC42NjY2NjdhNDIuNjY2NjY3IDQyLjY2NjY2NyAwIDAgMS00Mi42NjY2NjctNDIuNjY2NjY3VjIxMy4zMzMzMzNoODUuMzMzMzMzek0yNTYgMjEzLjMzMzMzM2g4NS4zMzMzMzMtODUuMzMzMzMzeiBtNDI2LjY2NjY2NyAwaDg1LjMzMzMzMy04NS4zMzMzMzN6IG0wIDU5Ny4zMzMzMzR2LTEyOEgzNDEuMzMzMzMzdjEyOEgyNTZ2LTE3MC42NjY2NjdhNDIuNjY2NjY3IDQyLjY2NjY2NyAwIDAgMSA0Mi42NjY2NjctNDIuNjY2NjY3aDQyNi42NjY2NjZhNDIuNjY2NjY3IDQyLjY2NjY2NyAwIDAgMSA0Mi42NjY2NjcgNDIuNjY2NjY3djE3MC42NjY2NjdoLTg1LjMzMzMzM3ogbTg1LjMzMzMzMyAwaC04NS4zMzMzMzMgODUuMzMzMzMzek0zNDEuMzMzMzMzIDgxMC42NjY2NjdIMjU2aDg1LjMzMzMzM3oiIHAtaWQ9IjI2NzkiIGZpbGw9IiMyYzJjMmMiPjwvcGF0aD48L3N2Zz4=' var previewEl = document.createElement('img') previewEl.src = 'data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBzdGFuZGFsb25lPSJubyI/PjwhRE9DVFlQRSBzdmcgUFVCTElDICItLy9XM0MvL0RURCBTVkcgMS4xLy9FTiIgImh0dHA6Ly93d3cudzMub3JnL0dyYXBoaWNzL1NWRy8xLjEvRFREL3N2ZzExLmR0ZCI+PHN2ZyB0PSIxNjIxMzI4NTkxMjQzIiBjbGFzcz0iaWNvbiIgdmlld0JveD0iMCAwIDEwMjQgMTAyNCIgdmVyc2lvbj0iMS4xIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHAtaWQ9IjU2NjMiIHhtbG5zOnhsaW5rPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5L3hsaW5rIiB3aWR0aD0iMjAwIiBoZWlnaHQ9IjIwMCI+PGRlZnM+PHN0eWxlIHR5cGU9InRleHQvY3NzIj48L3N0eWxlPjwvZGVmcz48cGF0aCBkPSJNNTEyIDE4Ny45MDRhNDM1LjM5MiA0MzUuMzkyIDAgMCAwLTQxOC41NiAzMTUuNjQ4IDQzNS4zMjggNDM1LjMyOCAwIDAgMCA4MzcuMTIgMEE0MzUuNDU2IDQzNS40NTYgMCAwIDAgNTEyIDE4Ny45MDR6TTUxMiAzMjBhMTkyIDE5MiAwIDEgMSAwIDM4NCAxOTIgMTkyIDAgMCAxIDAtMzg0eiBtMCA3Ni44YTExNS4yIDExNS4yIDAgMSAwIDAgMjMwLjQgMTE1LjIgMTE1LjIgMCAwIDAgMC0yMzAuNHpNMTQuMDggNTAzLjQ4OEwxOC41NiA0ODUuNzZsNC44NjQtMTYuMzg0IDQuOTI4LTE0Ljg0OCA4LjA2NC0yMS41NjggNC4wMzItOS43OTIgNC43MzYtMTAuODggOS4zNDQtMTkuNDU2IDEwLjc1Mi0yMC4wOTYgMTIuNjA4LTIxLjMxMkE1MTEuNjE2IDUxMS42MTYgMCAwIDEgNTEyIDExMS4xMDRhNTExLjQ4OCA1MTEuNDg4IDAgMCAxIDQyNC41MTIgMjI1LjY2NGwxMC4yNCAxNS42OGMxMS45MDQgMTkuMiAyMi41OTIgMzkuMTA0IDMyIDU5Ljc3NmwxMC40OTYgMjQuOTYgNC44NjQgMTMuMTg0IDYuNCAxOC45NDQgNC40MTYgMTQuODQ4IDQuOTkyIDE5LjM5Mi0zLjIgMTIuODY0LTMuNTg0IDEyLjgtNi40IDIwLjA5Ni00LjQ4IDEyLjYwOC00Ljk5MiAxMi45MjhhNTExLjM2IDUxMS4zNiAwIDAgMS0xNy4yOCAzOC40bC0xMi4wMzIgMjIuNC0xMS45NjggMjAuMDk2QTUxMS41NTIgNTExLjU1MiAwIDAgMSA1MTIgODk2YTUxMS40ODggNTExLjQ4OCAwIDAgMS00MjQuNDQ4LTIyNS42bC0xMS4zMjgtMTcuNTM2YTUxMS4yMzIgNTExLjIzMiAwIDAgMS0xOS44NC0zNS4wMDhMNTMuMzc2IDYxMS44NGwtOC42NC0xOC4yNC0xMC4xMTItMjQuMTI4LTcuMTY4LTE5LjY0OC04LjMyLTI2LjYyNC0yLjYyNC05Ljc5Mi0yLjQ5Ni05LjkyeiIgcC1pZD0iNTY2NCI+PC9wYXRoPjwvc3ZnPg==' previewEl.width = 16 previewEl.height = 16 xs = x_spreadsheet('#x-spreadsheet-demo', { showToolbar: true, showGrid: true, showBottomBar: true, extendToolbar: { left: [ { tip: 'Save', icon: saveIcon, onClick: (data, sheet) => { console.log('click save button：', data, sheet) } } ], right: [ { tip: 'Preview', el: previewEl, onClick: (data, sheet) => { console.log('click preview button：', data) } } ], } }) .loadData([{ // 加载数据 freeze: 'A1', styles: excel_styles // 样式 /*{ bgcolor: '#f4f5f8', textwrap: true, color: '#900b09', border: { top: ['thin', '#0366d6'], bottom: ['thin', '#0366d6'], right: ['thin', '#0366d6'], left: ['thin', '#0366d6'], }, },{align: "center"}*/ , merges: arr, // 合并单元格 位置 //["C3:D4", "G3:H4", "E4:F5", "B7:E9", "F7:H9", "B10:C11", "D10:E11"], cols: { // 显示的个数 len: 50//, 2: { width: 200 }, }, rows, // 数据 }, // 多个 sheet /*{ name: 'sheet-test', rows: rows10 }*/] ).change((cdata) => { //console.log(cdata); console.log('>>>', xs.getData()); }); xs.on('cell-selected', (cell, ri, ci) => { console.log('selected>cell:', cell, ', ri:', ri, ', ci:', ci); }).on('cell-edited', (text, ri, ci) => { console.log('edited>text:', text, ', ri: ', ri, ', ci:', ci); }); /*setTimeout(() => { // 隐藏 单元格 // xs.loadData([{ rows }]); xs.cellText(14, 3, 'cell-text').reRender(); console.log('cell(8, 8):', xs.cell(8, 8)); console.log('cellStyle(8, 8):', xs.cellStyle(8, 8)); }, 5000);*/ } </script> <script type="text/javascript" src="<%=basePath%>x-spreadsheet/xspreadsheet.js"></script> </html>
---
import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import net.sf.json.JSONObject; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; /** * * @author mysterious * */ public class ExcelFinal { // 最终 // https://blog.csdn.net/lianzhang861/article/details/86234515 /** * 获取单元格的值 * @param cell * @return */ public static String getCellValue(Cell cell){ if(cell == null) { cell.setCellType(Cell.CELL_TYPE_STRING); return cell.getStringCellValue(); } if(cell.getCellType() == Cell.CELL_TYPE_STRING){ return cell.getStringCellValue(); }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){ return String.valueOf(cell.getBooleanCellValue()); }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){ return cell.getCellFormula() ; }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ return String.valueOf(cell.getNumericCellValue()); } return ""; } private static boolean is(HSSFSheet sheet,int i,int j) { int MergeCount = sheet.getNumMergedRegions(); for(int l = 0 ; l < MergeCount ; l++){ CellRangeAddress ca = sheet.getMergedRegion(l); String fa = ca.formatAsString() ; //System.out.println(">>>"+fa); int firstColumn = ca.getFirstColumn(); // 左上角的列号 int lastColumn = ca.getLastColumn(); // 右下角的列号 int firstRow = ca.getFirstRow(); // 左上角的行号 int lastRow = ca.getLastRow(); // 右下角的行号 //System.out.println("左上角的列号:"+firstColumn+",右下角的列号:"+lastColumn+",左上角的行号:"+firstRow+",右下角的行号:"+lastRow); if(i >= firstRow && i <= lastRow){ if(j >= firstColumn && j <= lastColumn){ //Row fRow = sheet.getRow(firstRow); //Cell fCell = fRow.getCell(firstColumn); //System.out.println("此单元格在合并单元格内..."+getCellValue(fCell)); return true; } } } return false; } private static String excel_styles = "[{\"border\":{\"bottom\":[\"thin\",\"#000\"],\"left\":[\"thin\",\"#000\"],\"right\":[\"thin\",\"#000\"],\"top\":[\"thin\",\"#000\"]}},{\"align\":\"center\"},{\"align\":\"center\",\"border\":{\"bottom\":[\"thin\",\"#000\"],\"left\":[\"thin\",\"#000\"],\"right\":[\"thin\",\"#000\"],\"top\":[\"thin\",\"#000\"]}}]"; public static Map<String, Object> readExcelToObj(String path) throws Exception { File file = new File(path); InputStream is = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(is); Map<String, Object>map = new HashMap<String, Object>(); map = readExcel(wb); map.put("excel_styles",excel_styles); return map; } private static Map<String, Object> readExcel(HSSFWorkbook wb) { Map<String, Object>map = new HashMap<String, Object>(); JSONObject rowJosn = new JSONObject(); // 行 //JSONObject cellsJosn = new JSONObject(); // cells //JSONObject gridJson = new JSONObject(); // 格 //JSONObject gridValue = new JSONObject(); // 格数据 //int [] mergesArray = new int[2]; // 合并单元格 List<String> merges = new ArrayList<String>();// 合并单元格 String Emerges = ""; HSSFSheet sheet = wb.getSheetAt(0); int trLength = sheet.getLastRowNum(); // 获取工作表上的最后一行 for(int i=0; i<trLength; i++) { HSSFRow row = sheet.getRow(i); int tdLength = row.getLastCellNum(); // 获取此行中最后一个单元格的索引加1 JSONObject cellsJosn = new JSONObject(); // cells JSONObject gridJson = new JSONObject(); // 格 for(int j = 0;j<tdLength;j++) { //得到Excel工作表指定行的单元格 HSSFCell c = row.getCell(j,row.CREATE_NULL_AS_BLANK); //int cc = c.getColumnIndex(); //System.out.println("返回此单元格的列索引:"+cc); System.out.println("行："+i+",列"+j); //---------------------------------------------boolean isMerge = is(sheet, i, j); JSONObject gridValue = new JSONObject(); // 格数据 int [] mergesArray = new int[2]; // 合并单元格 if(isMerge) { // "4":{ "cells":{ "1":{"text":"","merge":[0,21]} } int MergeCount = sheet.getNumMergedRegions(); int lastColumn = 0; for(int l = 0 ; l < MergeCount ; l++){ CellRangeAddress ca = sheet.getMergedRegion(l); String fa = ca.formatAsString() ; System.out.println(">>>"+fa); int firstColumn = ca.getFirstColumn(); // 左上角的列号 lastColumn = ca.getLastColumn(); // 右下角的列号 int firstRow = ca.getFirstRow(); // 左上角的行号 int lastRow = ca.getLastRow(); // 右下角的行号 //System.out.println("左上角的列号:"+firstColumn+",右下角的列号:"+lastColumn+",左上角的行号:"+firstRow+",右下角的行号:"+lastRow); if(i == firstRow) { // 表示新 if(j == firstColumn ){ // 表示新 // 表示 传入的 单元格 是符合的单元格 // 左上角的列号:1,右下角的列号:2,左上角的行号:4,右下角的行号:7 //添加列数据 ( 列：｛text：‘数据’，merge[( 左下角行号 - h ),( -l)]｝ ) gridValue.put("text", c.getStringCellValue()); gridValue.put("style", 1); mergesArray[0]=lastRow-firstRow; // 右上角列号 - 左上角列号 mergesArray[1]=lastColumn-firstColumn; // 右上角列号 - 左上角列号 gridValue.put("merge", mergesArray); merges.add(fa); Emerges+=fa+","; break; }else{ if(j > firstColumn && j<= lastColumn){ //j = lastColumn; // 表示跳出 这一行 内符合 行号的 合并单元格 ， break; }else{// 一定是一个单独的 单元格 因为合并单元格也是 按着循序循环的 // 添加数据 } } }else{ // 旧 // 表示 这次的 合并单元格 于此次传入的 单元格不在一个位置 // 判断是否是一列的 合并单元格 if(i > firstRow && i<= lastRow){ // 表示 该行 存在合并单元格 if(j >= firstColumn && j<= lastColumn){ // 是否 为当前遍历出来的 单元格内 //j = lastColumn; // 表示跳出 这一行 内符合 行号的 合并单元格 ， break; }else{// 一定是一个单独的 单元格 因为合并单元格也是 按着循序循环的 // 添加数据 } } } } gridJson.put(j, gridValue); // 放入格内 j = lastColumn; // 表示跳出 这一行 内符合 行号的 合并单元格 ， }else{ c.setCellType(Cell.CELL_TYPE_STRING); gridValue.put("text", c.getStringCellValue()); gridValue.put("style", 1); gridJson.put(j, gridValue); // 放入格内 } } cellsJosn.put("cells", gridJson); rowJosn.put(i, cellsJosn); } System.out.println(">>>"+rowJosn); System.out.println(">>>"+merges.toString()); map.put("excel_rows", rowJosn); //map.put("excel_merges", merges); Emerges = Emerges.substring(0, Emerges.length()-1); map.put("excel_merges", Emerges); return map; } public static void main(String[] args) { // TODO Auto-generated method stub // mysterious try { String widz = "D:\\Book1.xls"; readExcelToObj(widz); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
---
/*左下角的行号 = 右下角的行号 左下角的列号 = 左上角的列号 右上角的行号 = 左上角的行号 右上角的列号 = 右下角的列号*/ int firstColumn = ca.getFirstColumn(); // 左上角的列号 int lastColumn = ca.getLastColumn(); // 右下角的列号 int firstRow = ca.getFirstRow(); // 左上角的行号 int lastRow = ca.getLastRow(); // 右下角的行号 a(s,h,l){ // sheet，行，列 if(h = 左上角的行号) { // 表示新 //if(){// 当前行是否已经有数据存在 一格一格遍历无需判断是否存在 //--- 添加行数据 ( 行:{cells: { ) } if(l = 左上角的列号 ){ // 表示新 // 表示 传入的 单元格 是符合的单元格 // 左上角的列号:1,右下角的列号:2,左上角的行号:4,右下角的行号:7 添加列数据 ( 列：｛text：‘数据’，merge[( 左下角行号 - h ),( -l)]｝ ) }else{ if(l > 左上角的列号 && l<= 右上角的列号){ l = 右上角的列号+1; // 表示跳出 这一行 内符合 行号的 合并单元格 ， return l; }else{// 一定是一个单独的 单元格 因为合并单元格也是 按着循序循环的 // 添加数据 } } }esle{ // 旧 // 表示 这次的 合并单元格 于此次传入的 单元格不在一个位置 // 判断是否是一列的 合并单元格 if(h > 左上角的行号 && h<= 左下角的行号){ // 表示 该行 存在合并单元格 if(l ){ // 是否 为当前遍历出来的 单元格内 l = 右上角的列号+1; // 表示跳出 这一行 内符合 行号的 合并单元格 ， }else{// 一定是一个单独的 单元格 因为合并单元格也是 按着循序循环的 // 添加数据 } } } }
--- 多页---------------------------------------------------------------------------------------------------------------------------
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@page import="com.base.util.WebUtil"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; String id=WebUtil.getParam("id"); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <meta http-equiv="content-type" content="text/html; charset=UTF-8" /> <meta name="viewport" content="width=device-width"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> <link href="<%=basePath%>x-spreadsheet/xspreadsheet.css" rel="stylesheet"></head> <link rel="stylesheet" href="<%=basePath %>base/layuiadmin/layui/css/layui.css" media="all"> <link rel="stylesheet" href="<%=basePath %>base/layuiadmin/style/admin.css" media="all"> </head> <body onload="load()"> <div style="position: fixed; right: 0; top: .3em;"> <input type="text" id="id" name="id" value="<%=id %>" lay-verify="required" autocomplete="off" class="layui-input" style="display: none;" > <button id="save" name="save" class="layui-btn site-demo-active" data-type="sjglTabChange" lay-filter="demo" onclick="save()">保存</button> </div> <div id="x-spreadsheet-demo"></div> </body> <script src="<%=basePath %>base/layuiadmin/layui/layui.js"></script> <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script> <script type="text/javascript" > function save(){ const d = xs.getData(); console.log(d[0]); var merges = JSON.stringify(d[0].merges); var rows = JSON.stringify(d[0].rows); var styles = JSON.stringify(d[0].styles); var id = "<%=id%>"; $.ajax({ url:'<%=basePath%>gzlc/saveExcel.do', data:{merges:merges,rows:rows,styles:styles,id:id}, type:'post', dataType: "json", success:function(obj){ console.log("---"); console.log(obj); } }); } var excel_styles = ""; var mycars=new Array(); function getExcel(){ $.ajax({ url:'<%=basePath%>gzlc/getExcel.do?id=<%=id%>', type:'post', dataType: "json", async:false, success:function(obj){ console.log("---"); //console.log(obj); if(obj.length>0){ excel_styles = obj[0].excel_styles; excel_styles = $.parseJSON( excel_styles ); //console.log( excel_styles); var htmlsourse = obj[0].excel_rows; console.log("-*-------------------------------------------------------"); htmlsourse = $.parseJSON( htmlsourse ); console.log( htmlsourse); var i = 0; $.each(htmlsourse, function(j) { var xx = htmlsourse[j]; console.log(htmlsourse[j]); //console.log(j); var person = { freeze : ""+j, styles : excel_styles, merges:j, cols: {len: 50}, // 显示的个数 rows:j }; var excel_rows = xx.excel_rows; var excel_merges = xx.excel_merges; excel_merges = excel_merges.replace(/\s+/g,""); // 去空格 excel_merges = excel_merges.split(','); //console.log(excel_rows); //console.log(excel_merges); //console.log("////////////////////"); //console.log(i); person.merges = excel_merges; person.rows = excel_rows; mycars[i]=person; i++; }); console.log(mycars); } } }); } var xs = ""; function load(){ console.log('---'); getExcel(); console.log('****'); //x_spreadsheet.locale('zh-cn'); var saveIcon = 'data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBzdGFuZGFsb25lPSJubyI/PjwhRE9DVFlQRSBzdmcgUFVCTElDICItLy9XM0MvL0RURCBTVkcgMS4xLy9FTiIgImh0dHA6Ly93d3cudzMub3JnL0dyYXBoaWNzL1NWRy8xLjEvRFREL3N2ZzExLmR0ZCI+PHN2ZyB0PSIxNTc3MTc3MDkyOTg4IiBjbGFzcz0iaWNvbiIgdmlld0JveD0iMCAwIDEwMjQgMTAyNCIgdmVyc2lvbj0iMS4xIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHAtaWQ9IjI2NzgiIHdpZHRoPSIxOCIgaGVpZ2h0PSIxOCIgeG1sbnM6eGxpbms9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkveGxpbmsiPjxkZWZzPjxzdHlsZSB0eXBlPSJ0ZXh0L2NzcyI+PC9zdHlsZT48L2RlZnM+PHBhdGggZD0iTTIxMy4zMzMzMzMgMTI4aDU5Ny4zMzMzMzRhODUuMzMzMzMzIDg1LjMzMzMzMyAwIDAgMSA4NS4zMzMzMzMgODUuMzMzMzMzdjU5Ny4zMzMzMzRhODUuMzMzMzMzIDg1LjMzMzMzMyAwIDAgMS04NS4zMzMzMzMgODUuMzMzMzMzSDIxMy4zMzMzMzNhODUuMzMzMzMzIDg1LjMzMzMzMyAwIDAgMS04NS4zMzMzMzMtODUuMzMzMzMzVjIxMy4zMzMzMzNhODUuMzMzMzMzIDg1LjMzMzMzMyAwIDAgMSA4NS4zMzMzMzMtODUuMzMzMzMzeiBtMzY2LjkzMzMzNCAxMjhoMzQuMTMzMzMzYTI1LjYgMjUuNiAwIDAgMSAyNS42IDI1LjZ2MTE5LjQ2NjY2N2EyNS42IDI1LjYgMCAwIDEtMjUuNiAyNS42aC0zNC4xMzMzMzNhMjUuNiAyNS42IDAgMCAxLTI1LjYtMjUuNlYyODEuNmEyNS42IDI1LjYgMCAwIDEgMjUuNi0yNS42ek0yMTMuMzMzMzMzIDIxMy4zMzMzMzN2NTk3LjMzMzMzNGg1OTcuMzMzMzM0VjIxMy4zMzMzMzNIMjEzLjMzMzMzM3ogbTEyOCAwdjI1NmgzNDEuMzMzMzM0VjIxMy4zMzMzMzNoODUuMzMzMzMzdjI5OC42NjY2NjdhNDIuNjY2NjY3IDQyLjY2NjY2NyAwIDAgMS00Mi42NjY2NjcgNDIuNjY2NjY3SDI5OC42NjY2NjdhNDIuNjY2NjY3IDQyLjY2NjY2NyAwIDAgMS00Mi42NjY2NjctNDIuNjY2NjY3VjIxMy4zMzMzMzNoODUuMzMzMzMzek0yNTYgMjEzLjMzMzMzM2g4NS4zMzMzMzMtODUuMzMzMzMzeiBtNDI2LjY2NjY2NyAwaDg1LjMzMzMzMy04NS4zMzMzMzN6IG0wIDU5Ny4zMzMzMzR2LTEyOEgzNDEuMzMzMzMzdjEyOEgyNTZ2LTE3MC42NjY2NjdhNDIuNjY2NjY3IDQyLjY2NjY2NyAwIDAgMSA0Mi42NjY2NjctNDIuNjY2NjY3aDQyNi42NjY2NjZhNDIuNjY2NjY3IDQyLjY2NjY2NyAwIDAgMSA0Mi42NjY2NjcgNDIuNjY2NjY3djE3MC42NjY2NjdoLTg1LjMzMzMzM3ogbTg1LjMzMzMzMyAwaC04NS4zMzMzMzMgODUuMzMzMzMzek0zNDEuMzMzMzMzIDgxMC42NjY2NjdIMjU2aDg1LjMzMzMzM3oiIHAtaWQ9IjI2NzkiIGZpbGw9IiMyYzJjMmMiPjwvcGF0aD48L3N2Zz4=' var previewEl = document.createElement('img') previewEl.src = 'data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBzdGFuZGFsb25lPSJubyI/PjwhRE9DVFlQRSBzdmcgUFVCTElDICItLy9XM0MvL0RURCBTVkcgMS4xLy9FTiIgImh0dHA6Ly93d3cudzMub3JnL0dyYXBoaWNzL1NWRy8xLjEvRFREL3N2ZzExLmR0ZCI+PHN2ZyB0PSIxNjIxMzI4NTkxMjQzIiBjbGFzcz0iaWNvbiIgdmlld0JveD0iMCAwIDEwMjQgMTAyNCIgdmVyc2lvbj0iMS4xIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHAtaWQ9IjU2NjMiIHhtbG5zOnhsaW5rPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5L3hsaW5rIiB3aWR0aD0iMjAwIiBoZWlnaHQ9IjIwMCI+PGRlZnM+PHN0eWxlIHR5cGU9InRleHQvY3NzIj48L3N0eWxlPjwvZGVmcz48cGF0aCBkPSJNNTEyIDE4Ny45MDRhNDM1LjM5MiA0MzUuMzkyIDAgMCAwLTQxOC41NiAzMTUuNjQ4IDQzNS4zMjggNDM1LjMyOCAwIDAgMCA4MzcuMTIgMEE0MzUuNDU2IDQzNS40NTYgMCAwIDAgNTEyIDE4Ny45MDR6TTUxMiAzMjBhMTkyIDE5MiAwIDEgMSAwIDM4NCAxOTIgMTkyIDAgMCAxIDAtMzg0eiBtMCA3Ni44YTExNS4yIDExNS4yIDAgMSAwIDAgMjMwLjQgMTE1LjIgMTE1LjIgMCAwIDAgMC0yMzAuNHpNMTQuMDggNTAzLjQ4OEwxOC41NiA0ODUuNzZsNC44NjQtMTYuMzg0IDQuOTI4LTE0Ljg0OCA4LjA2NC0yMS41NjggNC4wMzItOS43OTIgNC43MzYtMTAuODggOS4zNDQtMTkuNDU2IDEwLjc1Mi0yMC4wOTYgMTIuNjA4LTIxLjMxMkE1MTEuNjE2IDUxMS42MTYgMCAwIDEgNTEyIDExMS4xMDRhNTExLjQ4OCA1MTEuNDg4IDAgMCAxIDQyNC41MTIgMjI1LjY2NGwxMC4yNCAxNS42OGMxMS45MDQgMTkuMiAyMi41OTIgMzkuMTA0IDMyIDU5Ljc3NmwxMC40OTYgMjQuOTYgNC44NjQgMTMuMTg0IDYuNCAxOC45NDQgNC40MTYgMTQuODQ4IDQuOTkyIDE5LjM5Mi0zLjIgMTIuODY0LTMuNTg0IDEyLjgtNi40IDIwLjA5Ni00LjQ4IDEyLjYwOC00Ljk5MiAxMi45MjhhNTExLjM2IDUxMS4zNiAwIDAgMS0xNy4yOCAzOC40bC0xMi4wMzIgMjIuNC0xMS45NjggMjAuMDk2QTUxMS41NTIgNTExLjU1MiAwIDAgMSA1MTIgODk2YTUxMS40ODggNTExLjQ4OCAwIDAgMS00MjQuNDQ4LTIyNS42bC0xMS4zMjgtMTcuNTM2YTUxMS4yMzIgNTExLjIzMiAwIDAgMS0xOS44NC0zNS4wMDhMNTMuMzc2IDYxMS44NGwtOC42NC0xOC4yNC0xMC4xMTItMjQuMTI4LTcuMTY4LTE5LjY0OC04LjMyLTI2LjYyNC0yLjYyNC05Ljc5Mi0yLjQ5Ni05LjkyeiIgcC1pZD0iNTY2NCI+PC9wYXRoPjwvc3ZnPg==' previewEl.width = 16 previewEl.height = 16 xs = x_spreadsheet('#x-spreadsheet-demo', { showToolbar: true, showGrid: true, showBottomBar: true, extendToolbar: { left: [ { tip: 'Save', icon: saveIcon, onClick: (data, sheet) => { console.log('click save button：', data, sheet) } } ], right: [ { tip: 'Preview', el: previewEl, onClick: (data, sheet) => { console.log('click preview button：', data) } } ], } }); xs.loadData(mycars).change((cdata) => { console.log(cdata); console.log('>>>', xs.getData()); }); xs.on('cell-selected', (cell, ri, ci) => { console.log('selected>cell:', cell, ', ri:', ri, ', ci:', ci); }).on('cell-edited', (text, ri, ci) => { console.log('edited>text:', text, ', ri: ', ri, ', ci:', ci); }); } </script> <script type="text/javascript" src="<%=basePath%>x-spreadsheet/xspreadsheet.js"></script> </html>
---
import java.io.File; import java.io.FileInputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; import net.sf.json.JSONObject; /** * * @author mysterious * */ public class ExcelFinal { // 最终 // https://blog.csdn.net/lianzhang861/article/details/86234515 private static Workbook wb; private static String excel_styles = "[{\"border\":{\"bottom\":[\"thin\",\"#000\"],\"left\":[\"thin\",\"#000\"],\"right\":[\"thin\",\"#000\"],\"top\":[\"thin\",\"#000\"]}},{\"align\":\"center\"},{\"align\":\"center\",\"border\":{\"bottom\":[\"thin\",\"#000\"],\"left\":[\"thin\",\"#000\"],\"right\":[\"thin\",\"#000\"],\"top\":[\"thin\",\"#000\"]}}]"; /** * 获取单元格的值 * @param cell * @return */ public static String getCellValue(Cell cell){ if(cell == null) { cell.setCellType(Cell.CELL_TYPE_STRING); return cell.getStringCellValue(); } if(cell.getCellType() == Cell.CELL_TYPE_STRING){ return cell.getStringCellValue(); }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){ return String.valueOf(cell.getBooleanCellValue()); }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){ return cell.getCellFormula() ; }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ return String.valueOf(cell.getNumericCellValue()); } return ""; } private static boolean is(Sheet sheet,int i,int j) { int MergeCount = sheet.getNumMergedRegions(); for(int l = 0 ; l < MergeCount ; l++){ CellRangeAddress ca = sheet.getMergedRegion(l); String fa = ca.formatAsString() ; //System.out.println(">>>"+fa); int firstColumn = ca.getFirstColumn(); // 左上角的列号 int lastColumn = ca.getLastColumn(); // 右下角的列号 int firstRow = ca.getFirstRow(); // 左上角的行号 int lastRow = ca.getLastRow(); // 右下角的行号 //System.out.println("左上角的列号:"+firstColumn+",右下角的列号:"+lastColumn+",左上角的行号:"+firstRow+",右下角的行号:"+lastRow); if(i >= firstRow && i <= lastRow){ if(j >= firstColumn && j <= lastColumn){ //Row fRow = sheet.getRow(firstRow); //Cell fCell = fRow.getCell(firstColumn); System.out.println("此单元格在合并单元格内..."); return true; } } } return false; } private static Map<String, Object> readExcel(Workbook wb) { Map<String, Object>map = new HashMap<String, Object>(); JSONObject JO = new JSONObject(); int sheetCount = wb.getNumberOfSheets(); //Sheet的数量 for(int s = 0;s<sheetCount;s++) { JSONObject sheetMap = new JSONObject(); // 第二页时 Sheet sheet = wb.getSheetAt(s); String sheetName = sheet.getSheetName(); int trLength = sheet.getLastRowNum(); // 获取工作表上的最后一行 JSONObject rowJosn = new JSONObject(); // 行 String Emerges = "";// 合并单元格 for(int i=0; i<trLength; i++) { Row row = sheet.getRow(i); int tdLength = row.getLastCellNum(); // 获取此行中最后一个单元格的索引加1 JSONObject cellsJosn = new JSONObject(); // cells JSONObject gridJson = new JSONObject(); // 格 for(int j = 0;j<tdLength;j++) { Cell c = row.getCell(j,row.CREATE_NULL_AS_BLANK); System.out.println("sheet:"+s+",行："+i+",列"+j); boolean isMerge = is(sheet, i, j); JSONObject gridValue = new JSONObject(); // 格数据 int [] mergesArray = new int[2]; // 合并单元格 if(isMerge) { int MergeCount = sheet.getNumMergedRegions(); int lastColumn = 0; for(int l = 0 ; l < MergeCount ; l++){ CellRangeAddress ca = sheet.getMergedRegion(l); String fa = ca.formatAsString() ; System.out.println(">>>"+fa); int firstColumn = ca.getFirstColumn(); // 左上角的列号 lastColumn = ca.getLastColumn(); // 右下角的列号 int firstRow = ca.getFirstRow(); // 左上角的行号 int lastRow = ca.getLastRow(); // 右下角的行号 System.out.println("左上角的列号:"+firstColumn+",右下角的列号:"+lastColumn+",左上角的行号:"+firstRow+",右下角的行号:"+lastRow); if(i == firstRow) { // 表示新 if(j == firstColumn ){ // 表示新 // 表示 传入的 单元格 是符合的单元格 // 左上角的列号:1,右下角的列号:2,左上角的行号:4,右下角的行号:7 //添加列数据 ( 列：｛text：‘数据’，merge[( 左下角行号 - h ),( -l)]｝ ) gridValue.put("text", c.getStringCellValue()); gridValue.put("style", 1); mergesArray[0]=lastRow-firstRow; // 右上角列号 - 左上角列号 mergesArray[1]=lastColumn-firstColumn; // 右上角列号 - 左上角列号 gridValue.put("merge", mergesArray); Emerges+=fa+","; break; }else{ if(j > firstColumn && j<= lastColumn){ //j = lastColumn; // 表示跳出 这一行 内符合 行号的 合并单元格 ， break; }else{// 一定是一个单独的 单元格 因为合并单元格也是 按着循序循环的 // 添加数据 } } }else{ // 旧 // 表示 这次的 合并单元格 于此次传入的 单元格不在一个位置 // 判断是否是一列的 合并单元格 if(i > firstRow && i<= lastRow){ // 表示 该行 存在合并单元格 if(j >= firstColumn && j<= lastColumn){ // 是否 为当前遍历出来的 单元格内 //j = lastColumn; // 表示跳出 这一行 内符合 行号的 合并单元格 ， break; }else{// 一定是一个单独的 单元格 因为合并单元格也是 按着循序循环的 // 添加数据 } } } } gridJson.put(j, gridValue); // 放入格内 j = lastColumn; // 表示跳出 这一行 内符合 行号的 合并单元格 ， }else{ c.setCellType(Cell.CELL_TYPE_STRING); gridValue.put("text", c.getStringCellValue()); gridValue.put("style", 1); gridJson.put(j, gridValue); // 放入格内 } } cellsJosn.put("cells", gridJson); rowJosn.put(i, cellsJosn); } sheetMap.put("excel_rows", rowJosn); Emerges = Emerges.substring(0, Emerges.length()-1); sheetMap.put("excel_merges", Emerges); JO.put(sheetName, sheetMap); //map.put(sheetName, sheetMap); } System.out.println(">>>"+JO); map.put("excel_rows", JO); map.put("excel_styles", excel_styles); return map; } public static Map<String, Object> readExcelToObj(String path) throws Exception { // 获得文件所在地 File file = new File(path); List<Map<String, Object>> list = new ArrayList<Map<String,Object>>(); FileInputStream is = new FileInputStream(file); //文件流 wb = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的 return readExcel(wb); } public static void main(String[] args) { // TODO Auto-generated method stub String path = "设计交底记录.xls"; // 文件在服务器的地址 try { readExcelToObj(path); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
--- mysterious
时刻告诉自己，自己是个菜鸡......
