Hello,
I have problems with cell containing Carriage Return and Column/Row Auto Sizing.
I will compare the behaviors of Excel and Aspose.Excel
1. When putting the value in a cell containing Carriage Return :
- Excel set TextWrapped of the cell to true, and adjust the row height.
- Aspose.Excel does not set the TextWrapped to true. But, when i manually set the TextWrapped to true (before or after putting value), the row height is not adjust.
|
|
public void Test() {
Excel xls = new Excel();
Worksheet wks = xls.Worksheets[0]
wks.Cells[0,0].Style.IsTextWrapped = true;
wks.Cells[0,0].PutValue(“1st Line\r\n2nd Line”);
xls.Save(“C:\Test.xls”);
}
|
See Problems.jpg for the Excel outputs
2. The AutoFitColumn does not work.
- Excel adjust the width of the cell correctly
- Aspose.Excel seems not to taking accound of TextWrapped Cells. In the first test, the AutoFitColumn does not work at all in my test sample (ColumnWidth before and after is the same). I have added a short text in the second row, and I realized the AutoFitColumn adjust the width for this cell.
|
|
public void Test() {
Excel xls = new Excel();
Worksheet wks = xls.Worksheets[0]
wks.Cells[0,0].Style.IsTextWrapped = true;
wks.Cells[0,0].PutValue(“1st Line\r\n2nd Line”);
wks.Cells[1,0].PutValue(“ABC”);
wks.AutoFitColumn(0)
xls.Save(“C:\Test.xls”);
}
|
See Problem.jpg for the Excel outputs
Do you have solution for those problems.
For the second problem, I have try AutoFitRow, but my Excel file has 10000 rows, and it take a long time.
But for the first problem, I can’t see what I can do, except heavy workaround, like :
a. Setting IsTextWrapped = false
b. Putting the larger line in cell (when I detect a Carriage Return)
c. Calling AutoFitColumn
d. Setting IsTextWrapped = true
e. Putting in the cell the real value
f. Calling AutoFitRow
Thank you.
For the second issue, you don’t need to use your workaround. Just try the following code:
Excel xls = new Excel();
Worksheet wks = xls.Worksheets[0]
wks.Cells[0,0].Style.IsTextWrapped = true;
wks.Cells[0,0].PutValue(“1st Line\r\n2nd Line”);
wks.AutoFitRow(0);
wks.Cells[1,0].PutValue(“ABC”);
wks.AutoFitColumn(0)
xls.Save(“C:\Test.xls”);
When you set TextWrap property in MS Excel, it calls AutoFit method internally. It’s also time-consuming. You don’t find it because you manually set it in MS Excel. If you use VBA automation, you will see how it works.
To minimize the overhead, please call AutoFit method as less as possible. You can verify the cell content first to see if the AutoFit method should be called.
I have make choice to swap to Aspose.Excel to stop using Excel Automation.
I used Excel or Aspose.Excel for reporting issue.
Using Excel Automation is not confortable and many problems occurs.
I have found your component and it was a good revelation for me.
But, for the specific problem of cell size, it works perfectly with Excel Automation with this lines :
|
|
// Get Range that will received datas
Range rng = excel.GetRange(1,1,maxRow,maxCol);
// Before copy, we set WrapText to false. datas must take only one row (for future autosize)
rng.WrapText = false;
// Copy Data.
…
/* Final working on size :
* We need a readable worksheet, even with enormous comment string.
* We want also a nice worksheet, with a auto adjust cell size.
* But if a big comment string, the cell must have a limited width and auto adjust his height.
* The cell with carriage return must be auto adjust in height.
* So the fallowing code do this:
* 1. Cells must be set to WrapText false (allready done before data inserting)
* 2. We set the max width for cells
* 3. We reset the WrapText for cells to TRUE (With this action, cells with big comment or CR will takes several rows)
* 4. We call AutoFit() in columns to adjust automatically
* 5. We set the Vertical Alignment to Top (it is nicer when several cells in the same row are multiline
* 6. Finally, we call AutoFit() in rows
*/
rng.ColumnWidth = WIDTH_MAX_COLUMN;
rng.WrapText = true;
rng.Columns.AutoFit();
rng.VerticalAlignment = Constants.xlTop;
rng.Rows.AutoFit();
|
It takes a very short time to execute : less than 2 seconds for 10000 lines.
I think I will develop a self-made AutoFit() function, but could you please say to me how your AutoFit function works.
I have read in this forum that you use GDI or GDI+.
Could you say more :
For exemple, are you using Graphics.MeasureString to determine the size of a string or are you calculating approximatly with the width fond (x number of char in string), Do you use special algorithm to reduce the strings to analyse, … ?
Thank you very much.
Could you please tell me the time to execute with Aspose.Excel with your 10000 lines of data?
At first I tried to use GDI+ Graphics.MeasureString method to determine the size of a string. However it’s not accurate for MS Excel use GDI. Now I use GDI to calculate heigh and width.
Thank for you suggestion. I do need to consider some special algorithm to reduce the strings to analyse.
Here is the results of 2 tests:
1. Testing Data Fill And AutoSizing in all Aspose.Excel.
2. Testing Data Fill with Aspose.Excel (without Sizing consideration), Launching Excel in Automation to do the AutoSizing.
I have run this 2 tests with :
- 10000 rows / 15 cols
- 10000 rows / 7 cols
- 5000 rows / 15 cols
- 5000 rows / 7 cols
The C# codes for both tests:
TEST1:
|
|
// Data Init
Aspose.Excel.Excel xls = new Aspose.Excel.Excel();
Aspose.Excel.Worksheet sh = xls.Worksheets[0];
Aspose.Excel.Style stl = xls.Styles[ xls.Styles.Add()];
stl.IsTextWrapped = false;
object[,] data = new object[MAX_ROWS,MAX_COLS];
....FILL DATA....
// Aspose Fill
sh.Cells.ImportTwoDimensionArray(data,0,0);
// AutoFit Columns (and resize if too large)
for (byte i=0; i<MAX_COLS; i++) {
sh.AutoFitColumn(i);
if (sh.Cells.GetColumnWidth(i)>45) {
sh.Cells.SetColumnWidth(i,45);
}
}
// AutoSize Row (with WrapText To true)
stl.IsTextWrapped = true;
for (int i=0; i<MAX_ROWS; i++) {
sh.AutoFitRow(i);
}
// Save
string fileName = @"C:\Technique\ProofOfConcept\AsposeExcelTest\Output\Test1.xls";
xls.Save(fileName);
|
TEST2:
|
|
// Data Init
Aspose.Excel.Excel xls = new Aspose.Excel.Excel();
Aspose.Excel.Worksheet sh = xls.Worksheets[0];
Aspose.Excel.Style stl = xls.Styles[ xls.Styles.Add()];
stl.Font.Name = "Tahoma";
stl.IsTextWrapped = false;
object[,] data = new object[MAX_ROWS,MAX_COLS];
....FILL DATA....
// Aspose Fill
sh.Cells.ImportTwoDimensionArray(data,0,0);
// Save
__Log("Aspose Save");
string fileName = @"C:\Technique\ProofOfConcept\AsposeExcelTest\Output\Test2.xls";
xls.Save(fileName);
// Excel Load
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Workbooks.Open(@"C:\Technique\ProofOfConcept\AsposeExcelTest\Output\Test2.xls",Missing.Value,
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value); // No Comment
Microsoft.Office.Interop.Excel.Worksheet wsh =
(Microsoft.Office.Interop.Excel.Worksheet)app.Workbooks[1].Worksheets[1];
Microsoft.Office.Interop.Excel.Range rng = wsh.get_Range(wsh.Cells[1,1],wsh.Cells[MAX_ROWS,MAX_COLS]);
// Excel Autosize
rng.ColumnWidth = 45;
rng.WrapText = true;
rng.Columns.AutoFit();
rng.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlTop;
rng.Rows.AutoFit();
// Excel Save
app.Workbooks[1].Save();
app.Workbooks[1].Close(false,Missing.Value,Missing.Value);
|
The results of the code (time is in seconds)
Test in a notebook, HP Compaq NC61200, P IV Centrino 1,7 GHz.
RESULT TEST 1 : ALL ASPOSE
| Action | 10000/15 | 10000/7 | 5000/15 | 5000/7 |
| Data Init | 1.5 | 0.8 | 0.7 | 0.4 |
| Aspose Fill | 0.3 | 0.1 | 2.0 | 0.6 |
| Aspose AutoFit Col | 17.9 | 7.3 | 7.8 | 3.5 |
| Aspose AutoFit Row | 115.8 | 44.6 | 32.3 | 12.1 |
| Aspose Save | 9.0 | 3.6 | 2.7 | 1.0 |
RESULT TEST 2 : ASPOSE + AUTOMATION
| Action | 10000/15 | 10000/7 | 5000/15 | 5000/7 |
| Data Init | 1.6 | 0.8 | 0.8 | 0.4 |
| Aspose Fill | 0.4 | 0.1 | 0.2 | 0.1 |
| Aspose Save | 8.7 | 3.4 | 2.6 | 1.1 |
| Excel Load | 2.0 | 1.8 | 1.6 | 1.6 |
| Excel AutoSize | 6.4 | 3.3 | 3.2 | 1.7 |
| Excel Save | 1.1 | 0.7 | 0.7 | 0.5 |
I definitly cannot use AutoFitColumn nor AutoFitRow directly without modification.
I will think to a pragmative solution.
In my case, i don't need a precise AutoFit. Il use in all cells the save font (It is just a simple table report) and the cells containing Carriage Return or large text is the exception.
It is a pity, with my old solution in Excel Automation, it was the Fill in Excel that was too long, and the sizing was comparatively ridiculous.
Thank you for your help.
The time of AutoFit is really too long. I will check and optimize it. It may take about 2-3 weeks for this enhancement.