记录一下导出导入EXCEL方便以后查看。
导出EXCEL前端代码
function export(){
setTimeout(function() {
//可加入加载中控件
//导出Excel文件
//URL地址
var url = "/api/hello";
//传递参数名称
var ids = "123,321"
function getiframeDocument($iframe) {
var iframeDoc = $iframe[0].contentWindow || $iframe[0].contentDocument;
if (iframeDoc.document) {
iframeDoc = iframeDoc.document;
}
return iframeDoc;
}
//标识是否导出成功
var isSuccess = true;
var $iframe = $("<iframe style='display: none' src='about:blank'></iframe>").appendTo("body");
var formDoc = getiframeDocument($iframe);
formDoc.write("<html><head></head><body><form id='form1' method='post' enctype='application/json' action='" + url + "'><input type='hidden' name='ids' value='" + ids + "' /></form></body></html>");
var $form = $(formDoc).find('form');
$form.submit();
//如果后端报错
$iframe.load(function() {
debugger;
isSuccess = false;
})
setTimeout(function() {
//导出成功
if (isSuccess) {
}
}, 2000);
}, 500);
}
导出EXCEL后端代码
导出EXCEL后端代码
// 后端接口
@Path("/")
@Produces(MediaType.APPLICATION_JSON)
@Consumes(MediaType.APPLICATION_JSON)
public interface TestExportApi{
/***
* 导出方法
* @return
*/
@POST
@Path("exportData")
@Consumes(MediaType.APPLICATION_FORM_URLENCODED)
public void exportData(@Context HttpServletResponse response, @FormParam("ids") String ids);
}
// 后端接口实现类方法
@PersistenceContext(type = PersistenceContextType.EXTENDED)
EntityManager entityManagerExport;
@Transactional(rollbackFor = Exception.class)
@Override
public void exportData(HttpServletResponse response, String ids) {
// 创建Excel
HSSFWorkbook wb = new HSSFWorkbook();
String[] idsArr=ids.split(",");
HSSFSheet sheet=null;
HSSFRow row=null;
HSSFCell cell=null;
//数据库表名称
String tableName="tablename,tablename02";
int cellIndex=0;
int rowIndex=0;
try{
//设置表头样式
// 创建字体样式
HSSFCellStyle headerStyle = (HSSFCellStyle) wb.createCellStyle();
HSSFDataFormat format = wb.createDataFormat();
headerStyle.setDataFormat(format.getFormat("@"));
headerStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
HSSFFont headerFont = (HSSFFont) wb.createFont();
headerFont.setBold(true); // 字体加粗
headerStyle.setFont(headerFont); //为标题样式设置字体样式
//设置内容样式
HSSFCellStyle contentStyle = (HSSFCellStyle) wb.createCellStyle();
contentStyle.setDataFormat(format.getFormat("@"));
for(String item:idsArr){
item=item.trim();
if(item.length()!=0) {
cellIndex = 0;
rowIndex = 0;
// 获取需要导出的表格
String[] splitTable = tableName.split(",");
for (String tableMc : splitTable) {
cellIndex = 0;
rowIndex = 0;
List<Map> resultList = new ArrayList<>();
String tableColInfo = "";
// 获取到指定数据库表的所有列,根据表处理条件
switch (tableMc){
case "tablename":
tableColInfo = "SELECT * FROM tablename where ID = ?1";
break;
case "tablename02" :
tableColInfo = "SELECT * FROM tablename where ID = ?1";
break;
default:
break;
}
Query nativeQuery = entityManagerExport.createNativeQuery(tableColInfo)
.setParameter(1, item);
nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP.ALIAS_TO_ENTITY_MAP);
resultList =nativeQuery.getResultList();
if (resultList.size() != 0) {
if(wb.getSheet(tableMc) == null){
sheet = wb.createSheet(tableMc);
// region 样式
sheet.setVerticallyCenter(true);
sheet.setHorizontallyCenter(true);
//查询数据库数据并将其放入当前sheet页
row = sheet.createRow(0);
row.setHeightInPoints(23);
//设置表头
for (Object key : resultList.get(0).keySet()) {
sheet.setColumnWidth(cellIndex, 6000);
cell = row.createCell(cellIndex);
cell.setCellValue(key.toString());
cell.setCellStyle(headerStyle);
cellIndex++;
}
sheet.setColumnWidth(cellIndex, 18000);
rowIndex = 1;
}else{
sheet = wb.getSheet(tableMc);
rowIndex = sheet.getLastRowNum() + 1;
}
//设置内容
for (Map mapData : resultList) {
cellIndex = 0;
row = sheet.createRow(rowIndex);
row.setHeightInPoints(23);
for (Object key : mapData.keySet()) {
cell = row.createCell(cellIndex);
cell.setCellValue(mapData.get(key) != null ? mapData.get(key).toString() : "");
cell.setCellStyle(contentStyle);
cellIndex++;
}
rowIndex++;
}
}
}
}
}
// 输出Excel文件
OutputStream output = response.getOutputStream();
response.reset();
String dateStr= LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")).toString();
String fileName="生成文件名称"
// 设置文件头
response.setHeader("Content-Disposition",
"attchement;filename=" + new String((fileName+dateStr+".xls").getBytes("gb2312"), "ISO8859-1"));
response.setContentType("application/msexcel");
wb.write(output);
wb.close();
}catch(Exception e){
//异常回滚
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
}
导入EXCEL前端代码
function importData() {
debugger
var upload = $("<input>");
upload.attr("style", "display:none");
upload.attr("type", "file");
upload.attr("id", "upload")
$("body").append(upload);
//选中文件出触发
$('#upload').on('change', function(event) {
//可加入加载中控件
setTimeout(function() {
var formData = new FormData();
var name = $("#upload").val();
var fileName = $("#upload")[0].files[0];
var point = fileName.name.lastIndexOf(".");
var type = fileName.name.substr(point);
if (type != ".xls") {
return false;
}
formData.append("file", $("#upload")[0].files[0]);
formData.append("filename", name);
$.ajax({
url: '/api/hello',
dataType: 'json',
type: 'POST',
async: false,
data: formData,
processData: false, // 使数据不做处理
contentType: false, // 不要设置Content-Type请求头
success: function(data) {
if (data.Code !== 'error') {
//成功操作
}else{
//失败操作
}
//解决不能重复上传同一相同文件的问题
$("#upload").attr("type", "text");
$("#upload").attr("type", "file");
},
//后端报错
error: function(response) {
$("#upload").remove();
console.log(error);
}
});
}, 500);
});
$("#upload").click();
}
导入EXCEL后端代码
导入EXCEL后端代码
//导入后端接口
/**
* 导入Excel
*
* @return
*/
@POST
@Path("importData")
@Consumes(MediaType.MULTIPART_FORM_DATA)
String importData(@Multipart("file") Attachment attachment, @Multipart("filename") String filename, @Context HttpServletResponse response);
//导入后端实现类方法
@PersistenceContext(type = PersistenceContextType.EXTENDED)
EntityManager entityManagerImport;
@Transactional(rollbackFor = Exception.class)
@Override
public String importData(Attachment attachment, String filename, HttpServletResponse response) {
ObjectMapper objectMapper = new ObjectMapper();
//存放返回值
Map<String,Object> result=new HashMap<>();
InputStream inputStream=null;
try {
String name=attachment.getDataHandler().getName();
if(!StringUtils.isEmpty(name)){
inputStream=attachment.getObject(InputStream.class);
}
int rowNum = 0;//已取值的行数
int colNum = 0;//列号
int realRowCount = 0;//真正有数据的行数
//得到工作空间
Workbook workbook = null;
if(null == filename) {
return null;
}
if (!inputStream.markSupported()) {
inputStream = new PushbackInputStream(inputStream, 8);
}
workbook= WorkbookFactory.create(inputStream);
Sheet sheet=null;
//保存所有sql语句条数
long sqlCount=0;
List<Map> returnLists = new ArrayList<>();
int SaveCount = 0;
int reSaveCount = 0;
//存储重复数据id
ArrayList <String> repeat = new ArrayList<>();
//存储未重复数据id
ArrayList <String> repeatNot = new ArrayList<>();
for(int i=0;i<workbook.getNumberOfSheets();i++){
sheet = ImportUtilsOf.getSheetByWorkbook(workbook, i);
realRowCount = sheet.getPhysicalNumberOfRows();
//搭建插入列名
StringBuffer strKey = new StringBuffer();
List <String> strKeyList = new ArrayList<>();
strKeyList.clear();
strKey.setLength(0);
//搭建插入值名
StringBuffer strVal = new StringBuffer();
List <String> strValList = new ArrayList<>();
for(Row row:sheet) {
String tableName = sheet.getSheetName();
//每一行清空值数据
strVal.setLength(0);
strValList.clear();
if(realRowCount == rowNum) {
break;
}
if(ImportUtilsOf.isBlankRow(row)) {//空行跳过
continue;
}
if(row.getRowNum() == -1) {
continue;
}
int cellSum=row.getLastCellNum();
for(int cellIndex=0;cellIndex<cellSum;cellIndex++){
//获取当前单元格值
String cellValue=row.getCell(cellIndex).getStringCellValue().toString();
if(row.getRowNum() == 0) {
//列名,将列加入数组
strKey.append(cellValue);
strKeyList.add(cellValue);
if (cellIndex != cellSum - 1) {//前面的元素后面全拼上",",最后一个元素后不拼
strKey.append(",");
}
}else{
//拼接插入sql
if( "".equals(cellValue)){
strVal.append("" +null+ "");
}else{
//值名
strVal.append("'" + cellValue + "'");//拼接单引号
}
if (cellIndex != cellSum - 1) {//前面的元素后面全拼上",",最后一个元素后不拼
strVal.append(",");
}
//将值加入数组
strValList.add(cellValue);
}
//执行sql
if(cellIndex == cellSum - 1 && row.getRowNum() != 0){
String id = strValList.get(strKeyList.indexOf("ID"));
//验证是否重复导入
int countOf = getCountOf(tableName, id);
if(countOf > 0){
continue;
}
//保存数据
String sqlEntry= "insert into "+tableName+"("+ strKey +") values ("+strVal +")";
entityManagerImport.createNativeQuery(sqlEntry).executeUpdate();
//每一行清空值数据
strVal.setLength(0);
SaveCount++;
}
}
}
}
result.put("Code","导入成功"+SaveCount+"条,有"+reSaveCount+"条重复导入!");
} catch (Exception ex) {
result.put("Code","error");
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return objectMapper.writeValueAsString(result);
}