public static void createExcel(List> list, HttpServletResponse response)throws Exception {
try {
//创建工作簿
HSSFWorkbook wb =new HSSFWorkbook();
String filename ="驿站能耗统计表";
CellRangeAddress cellAddresses =new CellRangeAddress(0,0,0,list.get(0).size() -1);
HSSFCellStyle style =createCellStyle(wb,(short)18);
String titleName = list.get(list.size() -1).get("monthName") + filename;
HSSFSheet sheet=wb.createSheet(titleName);
sheet.addMergedRegion(cellAddresses);
// 设置默认列宽
sheet.setDefaultColumnWidth(16);
// sheet.setDefaultRowHeightInPoints(20);
// sheet.autoSizeColumn(1, true);
//创建表头行
HSSFRow row = sheet.createRow(0);
// row.setHeightInPoints(25);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(filename);
HSSFRow oneRow = sheet.createRow(1);
HSSFCell oneRowCell1=oneRow.createCell(0);
oneRowCell1.setCellValue("时间");
oneRowCell1.setCellStyle(createCellStyle(wb,(short)11));
HSSFCell oneRowCell2=oneRow.createCell(1);
oneRowCell2.setCellValue("用电量");
oneRowCell2.setCellStyle(createCellStyle(wb,(short)11));
HSSFCell oneRowCell3=oneRow.createCell(2);
oneRowCell3.setCellValue("光伏发电量");
oneRowCell3.setCellStyle(createCellStyle(wb,(short)11));
HSSFCell oneRowCell4=oneRow.createCell(3);
oneRowCell4.setCellValue("储能充电量");
oneRowCell4.setCellStyle(createCellStyle(wb,(short)11));
HSSFCell oneRowCell5=oneRow.createCell(4);
oneRowCell5.setCellValue("储能放电量");
oneRowCell5.setCellStyle(createCellStyle(wb,(short)11));
//循环数据
for (int i =0; i < list.size() -1; i++) {
HSSFRow listRow = sheet.createRow(2+i);
//第一列
HSSFCell listCell1 = listRow.createCell(0);
listCell1.setCellValue((String) list.get(i).get("time"));
listCell1.setCellStyle(createCellStyle(wb,(short)11));
//第二列
HSSFCell listCell2 = listRow.createCell(1);
listCell2.setCellValue((Double) list.get(i).get("yongdianliang"));
listCell2.setCellStyle(createCellStyle(wb,(short)11));
//第三列
HSSFCell listCell3 = listRow.createCell(2);
listCell3.setCellValue((Double) list.get(i).get("fadianliang"));
listCell3.setCellStyle(createCellStyle(wb,(short)11));
//第四列
HSSFCell listCell4 = listRow.createCell(3);
listCell4.setCellValue((Double) list.get(i).get("chongdianliang"));
listCell4.setCellStyle(createCellStyle(wb,(short)11));
//第五列
HSSFCell listCell5 = listRow.createCell(4);
listCell5.setCellValue((Double) list.get(i).get("fangdianliang"));
listCell5.setCellStyle(createCellStyle(wb,(short)11));
}
HSSFRow lastrow = sheet.createRow(2+list.size()-1);
HSSFCell lastCell1 = lastrow.createCell(0);
lastCell1.setCellValue((String) list.get(list.size()-1).get("name"));
lastCell1.setCellStyle(createCellStyle(wb,(short)11));
HSSFCell lastCell2 = lastrow.createCell(1);
lastCell2.setCellValue((Double) list.get(list.size()-1).get("sum1"));
lastCell2.setCellStyle(createCellStyle(wb,(short)11));
HSSFCell lastCell3 = lastrow.createCell(2);
lastCell3.setCellValue((Double) list.get(list.size()-1).get("sum2"));
lastCell3.setCellStyle(createCellStyle(wb,(short)11));
HSSFCell lastCell4 = lastrow.createCell(3);
lastCell4.setCellValue((Double) list.get(list.size()-1).get("sum3"));
lastCell4.setCellStyle(createCellStyle(wb,(short)11));
HSSFCell lastCell5 = lastrow.createCell(4);
lastCell5.setCellValue((Double) list.get(list.size()-1).get("sum4"));
lastCell5.setCellStyle(createCellStyle(wb,(short)11));
OutputStream out = response.getOutputStream();
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition",
"attachment;filename=" +new String(titleName.getBytes("utf-8"),"ISO-8859-1" )+".xls");
//文档输出
//FileOutputStream qwe = new FileOutputStream("D:\\" +"租户账期欠费表.xls");
try {
wb.write(out);
}catch (Exception e) {
e.printStackTrace();
throw new Exception("导出表单失败！！！");
}finally {
out.close();
}
}catch (Exception e){
e.printStackTrace();
throw new Exception("导出表单失败！");
}
}
/**
* 创建单元格样式
*
* @param workbook 工作簿
* @param fontSize 字体大小
* @return 单元格样式
*/
private static HSSFCellStylecreateCellStyle(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints(fontSize);
// 创建字体
font.setFontName("宋体");
// 加载字体
style.setFont(font);
return style;
}
/**
* 读取出filePath中的所有数据信息
* @param filePath excel文件的绝对路径
*
*/
public static void getDataFromExcel(String filePath)
{
//String filePath = "E:\\123.xlsx";
//判断是否为excel类型文件
if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx"))
{
System.out.println("文件不是excel类型");
}
FileInputStream fis =null;
Workbook wookbook =null;
try
{
//获取一个绝对地址的流
fis =new FileInputStream(filePath);
}
catch(Exception e)
{
e.printStackTrace();
}
try
{
//2003版本的excel，用.xls结尾
wookbook =new HSSFWorkbook(fis);//得到工作簿
}
catch (Exception ex)
{
//ex.printStackTrace();
try
{
//2007版本的excel，用.xlsx结尾
wookbook =new XSSFWorkbook(fis);//得到工作簿
}catch (IOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//得到一个工作表
Sheet sheet = wookbook.getSheetAt(0);
//获得表头
Row rowHead = sheet.getRow(0);
//判断表头是否正确
if(rowHead.getPhysicalNumberOfCells() !=3)
{
System.out.println("表头的数量不对!");
}
//获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
//要获得属性
String name ="";
// int latitude = 0;
String latitude ="";
//获得所有数据
for(int i =1 ; i <= totalRowNum; i++)
{
//获得第i行对象
Row row = sheet.getRow(i);
//获得获得第i行第0列的 String类型对象
Cell cell = row.getCell((short)0);
name = cell.getStringCellValue().toString();
cell = row.getCell((short)1);
latitude = cell.getStringCellValue().toString();
//获得一个数字类型的数据
// cell = row.getCell((short)1);
// latitude = (int) cell.getNumericCellValue();
System.out.println("名字："+name+",经纬度："+latitude);
}
}
