可以在評論區交流!!!
前端:
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"></i>導入</button>
<button type="button" class="layui-btn" id="excelExport"><i class="layui-icon"></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結尾的excel
alert("隻能上傳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)
@ResponseBody
public 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")
@ResponseBody
public 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 {
@Autowired
SsPersonInfoMapper ssPersonInfoMapper;
@Autowired
ExcelSaxReader excelSaxReader;
@Override
public Integer[] readExcelFile(@RequestParam("file") MultipartFile file){
//用于存放導入的結果資訊
Integer[] resultMap = {0,0};
InputStream inputStream=null;
try{
//新增的資料條數
int count = 0;
//新增的多個map
List<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 所有的鄉鎮/街道
*/
@Override
public List<Map<String, Object>> getAllTown() {
return ssPersonInfoMapper.getAllTown();
}
/**
* 根據鄉鎮擷取所轄的村/社群
* @param map 鄉鎮
* @return 本鎮所有的村
*/
@Override
public List<Map<String, Object>> getAllVillage(Map<String, Object> map) {
return ssPersonInfoMapper.getAllVillage(map);
}
/**
* 用于導出以村為機關的資料
* @param response 用于輸出檔案
*/
@Override
public 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(){
@Override
public 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">
select
identity_card,name,native_place,town,village
from ss_person_info
WHERE
town=#{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;
}
@Override
protected Serializable pkVal() {
return this.serialVersionUID;
}
@Override
public 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>();
@Override
public void init(List<String[]> datas) {
this.datas = datas;
// this.columsLength = columsLength;
}
@Override
public void startRow(int rowNum) {
if (rowNum != 0) {
readRow = new String[columsLength];
}
}
@Override
public 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();
}
@Override
public 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);
}
}
@Override
public 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;
}
}
}
最終效果:
導入:
導出:
** 按照鎮,村結構導出!!! **
總結
如果不需要大資料的傳輸,可以不用這麼麻煩,簡單的導入導出參考這個就夠了
Excel的導入導出