发布：2023/3/24 21:52:04作者：管理员 来源：本站 浏览次数：943
HSSFWorkbook操作excel读写
//exlel读操作
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Iterator<String> iter = multipartRequest.getFileNames();
File fileFile = null;
while (iter.hasNext()) {
MultipartFile multipartFile = multipartRequest.getFile(iter.next());
String sourceName = multipartFile.getOriginalFilename();
String base = request.getSession().getServletContext().getRealPath("/");
File file = new File(base);
if(!file.exists()){
file.mkdirs();
}
String path=base + File.separator + sourceName;
File fileFile = new File(path);
multipartFile.transferTo(fileFile);
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = wb.getSheetAt(0);
for(int j=0;j<sheet.getLastRowNum()+1;j++) {
HSSFRow row = sheet.getRow(j);
for(int i1=0; i1<row.getLastCellNum(); i1++) {
HSSFCell cell = row.getCell(i1);
System.out.println(cell.getRichStringCellValue());
}
}
}
//exlel写操作
// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(name);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
sheet.setColumnWidth(0, 10000);
sheet.setColumnWidth(1, 7000);
sheet.setColumnWidth(2, 4000);
cell.setCellValue(TeacherConstant.TCITY);
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(TeacherConstant.TNAME);
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue(TeacherConstant.TPHONE);
cell.setCellStyle(style);
for (int i = 0; i < pmUsers.size(); i++) {
Map<String, Object> map = pmUsers.get(i);
row = sheet.createRow(i + 1);
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString(map.get("TotalCityName") + ""));
cell = row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString(map.get("teacher_name") + ""));
cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue(new HSSFRichTextString(map.get("teacher_phone") + ""));
}
// 第六步，将文件存到指定位置
FileOutputStream file = null;
String fileAdd = "";
if (pmUsers.size() > 0) {
if (paramBean.getCityId() == null) {
fileAdd = realPath + pmUsers.get(0).get("TotalCityName").toString().substring(0, 3) + TeacherConstant.TTABLE + TeacherConstant.XLS;
} else if (paramBean.getCountyId() == null) {
fileAdd = realPath + pmUsers.get(0).get("TotalCityName").toString().substring(0, 6) + TeacherConstant.TTABLE + TeacherConstant.XLS;
} else {
fileAdd = realPath + pmUsers.get(0).get("TotalCityName").toString().substring(0, 9) + TeacherConstant.TTABLE + TeacherConstant.XLS;
}
} else {
return;
}
try
{
file = new FileOutputStream(fileAdd);
wb.write(file);
}
catch (Exception e)
{
e.printStackTrace();
}
finally {
file.close();
}
