JAVA导入导出Excel【POI技术】

来源：互联网发布：隐藏电脑录像软件编辑：程序博客网时间：2024/06/05 12:02

导入

package poi;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class JavaOpExcel {
   public String getCellValue(XSSFCell cell){
       if(cell.getCellType()==XSSFCell.CELL_TYPE_STRING)
       return cell.getStringCellValue();

       if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC)
           return String.valueOf((int)cell.getNumericCellValue());
       if(cell.getCellType()==XSSFCell.CELL_TYPE_BLANK)
           return "";
       return "";
   }
   //@Test
   //"C:\\Users\\zjj/Desktop/workbook.xlsx"
   public ArrayList<Map<String,Object>> readExcel(String path) {
       try {
           XSSFWorkbook workbook = new XSSFWorkbook(path);
           XSSFCell cell;
           ArrayList<Map<String,Object>> aList=new ArrayList<Map<String,Object>>();
           //得到sheets的数量
           int num = workbook.getNumberOfSheets();
           System.out.println("sheets的数量是："+num);

           for (int i = 0; i < num; i++) {
               //实例化XSSFSheet
               XSSFSheet sheet = workbook.getSheetAt(i);
               //得到第一行和最后一行
               int firstRowNum = sheet.getFirstRowNum();
               int lastRowNum = sheet.getLastRowNum();
               System.out.println("第一行："+firstRowNum+1);
               System.out.println("最后一行："+lastRowNum+1);
               //遍历行数
               for (int j = firstRowNum+1; j <= lastRowNum; j++) {
                   Map<String,Object> map=new HashMap<String,Object>();
                   XSSFRow row = sheet.getRow(j);
                   //System.out.println("第"+row+"行");
                   //获取单元格
                   int firstCellNum = row.getFirstCellNum();
                   int lastCellNum = row.getLastCellNum();
                   System.out.println("第一个单元格："+firstCellNum);
                   System.out.println("最后一个单元格："+lastCellNum);
                   //遍历一行,遍历每个单元格
                   for (int k = firstCellNum; k < lastCellNum; k++) {
                       cell = row.getCell(k);
                       System.out.println("cell:"+cell);
                   String str=   getCellValue(cell);
                   System.out.println("单元格值是："+str);
                   map.put("cell"+k, str);
                  System.out.print(str+"\t");
               }
                   aList.add(map);
                   System.out.println();
               }
           }
           System.out.println(aList);
       return aList;
       } catch (IOException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
      return null;
   }

   public void writeExcel() {

   }

}

导出

package poi;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

public class JavaExportExcel {
   @Test
   public void writeExcel(String sheetName, String[] filedName, List<Map> list, String filePath) {
       try {
           // 创建一个文件输出流
           FileOutputStream fileOut = new FileOutputStream(filePath);
           Set set = list.get(0).keySet();
           Workbook workbook = null;
           // System.out.println(filePath.substring(filePath.lastIndexOf(".")));
           // 判断文件类型
           if (filePath.substring(filePath.lastIndexOf(".")).equals(".xls"))
               workbook = new HSSFWorkbook();
           else if (filePath.substring(filePath.lastIndexOf(".")).equals(".xlsx"))
               workbook = new XSSFWorkbook();
           else {
               System.out.println("文件类型不匹配");
               return;
           }
           //创建工作表单sheet
           Sheet sheet = workbook.createSheet(sheetName);
           //创建第一行（表头）
           Row row = sheet.createRow(0);
           //单元格（列）
           Cell cell;
           for (int i = 0; i < filedName.length; i++) {
               cell = row.createCell(i);
               cell.setCellValue(filedName[i]);
           }
           //创建行
           for (int i = 1; i <= list.size(); i++) {
               row = sheet.createRow(i);
               //迭代集合
               Iterator iterator = set.iterator();
               int j = 0;
               while (iterator.hasNext()) {
                   String key = (String) iterator.next();
                   cell = row.createCell(j++);
                   if (list.get(i - 1).get(key) != null)
                       cell.setCellValue(list.get(i - 1).get(key).toString());
                   else
                       cell.setCellValue("");

               }

           }
           workbook.write(fileOut);
           workbook.close();
       } catch (Exception e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
   }

}

阅读全文

0 0