java excel 导入

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.Region;

public class ExcelApp {

 /**
  * @param args
  */
 public static void main(String[] args) throws IOException {

  try {
   HSSFWorkbook wb = new HSSFWorkbook();
   HSSFSheet sheet = wb.createSheet("new   sheet");
   HSSFCellStyle style = wb.createCellStyle(); // 样式对象

   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
   HSSFRow row = sheet.createRow((short) 0);
   HSSFRow row2 = sheet.createRow((short) 1);

   sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 0));
   HSSFCell ce = row.createCell((short) 0);
   ce.setEncoding(HSSFCell.ENCODING_UTF_16);// 中文处理
   ce.setCellValue("项目\\日期"); // 表格的第一行第一列显示的数据
   ce.setCellStyle(style); // 样式，居中
   int num = 0;
   for (int i = 0; i < 9; i++) { // 循环9次，每一次都要跨单元格显示
    // 计算从那个单元格跨到那一格
    int celln = 0;
    int celle = 0;
    if (i == 0) {
     celln = 0;
     celle = 1;
    } else {
     celln = (i * 2);
     celle = (i * 2 + 1);
    }
    // 单元格合并
    // 四个参数分别是：起始行，起始列，结束行，结束列
    sheet.addMergedRegion(new Region(0, (short) (celln + 1), 0,
      (short) (celle + 1)));
    HSSFCell cell = row.createCell((short) (celln + 1));
    cell.setCellValue("merging" + i); // 跨单元格显示的数据
    cell.setCellStyle(style); // 样式
    // 不跨单元格显示的数据，如：分两行，上一行分别两格为一格，下一行就为两格，“数量”，“金额”
    HSSFCell cell1 = row2.createCell((short) celle);
    HSSFCell cell2 = row2.createCell((short) (celle + 1));
    cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell1.setCellValue("数量");
    cell1.setCellStyle(style);
    cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell2.setCellValue("金额");
    cell2.setCellStyle(style);
    num++;
   }

   // 在后面加上合计百分比

   // 合计 在最后加上，还要跨一个单元格
   sheet.addMergedRegion(new Region(0, (short) (2 * num + 1), 0,
     (short) (2 * num + 2)));
   HSSFCell cell = row.createCell((short) (2 * num + 1));
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue("合计");
   cell.setCellStyle(style);
   HSSFCell cell1 = row2.createCell((short) (2 * num + 1));
   HSSFCell cell2 = row2.createCell((short) (2 * num + 2));
   cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell1.setCellValue("数量");
   cell1.setCellStyle(style);
   cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell2.setCellValue("金额");
   cell2.setCellStyle(style);

   // 百分比 同上
   sheet.addMergedRegion(new Region(0, (short) (2 * num + 3), 0,
     (short) (2 * num + 4)));
   HSSFCell cellb = row.createCell((short) (2 * num + 3));
   cellb.setEncoding(HSSFCell.ENCODING_UTF_16);
   cellb.setCellValue("百分比");
   cellb.setCellStyle(style);
   HSSFCell cellb1 = row2.createCell((short) (2 * num + 3));
   HSSFCell cellb2 = row2.createCell((short) (2 * num + 4));
   cellb1.setEncoding(HSSFCell.ENCODING_UTF_16);
   cellb1.setCellValue("数量");
   cellb1.setCellStyle(style);
   cellb2.setEncoding(HSSFCell.ENCODING_UTF_16);
   cellb2.setCellValue("金额");
   cellb2.setCellStyle(style);

