public Map<String, Object> importJdCSV(@RequestParam("file") MultipartFile file) throws IOException, ParseException, InterruptedException {
logger.info("<== 接收文件成功!");
Map<String, Object> params = new HashedMap();
//params.put("createBy", UserSessionUtil.getUserNo());
//params.put("updateBy", UserSessionUtil.getUserNo());
params.put("createBy", "123456");
params.put("updateBy", "123456");
String name = file.getOriginalFilename();
String fileName = file.getOriginalFilename();
String fileType = fileName.substring(fileName.lastIndexOf(".")+1);//文件类型
if(!"csv".equals(fileType)){
return ReturnResult.error("请导入csv格式的文件");
}
String dirs = RECHARGE_JD;
String filePath = dirs + File.separator + fileName;
logger.info("保存路径: " + filePath);
File dir = new File(dirs);
OwinfoBeanUtils.delAllFile(dirs); //删除完里面所有内容
dir.mkdirs();
try {
file.transferTo(new File(filePath)); // 转存文件
logger.info("转存文件成功,开始读取文件数据");
BufferedReader reader = new BufferedReader(new FileReader(filePath));//换成你的文件名
reader.readLine();//第一行信息,为标题信息,不用,如果需要,注释掉
String line = null;
String tradeDate = "";
List<OnlineRechargeDetail> onlineRechargeDetailList = new ArrayList<>();
while((line=reader.readLine())!=null){
OnlineRechargeDetail onlineRechargeDetail = new OnlineRechargeDetail();
String[] item = line.split(",");//CSV格式文件为逗号分隔符文件,这里根据逗号切分
onlineRechargeDetail.setTransId(item[0]);
onlineRechargeDetail.setTradeAmount(Long.parseLong(item[10]));
onlineRechargeDetail.setTradeTime(item[6]);
tradeDate = item[6];
onlineRechargeDetail.setSpare(item[14]);
onlineRechargeDetail.setCreateBy("123456");
onlineRechargeDetail.setUpdateBy("123456");
onlineRechargeDetailList.add(onlineRechargeDetail);
}
logger.info("读取文件数据完成,开始插入数据");
if(onlineRechargeDetailList == null){
return ReturnResult.error("文件内容为空,请检查后重新导入");
}
params.put("list",onlineRechargeDetailList);
tradeDate = tradeDate.replace("/","-");
tradeDate = OwinfoBeanUtils.formatDateStr(tradeDate);
params.put("tradeDate",tradeDate);
logger.info("开始插入JD在线充值数据 params = " + params);
//调用在线充值详情数据接口(异步)
onlineRechargeService.jdListAdd(params);
return ReturnResult.success("接收文件成功,正在处理数据请稍等两分钟");
} catch (IOException e) {
e.printStackTrace();
return ReturnResult.error("上传文件出错,请重试");
}
}
//下载
@RequestMapping(value = "/modelExport")
public Map<String, Object> modelExport(@RequestParam Map<String, Object> param, HttpServletResponse response) throws IOException {
Map<String, Object> tempMap = new HashMap<String, Object>();
if(param == null || param.get("type") == null ||"".equals(String.valueOf(param.get("type")))){
tempMap.put("ResultCode", 0);
tempMap.put("msg", "获取模板类型为空");
return tempMap;
}
String filePath = RECHARGE_MODEL + File.separator;
String fileName = "";
if("京东".equals(String.valueOf(param.get("type")))){
fileName = "京东充值模板.csv";
filePath = filePath + fileName;
}
File file = new File(filePath);
//重置response
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//解决中文文件名显示问题
response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
//设置文件长度
int fileLength = (int) file.length();
response.setContentLength(fileLength);
if (fileLength != 0) {
InputStream inStream = new FileInputStream(file);
byte[] buf = new byte[4096];
//创建输出流
ServletOutputStream servletOS = response.getOutputStream();
int readLength;
//读取文件内容并写入到response的输出流当中
while ((readLength = inStream.read(buf)) != -1) {
servletOS.write(buf, 0, readLength);
}
//关闭输入流
inStream.close();
//刷新输出流缓冲
servletOS.flush();
//关闭输出流
servletOS.close();
tempMap.put("ResultCode", 1);
tempMap.put("msg", "下载成功");
} else {
tempMap.put("ResultCode", 0);
tempMap.put("msg", "下载出错");
}
return tempMap;
}
//工具类
package com.owinfo.service.util;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sun.xml.internal.messaging.saaj.util.ByteOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
public class ExportExcel {
public static String NO_DEFINE = "no_define";//未定义的字段
public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";//默认日期格式
public static int DEFAULT_COLOUMN_WIDTH = 17;
/**
* 导出Excel 2007 OOXML (.xlsx)格式
*
// * @param title 标题行
// * @param headMap 属性-列头
// * @param jsonArray 数据集
// * @param datePattern 日期格式,传null值则默认 年月日
// * @param colWidth 列宽 默认 至少17个字节
// * @param out 输出流
*/
public static byte[] exportExcelX(Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, ByteOutputStream out) {
if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
/*CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);*/
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setFillForegroundColor((short) 10);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据,产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if (rowIndex == 65535 || rowIndex == 0) {
if (rowIndex != 0) sheet = (SXSSFSheet) workbook.createSheet();//如果数据超过了,则在第二页显示
/*SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));*/
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 1;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if (o == null) cellValue = "";
else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else if (o instanceof Float || o instanceof Double)
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
else cellValue = o.toString();
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
workbook.write(out);
workbook.dispose();
return out.toByteArray();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
public static void exportExcelX(Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
/*CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);*/
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setFillForegroundColor((short) 10);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据,产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if (rowIndex == 65535 || rowIndex == 0) {
if (rowIndex != 0) sheet = (SXSSFSheet) workbook.createSheet();//如果数据超过了,则在第二页显示
/*SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));*/
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 1;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if (o == null) cellValue = "";
else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else if (o instanceof Float || o instanceof Double)
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
else cellValue = o.toString();
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
workbook.write(out);
workbook.dispose();
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void exportExcelXT(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
/*CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);*/
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setFillForegroundColor((short) 10);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
//设置列宽
int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii,arrColWidth[ii]*256);
ii++;
}
// 遍历集合数据,产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if(rowIndex == 65535 || rowIndex == 0){
if ( rowIndex != 0 ) sheet = (SXSSFSheet) workbook.createSheet();//如果数据超过了,则在第二页显示
/*SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));*/
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0); //列头 rowIndex =1
for(int i=0;i<headers.length;i++)
{
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 1;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++)
{
SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if(o==null) cellValue = "";
else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else if(o instanceof Float || o instanceof Double)
cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
else cellValue = o.toString();
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
workbook.write(out);
System.out.println("end!!!");
workbook.dispose();
} catch (IOException e) {
e.printStackTrace();
}
}
}