Java POI operation excel example code

2019-02-12 • Java
<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)
            {
                Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.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)
            {
                Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.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;ite<a href="https://www.jb51.cc/tag/rating/" target="_blank" class="keywords">rating</a> 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,<a href="https://www.jb51.cc/tag/fangfa/" target="_blank" class="keywords">方法</a>总变量的首字母大写</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)
            {
                Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.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)
            {
                Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.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;ite<a href="https://www.jb51.cc/tag/rating/" target="_blank" class="keywords">rating</a> 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;ite<a href="https://www.jb51.cc/tag/rating/" target="_blank" class="keywords">rating</a> 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,S<a href="https://www.jb51.cc/tag/ecurity/" target="_blank" class="keywords">ecurity</a>Exception,<a href="https://www.jb51.cc/tag/illegalaccessexception/" target="_blank" class="keywords">illegalaccessexception</a>,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>
The content of this article comes from the network collection of netizens. It is used as a learning reference. The copyright belongs to the original author.
THE END
Java
二维码
Session
< <上一篇
Array of tasteless food and pitiful discard in Java
下一篇>>
搜索内容

Java POI operation excel example code

热门文章
