Excel的导入导出(大量数据)

编程入门行业动态更新时间:2024-10-27 16:31:05

Excel的导入导出(大量<a href=https://www.elefans.com/category/jswz/34/1771445.html style= 数据)"/>

Excel的导入导出(大量数据)

可以在评论区交流!!!

前端:

html代码:

<form enctype="multipart/form-data" id="batchUpload" class="form-horizontal"><button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;float: left;margin-left:30px;margin-top: 20px"  type="button" >选择文件</button><input type="file" name="file"  style="width:0px;height:0px;float:left;" id="uploadEventFile"><input id="uploadEventPath"  disabled="disabled"  type="text" placeholder="请选择excel表" style="border: 1px solid #e6e6e6; height: 26px;width: 200px;margin-top:20px;" /></form><button type="button" class="btn btn-success btn-sm"  id="uploadBtn" style="margin-left: 220px;margin-top: 10px;">上传</button></div><button type="button" class="layui-btn" id="excelImport"><i class="layui-icon">&#xe654;</i>导入</button><button type="button" class="layui-btn" id="excelExport"><i class="layui-icon">&#xe642;</i>导出</button>

JS代码:

/*** 导入*/var excelImport = document.getElementById("excelImport");excelImport.onclick = function() {layer.open({type: 1,content: $('#choose'), //这里content是一个DOM，注意：最好该元素要存放在body最外层，否则可能被其它的相对元素所影响});}var User = function() {this.init = function() {//模拟上传excel$("#uploadEventBtn").unbind("click").bind("click", function() {$("#uploadEventFile").click();});$("#uploadEventFile").bind("change", function() {$("#uploadEventPath").attr("value",    $("#uploadEventFile").val());});};//点击上传钮var uploadBtn = document.getElementById("uploadBtn");uploadBtn.onclick = function() {var uploadEventFile = $("#uploadEventFile").val();if (uploadEventFile == '') {alert("请择excel,再上传");} else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excelalert("只能上传Excel文件");} else {var url = "/phySsPersonInfo/import";var formData = new FormData($('form')[0]);user.sendAjaxRequest(url, "POST", formData);}};this.sendAjaxRequest = function(url, type, data) {$.ajax({url : url,type : type,data : data,dataType : "json",success : function(result) {if (result.count != null ) {alert("成功导入"+result.count+"条数据! \n共耗时"+result.time+"毫秒!");layer.closeAll();$('#SsPersonInfoTable').bootstrapTable('refresh');}else {alert("导入失败!");layer.closeAll();$('#SsPersonInfoTable').bootstrapTable('refresh');}},error : function() {alert("导入出错!!!");layer.closeAll();$('#SsPersonInfoTable').bootstrapTable('refresh');},cache : false,contentType : false,processData : false});};};var user;$(function() {user = new User();user.init();});/**
*导出
*/var exportBtn = document.getElementById("excelExport");exportBtn.onclick = function() {if (confirm("确定导出?")) {$.ajax({url : "/phySsPersonInfo/exportVillageFile",type : "post",data:{},dataType :"json",async: false,success : function(result) {alert(result.message);},error : function(result) {alert("导出成功,请稍等---");},cache : false,contentType : false,processData : false});}}

后端代码:

Controller层:

/*** 导入excl*///导入excel@RequestMapping(value = "/import", method=RequestMethod.POST)@ResponseBodypublic Map<String, Object> importExcel(@RequestParam("file") MultipartFile file){Map<String, Object> map = new HashMap<String, Object>();Integer[] resultMap = ssPersonInfoService.readExcelFile(file);map.put("count",resultMap[0]);  //导入数据条数map.put("time",resultMap[1]);	 //导入所耗时间return map;}/*** 按村为单位导出excel*/@RequestMapping("/exportVillageFile")@ResponseBodypublic Map<String, Object> exportVillageFile(HttpServletResponse response,HttpServletRequest request) {String result = "";try{result = ssPersonInfoService.VillageFile(response,request);}catch(Exception e){e.printStackTrace();}Map<String, Object> map = new HashMap<String, Object>();map.put("message", result);return map;}

Service接口:

/*** 插入数据* */Integer[] insertall(SsPersonInfo ssPersonInfo);/*** 获取所有的镇*/List<Map<String,Object>> getAllTown();/*** 根据镇获取所有的村*/List<Map<String,Object>> getAllVillage(Map<String,Object> map);/*** 以村为单位获取人员信息*/List<SsPersonInfo> getPayableInfoByTown(Map<String,Object> map);

Service实现类:

@Service
public class SsPersonInfoServiceImpl extends ServiceImpl<SsPersonInfoMapper, SsPersonInfo> implements ISsPersonInfoService {@AutowiredSsPersonInfoMapper ssPersonInfoMapper;@AutowiredExcelSaxReader excelSaxReader;@Overridepublic Integer[] readExcelFile(@RequestParam("file") MultipartFile file){//用于存放导入的结果信息Integer[] resultMap = {0,0};InputStream inputStream=null;try{//新增的数据条数int count = 0;//新增的多个mapList<SsPersonInfo> personInfos = new ArrayList<>();long startTime = System.currentTimeMillis();//转换为输入流inputStream = file.getInputStream();ExcelSaxReader reader = excelSaxReader.parse(inputStream);List<String[]> datas = reader.getDatas();for(String[] str : datas){SsPersonInfo personInfo = new SsPersonInfo();personInfo.setIdentityCard(str[1]);personInfo.setName(str[2]);personInfo.setNativePlace(str[17]);/*** 根据籍贯截取出镇*/int index = str[17].indexOf(" ");int lastIndexOf = str[17].lastIndexOf(" ");String town = str[17].substring(index, lastIndexOf);personInfo.setTown(town);/*** 根据籍贯截取出村*/String village = str[17].substring(lastIndexOf);personInfo.setVillage(village);personInfos.add(personInfo);}long endTime = System.currentTimeMillis();//读取Excel耗时            =============45s左右long time = endTime-startTime;long startTime2 = System.currentTimeMillis();resultMap[0] = ssPersonInfoMapper.batchInsertAll(personInfos);long endTime2 = System.currentTimeMillis();//执行插入耗时             =============57s左右long time2 = endTime2-startTime2;long allTime = time+time2;resultMap[1] = (int)allTime;}catch (Exception e){e.printStackTrace();}finally {if (inputStream!=null) {try {inputStream.close();} catch (IOException e) {e.printStackTrace();}}}return resultMap;}/*** 获取所有的乡镇/街道* @return 所有的乡镇/街道*/@Overridepublic List<Map<String, Object>> getAllTown() {return ssPersonInfoMapper.getAllTown();}/*** 根据乡镇获取所辖的村/社区* @param map 乡镇* @return 本镇所有的村*/@Overridepublic List<Map<String, Object>> getAllVillage(Map<String, Object> map) {return ssPersonInfoMapper.getAllVillage(map);}/*** 用于导出以村为单位的数据* @param response 用于输出文件*/@Overridepublic String VillageFile(HttpServletResponse response, HttpServletRequest request) {String result = "";List<Map<String, Object>> allTowns = ssPersonInfoMapper.getAllTown();if (allTowns.size()!=0){result = "导出成功";}
//        String dirPath = request.getSession().getServletContext().getRealPath("/static/download/");String dirPath = "D:\\study\\";long startTime = System.currentTimeMillis();for (Map<String, Object> town : allTowns){//获取乡镇名称String Town = (String) town.get("Town");//获取乡镇下辖的村List<Map<String, Object>> allVillages = ssPersonInfoMapper.getAllVillage(town);new Thread(){@Overridepublic void run() {for (Map<String, Object> village : allVillages){Map<String,Object> param = new HashMap<>(5);String Village = (String) village.get("Village");param.put("Town",Town);param.put("Village",Village);//填入Excel表格中的数据List<SsPersonInfo> ssPersonInfos = ssPersonInfoMapper.getPayableInfoByVillage(param);String fileName = Village+"村（社区）社保人员清单.xls";String path = dirPath+Town+"\\"+Village+"\\";File targetFile = new File(path);if(!targetFile.exists()){//如果文件夹不存在targetFile.mkdirs();}response.setContentType("octets/stream");try {FileOutputStream fos = new FileOutputStream(new File(path+fileName));ByteArrayOutputStream os = new ByteArrayOutputStream();response.addHeader("Content-Disposition","attachment;filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));HSSFWorkbook wb = new HSSFWorkbook();String sheetName=Village+"村（社区）社保人员清单";HSSFSheet sheet = wb.createSheet(sheetName);/* 设置打印格式 */HSSFPrintSetup hps = sheet.getPrintSetup();hps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);hps.setLandscape(true);hps.setFitHeight((short) 1);hps.setFitWidth((short) 1);hps.setScale((short) 65);hps.setFooterMargin(0);hps.setHeaderMargin(0);sheet.setMargin(HSSFSheet.TopMargin, 0.3);sheet.setMargin(HSSFSheet.BottomMargin, 0);sheet.setMargin(HSSFSheet.LeftMargin, 0.3);sheet.setMargin(HSSFSheet.RightMargin, 0);//创建第一行HSSFRow row = sheet.createRow((short) 0);HSSFCell cell ;row.setHeightInPoints(40);HSSFFont font = wb.createFont();font.setFontName("宋体");//粗体显示font.setBold(true);font.setFontHeightInPoints((short) 16);cell = row.createCell(0);cell.setCellValue("身份证");cell = row.createCell(1);cell.setCellValue("姓名");cell = row.createCell(2);cell.setCellValue("籍贯");cell = row.createCell(3);cell.setCellValue("镇");cell = row.createCell(4);cell.setCellValue("村");sheet.setColumnWidth(0, 4096);sheet.setColumnWidth(1, 4096);sheet.setColumnWidth(2, 4096);sheet.setColumnWidth(3, 4096);sheet.setColumnWidth(4, 4096);//设置列值-内容for (int i = 0; i < ssPersonInfos.size(); i++) {row = sheet.createRow(i + 1);row.setHeightInPoints(20);SsPersonInfo ssPersonInfo = ssPersonInfos.get(i);cell = row.createCell(0);cell.setCellValue(ssPersonInfo.getIdentityCard());cell = row.createCell(1);cell.setCellValue(ssPersonInfo.getName());cell = row.createCell(2);cell.setCellValue(ssPersonInfo.getNativePlace());cell = row.createCell(3);cell.setCellValue(ssPersonInfo.getTown());cell = row.createCell(4);cell.setCellValue(ssPersonInfo.getVillage());}wb.write(os);InputStream excelStream = new ByteArrayInputStream(os.toByteArray());//写入目标文件byte[] buffer = new byte[1024*1024];int byteRead = 0;while((byteRead= excelStream.read(buffer))!=-1){fos.write(buffer, 0, byteRead);fos.flush();}fos.close();excelStream.close();} catch (Exception e) {e.printStackTrace();}}}}.start();}long endTime = System.currentTimeMillis();//导出总耗时long time = endTime-startTime;return result;}}

Mapper接口:

@Mapper
public interface SsPersonInfoMapper extends BaseMapper<SsPersonInfo> {/*** 插入数据* */Integer insertAll(SsPersonInfo ssPersonInfo);/*** 批量插入*/Integer batchInsertAll(List<SsPersonInfo> list);/*** 获取所有的镇*/List<Map<String,Object>> getAllTown();/*** 根据镇获取所有的村*/List<Map<String,Object>> getAllVillage(Map<String,Object> map);/*** 以村为单位获取人员信息*/List<SsPersonInfo> getPayableInfoByVillage(Map<String,Object> map);}

Mapper.xml:

<insert id="insertAll">insert into ss_person_info_phy(identity_card,name,native_place,town,village) values (#{identityCard},#{name},#{nativePlace},#{town},#{village})</insert><!--批量插入--><insert id="batchInsertAll" parameterType="java.util.List">INSERT INTO ss_person_info_phy(identity_card,name,native_place,town,village)VALUES<foreach collection="list" item="item" index="index" separator=",">(#{item.identityCard,jdbcType=VARCHAR},#{item.name,jdbcType=VARCHAR},#{item.nativePlace,jdbcType=VARCHAR},#{item.town,jdbcType=VARCHAR},#{item.village,jdbcType=VARCHAR})</foreach></insert><!--获取所有镇--><select id="getAllTown" resultType="map">SELECT DISTINCT town Town FROM ss_person_info</select><!--根据镇获取所有的村--><select id="getAllVillage" resultType="map" parameterType="map">SELECT DISTINCT village Village FROM ss_person_info WHERE town=#{Town}</select><!--根据镇和村获取一个村的人员信息--><select id="getPayableInfoByVillage" parameterType="map" resultMap="BaseResultMap">selectidentity_card,name,native_place,town,villagefrom ss_person_infoWHEREtown=#{Town}and village=#{Village}</select>

实体类:

@TableName("ss_person_info")
public class SsPersonInfo extends Model<SsPersonInfo> {private static final long serialVersionUID = 1L;/*** 身份证*/@TableField("identity_card")private String identityCard;/*** 姓名*/@TableField("name")private String name;/*** 籍贯*/@TableField("native_place")private String nativePlace;/*** 镇*/@TableField("town")private String town;/*** 村*/@TableField("village")private String village;public String getIdentityCard() {return identityCard;}public void setIdentityCard(String identityCard) {this.identityCard = identityCard;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getNativePlace() {return nativePlace;}public void setNativePlace(String nativePlace) {this.nativePlace = nativePlace;}public String getTown() {return town;}public void setTown(String town) {this.town = town;}public String getVillage() {return village;}public void setVillage(String village) {this.village = village;}@Overrideprotected Serializable pkVal() {return this.serialVersionUID;}@Overridepublic String toString() {return "SsPersonInfo{" +"identityCard=" + identityCard +", name=" + name +", nativePlace=" + nativePlace +", town=" + town +", village=" + village +"}";}
}

PS.根据实体类改动service层和自定义类的内容

自定义读取Excel类:

package com.jxdinfo.hussar.util;import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;/*** @author wenqingkuan* @date 2019-10-10 14:15*/
@Component
public class ExcelSaxReader {private static final Logger logger = LoggerFactory.getLogger(ExcelSaxReader.class);/*** 表格默认处理器*/private ISheetContentHandler contentHandler = new DefaultSheetHandler();/*** 读取数据*/private List<String[]> datas = new ArrayList<String[]>();/*** 转换表格,默认为转换第一个表格* @param stream* @return* @throws InvalidFormatException* @throws IOException* @throws ParseException*/public ExcelSaxReader parse(InputStream stream)throws InvalidFormatException, IOException, ParseException {return parse(stream, 1);}/**** @param stream* @param sheetId:为要遍历的sheet索引，从1开始* @return* @throws InvalidFormatException* @throws IOException* @throws ParseException*/public synchronized ExcelSaxReader parse(InputStream stream, int sheetId)throws InvalidFormatException, IOException, ParseException {// 每次转换前都清空数据datas.clear();// 打开表格文件输入流OPCPackage pkg = OPCPackage.open(stream);try {// 创建表阅读器XSSFReader reader;try {reader = new XSSFReader(pkg);} catch (OpenXML4JException e) {logger.error("读取表格出错");throw new ParseException(e.fillInStackTrace());}// 转换指定单元表InputStream shellStream = reader.getSheet("rId" + sheetId);try {InputSource sheetSource = new InputSource(shellStream);StylesTable styles = reader.getStylesTable();ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);getContentHandler().init(datas);// 设置读取出的数据// 获取转换器XMLReader parser = getSheetParser(styles, strings);parser.parse(sheetSource);} catch (SAXException e) {logger.error("读取表格出错");throw new ParseException(e.fillInStackTrace());} finally {shellStream.close();}} finally {pkg.close();}return this;}/*** 获取表格读取数据,获取数据前，需要先转换数据<br>* 此方法不会获取第一行数据** @return 表格读取数据*/public List<String[]> getDatas() {return getDatas(true);}/*** 获取表格读取数据,获取数据前，需要先转换数据** @param dropFirstRow*            删除第一行表头记录* @return 表格读取数据*/public List<String[]> getDatas(boolean dropFirstRow) {if (dropFirstRow && datas.size() > 0) {datas.remove(0);// 删除表头}return datas;}/*** 获取读取表格的转换器** @return 读取表格的转换器* @throws SAXException*             SAX错误*/protected XMLReader getSheetParser(StylesTable styles, ReadOnlySharedStringsTable strings) throws SAXException {XMLReader parser = XMLReaderFactory.createXMLReader();parser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, getContentHandler(), false));return parser;}public ISheetContentHandler getContentHandler() {return contentHandler;}public void setContentHandler(ISheetContentHandler contentHandler) {this.contentHandler = contentHandler;}/*** 表格转换错误*/public class ParseException extends Exception {private static final long serialVersionUID = -2451526411018517607L;public ParseException(Throwable t) {super("表格转换错误", t);}}public interface ISheetContentHandler extends SheetContentsHandler {/*** 设置转换后的数据集，用于存放转换结果** @param datas*            转换结果*/void init(List<String[]> datas);}/*** 默认表格解析handder*/class DefaultSheetHandler implements ISheetContentHandler {/*** 读取数据*/private List<String[]> datas;private int columsLength;// 读取行信息private String[] readRow;private ArrayList<String> fristRow = new ArrayList<String>();@Overridepublic void init(List<String[]> datas) {this.datas = datas;
//          this.columsLength = columsLength;}@Overridepublic void startRow(int rowNum) {if (rowNum != 0) {readRow = new String[columsLength];}}@Overridepublic void endRow(int rowNum) {//将Excel第一行表头的列数当做数组的长度，要保证后续的行的列数不能超过这个长度，这是个约定。if (rowNum == 0) {columsLength = fristRow.size();readRow = fristRow.toArray(new String[fristRow.size()]);}else {readRow = fristRow.toArray(new String[columsLength]);}datas.add(readRow.clone());readRow = null;fristRow.clear();}@Overridepublic void cell(String cellReference, String formattedValue, XSSFComment comment) {int index = getCellIndex(cellReference);//转换A1,B1,C1等表格位置为真实索引位置try {fristRow.set(index, formattedValue);} catch (IndexOutOfBoundsException e) {int size = fristRow.size();for (int i = index - size+1;i>0;i--){fristRow.add(null);}fristRow.set(index,formattedValue);}}@Overridepublic void headerFooter(String text, boolean isHeader, String tagName) {}/*** 转换表格引用为列编号** @param cellReference*            列引用* @return 表格列位置，从0开始算*/public int getCellIndex(String cellReference) {String ref = cellReference.replaceAll("\\d+", "");int num = 0;int result = 0;for (int i = 0; i < ref.length(); i++) {char ch = cellReference.charAt(ref.length() - i - 1);num = (int) (ch - 'A' + 1);num *= Math.pow(26, i);result += num;}return result - 1;}}
}