I have an Excel template with only headers with some style set. The header is the first row of the sheet. While adding data to the sheet using getCells().importArrayList(java.util.ArrayListarrayList, intfirstRow, intfirstColumn, booleanisVertical) method, where I have given the value of intfirstRow as 0. Then the header was missing. When I gave the value of intfirstRow as 1, I got the header but without any style. Why it is happening? The same code(with intfirstRow as 0) was working fine with Aspose 2.0.0 version. Then we were using importCollection() in place of importArrayList(). Recently we upgraded to Aspose 7.3.4. What could be the reason for this behaviour?
Hi there,
Thank you for using Aspose products, and welcome to the Aspose.Cells forum.
I have evaluated your said issue with latest version of Aspose.Cells for Java v7.6.0 and the simplest code as provided below. The results produced are as expected, i.e; the imported data is injected at proper place while retaining the style of cells where data is injected (header row). Please check the input and output files for your reference.
Java
//Instantiating an ArrayList object
ArrayList list=new ArrayList();
//Add few names to the list as string values
list.add(“John”);
list.add(“Mickel”);
list.add(“Diana”);
list.add(“Celtic”);
//Load workbook
Workbook book = new Workbook(myDir+“book1.xlsx”);
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
//Importing the contents of ArrayList to 1st row and first column vertically
cells.importArrayList(list,0,0,false);
//Save workbook
book.save(myDir+“OUTPUT.XLSX”);
I would request you to please give the latest version a try on your end with your template file. In case you face any difficulties, please provide us your template as well as source code to reproduce the issue on our end.
Hi ,
Thanks for your reply.
The importArrayList is working for me with 7.3 version. The issue is I have template with headers as the first row. The data insert should happen from the second row. The header is having one style and for the data tha style is different. and for the data the style is set by column wise. Now what is happening is when I insert data to the template, the header style is getting overridden by the column style. But for the older version the same code was working fine. I have shared the code and the template(Template.xlsx).
In code, we set the style of the column first and then insert data.
final Worksheet sheet0 = book.getWorksheets().get(0);
final Style styleDates = getStyle(DATE_STYLE);
final Style styleOther = getStyle(DEFAULT_STYLE);
for (final String key : fieldMap.keySet()) {
int columnIndex = fieldMap.get(key).column();
Column column = sheet.getCells().getColumns()
.get(columnIndex);
if (datecolumnlist.contains(key)) {
column.applyStyle(styleDates, styleFlag);
} else {
column.applyStyle(styleOther, styleFlag);
}
}
for (final String key : fieldMap.keySet()) {
ArrayList list = new ArrayList();
//add data to arrayList
sheet.getCells().importArrayList(list, 1, fieldMap.get(key).column(), true);
}
Hi,
Hi,
I am still facing the issue. Please find below a sample program. Attached is the template.
package caf.opm.cpm.server.service.impl;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import com.aspose.cells.Column;
import com.aspose.cells.Style;
import com.aspose.cells.StyleFlag;
import com.aspose.cells.TextAlignmentType;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
public class TestClass {
/**
* @param args
*/
public static void main(String[] args) {
InputStream streamedFile = null;
try {
streamedFile = new FileInputStream(new File("C:\\Template.xlsx"));
final byte[] templateData = new byte[streamedFile.available()];
int totalBytes = 0, readBytes = 0;
do {
totalBytes += readBytes;
int sizeof = Math.min(1024, templateData.length - totalBytes);
readBytes = streamedFile.read(templateData, totalBytes, sizeof);
} while (readBytes > 0);
final ByteArrayInputStream arrayInputStream = new ByteArrayInputStream(
templateData);
Workbook workbook = new Workbook();
workbook.getWorksheets().add();
workbook = new Workbook(arrayInputStream);
final Worksheet sheet0 = workbook.getWorksheets().get(0);
final Style dateStyle = workbook.createStyle();
dateStyle.setHorizontalAlignment(TextAlignmentType.CENTER);
dateStyle.getFont().setSize(9);
final Style defaultStyle = workbook.createStyle();
defaultStyle.setHorizontalAlignment(TextAlignmentType.LEFT);
defaultStyle.getFont().setSize(9);
defaultStyle.setNumber(10);
StyleFlag styleFlag = new StyleFlag();
styleFlag.setAll(true);
Column column0 = sheet0.getCells().getColumns()
.get(0);
column0.applyStyle(dateStyle, styleFlag);
Column column1 = sheet0.getCells().getColumns()
.get(1);
column1.applyStyle(defaultStyle, styleFlag);
Column column2 = sheet0.getCells().getColumns()
.get(2);
column2.applyStyle(defaultStyle, styleFlag);
ArrayList list0 = new ArrayList();
list0.add("Col0 Data0");
list0.add("Col0 Data1");
sheet0.getCells().importArrayList(list0, 1, 0, true);
ArrayList list1 = new ArrayList();
list1.add("Col1 Data0");
list1.add("Col1 Data1");
sheet0.getCells().importArrayList(list1, 1, 1, true);
ArrayList list2 = new ArrayList();
list2.add("Col2 Data0");
list2.add("Col2 Data1");
sheet0.getCells().importArrayList(list2, 1, 2, true);
String fileName = "DataExcel";
byte[] buffer = null;
File file = File.createTempFile(fileName, ".xlsx");
workbook.getWorksheets().setActiveSheetIndex(0);
workbook.save(file.getAbsolutePath());
final int fileLength = (int) file.length();
buffer = new byte[fileLength];
InputStream streamedFile1 = new FileInputStream(file);
streamedFile1.read(buffer);
streamedFile1.close();
FileOutputStream stream = new FileOutputStream(file);
stream.write(buffer);
stream.close();
Runtime.getRuntime().exec(
"cmd /c \"" + file.getAbsolutePath() + "\"");
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (streamedFile != null) {
streamedFile.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
Hi,
Hi Amjad,
Its working...
Thank you so much...
Hi,