java poi操作excel示例代码

<div class="cnblogs_code">

<span style="color: #0000ff;">import<span style="color: #000000;"> org.apache.poi.hssf.usermodel.HSSFCell;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.apache.poi.hssf.usermodel.HSSFRow;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.apache.poi.hssf.usermodel.HSSFSheet;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.apache.poi.hssf.usermodel.HSSFWorkbook;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.apache.poi.xssf.usermodel.XSSFCell;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.apache.poi.xssf.usermodel.XSSFRow;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.apache.poi.xssf.usermodel.XSSFSheet;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.apache.poi.xssf.usermodel.XSSFWorkbook;

<span style="color: #0000ff;">import<span style="color: #000000;"> java.lang.reflect.Field;

<span style="color: #0000ff;">import<span style="color: #000000;"> java.lang.reflect.InvocationTargetException;

<span style="color: #0000ff;">import<span style="color: #000000;"> java.lang.reflect.Method;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.slf4j.Logger;

<span style="color: #0000ff;">import<span style="color: #000000;"> org.slf4j.LoggerFactory;
<span style="color: #0000ff;">public <span style="color: #0000ff;">class<span style="color: #000000;"> ReadWriteExcelFile {

<span style="color: #0000ff;">private <span style="color: #0000ff;">static Properties props = <span style="color: #0000ff;">new<span style="color: #000000;"> Properties();

<span style="color: #0000ff;">static<span style="color: #000000;"> {

<span style="color: #0000ff;">try<span style="color: #000000;"> {

InputStream is = ReadWriteExcelFile.<span style="color: #0000ff;">class.getClassLoader().getResourceAsStream("META-INF/ExcelHeader.properties"<span style="color: #000000;">);

props.load(is);

} <span style="color: #0000ff;">catch<span style="color: #000000;"> (Exception e) {

e.printStackTrace();

}

}

<span style="color: #0000ff;">public <span style="color: #0000ff;">static<span style="color: #000000;"> String getValue(String key) {

String value = ""<span style="color: #000000;">;

<span style="color: #0000ff;">if<span style="color: #000000;"> (props.containsKey(key)) {

value = props.getProperty(key,""<span style="color: #000000;">);

}

<span style="color: #0000ff;">return<span style="color: #000000;"> value;

}

<span style="color: #0000ff;">private <span style="color: #0000ff;">final <span style="color: #0000ff;">static Logger logger = LoggerFactory.getLogger(ReadWriteExcelFile.<span style="color: #0000ff;">class<span style="color: #000000;">);

@SuppressWarnings({ "resource","rawtypes"<span style="color: #000000;"> })

<span style="color: #0000ff;">public <span style="color: #0000ff;">static <span style="color: #0000ff;">void readXLSFile() <span style="color: #0000ff;">throws<span style="color: #000000;"> IOException

{

InputStream ExcelFileToRead = <span style="color: #0000ff;">new FileInputStream("E:/source/Test.xls"<span style="color: #000000;">);

HSSFWorkbook wb = <span style="color: #0000ff;">new<span style="color: #000000;"> HSSFWorkbook(ExcelFileToRead);
    HSSFSheet sheet</span>=wb.getSheetAt(0<span style="color: #000000;"&gt;);
    HSSFRow row; 
    HSSFCell cell;

    Iterator rows </span>=<span style="color: #000000;"&gt; sheet.rowIterator();

    </span><span style="color: #0000ff;"&gt;while</span><span style="color: #000000;"&gt; (rows.hasNext())
    {
        row</span>=<span style="color: #000000;"&gt;(HSSFRow) rows.next();
        Iterator cells </span>=<span style="color: #000000;"&gt; row.cellIterator();

        </span><span style="color: #0000ff;"&gt;while</span><span style="color: #000000;"&gt; (cells.hasNext())
        {
            cell</span>=<span style="color: #000000;"&gt;(HSSFCell) cells.next();

            </span><span style="color: #0000ff;"&gt;if</span> (cell.getCellType() ==<span style="color: #000000;"&gt; HSSFCell.CELL_TYPE_STRING)
            {
                System.out.print(cell.getStringCellValue()</span>+" "<span style="color: #000000;"&gt;);
            }
            </span><span style="color: #0000ff;"&gt;else</span> <span style="color: #0000ff;"&gt;if</span>(cell.getCellType() ==<span style="color: #000000;"&gt; HSSFCell.CELL_TYPE_NUMERIC)
            {
                System.out.print(cell.getNumericCellValue()</span>+" "<span style="color: #000000;"&gt;);
            }
            </span><span style="color: #0000ff;"&gt;else</span><span style="color: #000000;"&gt;
            {
                </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;U Can Handel Boolean,Formula,Errors</span>
<span style="color: #000000;">                }

}

System.out.println();

}
}
</span><span style="color: #0000ff;"&gt;public</span> <span style="color: #0000ff;"&gt;static</span> <span style="color: #0000ff;"&gt;void</span> writeXLSFile(List<? <span style="color: #0000ff;"&gt;extends</span> Recording> records) <span style="color: #0000ff;"&gt;throws</span><span style="color: #000000;"&gt; IOException{
    </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;String directory=getValue("excel.file.directory");</span>
    String directory="E:/source"<span style="color: #000000;"&gt;;
    Date d </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; Date(); 
    SimpleDateFormat sdf </span>= <span style="color: #0000ff;"&gt;new</span> SimpleDateFormat("yyyy-MM-dd"<span style="color: #000000;"&gt;);  
    String date </span>=<span style="color: #000000;"&gt; sdf.format(d);
    Recording record</span>=records.get(0<span style="color: #000000;"&gt;);
    Class</span><? <span style="color: #0000ff;"&gt;extends</span> Recording> cls=<span style="color: #000000;"&gt;record.getClass();
    String className</span>=<span style="color: #000000;"&gt;cls.getCanonicalName();
    String[] nameAlias</span>=className.split("."<span style="color: #000000;"&gt;);
    String excelFileName</span>=directory+File.separator+nameAlias[nameAlias.length-1]+date+".xls"<span style="color: #000000;"&gt;;
    writeXLSFile(records,excelFileName);
}

@SuppressWarnings(</span>"resource"<span style="color: #000000;"&gt;)
</span><span style="color: #0000ff;"&gt;public</span> <span style="color: #0000ff;"&gt;static</span> <span style="color: #0000ff;"&gt;void</span> writeXLSFile(List<? <span style="color: #0000ff;"&gt;extends</span> Recording> records,String excelFileName) <span style="color: #0000ff;"&gt;throws</span><span style="color: #000000;"&gt; IOException{

    </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;String excelFileName = "E:/source/Test.xls";</span>
    String sheetName = "Sheet1";<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;name of sheet</span>
<span style="color: #000000;">

HSSFWorkbook wb = <span style="color: #0000ff;">new<span style="color: #000000;"> HSSFWorkbook();

HSSFSheet sheet =<span style="color: #000000;"> wb.createSheet(sheetName) ;

Recording record;

<span style="color: #008000;">//<span style="color: #008000;">excel header

HSSFRow row = sheet.createRow(0<span style="color: #000000;">);

record=records.get(0<span style="color: #000000;">);

Class<? <span style="color: #0000ff;">extends Recording> cls=<span style="color: #000000;">record.getClass();

String className=<span style="color: #000000;">cls.getCanonicalName();

String[] nameAlias=className.split("."<span style="color: #000000;">);

Field[] fields=<span style="color: #000000;">cls.getDeclaredFields();

<span style="color: #008000;">//<span style="color: #008000;">去除serialVersionUID列,

List fieldsNoSer=<span style="color: #0000ff;">new ArrayList<span style="color: #000000;">();

<span style="color: #0000ff;">for(<span style="color: #0000ff;">int i=0;i<fields.length;i++<span style="color: #000000;">){

String fieldName=<span style="color: #000000;">fields[i].getName();

<span style="color: #0000ff;">if(fieldName.equalsIgnoreCase("serialVersionUID"<span style="color: #000000;">)){

<span style="color: #0000ff;">continue<span style="color: #000000;">;

}<span style="color: #0000ff;">else<span style="color: #000000;">{

fieldsNoSer.add(fields[i]);

}

}

<span style="color: #0000ff;">for(<span style="color: #0000ff;">int i=0;i<fieldsNoSer.size();i++<span style="color: #000000;">){

HSSFCell cell =<span style="color: #000000;"> row.createCell(i);

String fieldName=<span style="color: #000000;">fieldsNoSer.get(i).getName();

cell.setCellValue(getValue(nameAlias[nameAlias.length-1]+"."+<span style="color: #000000;">fieldName));

}
    </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;iterating r number of rows</span>
    <span style="color: #0000ff;"&gt;for</span> (<span style="color: #0000ff;"&gt;int</span> r=0;r < records.size(); r++<span style="color: #000000;"&gt; )
    {
        row </span>= sheet.createRow(r+1<span style="color: #000000;"&gt;);
        record</span>=<span style="color: #000000;"&gt;records.get(r);
        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;table content</span>
        <span style="color: #0000ff;"&gt;for</span> (<span style="color: #0000ff;"&gt;int</span> c=0;c < fieldsNoSer.size(); c++<span style="color: #000000;"&gt; )
        {    
            HSSFCell cell </span>=<span style="color: #000000;"&gt; row.createCell(c);
            </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;加header,方法总变量的首字母大写</span>
            String fieldName=<span style="color: #000000;"&gt;fieldsNoSer.get(c).getName();
            </span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
                Method method</span>=cls.getDeclaredMethod("get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1<span style="color: #000000;"&gt;));
                Object ret</span>=<span style="color: #000000;"&gt;method.invoke(record);
                </span><span style="color: #0000ff;"&gt;if</span>(<span style="color: #0000ff;"&gt;null</span>!=<span style="color: #000000;"&gt;ret){
                    cell.setCellValue(method.invoke(record).toString());
                }

            } </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (Exception e) {
                logger.info(</span>"write xls error,please check it"<span style="color: #000000;"&gt;);
            }
        }
    }
    FileOutputStream fileOut </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; FileOutputStream(excelFileName);

    </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;write this workbook to an Outputstream.</span>
<span style="color: #000000;">        wb.write(fileOut);

fileOut.flush();

fileOut.close();

}
@SuppressWarnings({ </span>"resource","unused","rawtypes"<span style="color: #000000;"&gt; })
</span><span style="color: #0000ff;"&gt;public</span> <span style="color: #0000ff;"&gt;static</span> <span style="color: #0000ff;"&gt;void</span> readXLSXFile() <span style="color: #0000ff;"&gt;throws</span><span style="color: #000000;"&gt; IOException
{
    InputStream ExcelFileToRead </span>= <span style="color: #0000ff;"&gt;new</span> FileInputStream("E:/source/Test1.xlsx"<span style="color: #000000;"&gt;);
    XSSFWorkbook  wb </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; XSSFWorkbook(ExcelFileToRead);

    XSSFWorkbook test </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; XSSFWorkbook(); 

    XSSFSheet sheet </span>= wb.getSheetAt(0<span style="color: #000000;"&gt;);
    XSSFRow row; 
    XSSFCell cell;

    Iterator rows </span>=<span style="color: #000000;"&gt; sheet.rowIterator();

    </span><span style="color: #0000ff;"&gt;while</span><span style="color: #000000;"&gt; (rows.hasNext())
    {
        row</span>=<span style="color: #000000;"&gt;(XSSFRow) rows.next();
        Iterator cells </span>=<span style="color: #000000;"&gt; row.cellIterator();
        </span><span style="color: #0000ff;"&gt;while</span><span style="color: #000000;"&gt; (cells.hasNext())
        {
            cell</span>=<span style="color: #000000;"&gt;(XSSFCell) cells.next();

            </span><span style="color: #0000ff;"&gt;if</span> (cell.getCellType() ==<span style="color: #000000;"&gt; XSSFCell.CELL_TYPE_STRING)
            {
                System.out.print(cell.getStringCellValue()</span>+" "<span style="color: #000000;"&gt;);
            }
            </span><span style="color: #0000ff;"&gt;else</span> <span style="color: #0000ff;"&gt;if</span>(cell.getCellType() ==<span style="color: #000000;"&gt; XSSFCell.CELL_TYPE_NUMERIC)
            {
                System.out.print(cell.getNumericCellValue()</span>+" "<span style="color: #000000;"&gt;);
            }
            </span><span style="color: #0000ff;"&gt;else</span><span style="color: #000000;"&gt;
            {
                </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;U Can Handel Boolean,Errors</span>
<span style="color: #000000;">                }

}

System.out.println();

}
}

@SuppressWarnings(</span>"resource"<span style="color: #000000;"&gt;)
</span><span style="color: #0000ff;"&gt;public</span> <span style="color: #0000ff;"&gt;static</span> <span style="color: #0000ff;"&gt;void</span> writeXLSXFile() <span style="color: #0000ff;"&gt;throws</span><span style="color: #000000;"&gt; IOException {

    String excelFileName </span>= "E:/source/Test1.xlsx";<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;name of excel file</span>
<span style="color: #000000;">

String sheetName = "Sheet1";<span style="color: #008000;">//<span style="color: #008000;">name of sheet

<span style="color: #000000;">

XSSFWorkbook wb = <span style="color: #0000ff;">new<span style="color: #000000;"> XSSFWorkbook();

XSSFSheet sheet =<span style="color: #000000;"> wb.createSheet(sheetName) ;
    </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;iterating r number of rows</span>
    <span style="color: #0000ff;"&gt;for</span> (<span style="color: #0000ff;"&gt;int</span> r=0;r < 5; r++<span style="color: #000000;"&gt; )
    {
        XSSFRow row </span>=<span style="color: #000000;"&gt; sheet.createRow(r);

        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;iterating c number of columns</span>
        <span style="color: #0000ff;"&gt;for</span> (<span style="color: #0000ff;"&gt;int</span> c=0;c < 5; c++<span style="color: #000000;"&gt; )
        {
            XSSFCell cell </span>=<span style="color: #000000;"&gt; row.createCell(c);

            cell.setCellValue(</span>"Cell "+r+" "+<span style="color: #000000;"&gt;c);
        }
    }

    FileOutputStream fileOut </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; FileOutputStream(excelFileName);

    </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt;write this workbook to an Outputstream.</span>
<span style="color: #000000;">        wb.write(fileOut);

fileOut.flush();

fileOut.close();

}
</span><span style="color: #0000ff;"&gt;public</span> <span style="color: #0000ff;"&gt;static</span> <span style="color: #0000ff;"&gt;void</span> main(String[] args) <span style="color: #0000ff;"&gt;throws</span><span style="color: #000000;"&gt; IOException,NoSuchMethodException,SecurityException,IllegalAccessException,IllegalArgumentException,InvocationTargetException {
    List</span><Log> logs=<span style="color: #0000ff;"&gt;new</span> ArrayList<Log><span style="color: #000000;"&gt;();
    </span><span style="color: #0000ff;"&gt;for</span>(<span style="color: #0000ff;"&gt;int</span> i=1;i<2;i++<span style="color: #000000;"&gt;){
        Log log</span>=<span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; Log();
        log.setId(Long.parseLong(</span>""+(i+6<span style="color: #000000;"&gt;)));
        log.setUserId(Long.parseLong(</span>""+<span style="color: #000000;"&gt;i));
        log.setUserName(</span>"www"+<span style="color: #000000;"&gt;i);
        logs.add(log);
    }
    writeXLSFile(logs,</span>"E:/source/aa.xls"<span style="color: #000000;"&gt;);
    </span><span style="color: #000000;"&gt;
}</span></pre>
                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!