   FileOutputStream fileOut = new FileOutputStream("workbook.xls");
   wb.write(fileOut);
   fileOut.close();
   System.out.print("OK");
  } catch (Exception ex) {
   ex.printStackTrace();
  }

  test();

 }

 /**
  * 导出excel
  *
  * @param aServiceAccountList
  */

 static void test() {
  ArrayList listResult = new ArrayList();
  /** test--- */
  SServiceAccount sc = new SServiceAccount();
  sc.set_planId(931195);
  sc.set_orgId(10);
  sc.set_total(13);
  listResult.add(sc);
  sc = new SServiceAccount();
  sc.set_planId(931193);
  sc.set_orgId(3);
  sc.set_total(14);
  listResult.add(sc);
  //
  sc = new SServiceAccount();
  sc.set_planId(931193);
  sc.set_orgId(11);
  sc.set_total(15);
  listResult.add(sc);
  sc.set_planId(931193);
  sc.set_orgId(2);
  sc.set_total(16);
  listResult.add(sc);
  //
  sc = new SServiceAccount();
  sc.set_planId(931193);
  sc.set_orgId(1);
  sc.set_total(17);
  listResult.add(sc);
  //
  sc = new SServiceAccount();
  sc.set_planId(931193);
  sc.set_orgId(10);
  sc.set_total(12);
  listResult.add(sc);

  sc = new SServiceAccount();
  sc.set_planId(9311934);
  sc.set_orgId(10);
  sc.set_total(19);
  listResult.add(sc);
  exportExcel(listResult);
 }

 static void exportExcel(ArrayList sServiceAccountList) {

  try {
   HSSFWorkbook wb = new HSSFWorkbook();

   HSSFSheet sheet = wb.createSheet("product export");
   sheet.setDefaultColumnWidth((short)45);
   HSSFCellStyle style = wb.createCellStyle(); // 样式对象
   
   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
   HSSFRow row = sheet.createRow(0);
   
   HSSFCell orgCell = row.createCell((short) 0);
   orgCell.setEncoding(HSSFCell.ENCODING_UTF_16);
   orgCell.setCellValue("局向编号");
   orgCell.setCellStyle(style);
   

   HSSFCell orgName = row.createCell((short) 1);
   orgName.setEncoding(HSSFCell.ENCODING_UTF_16);
   orgName.setCellValue("局向名称");
   orgName.setCellStyle(style);

   HSSFCell productName = row.createCell((short) 2);
   productName.setEncoding(HSSFCell.ENCODING_UTF_16);
   productName.setCellValue("产品名称");
   productName.setCellStyle(style);

   HSSFCell productCountse = row.createCell((short) 3);
   productCountse.setEncoding(HSSFCell.ENCODING_UTF_16);
   productCountse.setCellValue("产品数目");
   productCountse.setCellStyle(style);

   // export excel

   // 第一步 获取 局向 的的数目
   int size = sServiceAccountList.size();
   ArrayList tmpList = new ArrayList();
   ArrayList orgList = new ArrayList();
   for (int i = 0; i < size; i++) {
    SServiceAccount sc = (SServiceAccount) sServiceAccountList
      .get(i);
    String orgidStr = sc.get_orgId() + "";
    if (!tmpList.contains(orgidStr)) {
     tmpList.add(orgidStr);
     orgList.add(sc);
    }
   }

   // 第二 通过局向id获取对应产品的数目
   int orgSize = orgList.size();
   ArrayList excelArrayList = new ArrayList();
   for (int i = 0; i < orgSize; i++) {
    ProductExcelInfo info = new ProductExcelInfo();

    SServiceAccount sc = (SServiceAccount) orgList.get(i);
    info.setOrgId(sc.get_orgId());
    // 第三部 获取 orgid的 所有的产品信息
    ArrayList productList = new ArrayList();
    int counts = 0;
    for (int j = 0; j < size; j++) {
     SServiceAccount tmpSc = (SServiceAccount) sServiceAccountList
       .get(j);
     if (sc.get_orgId() == tmpSc.get_orgId()) {
      productList.add(tmpSc);
      counts += tmpSc.get_total();
     }
    }
    info.setProductList(productList);
    info.setTotalCounts(counts);
    excelArrayList.add(info);
   }

   // 导出成excel

   int excelCount = excelArrayList.size();
   System.out.println("excelCount=" + excelCount);
   int rowNumber = 1;
   for (int i = 0; i < excelCount; i++) {

    ProductExcelInfo excelProduct = (ProductExcelInfo) excelArrayList.get(i);
    int totalRows = excelProduct.getProductList().size() ;
    for (int j = 0; j < totalRows; j++) {
     SServiceAccount sc = (SServiceAccount) excelProduct.getProductList().get(j);
     
    
     HSSFRow curRow = sheet.createRow((short) (rowNumber + j));
     //orgId
     if(j==0) {
      HSSFCell orgIdCell = curRow.createCell((short) 0); 
      sheet.addMergedRegion(new Region(rowNumber, (short) (  0), rowNumber+totalRows,(short) ( 0)));
      orgIdCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      orgIdCell.setCellValue(excelProduct.getOrgId() + "");
      orgIdCell.setCellStyle(style);
      
      //orgName
      HSSFCell orgNameCell = curRow.createCell((short) 1); 
      sheet.addMergedRegion(new Region(rowNumber, (short) (  1), rowNumber+totalRows,(short) ( 1)));
      orgNameCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      orgNameCell.setCellValue(excelProduct.getOrgId() + "");
      orgNameCell.setCellStyle(style);
     }
       //产品名称
     HSSFCell productNameCell = curRow.createCell((short) 2);                 
     productNameCell.setEncoding(HSSFCell.ENCODING_UTF_16);
     productNameCell.setCellValue(sc.get_planId() + "");
     productNameCell.setCellStyle(style);
                     //产品数目
     HSSFCell productCountCell = curRow.createCell((short) 3);
     productCountCell.setEncoding(HSSFCell.ENCODING_UTF_16);
     productCountCell.setCellValue(sc.get_total() + "");
     productCountCell.setCellStyle(style);
    }
    //统计数目的 行
    HSSFRow statRow = sheet.createRow((short) (rowNumber + totalRows));
    
    HSSFCell totalCell = statRow.createCell((short) 2);

    totalCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    totalCell.setCellValue(  "合计");
    totalCell.setCellStyle(style);

    HSSFCell numCountCell = statRow.createCell((short) 3);
    numCountCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    numCountCell.setCellValue(excelProduct.getTotalCounts());
    numCountCell.setCellStyle(style);
    
    rowNumber += totalRows + 1;

   }
   FileOutputStream fileOut = new FileOutputStream("export.xls");
   wb.write(fileOut);
   fileOut.close();
   System.out.print("OK");
  } catch (Exception e) {

   e.printStackTrace();
  }

 }
}