https://bz.apache.org/bugzilla/show_bug.cgi?id=69557
--- Comment #1 from Gonzalo Aguilar Delgado <gaguilardelg...@medallia.com> ---
Forgot to mention that it only happens with EXCEL2007
I wrote few test cases...
private static File getTempFile(String resourceName) throws IOException
{
return File.createTempFile(resourceName, ".xlsx");
}
private static File setupTempExcelFileWithRecords(int records) throws
IOException {
var customTempFile = getTempFile("TestExcel");
TestDataGenerator.writeExcel(records, customTempFile);
generatedFile = true;
return customTempFile;
}
This will create an excel file. And the class I use is this one:
import com.google.common.collect.ImmutableMap;
import com.google.gson.GsonBuilder;
import common.Common;
import common.propernames.ProperNames;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import tiny.Clock;
import tiny.CollUtils;
import tiny.Crypto;
import tiny.Empty;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.util.List;
import java.util.Map;
import javax.annotation.Nonnull;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
/**
* Simple utility class used to generate test data for uploading to Auto
Importer.
*/
public class TestDataGenerator {
/** Generate the test data and write it to a JSON file in the current
directory */
public static void writeJson(int records, String filePath) throws
IOException {
List<Map<String, String>> values = generateValues(records);
if (!values.isEmpty()) {
try (Writer w = Common.asPrintWriterUTF8(new
FileOutputStream(filePath))) {
w.write(new
GsonBuilder().setPrettyPrinting().create().toJson(values));
}
}
}
public static List<Map<String, String>> generateValues(int records) {
List<Map<String, String>> values = Empty.list();
int names = (int) Math.sqrt(records);
DateTime now = Clock.getDateTimeNow();
List<String> firstnames = CollUtils.subList(names,
ProperNames.humanFirstNames);
List<String> lastnames = CollUtils.subList(names,
ProperNames.humanLastNames);
for (int i = 0; i < records; i++) {
String firstname = CollUtils.randomElement(firstnames);
String lastname = CollUtils.randomElement(lastnames);
values.add(createRow(firstname, lastname, now));
}
return values;
}
public static ImmutableMap<String, String> createRow(String firstname,
String lastname, DateTime now) {
return ImmutableMap.of(
"firstname", firstname,
"lastname", lastname,
"email", firstname + "." + lastname +
"@example.com",
"property", "ABZPD" + Crypto.nextInt(10000000),
"checkout",
Common.ISO_DATE_FORMATTER.print(now.minusDays(Crypto.nextInt(30))));
}
public static void writeExcel(int records, File file) throws
IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("TestSheet");
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 4000);
Row header = sheet.createRow(sheet.getLastRowNum() + 1);
CellStyle headerStyle = defaultHeaderCellStyle(workbook);
XSSFFont font = defaultFontStyle((XSSFWorkbook) workbook);
headerStyle.setFont(font);
setHeaders(header, headerStyle);
if (records > 0) {
for (Map<String, String> generatedRow :
TestDataGenerator.generateValues(records)) {
createRow(generatedRow, sheet);
}
}
FileOutputStream outputStream = new FileOutputStream(file);
workbook.write(outputStream);
workbook.close();
outputStream.flush();
outputStream.close();
}
private static void createRow(Map<String, String> generatedRow, Sheet
sheet) {
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
int index = 0;
for (Map.Entry<String, String> entry : generatedRow.entrySet())
{
Cell rowCell = row.createCell(index++);
rowCell.setCellValue(entry.getValue());
}
}
private static void setHeaders(Row header, CellStyle headerStyle) {
var row = TestDataGenerator.createRow("firstname", "lastname",
DateTime.now());
int index = 0;
for (String key : row.keySet()) {
Cell headerCell = header.createCell(index++);
headerCell.setCellValue(key);
headerCell.setCellStyle(headerStyle);
}
}
private static @Nonnull XSSFFont defaultFontStyle(XSSFWorkbook
workbook) {
XSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 10);
font.setBold(true);
return font;
}
private static @Nonnull CellStyle defaultHeaderCellStyle(Workbook
workbook) {
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return headerStyle;
}
public static void writeCSV(int records, String filePath) throws
IOException {
List<Map<String, String>> data = generateValues(records);
if (!data.isEmpty()) {
try (FileWriter writer = new FileWriter(filePath)) {
// Write header
Map<String, String> headerMap = data.get(0);
writer.append(String.join(",",
headerMap.keySet()));
writer.append("\n");
// Write data rows
for (Map<String, String> row : data) {
writer.append(String.join(",",
row.values()));
writer.append("\n");
}
}
}
}
public static void writeXml(int records, String filePath) throws
IOException {
List<Map<String, String>> data = generateValues(records);
try {
// Create XML document
DocumentBuilderFactory documentFactory =
DocumentBuilderFactory.newInstance();
DocumentBuilder documentBuilder =
documentFactory.newDocumentBuilder();
Document document = documentBuilder.newDocument();
// Create root element
Element root = document.createElement("root");
document.appendChild(root);
// Convert data to XML elements
for (Map<String, String> record : data) {
Element recordElement =
document.createElement("record");
for (Map.Entry<String, String> entry :
record.entrySet()) {
Element element =
document.createElement(entry.getKey());
element.appendChild(document.createTextNode(entry.getValue()));
recordElement.appendChild(element);
}
root.appendChild(recordElement);
}
// Write XML document to file
TransformerFactory transformerFactory =
TransformerFactory.newInstance();
Transformer transformer =
transformerFactory.newTransformer();
transformer.setOutputProperty(OutputKeys.INDENT,
"yes");
DOMSource domSource = new DOMSource(document);
StreamResult streamResult = new StreamResult(new
File(filePath));
transformer.transform(domSource, streamResult);
} catch (Exception e) {
e.printStackTrace();
}
}
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org
