Java Web使用POI导出Excel的方法详解

网友投稿 242 2023-05-10


Java Web使用POI导出Excel的方法详解

本文实例讲述了java Web使用POI导出Excel的方法。分享给大家供大家参考,具体如下:

采用Spring mvc架构:

Controller层代码如下

@Controller

public class StudentExportController{

@Autowired

private StudentExportService studentExportService;

@RequestMapping(value = "/excel/export")

public void exportExcel(HttpServletRequest request, HttpServletResponse response)

throws Exception {

List list = new ArrayList();

list.add(new Student(1000,"zhangsan","20"));

list.add(new Student(1001,"lisi","23"));

list.add(new Student(1002,"wangwu","25"));

HSSFWorkbook wb = studentExportService.export(list);

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-disposition", "attachment;filename=student.xls");

OutputStream ouputStream = response.getOutputStream();

wb.write(ouputStream);

ouputStream.flush();

ouputStream.close();

}

}

Service层代码如下:

@Service

public class StudentExportService {

String[] excelHeader = { "Sno", "Name", "Age"};

public HSSFWorkbook export(List list) {

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Campaign");

HSSFRow row = sheet.createRow((int) 0);

HSSFCellStyle style = wb.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

for (int i = 0; i < excelHeader.length; i++) {

HSSFCell cell = row.createCell(i);

cell.setCellValue(excelHeader[i]);

cell.setCellStyle(style);

sheet.autoSizeColumn(i);

}

for (int i = 0; i < list.size(); i++) {

row = sheet.createRow(i + 1);

Student student = list.get(i);

row.createCell(0).setCellValue(student.getSno());

row.createCell(1).setCellValue(student.getName());

row.createCell(2).setCellValue(student.getAge());

}

return wb;

}

}

前台的js代码如下:

设置Excel样式以及注意点:

String[] excelHeader = { "所属区域(地市)", "机房", "机架资源情况", "", "", "", "",

"", "端口资源情况", "", "", "", "", "", "机位资源情况", "", "", "设备资源情况",

"", "", "IP资源情况", "", "", "", "", "网络设备数" };

String[] excelHeader1 = { "", "", "总量(个)", "空闲(个)", "预占(个)", "实占(个)",

"自用(个)", "其它(个)", "总量(个) ", "在用(个)", "空闲(个)", "总带宽(M)",

"在用带宽(M)", "空闲带宽(M)", "总量(个)", "在用(个)", "空闲(个)", "设备总量(个)",

"客户设备(个)", "电信设备(个)", "总量(个)", "空闲(个)", "预占用(个)", "实占用(个)",

"自用(个)", "" };

// 单元格列宽

int[] excelHeaderWidth = { 150, 120, 100, 100, 100, 100, 100, 100, 100,

100, 100, 120, 120, 120, 120, 120, 120, 150, 150, 150, 120,

120, 150, 150, 120, 150 };

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("机房报表统计");

HSSFRow row = sheet.createRow((int) 0);

HSSFCellStyle style = wb.createCellStyle();

// 设置居中样式

style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中

// 设置合计样式

HSSFCellStyle style1 = wb.createCellStyle();

Font font = wb.createFont();

font.setColor(HSSFColor.RED.index);

font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体

style1.setFont(font);

style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中

style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中

// 合并单元格

// first row (0-based) last row (0-based) first column (0-based) last

// column (0-based)

sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));

sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 7));

sheet.addMergedRegion(new CellRangeAddress(0, 0, 8, 13));

sheet.addMergedRegion(new CellRangeAddress(0, 0, 14, 16));

sheet.addMergedRegion(new CellRangeAddress(0, 0, 17, 19));

sheet.addMergedRegion(new CellRangeAddress(0, 0, 20, 24));

sheet.addMergedRegion(new CellRangeAddress(0, 1, 25, 25));

// 设置列宽度(像素)

for (int i = 0; i < excelHeaderWidth.length; i++) {

sheet.setColumnWidth(i, 32 * excelHeaderWidth[i]);

}

// 添加表格头

for (int i = 0; i < excelHeader.length; i++) {

HSSFCell cell = row.createCell(i);

cell.setCellValue(excelHeader[i]);

cell.setCellStyle(style);

}

row = sheet.createRow((int) 1);

for (int i = 0; i < excelHeader1.length; i++) {

HSSFCell cell = row.createCell(i);

cell.setCellValue(excelHeader1[i]);

cell.setCellStyle(style);

}

注意点1:合并单元格    new CellRangeAddress(int,int,int,int)

first row (0-based) ,last row (0-based), first column (0-based),last column (0-based)

注意点2:合并单元格

String[] excelHeader = { "所属区域(地市)", "机房", "机架资源情况", "", "", "", "","", "端口资源情况", "", "", "", "", "", "机位资源情况", "", "", "设备资源情况","", "", "IP资源情况", "", "", "", "", "网络设备数" };

合并以后的单元格虽然是一个,但是仍然要保留其单元格内容,此处用空字符串代替,否则后续表头显示不出

注意点3:填充单元格

正确写法:

HSSFCell cell = row.createCell(i);

cell.setCellValue(excelHeader1[i]);

cell.setCellStyle(style);

错误写法:

row.createCell(i).setCellValue(excelHeader1[i]);

row.createCell(i).setCellStyle(style);

本人为了省一个HSSFCell对象,使用了错误写法,导致HSSFCell对象创建了2次,最后只保留了样式,而内容无法显示

更多关于java相关内容感兴趣的读者可查看本站专题:《Java数据结构与算法教程》、《Java文件与目录操作技巧汇总》、《Java操作DOM节点技巧总结》和《Java缓存操作技巧汇总》

希望本文所述对大家java程序设计有所帮助。


版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:Java实现双色球抽奖随机算法示例
下一篇:完美解决Spring声明式事务不回滚的问题
相关文章

 发表评论

暂时没有评论,来抢沙发吧~