Java poi导出excel

最新推荐文章于 2024-01-09 17:30:11 发布

KingFu28

最新推荐文章于 2024-01-09 17:30:11 发布

阅读量146

点赞数

文章标签： excel

本文链接：https://blog.csdn.net/kingwin28/article/details/129120113

版权

这段代码展示了如何使用Java的ApachePOI库创建Excel文件并导出订单信息。程序首先创建一个工作簿，定义样式和字体，然后填充标题和数据，最后将内容写入到指定路径的Excel文件中。此外，还包括了Base64编码图片的解码和插入到Excel，以及使用POST方式下载Excel的前端实现。

摘要由CSDN通过智能技术生成

public void findOrdersByDay(String date){

String name = "预约信息";
String fileName = name+"_"+date+".xls";

/*String path = ServletActionContext.getServletContext().getRealPath("/")
+ "templates/"+fileName;*/
String path = "C:\\order_Excels\\" + fileName;
File file =new File("C:\\order_Excels");    
//如果文件夹不存在则创建    
if  (!file .exists()  && !file .isDirectory())      
{       
   System.out.println("//目录不存在将创建!");  
   file .mkdir();    
} 
// 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet(date);  
        // 设置表格默认列宽度为15个字节  
        sheet.setDefaultColumnWidth((short) 15);  
        
      //设置某列宽度
        sheet.setColumnWidth(0, 1500);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(6, 9000);
        sheet.setColumnWidth(9, 15000);
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle();  
        // 设置这些样式  
//        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        // 生成一个字体
        HSSFFont font = workbook.createFont();  
//        font.setColor(HSSFColor.VIOLET.index);  
        font.setFontHeightInPoints((short) 12);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        // 把字体应用到当前的样式  
        style.setFont(font);          
  
        // 声明一个画图的顶级管理器  
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();  
        // 定义注释的大小和位置,详见文档  
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,  
                0, 0, 0, (short) 4, 2, (short) 6, 5));  
        // 设置注释内容  
//        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));  
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.  
        comment.setAuthor("KingFu");  
        
        // 产生表格标题行  
        HSSFRow row1 = sheet.createRow(0);  
        //10列
        String[] headers =  
        { "序号","预约号", "预约方式", "姓名","预约日期","预约时间段", "身份证号", "登记字", "房产登记号", "房产证地址" };  
        for (short i = 0; i < headers.length; i++)  
        {  
            HSSFCell cell = row1.createCell(i);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text);  
        }
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        
        String hql = "from Order o ";
        String condition = " where boookingDate = CONVERT(varchar(10), GETDATE()-1, 23) ";
        
        List<Order> list = this.getHibernateTemplate().find(hql + condition);
        int startIndex = 1;//从第二行开始
        for(int i=0;i<list.size();i++){
//        System.out.println(o.getOrderID());
        HSSFRow row = sheet.createRow((short) startIndex+i);//创建行
       
        HSSFCell cell = row.createCell(0);//创建列
cell.setCellStyle(style);
        cell.setCellValue(i+1);//序号

        cell = row.createCell(1);//预约号
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getOrderID()==null?"":list.get(i).getOrderID());
       

        cell = row.createCell(2);//预约方式
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getBookingMode()==null?"":list.get(i).getBookingMode());

        cell = row.createCell(3);//姓名
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getUsername()==null?"":list.get(i).getUsername());

        cell = row.createCell(4);//预约日期
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getBoookingDate()==null?"":sdf.format(list.get(i).getBoookingDate()));
       
        cell = row.createCell(5);//预约时间段
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getTimeBw()==null?"":list.get(i).getTimeBw());

        cell = row.createCell(6);//身份证号
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getCardNo()==null?"":list.get(i).getCardNo());

        cell = row.createCell(7);//登记字
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getHouseWord()==null?"":list.get(i).getHouseWord());

        cell = row.createCell(8);//房产登记号
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getHouseNO()==null?"":list.get(i).getHouseNO());

        cell = row.createCell(9);//房产证地址
        cell.setCellStyle(style);
        cell.setCellValue(list.get(i).getAddress()==null?"":list.get(i).getAddress());
       
        }
        try {
       FileOutputStream out = new FileOutputStream(path);
   workbook.write(out);
       out.close();
       System.out.println("导出成功!");
        } catch (FileNotFoundException e) {
        System.out.println("导出失败!");
//            JOptionPane.showMessageDialog(null, "导出失败!");
            e.printStackTrace();
        } catch (IOException e) {
        System.out.println("导出失败!");
            e.printStackTrace();
        }

}

-------base64位图片导出excel------

FileOutputStream fileOut = null;
try { 
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();     
//base64字符串转byte
            byte[] imageBytes = Base64.decodeBase64(imgData.replace("data:image/png;base64,", ""));
       ByteArrayInputStream bais = new ByteArrayInputStream(imageBytes);      
       BufferedImage bufferImg =ImageIO.read(bais);
       ImageIO.write(bufferImg, "png", byteArrayOut); 
       
       HSSFWorkbook wb = new HSSFWorkbook();     
            HSSFSheet sheet1 = wb.createSheet("sheet1"); //sheet1
            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）  
            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();     
            //anchor主要用于设置图片的属性  ，占18列，29行
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255,(short) 1, 1, (short) 18, 29); 
            anchor.setAnchorType(3);     
            //插入图片    
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));   
       
            HSSFSheet sheet2 = wb.createSheet("data"); //sheet2
            fileOut = new FileOutputStream("D:/textExcel.xlsx");     
       // 写入excel文件     
       wb.write(fileOut);
} catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            if(fileOut != null){  
                 try {  
                    fileOut.close();  
                } catch (IOException e) {  
                    e.printStackTrace();  
                }
            }  
        }

-------post方式下载excel------
前台页面：

做一个隐藏的form
<div id="exportdiv"  class="hide">
	<form id="submitForm" class="" method="post" action="">
		<input id="type" name="type" value=""/>
		<input id="imgData" name="imgData" value=""/>
	</form>
</div>

js部份: 
$("#submitForm")[0].reset();
var path = "<%= request.getContextPath() %>/finance/export";
$("#submitForm").attr("action", path);
$('#submitForm #type').val(type);
$('#submitForm #imgData').val(dataUrl);
$("#submitForm").submit();