导出（Excel格式）

poi导出，需要的poi jar包：

步骤一、两个工具类：

1、ExcelUtil.java

package util;

import java.awt.Color;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;

public class ExcelUtil {

     public static void creatExcel(Object[][] datas,String table) throws FileNotFoundException, IOException{
          HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("table");  //创建table工作薄
            //Object[][] datas = {{"区域产品销售额","",""},{"区域", "总销售额(万元)", "总利润(万元)简单的表格"}, {"江苏省" , 9045,  2256}, {"广东省", 3000, 690}};
            HSSFRow row;
            HSSFCell cell;
            
            short colorIndex = 10;
            HSSFPalette palette = wb.getCustomPalette();
            Color rgb = Color.GREEN;
            short bgIndex = colorIndex ++; 
            palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
            short bdIndex = colorIndex ++;
            rgb = Color.BLACK;
            palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
            
            for(int i = 0; i < datas.length; i++) {
                row = sheet.createRow(i);//创建表格行
                for(int j = 0; j < datas[i].length; j++) {
                    cell = row.createCell(j);//根据表格行创建单元格
                    cell.setCellValue(String.valueOf(datas[i][j]));
                    
                    HSSFCellStyle cellStyle = wb.createCellStyle();
                    if(i == 0 || i == 1) {            //设置第一，二行背景颜色
                          cellStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
                          cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    }
                  
                    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
                    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
                    //bdIndex 边框颜色下标值
                    cellStyle.setBottomBorderColor(bdIndex);
                    cellStyle.setLeftBorderColor(bdIndex);
                    cellStyle.setRightBorderColor(bdIndex);
                    cellStyle.setTopBorderColor(bdIndex);
                    
                    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                    
                    if(i == datas.length - 1 && j == datas[0].length - 1) {        //设置最后一行，最后一列的字体，即表格中最后一个字
                        //单元格文本可设置字体大小、颜色、斜体、粗体、下划线等。
                        HSSFFont font = wb.createFont();
                        font.setItalic(true);
                        font.setUnderline(HSSFFont.U_SINGLE);
                        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                        font.setFontHeightInPoints((short)14);
                        cellStyle.setFont(font);
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
            
            //加入图片
           /* byte[] bt = FileUtils.readFileToByteArray(new File("E:/yes.png"));
            int pictureIdx = wb.addPicture(bt,HSSFWorkbook.PICTURE_TYPE_PNG);
            CreationHelper helper = wb.getCreationHelper();
            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = helper.createClientAnchor();
            //anchor.setDx1(MSExcelUtil.pixel2WidthUnits(60));
            //anchor.setDy1(MSExcelUtil.pixel2WidthUnits(60));
            anchor.setCol1(0);
            anchor.setRow1(4);
            anchor.setCol2(3);
            anchor.setRow2(25);
            drawing.createPicture(anchor, pictureIdx);*/
            
           //合并单元格
            /*CellRangeAddress region = new CellRangeAddress(0, // first row
                    0, // last row
                    0, // first column
                    2 // last column
            );
            sheet.addMergedRegion(region);*/
            
            //创建表格之后设置行高与列宽
            for(int i = 0; i < datas.length; i++) {
                row = sheet.getRow(i);
                row.setHeightInPoints(30);
            }
            for(int j = 0; j < datas[0].length; j++) {
                sheet.setColumnWidth(j, MSExcelUtil.pixel2WidthUnits(160));
            }
            wb.write(new FileOutputStream(table));
            
      }
    
}

View Code

 

2、MSExcel.java

package util;
public class MSExcelUtil {

    public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;
    public static final int UNIT_OFFSET_LENGTH = 7;
    public static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146, 182, 219 };

    /**
     * pixel units to excel width units(units of 1/256th of a character width)
     * 
     * @param pxs
     * @return
     */
    public static short pixel2WidthUnits(int pxs) {
        short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH));
        widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
        return widthUnits;
    }

    /**
     * excel width units(units of 1/256th of a character width) to pixel units
     * 
     * @param widthUnits
     * @return
     */
    public static int widthUnits2Pixel(int widthUnits) {
        int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR) * UNIT_OFFSET_LENGTH;
        int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;
        pixels += Math.round(offsetWidthUnits
                / ((float) EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH));

        return pixels;
    }
}

View Code

 

步骤二、action中调用工具类代码：

public void daochu() throws FileNotFoundException, IOException {
        User user = (User) ActionContext.getContext().getSession().get("user");
        List<Attendance> list = new ArrayList<Attendance>();
        if (user != null) {
        Server server = serverService.getEntity(user.getServer().getId());
        String[] str = order.split(",");
        for (int i = 0; i < str.length; i++) {
            List<Department> dapartmentList = departmentService
                    .find(str[i],server.getName());
            for (int j = 0; j < dapartmentList.size(); j++) {
                List<Attendance> list0 = attendanceService.findByDT(dapartmentList.get(j).getId(), date1, date2);
                if(list0.size() > 0){
                    for(Attendance a : list0){
                        list.add(a);
                    }
                 }
              }
           }
        }
        HttpServletResponse response = ServletActionContext.getResponse();
        response.setContentType("text/html;charset=utf-8");
        JSONObject json = new JSONObject();
        //List<Attendance> list = attendanceService.getScrollData().getResultlist();
        Object[][] datas = new Object[list.size()+1][4];        //第一个'3'：数据量(行)     第二个'3'：字段个数(列)
        datas[0][0] = "部门";
        datas[0][1] = "职务";
        datas[0][2] = "姓名";
        datas[0][3] = "异常显示";
        for(int i=0;i<list.size();i++){
            for(int j=0;j<4;j++){
                datas[i+1][0] = list.get(i).getDepartment().getName();
                datas[i+1][1] = list.get(i).getUser().getPost().getName();
                datas[i+1][2] = list.get(i).getUser().getUsername();
                datas[i+1][3] = list.get(i).getState();
            }
        }
        String table = "e:/考勤.xls";
        ExcelUtil.creatExcel(datas, table);
        json.put("path", table);
        response.getWriter().print(json.toString());
}

View Code

 

步骤三、前台代码：

jQuery("#search_button2").click(function() {
          var obj = document.getElementsByName("Department_choice");
        var s1 = '';
        for (var i = 0; i < obj.length; i++) {
            if (obj[i].checked)
                s1 += obj[i].value + ',';
        }
        var year = document.getElementById("year").value;
        var month = document.getElementById("month").value;
        var day = document.getElementById("day").value;
        var s2 = year + '-' + month + '-' + day;
        var year1 = document.getElementById("year1").value;
        var month1 = document.getElementById("month1").value;
        var day1 = document.getElementById("day1").value;
        var s3 = year1 + '-' + month1 + '-' + day1;
          jQuery.post("AttendanceAction_daochu.do",{
              order:s1,
            date1:s2,
            date2:s3,
          }, function(data, status) {
                alert("导出成功，请查看"+data.path);
            },"json");
          
      });

View Code

 

转载于:https://www.cnblogs.com/Crysta1/p/6187699.html