SpringBoot (导入导出)上传、下载
XSSFWorkbook(Excel-2003)
HSSFWorkbook(Excel-2007)
上传
public WrapperResponse getDownData(MultipartFile file) throws IOException {
WrapperResponse objectWrapperResponse = new WrapperResponse<>();
List<String> mobString = new ArrayList<>();
StringBuffer stringBuffer = new StringBuffer();
InputStream is =file.getInputStream() ;
try {
Workbook wb = null;
wb = new HSSFWorkbook(is);
Sheet sheet = wb.getSheetAt(0);
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < physicalNumberOfRows; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
Cell cells = row.getCell(2);
cells.setCellType(CellType.STRING);
if (i == 1 || i == physicalNumberOfRows) {
if (!String.valueOf(cells.getStringCellValue()).equals("mob")) {
stringBuffer.append(cells.getStringCellValue());
mobString.add(String.valueOf(cells.getStringCellValue()));
}
} else {
if (!String.valueOf(cells.getStringCellValue()).equals("mob")) {
stringBuffer.append(",");
stringBuffer.append(cells.getStringCellValue());
mobString.add(String.valueOf(cells.getStringCellValue()));
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
objectWrapperResponse.setData(stringBuffer.toString());
objectWrapperResponse.setMessage("下载成功!");
return objectWrapperResponse;
}
下载
public void downDateText(HttpServletResponse httpServletResponse) throws MbsException {
String downName = "批量发送短信人员手机号码登记模板.xlsx";
String[] titles = {"name", "certno", "mob"};
httpServletResponse.setHeader("Content-disposition", "attachment;fileName="+ downName);
httpServletResponse.setContentType("application/vnd.ms-template;charset=UTF-8");
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("手机号码登记");
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFillForegroundColor((short) 70);
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HSSFDataFormat dataFormat = wb.createDataFormat();
headStyle.setDataFormat(dataFormat.getFormat("@"));
headStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFDataFormat format = wb.createDataFormat();
headStyle.setDataFormat(format.getFormat("@"));
HSSFCellStyle columnHeadStyle = wb.createCellStyle();
columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);
for (int i = 0; i < titles.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(headStyle);
}
OutputStream os = new BufferedOutputStream(httpServletResponse.getOutputStream());
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
throw new MbsException(e.getMessage());
}
}