package com.poi.excel2;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
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.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFRow; 
import org.apache.poi.hssf.usermodel.HSSFSheet; 
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
       
       
/** 
 * 
 * @author Hongten</br> 
 * 
 *         参考地址：http://hao0610.iteye.com/blog/1160678 
 * 
 */
public class XlsMain { 
       
    public static void main(String[] args) throws IOException { 
        XlsMain xlsMain = new XlsMain();
        List<String []> list=null;
        String filepath="C:/007.xlsx";
        try
    {
            //读取的excel07数据
            list = xlsMain.readXls(filepath);
    }
    catch (Exception e)
    {
      //读取的excel03数据
        System.out.println("改为03的执行！");
        list = xlsMain.readXls03(filepath.substring(0, filepath.length()-1));
    }
           
               
        try { 
            //
            XlsDto2Excel.xlsDto2Excel07(list);
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
//        for (int i = 0; i < list.size(); i++) { 
//          String result[]=list.get(i);
//            System.out.println(result[0] + "    " + result[1]  + "    "
//                    + result[2]  + "    " + result[3]  + "    "
//                    + result[4] ); 
//        } 
       
    } 
       
    /** 
     * 读取xls文件内容 
     * 
     * @return List<String[]>
     * @throws IOException 
     *             输入/输出(i/o)异常 
     */
    private List<String []> readXls(String filepath) throws IOException { 
        InputStream is = new FileInputStream(filepath);
            
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is); 
        //XlsDto xlsDto = null;
        String [] arr=null;
        List<String []> list = new ArrayList<String []>(); 
        // 循环工作表Sheet 
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { 
            XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                
            if (hssfSheet == null) { 
                continue; 
            } 
            // 循环行Row 
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 
                XSSFRow hssfRow = hssfSheet.getRow(rowNum); 
                if (hssfRow == null) { 
                    continue; 
                } 
                arr=new String[5];
                //xlsDto = new XlsDto(); 
                // 循环列Cell 
                // 0学号 1姓名 2学院 3课程名 4 成绩 
                // for (int cellNum = 0; cellNum <=4; cellNum++) { 
                XSSFCell xh = hssfRow.getCell(0); 
                if (xh == null) { 
                    continue; 
                }
                arr[0]=getValue(xh);
                //xlsDto.setXh(getValue(xh)); 
                XSSFCell xm = hssfRow.getCell(1); 
                if (xm == null) { 
                    continue; 
                } 
                arr[1]=getValue(xm);
                XSSFCell yxsmc = hssfRow.getCell(2); 
                if (yxsmc == null) { 
                    continue; 
                } 
                arr[2]=getValue(yxsmc);
                XSSFCell kcm = hssfRow.getCell(3); 
                if (kcm == null) { 
                    continue; 
                } 
                arr[3]=getValue(kcm);
                XSSFCell cj = hssfRow.getCell(4); 
                if (cj == null) { 
                    continue; 
                } 
                arr[4]=getValue(cj);
                list.add(arr); 
            } 
        } 
        return list; 
    } 
       
    /** 
     * 得到Excel表中的值 
     * 
     * @param xh 
     *            Excel中的每一个格子 
     * @return Excel中每一个格子中的值 
     */
    @SuppressWarnings("static-access") 
    private String getValue(XSSFCell xh) { 
        if (xh.getCellType() == xh.CELL_TYPE_BOOLEAN) { 
            // 返回布尔类型的值 
            return String.valueOf(xh.getBooleanCellValue()); 
        } else if (xh.getCellType() == xh.CELL_TYPE_NUMERIC) { 
            // 返回数值类型的值 
            return String.valueOf(xh.getNumericCellValue()); 
        } else { 
            // 返回字符串类型的值 
            return String.valueOf(xh.getStringCellValue()); 
        } 
    }
    private List<String []> readXls03(String filepath) throws IOException { 
        InputStream is = new FileInputStream(filepath); 
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); 
        //XlsDto xlsDto = null;
       String arr[]=null;
        List<String []> list = new ArrayList<String []>(); 
        // 循环工作表Sheet 
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { 
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); 
            if (hssfSheet == null) { 
                continue; 
            } 
            // 循环行Row 
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 
                HSSFRow hssfRow = hssfSheet.getRow(rowNum); 
                if (hssfRow == null) { 
                    continue; 
                }
                arr=new String[5];
                //xlsDto = new XlsDto(); 
                // 循环列Cell 
                // 0学号 1姓名 2学院 3课程名 4 成绩 
                // for (int cellNum = 0; cellNum <=4; cellNum++) { 
                HSSFCell xh = hssfRow.getCell(0); 
                if (xh == null) { 
                    continue; 
                } 
                //xlsDto.setXh(getValue(xh));
                arr[0]=getValue03(xh);
                HSSFCell xm = hssfRow.getCell(1); 
                if (xm == null) { 
                    continue; 
                } 
                //xlsDto.setXm(getValue(xm));
                arr[1]=getValue03(xm);
                HSSFCell yxsmc = hssfRow.getCell(2); 
                if (yxsmc == null) { 
                    continue; 
                } 
                //xlsDto.setYxsmc(getValue(yxsmc));
                arr[2]=getValue03(yxsmc);
                HSSFCell kcm = hssfRow.getCell(3); 
                if (kcm == null) { 
                    continue; 
                } 
               // xlsDto.setKcm(getValue(kcm));
                arr[3]=getValue03(kcm);
                HSSFCell cj = hssfRow.getCell(4); 
                if (cj == null) { 
                    continue; 
                } 
               // xlsDto.setCj(Float.parseFloat(getValue(cj)));
                arr[4]=getValue03(cj);
                list.add(arr);
                //list.add(xlsDto); 
            } 
        } 
        return list; 
    } 
    /** 
     * 得到Excel表中的值 
     * 
     * @param hssfCell 
     *            Excel中的每一个格子 
     * @return Excel中每一个格子中的值 
     */
    @SuppressWarnings("static-access") 
    private String getValue03(HSSFCell hssfCell) { 
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { 
            // 返回布尔类型的值 
            return String.valueOf(hssfCell.getBooleanCellValue()); 
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { 
            // 返回数值类型的值 
            return String.valueOf(hssfCell.getNumericCellValue()); 
        } else { 
            // 返回字符串类型的值 
            return String.valueOf(hssfCell.getStringCellValue()); 
        } 
    } 
       
}
package com.poi.excel2;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XlsDto2Excel
{
      
    /** 
     * 
     * @param xls 
     *            XlsDto实体类的一个对象 
     * @throws Exception 
     *             在导入Excel的过程中抛出异常 
     */
    public static void xlsDto2Excel07(List<String[]> xls) throws Exception
    {
    // 获取总列数 
    int CountColumnNum = xls.size();
    // 创建Excel文档 
    XSSFWorkbook hwb = new XSSFWorkbook();
    // sheet 对应一个工作页 
    XSSFSheet sheet = hwb.createSheet("pldrxkxxmb");
    XSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
    XSSFCell[] firstcell = new XSSFCell[CountColumnNum];
    String[] names = new String[CountColumnNum];
    names[0] = "学号";
    names[1] = "姓名";
    names[2] = "学院";
    names[3] = "课程名";
    names[4] = "成绩";
    for (int j = 0; j < CountColumnNum; j++)
    {
        firstcell[j] = firstrow.createCell(j);
        //firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
        firstcell[j].setCellValue(new XSSFRichTextString(names[j]));
          
    }
    for (int i = 0; i < xls.size(); i++)
    {
        // 创建一行 
        XSSFRow row = sheet.createRow(i + 1);
        // 得到要插入的每一条记录 
        String arr[] = xls.get(i);
        //xlsDto = xls.get(i); 
        for (int colu = 0; colu <= 4; colu++)
        {
        // 在一行内循环 
        XSSFCell xh = row.createCell(0);
        xh.setCellValue(arr[0]);
        XSSFCell xm = row.createCell(1);
        xm.setCellValue(arr[1]);
        XSSFCell yxsmc = row.createCell(2);
        yxsmc.setCellValue(arr[2]);
        XSSFCell kcm = row.createCell(3);
        kcm.setCellValue(arr[3]);
        XSSFCell cj = row.createCell(4);
        cj.setCellValue(arr[4]);
          
        }
    }
    // 创建文件输出流，准备输出电子表格 
    OutputStream out = new FileOutputStream("c:/pldrxkxxmb.xlsx");
    hwb.write(out);
    out.close();
    System.out.println("数据库导出成功");
    }
      
    public static void xlsDto2Excel(List<String[]> xls) throws Exception
    {
    // 获取总列数 
    int CountColumnNum = xls.size();
    // 创建Excel文档 
    HSSFWorkbook hwb = new HSSFWorkbook();
    // sheet 对应一个工作页 
    HSSFSheet sheet = hwb.createSheet("pldrxkxxmb");
    HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始 
    HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
    String[] names = new String[CountColumnNum];
    names[0] = "学号";
    names[1] = "姓名";
    names[2] = "学院";
    names[3] = "课程名";
    names[4] = "成绩";
    for (int j = 0; j < CountColumnNum; j++)
    {
        firstcell[j] = firstrow.createCell(j);
        firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
    }
    for (int i = 0; i < xls.size(); i++)
    {
        // 创建一行 
        HSSFRow row = sheet.createRow(i + 1);
        // 得到要插入的每一条记录 
        String arr[] = xls.get(i);
        //xlsDto = xls.get(i); 
        for (int colu = 0; colu <= 4; colu++)
        {
        // 在一行内循环 
        HSSFCell xh = row.createCell(0);
        xh.setCellValue(arr[0]);
        HSSFCell xm = row.createCell(1);
        xm.setCellValue(arr[1]);
        HSSFCell yxsmc = row.createCell(2);
        yxsmc.setCellValue(arr[2]);
        HSSFCell kcm = row.createCell(3);
        kcm.setCellValue(arr[3]);
        HSSFCell cj = row.createCell(4);
        cj.setCellValue(arr[4]);
          
        }
    }
    // 创建文件输出流，准备输出电子表格 
    OutputStream out = new FileOutputStream("c:/pldrxkxxmb.xls");
    hwb.write(out);
    out.close();
    System.out.println("数据库导出成功");
    }
      
}