public class ExcelUtil {
public static void writeBookExcel(List<Book> list, HttpServletResponse response){
try {
String filename= "用户查询结果.xlsx";
//创建一个excel的内存对象
Workbook wb = null;
if (filename.endsWith("xls")){
wb = new HSSFWorkbook();
}else{
wb = new XSSFWorkbook();
}
//创建sheet页
Sheet sheet = wb.createSheet("用户查询结果");
//添加表头
String titleRow[] ={"编号","姓名","密码"};
Row row = sheet.createRow(0);//第一行
Cell cell= row.createCell(0);
cell.setCellValue("用户查询结果表");
//设置行高
row.setHeight((short) 540);//540=27像素
CellStyle style = wb.createCellStyle();
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setWrapText(true);
cell.setCellStyle(style);
//定义字体
Font font = wb.createFont();
font.setFontName("微软雅黑");
font.setFontHeight((short) 280);
style.setFont(font);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//第二行数据
row = sheet.createRow(1);
for (int i = 0; i < titleRow.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
cell.setCellStyle(style);
sheet.setColumnWidth(1,20*256);
}
row.setHeight((short) 540);
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i+2);
row.setHeight((short) 500);
row.createCell(0).setCellValue(list.get(i).getId());
//后面自己补齐
}
//response.sethead需要写
OutputStream stream = response.getOutputStream();
wb.write(stream);
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static List<Book> readBookExcel(File file){
List<Book> list = new ArrayList<>();
try {
FileInputStream inputStream =new FileInputStream(file);
//定义一个空的excel文件
Workbook wb = null;
//根据excel文件的扩展名,进行不同的实例化
if (file.getName().endsWith("xls")){//xls是老版的excel文件扩展名
wb = new HSSFWorkbook(inputStream);
}else{
wb = new XSSFWorkbook(inputStream);//针对xlsx格式的excel文件
}
//获取第一个sheet页
Sheet sheet = wb.getSheetAt(0);
//从当前的sheet页中,获取所有行数据
Iterator<Row> rows= sheet.rowIterator();
//由于表格中数据的前两行是没有用的数据,因此向下移动2行数据
rows.next();
rows.next();
Book book=null;
//通过循环的方式处理数据
while (rows.hasNext()){
Row row = rows.next();
book = new Book();
book.setId(row.getCell(0).getStringCellValue());
book.setBookname(row.getCell(1).getStringCellValue());
book.setBookauthor(row.getCell(2).getStringCellValue());
book.setBookprice((double) row.getCell(3).getNumericCellValue());
list.add(book);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
java后台处理excel表格的读写操作
最新推荐文章于 2024-05-17 20:15:57 发布