java如何复制几百万行的Excel数据？自动拆成多个表？

Question

数据表如图1,2,3行所示，把第2行复制3次（d次），第3行复制4次（d次）
然后只粘贴abc列的数据（6,7,8有d行相同的数据，但是不包括d列的数据）
根据上面的数据，把复制之后的数据写入新的表，用java如何操作呀？
有40多个表，每个表中都有200多条数据，数据量很大，所有要用程序写，
还有一个问题就是一个表中复制之后的数据量可能超过Excel20007的最大行数（1048576行），如何自动拆成多个表呢？比如前50个数据在一个表，51-100在第二个表，101-200在第三个表......

注意！！！
复制之后的数据是写到Excel表中，并不是数据库中，java初学。。不太会

Answer 1

Okay, let me straighten out the problem first to make it easier to read...
The excel table has a total of four columns a, b, c, and d. Columns a, b, and c are the stored data, and column d represents the data in the row. The number of copies, the first line is the header.

Also, is the table you are talking about a database table or an excel table?

---

Update

You can take a lookApache POIThis tool library is specially used to operate office documents.

If it is only for one-time use, then there is no need to think so much. Use POI to read the excel document into the memory, then copy the data in the program, and then partition the copied data and write it into the excel table.

If you know a little bit about js, I suggest you use node-xlsx (depending on nodejs) to achieve this requirement, which is faster than writing java. The number of lines of code should be within 50 lines.

Update 2

I can only write a piece of nodejs code for you, because I really don’t want to write java...

'use strict'

const fs = require('fs');
const xlsx = require('node-xlsx');
const sheet1 = xlsx.parse(process.argv[2])[0].data;
const savedFile = process.argv[3];

let result = [];
for(let i = 1, length = sheet1.length;i < length;i++) {
  let row = sheet1[i];
  let loopTimes = row.splice(3);// 将d列数据取出并移除
  for(let j = 0;j < loopTimes;j++) {
    result.push(row);
  }
}

let file = xlsx.build([{name:'sheet1', data:result}]);

fs.writeFileSync(savedFile, file, 'binary');

How to use

1. Install nodejs;

2. Open the cmd window and run npm --registry https://registry.npm.taobao.org install -g nrm;

3. Runnrm use taobao;

4. Save the above code as process.js and put the file in the same directory as your excel source file;

5. Enter the directory in the cmd window and run npm i node-xlsx;

6. Runnode process.js source.xlsx dest.xlsx (the second parameter is the name of your excel source file, and the third parameter is the name of the excel file you want to save);

Answer 2

Tool: Java POI operates Excel and scans each row and column. According to your business rules, since one sheet cannot fit, put multiple storages. After each row of data is copied, it is placed in a new sheet in Excel. This way It won’t overflow