Java Thinking Caused by special requirements of excel tables and java thinking of excel tables

Java Thinking Caused by special requirements of excel tables and java thinking of excel tables

Preface:

If you encounter such a requirement a few days ago:

 

Combine the rows in the table with the same row in the previous section and add the prices in the second and third rows to the first row for price 2 and price 3.

After receiving such a requirement, I first felt that it was too simple to directly merge manually (it was too easy to think about it again). Then I gave up seeing the total number of records, I decided to look for the excel operation method on the Internet. I did not find it after looking for a while. I thought I could not waste too much time. I think it would be foolish to be a younger brother (I just said the old man was criticized, after all, I have never learned excel. I hope I can leave a message and I want to learn it. Now let's take a look at how to implement it.

To implement this function, you need to read the excel table. Here I use HSSFWorkbook, because it is in version 03. If you want to be compatible with version 07, you can access this blog at http://www.cnblogs.com/yejg1212/p/2017822.html. To read a file, we first need to get the file stream, that is:

InputStream is = new FileInputStream("C://jlo.xls");

Then use HSSFWorkbook to read the data. First, read the sheet, find the desired sheet, and obtain the values of each column from all rows in the loop, as shown below:

HSSFWorkbook hssfWorkbook = new HSSFWorkbook (is); HashMap <String, String> map = new HashMap <> (); // cyclic worksheet Sheet for (int numSheet = 0; numSheet 
I have created an object class to save the obtained values for better storage, and it says it suddenly stops, and it's blind, how can I splice the same contents of a category into an entity class? I thought it would be inappropriate to use a database, which would affect performance, so I chose global variables, similar to caching, because at most three items will be the same according to the excel display rules, and the other content will be the same, so you only need to record the price of the same row, save it to the HashMap set, save it all to the last object model, and put it into the global variable hashMap used for caching, finally, all the value values in its hashMap are processed and the entity is cyclically written into an excel file. Wow, that's all done. It's a little simpler. It's faster than searching for an excel file on the Internet. The following describes how to read the specific excel Code:
/*** Read the content of the xls file ** @ throws IOException * input/output (I/o) exception */private void readXls () throws IOException {InputStream is = new FileInputStream ("C: // jlo.xls"); HSSFWorkbook hssfWorkbook = new HSSFWorkbook (is); HashMap <String, string> map = new HashMap <> (); // cyclic worksheet Sheet for (int numSheet = 0; numSheet 
　　After the processing is complete, import it to excel again, as shown below:
/***** @ Param xls * an object in the XlsDto object class * @ throws Exception * throws an Exception during Excel import */public static void xlsDto2Excel (List <XlsDto> xls) throws Exception {// get the total number of columns int CountColumnNum = xls. size (); // create an Excel file HSSFWorkbook hwb = new HSSFWorkbook (); XlsDto xlsDto = null; // sheet corresponds to a work page HSSFSheet sheet = hwb. createSheet ("sheet1"); HSSFRow firstrow = sheet. createRow (0); // start HSSFCell [] firstcell = new HSSFCell [CountColumnNum]; String [] names = new String [12]; names [0] = "SMILES"; names [1] = "inherit"; names [2] = "Product Name "; names [3] = "Product Name (Chinese"; names [4] = "CAS"; names [5] = "calendar (days )"; names [6] = "purity"; names [7] = "inventory"; names [8] = "customizable"; names [9] = "packaging/price 1 "; names [10] = "packaging/price 2"; names [11] = "packaging/price 3"; for (int j = 0; j <12; j ++) {firstcell [j] = firstrow. createCell (j); firstcell [j]. setCellValue (new HSSFRichTextString (names [j]) ;}for (int I = 0; I <xls. size (); I ++) {// create a row HSSFRow row = sheet. createRow (I + 1); // obtain each record to be inserted xlsDto = xls. get (I); // loop HSSFCell xh = row in a row. createCell (0); xh. setCellValue (xlsDto. getSmiles (); HSSFCell xm = row. createCell (1); xm. setCellValue (xlsDto. getHuoHao (); HSSFCell yxsmc = row. createCell (2); yxsmc. setCellValue (xlsDto. geteName (); HSSFCell kcm = row. createCell (3); kcm. setCellValue (xlsDto. getcName (); HSSFCell cj = row. createCell (4); cj. setCellValue (xlsDto. getCas (); HSSFCell hd = row. createCell (5); hd. setCellValue (xlsDto. getHuoDate (); HSSFCell purity = row. createCell (6); purity. setCellValue (xlsDto. getPurity (); HSSFCell kuncun = row. createCell (7); kuncun. setCellValue (xlsDto. getKunCun (); HSSFCell isc = row. createCell (8); isc. setCellValue (xlsDto. getIsCreate (); HSSFCell ac = row. createCell (9); ac. setCellValue (xlsDto. getaco ST (); HSSFCell bc = row. createCell (10); bc. setCellValue (xlsDto. getbCost (); HSSFCell ec = row. createCell (11); ec. setCellValue (xlsDto. geteCost ();} // create a file output stream and prepare to output the workbook OutputStream out = new FileOutputStream ("C: // jlol.xls"); hwb. write (out); out. close (); System. out. println ("database exported ");}
It perfectly solves this special but not special requirement. The code is provided only for mutual learning. You are welcome to raise some shortcomings.