Java is easy to operate on excel. javaexcel

Java is easy to operate on excel. javaexcel

Apache POI open source library is used, combined with mysql database

When you receive the notification, you need to clear the given table. All you need to do is to compare all the data in this table with the data in another local table, if a piece of data exists, mark it.

Download poi. jar and related packages, configure environment variables, install the database, and download the java driver of the database to configure related environment variables.

1. First, import all the feature quantities in the reference table to the database.

Import java. io. *; import org. apache. poi. hssf. usermodel. *; import java. SQL. *; public class test2 {public static void main (String [] args) {String driver = "com. mysql. jdbc. driver "; String url =" jdbc: mysql: /localhost: 3306/test "; String user =" root "; String password =" root "; Connection conn = null; preparedStatement ps = null; ResultSet rs = null; FileInputStream indium = null; HSSFWorkbook wb = null; try {indium = New FileInputStream ("E:/test.xls"); // open the file wb = new HSSFWorkbook (indium); // create the HSSFWorkbook object HSSFSheet sheet = wb. getSheetAt (0); // get the first table in the file int coloumNum = sheet. getRow (0 ). getPhysicalNumberOfCells (); // The valid column int rowNum = sheet in the table. getLastRowNum (); // valid row Class in the table. forName (driver); conn = DriverManager. getConnection (url, user, password); String sqlInset = "insert into xls (id) values (?) "; Ps = conn. prepareStatement (sqlInset); String temp = null; for (int I = 0; I <= rowNum; I ++) {HSSFRow row = sheet. getRow (I); // obtain the I rows in the Table. HSSFCell cell = row. getCell (0); // obtain the cell if (cell. getCellType ()! = 1) {cell. setCellType (1); // if the data in the obtained cell is not in string format, set it to a flag that can be converted to string format.} temp = cell. getStringCellValue (); // convert HSSFCell object to string object ps. setString (1, temp); ps.exe cuteUpdate (); // update to database} ps. close (); conn. close (); wb. close (); indium. close ();} catch (Exception e) {e. printStackTrace ();}}}

2. Open the table to be marked and retrieve each row of data in sequence and compare all the data in the database. If yes, mark it.

Import java. io. *; import org. apache. poi. hssf. usermodel. *; import java. SQL .*;
Public class test3 {public static void main (String [] args) {String driver = "com. mysql. jdbc. driver "; String url =" jdbc: mysql: /localhost: 3306/test "; String user =" root "; String password =" root "; Connection conn = null; preparedStatement ps = null; ResultSet rs = null; FileInputStream indium = null; HSSFWorkbook wb = null; try {indium = new FileInputStream ("E:/test3.xls "); wb = new HSSFWorkbook (indium); HSSFShee T sheet = wb. getSheetAt (0); int coloumNum = sheet. getRow (0 ). getPhysicalNumberOfCells (); int rowNum = sheet. getLastRowNum (); Class. forName (driver); conn = DriverManager. getConnection (url, user, password); String sqlInset = "select * from xls"; ps = conn. prepareStatement (sqlInset); rs = ps.exe cuteQuery (); String temp = null; boolean flag = false; for (int I = 0; I <= rowNum; I ++) {HSSFRow row = sheet. getRow (I); HSSF Cell cell = row. getCell (0); HSSFCell cell2 = row. getCell (1); if (cell. getCellType ()! = 1) {cell. setCellType (1);} temp = cell. getStringCellValue (); rs = ps.exe cuteQuery (); while (rs. next () {if (temp. equals (rs. getString (1) {flag = true; System. out. println (temp); break; }}if (flag) {FileOutputStream oup = new FileOutputStream ("E:/test3.xls"); // write the stream, let the disk cell2.setCellType (1) be written back after each operation; // write back cell2.setCellValue ("yes") as a string; // set the write-back value to yes wb. write (oup); // write back to disk oup through the function provided by HSSFWrokbook. close (); flag = false ;}} rs. close (); ps. close (); conn. close (); wb. close (); indium. close ();} catch (Exception e) {e. printStackTrace ();}}}