excel处理全套流程
前端代码
<el-upload
class="upload-demo"
ref="upload"
action="http://127.0.0.1:8080/help/file"
accept=".xls,.xlsx"
:on-success="handleAvatarSuccess"
name="excelFile"
:auto-upload="false">
<el-button slot="trigger" size="small" type="primary">选取文件</el-button>
<el-button style="margin-left: 10px;" size="small" type="success" @click="submitUpload">上传到服务器</el-button>
<div slot="tip" class="el-upload__tip">上传文件请命名为tid.xlsx或者utid.xlsx,excel文件第一列为要转换的数据</div>
</el-upload>
on-success
表示文件上传处理成功之后调用的下载函数
submitUpload
为点击上传的函数
submitUpload () {
if (this.type == null) {
this.$alert('请选择转换类型', '警告')
return
}
this.$refs.upload.submit()
},
handleAvatarSuccess () {
axios({
url: 'http://127.0.0.1:8080/help/download',
method: 'post',
//下面这句必须有,表示返回的是文件
responseType: 'blob',
headers: {
'X-Requested-With': 'XMLHttpRequest'
},
params: {
type: this.type
}
}).then(res => {
//获取文件名,需要在后端进行配置
let filename = res.headers['filename']
let type = res.headers['content-type'].split(';')[0]
let blob = new Blob([res.data], { type: type })
const a = document.createElement('a')
// 创建URL
const blobUrl = window.URL.createObjectURL(blob)
a.download = filename
a.href = blobUrl
document.body.appendChild(a)
// 下载文件
a.click()
// 释放内存
URL.revokeObjectURL(blobUrl)
document.body.removeChild(a)
})
}
后端代码
//文件下载
@PostMapping(value = "/download")
@ResponseBody
public void downloadFiles(@RequestParam(value = "type", required = true) String type,
HttpServletResponse response) {
if (StringUtils.isBlank(type)) {
//return error(CommonErrorCode.COM_INVALID_PARAMETER, "文档id有误");
System.out.println("请先输入要处理的文档");
}
try {
String fileName;
if (type.equals("tidToUtid")) {
fileName = "tidToUtid.xlsx";
} else {
fileName = "utidToTid.xlsx";
}
//服务器对应的文件的路径
String file = "/Users/dxm/Desktop/" + fileName;
// 得到这个excel表格对象
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
OutputStream fos = null;
fos = response.getOutputStream();
response.setCharacterEncoding("UTF-8");
// 设置contentType为excel格式
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//下面这三行必须设置前端才可以读取到文件名
response.setHeader("Content-Disposition", fileName);
response.setHeader("filename", fileName);
response.setHeader("Access-Control-Expose-Headers", "filename");
wb.write(fos);
fos.close();
} catch (FileNotFoundException e) {
logger.error("下载文档异常-找不到文件:{}", e);
//return error(CommonErrorCode.COM_UNKNOWN_ERROR, "找不到文件");
} catch (UnsupportedEncodingException e) {
logger.error("下载文档异常-编码转换:{}", e);
//return error(CommonErrorCode.COM_UNKNOWN_ERROR, "编码转换失败");
} catch (IOException e) {
logger.error("下载文档异常-io:{}", e);
//return error(CommonErrorCode.COM_UNKNOWN_ERROR, "下载文档异常");
}
}
java生成excel文件
public class ExcelExporter {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExporter.class);
private static final String[] tableNameTU = {"***", "***", "***"};
private static final String[] tableNameUT = {"***", "***", "***"};
private String[] headerNames;
private Workbook workBook = new XSSFWorkbook();
private Sheet sheet;
/**
* @param tableName 表头
*/
public ExcelExporter(String tableName) {
if (tableName.equals("tidToUtid")) {
this.headerNames = tableNameTU;
} else {
this.headerNames = tableNameUT;
}
sheet = workBook.createSheet("sheet1");
initHeader();
}
/**
* 初始化表头信息
*/
private void initHeader() {
// 创建第一行
Row row = sheet.createRow(0);
Cell cell = null;
// 创建表头
for (int i = 0; i < headerNames.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headerNames[i]);
setCellStyle(cell);
}
}
/**
* 设置单元格样式
*
* @param cell 单元格
*/
public void setCellStyle(Cell cell) {
// 设置样式
CellStyle cellStyle = workBook.createCellStyle();
// 设置字体
Font font = workBook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 13);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
public void createTableRows(List<String> data) {
int size = data.size();
for (int i = 0; i < size; i++) {
String[] flag = data.get(i).split(",");
Row row = sheet.createRow(i + 1);
Cell cell;
int tableLength = 3;
for (int j = 0; j < tableLength; j++) {
String value = flag[j];
cell = row.createCell(j);
cell.setCellType(CellType.STRING);
cell.setCellValue(value);
}
}
}
/**
* 根据表头自动调整列宽度
*/
public void autoAllSizeColumn() {
if (sheet instanceof SXSSFSheet) {
// 如果是SXSSFSheet,需要调用trackAllColumnsForAutoSizing方法一次
SXSSFSheet tmpSheet = (SXSSFSheet) sheet;
tmpSheet.trackAllColumnsForAutoSizing();
}
for (int i = 0, length = headerNames.length; i < length; i++) {
sheet.autoSizeColumn(i);
}
}
/**
* 将数据写出到excel中
*
* @param outputStream
*/
public void exportExcel(OutputStream outputStream) {
// 导出之前先自动设置列宽
this.autoAllSizeColumn();
try {
workBook.write(outputStream);
} catch (IOException e) {
LOGGER.error(" exportExcel error", e);
} finally {
IOUtils.closeQuietly(outputStream);
}
}
/**
* 将数据写出到excel中
*
* @param outputFilePath
*/
public void exportExcel(String outputFilePath) {
// 导出之前先自动设置列宽
this.autoAllSizeColumn();
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream(outputFilePath);
workBook.write(outputStream);
} catch (IOException e) {
LOGGER.error(" exportExcel error", e);
} finally {
IOUtils.closeQuietly(outputStream);
}
}
}
java读取excel文件
public class ExcelUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
public static List<String> excelToShopIdList(InputStream inputStream) {
List<String> list = new ArrayList<>();
Workbook workbook;
try {
workbook = WorkbookFactory.create(inputStream);
inputStream.close();
//工作表对象
Sheet sheet = workbook.getSheetAt(0);
//总行数
int rowLength = sheet.getLastRowNum() + 1;
//工作表的列
Row row = sheet.getRow(0);
//总列数
//int colLength = row.getLastCellNum();
//得到指定的单元格
Cell cell;
for (int i = 0; i < rowLength; i++) {
row = sheet.getRow(i);
cell = row.getCell(0);
if (cell != null) {
String data = cell.getStringCellValue();
data = data.trim();
if (!data.equals("")) {
list.add(data);
}
}
}
} catch (Exception e) {
LOGGER.error("parse excel file error :", e);
}
return list;
}
}