"POI" modifies Excel content

Package Com.what21.test;import Java.io.file;import Java.io.fileinputstream;import java.io.FileNotFoundException; Import Java.io.fileoutputstream;import Java.io.ioexception;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;import Org.apache.poi.xssf.usermodel.xssfcell;import Org.apache.poi.xssf.usermodel.xssfsheet;import Org.apache.poi.xssf.usermodel.xssfworkbook;public class TestPoi { public static void Updateexcel (File exlfile, string sheetname, int col,int row, string value) throws Exception {fileinputs Tream fis = new FileInputStream (exlfile); Hssfworkbook workbook = new Hssfworkbook (FIS);//Workbook. Hssfsheet sheet = workbook.getsheet (sheetname); Hssfcell MyCell = Sheet.createrow (Row). Createcell (col); Mycell.setcellvalue (value); Hssfrow r = sheet.getrow (row); Hssfcell cell = R.getcell (col);//int type=cell.getcelltype (); String str1 = Cell.getstringcellvaLue ();//This assumes that the original type of the corresponding cell is also a string type Cell.setcellvalue (value); System.out.println ("Cell original value is" + str1); SYSTEM.OUT.PRINTLN ("Cell value is updated to" + value); Fis.close ();//close file input stream FileOutputStream fos = new FileOutputStream (exlfile); Workbook.write (FOS); Fos.close ()//close file output stream}public static void Update2 (String url) {int coloum = 2;//For example you want to get 1th column try {HSSFWO Rkbook workbook = new Hssfworkbook (new FileInputStream (URL)); Hssfsheet sheet = workbook.getsheet ("Sheet1"); for (int i = 0; I <= sheet.getlastrownum (); i++) {Hssfrow row = Sheet.get Row (short) i), if (null = = row) {continue;} else {Hssfcell cell = Row.getcell ((short) Coloum); if (null = = cell) {continue; } else {cell.setcellvalue ("he1");}}} FileOutputStream out = null;try {off = new FileOutputStream (URL); workbook.write (out);} catch (IOException e) {E.printstac Ktrace ();} Finally {try {out.close ();} catch (IOException e) {e.printstacktrace ()}}} catch (FileNotFoundException e) {e.printstacktrace ();} catch (IOException e) {e.printstacktrace ()}} public static Boolean Writexlsx (String fileName, int row, int column,string content) {Boolean flag = false; FileOutputStream out = null; Xssfworkbook xwb;try {XWB = new Xssfworkbook (new FileInputStream (FileName)); Xssfsheet xsheet = xwb.getsheetat (0); Xssfcell XCell = Xsheet.createrow (Row). Createcell (column); Xcell.setcellvalue (content); out = new FileOutputStream ( FileName); Xwb.write (out); Out.close (); flag = true;} catch (IOException e) {e.printstacktrace ();} catch (RuntimeException e) {e.printstacktrace ();} return flag;} public static void Main (string[] args) throws Exception {//TODO auto-generated method stub//The following changes to your own XLS file for testing, 2003 format, cannot 2007File file = new file ("C:\\users\\administrator\\desktop\\test.xls");//The next attempt to change the value of the first column of the first row of cells Updateexcel (file, " Sheet1 ", 0, 0," hehe "); Update2 (" C:\\users\\administrator\\desktop\\test.xls "); File File1 = new file ("C:\\users\\administrator\\desktop\\test-copy. xlsx"); Writexlsx ("c:\\users\\administrator\\ Desktop\\test-Copy. xlsx ", 0, 0," 1 ");}}

Note: If the modified coordinates correspond to a cell that is empty, an error will be added.

It is recommended to write this:

Hssfcell MyCell = Sheet.createrow (Row). Createcell (COL);
Mycell.setcellvalue (value);

There is a problem with this writing: If you locate the error, it will cause unnecessary trouble.

Typically, when generating the original Excel, it is best to Goessing the empty cell (forcing the addition of an empty string) so that when using Saxpoi or sax to parse the larger text of Excel, you can

Effectively avoid problems that result in column data dislocation due to empty cells.

The above code is very common and can be found on the web. Depending on the scene you can pass in a set of coordinates that need to be modified, and modify it by looping. This can reduce the number of open files and improve

Efficiency.

"POI" modifies Excel content