Preface:
A few days ago, we met such a demand. Here is the following picture:
Combine the lines with the same number in the front part of the table into one line, and add the price of the second line and the third line to the first line for Price Two and Price Three. As shown in the picture:
After receiving such a demand, my first feeling is to merge directly by hand (it's too simple to think about in secret). Then I looked at the total number of records and gave up. I decided to find the operation method of Excel on the internet. After searching for it for a while, I didn't find it. I thought that I could not waste too much time, instead, I could do enough food and clothes by myself. Maybe my younger brother (just said that the old man had been criticized) was ignorant, Bi. Unexpectedly, I haven't learned excel very much. I hope the God will leave a message for me. I should also learn it. Well, let's stop talking nonsense. Let's see how to achieve it.
First of all, to achieve this function, you need to read excel tables. I use HSSF Workbook here, because it is version 03. If you want to be compatible with version 07, you can visit this blog http://www.cnblogs.com/yejg1212/p/3969822.html, so I won't do much about it. To read a file, we first need to get the file stream, that is:
InputStream is = new FileInputStream("C://jlo.xls");
Then use HSSF Workbook to read, first read the sheet, find the sheet you want, get all the rows in the loop to get the values of each column, as follows:
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
HashMap<String, String> map = new HashMap<>();
// Cyclic worksheet Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Circular row Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
}
}
In order to save better, I built an entity class to save the value I got. It said that it stopped suddenly. Meng, how can I stitch the same number of goods into an entity class? I thought that it would be inappropriate to use the database, which would affect the performance too much. So I chose the global variable tactfully, which is similar to caching, because it is shown in this excel. Rules, at most three will be the same, while the rest will be the same, so you just need to record the price of the same row, save it in the HashMap collection, save it all in the last entity model, and put it in the global variable hashMap used for caching, and finally write all the value values in the hashMap to an excel after processing entity loop. Chinese, wow, that's it. It's a little simpler, faster than finding excel on the Internet and not finding it yet. The next step is to read the specific code implementation of excel:
/**
* Read the contents of xls files
*
* @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 < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Circular row Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
XlsDto xld = new XlsDto();
xld.setSmiles(getValue(hssfRow.getCell(0)));
xld.setHuoHao(getValue(hssfRow.getCell(1)).substring(0,getValue(hssfRow.getCell(1)).indexOf("-")));
xld.seteName(getValue(hssfRow.getCell(2)));
xld.setcName(getValue(hssfRow.getCell(3)));
xld.setCas(getValue(hssfRow.getCell(4)));
xld.setHuoDate(getValue(hssfRow.getCell(5)));
xld.setPurity(getValue(hssfRow.getCell(6)));
xld.setKunCun(getValue(hssfRow.getCell(7)));
xld.setIsCreate(getValue(hssfRow.getCell(8)));
xld.setaCost(getValue(hssfRow.getCell(9)));
xld.setxType(getValue(hssfRow.getCell(1)).substring(0,getValue(hssfRow.getCell(1)).indexOf("-")));
if(StringUtils.isNotBlank(getValue(hssfRow.getCell(1)))){
if(!map.containsKey(xld.getxType())){
String cost = getValue(hssfRow.getCell(9));
//insertX(xld);
hashMap.put(xld.getxType(), xld);
map.put(xld.getxType(), "1");
map.put(xld.getxType()+"1", cost);
}else{
//String xType = getValue(hssfRow.getCell(1)).substring(0,getValue(hssfRow.getCell(1)).indexOf("-"));
if("1".equals(map.get(xld.getxType()))){
String cost = getValue(hssfRow.getCell(9));
xld.setaCost(map.get(xld.getxType()+"1"));
xld.setbCost(cost);
hashMap.put(xld.getxType(), xld);
//updateX(xType, cost,1);
map.put(xld.getxType(), "2");
map.put(xld.getxType()+"2", cost);
}else{
String cost = getValue(hssfRow.getCell(9));
xld.setaCost(map.get(xld.getxType()+"1"));
xld.setbCost(map.get(xld.getxType()+"2"));
xld.seteCost(cost);
hashMap.put(xld.getxType(), xld);
//updateX(xType, cost,2);
map.put(xld.getxType(), "3");
}
}
}
}
}
}
After the processing is completed, it is imported into excel again, which is implemented as follows:
/**
*
* @param xls
* XlsDto An object of entity class
* @throws Exception
* Throw an exception during importing Excel
*/
public static void xlsDto2Excel(List<XlsDto> xls) throws Exception {
// Get the total number of columns
int CountColumnNum = xls.size();
// Create Excel Documents
HSSFWorkbook hwb = new HSSFWorkbook();
XlsDto xlsDto = null;
// sheet corresponds to a work page
HSSFSheet sheet = hwb.createSheet("sheet1");
HSSFRow firstrow = sheet.createRow(0); // // Lines with subscripts of 0 begin
HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
String[] names = new String[12];
names[0] = "SMILES";
names[1] = "Item number";
names[2] = "Product Name";
names[3] = "Product Name (Chinese)";
names[4] = "CAS Number";
names[5] = "Date of delivery (days)";
names[6] = "purity";
names[7] = "Stock";
names[8] = "Is it customizable";
names[9] = "Packing/Price 1";
names[10] = "Packing/Price 2";
names[11] = "Packing/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);
// Get each record to insert
xlsDto = xls.get(i);
// Cycle in a row
HSSFCell xh = 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.getaCost());
HSSFCell bc = row.createCell(10);
bc.setCellValue(xlsDto.getbCost());
HSSFCell ec = row.createCell(11);
ec.setCellValue(xlsDto.geteCost());
}
// Create a file output stream to prepare the output spreadsheet
OutputStream out = new FileOutputStream("C://jlol.xls");
hwb.write(out);
out.close();
System.out.println("Successful database export");
}
It perfectly solves this special and Non-special requirement. The code is provided only for everyone to learn from each other. Welcome to visit the shortcomings of the prompts.