https://bz.apache.org/bugzilla/show_bug.cgi?id=63973
zach changed:
What|Removed |Added
Status|NEEDINFO|NEW
--- Comment #2 from zach ---
```
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class SlowExcelWriter {
public static void main(String[] args) {
SlowExcelWriter slowExcelWriter = new SlowExcelWriter();
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet("sheet1");
sxssfSheet.setRandomAccessWindowSize(-1);
String rowName = "Date";
List> measureNames =
Arrays.asList(Optional.of("Sales"), Optional.of("GC"), Optional.of("AC"));
int dataUnitLength = 5;
Object[] data1= {"2019-11-19", "city1", "1.23", 223, "39.54"};
Object[] data2= {"2019-11-20", "city1", "11000.23", 233, "69.54"};
Object[] data3= {"2019-11-19", "city2", "41000.23", 833, "49.54"};
Object[] data4= {"2019-11-20", "city2", "21000.23", 433, "89.54"};
//it is very slow when there is more than 3000 cities;
Map> sortCityMap = new HashMap<>();
sortCityMap.put("city1",Arrays.asList(data1, data2));
sortCityMap.put("city2",Arrays.asList(data3, data4));
Map> sortDateMap = new HashMap<>();
sortDateMap.put("2019-11-19",Arrays.asList(data1, data3));
sortDateMap.put("2019-11-20",Arrays.asList(data2, data4));
slowExcelWriter.writeMap2Sheet4RowColMeasure(sxssfSheet, rowName, 0, 1,
0, sortCityMap, measureNames, sortDateMap, dataUnitLength);;
try {
OutputStream outputStream = new
FileOutputStream("output3000cities.xlsx");
sxssfWorkbook.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
private void writeMap2Sheet4RowColMeasure(Sheet sheet,
String rowName,
int firstRow,
int lastRow,
int firstCol,
Map>
sortCityMap,
List>
headerUnitList,
Map>
sortDateMap,
int arrayLength) {
int lastCol = firstCol;
int mergedRowColLastCol = firstCol;
createMergedCell(sheet, firstRow, lastRow, firstCol,
mergedRowColLastCol, rowName);
List rowData = new ArrayList<>(sortDateMap.keySet());
writeRowHeaderData(sheet, lastRow + 1, firstCol, rowData);
int headerUnitSize = headerUnitList.size();
Object[] mergedHeaders = sortCityMap.keySet().toArray();
int mergedCellFirstCol = mergedRowColLastCol + 1;
int mergedCellLastCol = mergedRowColLastCol + headerUnitSize;
int measureHeaderFirstCol = mergedRowColLastCol + 1;
int dataFirstCol = mergedRowColLastCol + 1;
for (int i = 0; i < mergedHeaders.length; i++) {
lastCol = lastCol + headerUnitSize;
createMergedCell(sheet, firstRow, lastRow - 1, mergedCellFirstCol,
mergedCellLastCol, String.valueOf(mergedHeaders[i]));
mergedCellFirstCol = mergedCellLastCol + 1;
mergedCellLastCol = mergedCellLastCol + headerUnitSize;
createMeasureHeader(sheet, lastRow, measureHeaderFirstCol,
headerUnitList);
measureHeaderFirstCol = measureHeaderFirstCol + headerUnitSize;
List colData = sortCityMap.get(mergedHeaders[i]);
writeArrayData(sheet, lastRow + 1, dataFirstCol, colData, rowData,
arrayLength);
dataFirstCol = dataFirstCol + headerUnitSize;
if (0 == i % 500) {
System.out.println("finish " + i + " col data: " +
System.currentTimeMillis());
}
}
}
private void createMergedCell(Sheet sheet, int firstRow, int lastRow, int
firstCol, int lastCol, String value) {
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow,
firstCol, lastCol);
sheet.addMergedRegionUnsafe(region);
Row row = sheet.getRow(firstRow);
if (row == null) {
row = sheet.createRow(firstRow);
}
Cell cell = row.createCell(firstCol);
cell.setCellValue(value);
CellStyle cellStyle = se
