poi多sheet练习

package com.pio.sheet;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.HSSFRichTextString;
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.hssf.util.HSSFColor;
import com.pio.sheet.WlLocationAllocPojo;


public class CopyOfPoiTest {

    @SuppressWarnings("unchecked")
    public static void main(String[] args) {
        try {
            OutputStream out = new FileOutputStream("D:\\test.xls");
            List<List<String>> data = new ArrayList<List<String>>();
            for (int i = 1; i < 15; i++) {
                List rowData = new ArrayList();
                rowData.add(String.valueOf(i));
                rowData.add("东霖柏鸿"+i);
                data.add(rowData);
            }
            List<WlLocationAllocPojo> t_list =new ArrayList<WlLocationAllocPojo>();
            for (int i = 0; i < 2800; i++) {
                    WlLocationAllocPojo wlLocationAllocPojo=new WlLocationAllocPojo();
                    wlLocationAllocPojo.setAvailStatus(i+":"+i);
                    wlLocationAllocPojo.setApMac("2");
                    wlLocationAllocPojo.setApName("3");
                    wlLocationAllocPojo.setApId("4");
                    wlLocationAllocPojo.setInstName("5");
                    wlLocationAllocPojo.setAcId("6");
                    wlLocationAllocPojo.setLocationName("7");
                    wlLocationAllocPojo.setInsertTime("8");
                    t_list.add(wlLocationAllocPojo);
            }
            Map<String, String> wlLocationAllocPojoMap=new HashMap<String, String>();
            wlLocationAllocPojoMap.put("00", "wlLocationAllocPojo.getAvailStatus()");
            wlLocationAllocPojoMap.put("11", "wlLocationAllocPojo.getApMac()");
            wlLocationAllocPojoMap.put("22", "wlLocationAllocPojo.getApName()");
            wlLocationAllocPojoMap.put("33", "wlLocationAllocPojo.getApId()");
            wlLocationAllocPojoMap.put("44", "wlLocationAllocPojo.getInstName()");
            wlLocationAllocPojoMap.put("55", "wlLocationAllocPojo.getAcId()");
            wlLocationAllocPojoMap.put("66", "wlLocationAllocPojo.getLocationName()");
            wlLocationAllocPojoMap.put("77", "wlLocationAllocPojo.getInsertTime()");
            
            System.out.println(t_list.size());
            
            int readIndex=0;
            int pageSize=1000;
              String[] headers = { "数量", "可用状态", "AP MAC" , "AP名称", 
                        "APID", "AC 名称", "AC ID", "位置名称" , "入库时间"};
                HSSFWorkbook workbook = new HSSFWorkbook();
                int page =(int) Math.ceil((float)t_list.size()/pageSize);
                for (int i = 0; i < page; i++) {

                    // 生成一个表格
                    HSSFSheet sheet = workbook.createSheet();
                    int pageCount=i+1;
                    workbook.setSheetName(i, "AP分配位置SHEET"+pageCount);
                    // 设置表格默认列宽度为20个字节
                    sheet.setDefaultColumnWidth(20);
                    // 生成一个样式
                    HSSFCellStyle style = workbook.createCellStyle();
                    // 设置这些样式
                    style.setFillForegroundColor(HSSFColor.PALE_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.BLACK.index);
                    font.setFontHeightInPoints((short) 12);
                    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                    // 把字体应用到当前的样式
                    style.setFont(font);

                    // 指定当单元格内容显示不下时自动换行
                    style.setWrapText(true);

                    // 产生表格标题行
                    HSSFRow row = sheet.createRow(0);
                    for (int j = 0; j < headers.length; j++) {
                        HSSFCell cell = row.createCell( j);
                    
                        cell.setCellStyle(style);
                        HSSFRichTextString text = new HSSFRichTextString(headers[j]);
                        cell.setCellValue(text.toString());
                    }
                    // 遍历集合数据，产生数据行
                    if (t_list != null) {
                        for ( int n =i*pageSize; n<( i+1)*pageSize; n++) {
                            row = sheet.createRow((1+readIndex++)-i*pageSize);
                            if (readIndex>t_list.size()) {
                                break;
                            }
                            //int cellIndex = 0;
                            for (int w = 0; w < 9; w++) {
                                WlLocationAllocPojo wlLocationAllocPojo=t_list.get(readIndex-1);
                                HSSFCell cell = row.createCell(w);
                                if (w ==0)  cell.setCellValue(readIndex) ;
                                if (w ==1)  cell.setCellValue(wlLocationAllocPojo.getAvailStatus()) ;
                                if (w ==2)  cell.setCellValue(wlLocationAllocPojo.getApMac());
                                if (w ==3)  cell.setCellValue(wlLocationAllocPojo.getApName());
                                if (w ==4)  cell.setCellValue(wlLocationAllocPojo.getApId());
                                if (w ==5)  cell.setCellValue(wlLocationAllocPojo.getInstName());
                                if (w ==6)  cell.setCellValue(wlLocationAllocPojo.getAcId());
                                if (w ==7)  cell.setCellValue(wlLocationAllocPojo.getLocationName());
                                if (w ==8)  cell.setCellValue(wlLocationAllocPojo.getInsertTime());
                            }
                           // cellIndex++;
                    /*        for (WlLocationAllocPojo wlLocationAllocPojo  t_list.get(readIndex-1)) {
                                HSSFCell cell = row.createCell(cellIndex);
                                //cell.setCellValue(str.toString());
                                cellIndex++;
                            }*/
//                            for (WlLocationAllocPojo wlLocationAllocPojo : t_list) {
//                                for (int w = 1; w < 9; w++) {
//                                    HSSFCell cell = row.createCell(w-1);
//                                    cell.setCellValue(wlLocationAllocPojoMap.get(w));
//                                }
//                            }
                        }
                
             /*           if (readIndex%4==0) {
                            continue;
                        }*/
                    }
                }
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

package com.pio.sheet;


/**
 * <br>
 *  
 * <p>

 * <p>
 * </p>
 * <br>

 * <p>
 *<br>
 * <strong>Modify History:</strong><br>
 * user     modify_date    modify_content<br>
 * -------------------------------------------<br>
 * <br>
 */
public class WlLocationAllocPojo   {

    
    /**
     * <code>serialVersionUID</code> - {description}.
     */
    private static final long serialVersionUID = 1703077541035066178L;

    /**
     * <code>m_mac</code> - {description} .
     */
    private String m_mac;

    /**
     * <code>m_locationName</code> - {description} .
     */
    private String m_locationName;
    
    /**
     * <code>m_locationId</code> - {description} .
     */
    private String m_locationId;
    
    /**
     * <code>m_apId</code> - {description} .
     */
    private String m_apId;
    
    /**
     * <code>m_apMac</code> - {description} .
     */
    private String m_apMac;
    
    /**
     * <code>m_availStatus</code> -
     */
    private String m_availStatus;
    
    /**
     * <code>m_acId</code> -
     */
    private String m_acId;
    
    /**
     * <code>m_apName</code> -
     */
    private String m_apName;
    
    /**
     * <code>m_instName</code> -
     */
    private String m_instName;
    
    /**
     * <code>m_insertTime</code> - 
     */
    private String m_insertTime;
    
    /**
     * @return m_mac
     */
    public String getMac() {
        return m_mac;
    }

    /**
     * @param mac mac
     */
    public void setMac(final String mac) {
        this.m_mac = mac;
    }

    /**
     * @return m_locationName
     */
    public String getLocationName() {
        return m_locationName;
    }

    /**
     * @param locationName locationName
     */
    public void setLocationName(final String locationName) {
        this.m_locationName = locationName;
    }
    
    /**
     * @return m_locationId
     */
    public String getLocationId() {
        return m_locationId;
    }

    /**
     * @param locationId locationId
     */
    public void setLocationId(final String locationId) {
        this.m_locationId = locationId;
    }
    
    /**
     * @return m_apId
     */
    public String getApId() {
        return m_apId;
    }

    /**
     * @param apId apId
     */
    public void setApId(final String apId) {
        this.m_apId = apId;
    }
    
    /**
     * @return m_apMac
     */
    public String getApMac() {
        return m_apMac;
    }

    /**
     * @param apMac apMac
     */
    public void setApMac(final String apMac) {
        this.m_apMac = apMac;
    }
    
    
    /**
     * {method description}.
     * @return m_availStatus
     */
    public String getAvailStatus() {
        return m_availStatus;
    }


    /**
     * {method description}.
     * @param availStatus availStatus
     */
    public void setAvailStatus(final String availStatus) {
        this.m_availStatus = availStatus;
    }
    
    /**
     * {method description}.
     * @return m_acId
     */
    public String getAcId() {
        return m_acId;
    }


    /**
     * {method description}.
     * @param acId acId
     */
    public void setAcId(final String acId) {
        this.m_acId = acId;
    }
    
    /**
     * {method description}.
     * @return m_apName
     */
    public String getApName() {
        return m_apName;
    }


    /**
     * {method description}.
     * @param apName apName
     */
    public void setApName(final String apName) {
        this.m_apName = apName;
    }
    
    /**
     * {method description}.
     * @return m_instName
     */
    public String getInstName() {
        return m_instName;
    }


    /**
     * {method description}.
     * @param instName instName
     */
    public void setInstName(final String instName) {
        this.m_instName = instName;
    }
    
    
    /**
     * {method description}.
     * @return m_insertTime
     */
    public String getInsertTime() {
        return m_insertTime;
    }


    /**
     * {method description}.
     * @param insertTime insertTime
     */
    public void setInsertTime(final String insertTime) {
        m_insertTime = insertTime;
    }
    
    




}

 

转载于:https://www.cnblogs.com/xdrlczj/p/7723178.html