Preserving Cell Size from old to new worksheet

[mwirzbicki]

Greetings,

I'm a newbie to Aspose.Cells (v7.0.2) but I am trying to copy from one worksheet to another an want to preserve cell width (it's different in 6 different row) but everything new is being resized down to 8.11. I've tried apose.cells.CopyStyle/CopyData but to no avail. I get data right but column width still reduced.

WithIn Xcell you can do a copy and then paste special (column width) which does what I want but where does that feature translate in Aspose? (Java on a Windows platform).

Thanks Very Much in advance!

Mike W.

[shakeel.faiz]

Hi,

I think, you should copy range data with style instead.

Please see the following article and see if it works for you. The code is in .NET but you can easily port it to Java.

Copy Range Data with Style

[mwirzbicki]

I've looked at the code suggested and it's setting up a style in the new workbook and not coping an existing workbook/sheet. That's what I am trying to do because the existing workbook is formated correctly and all I am trying to do is copy it existing cell settings.

Again I have setup a range in the old workbook and did a copyStyle and copyData but the width in the new cells all get the same size of 8.11 which is not correct.

Any other ideas/help is greatly appreciated.

Mike W

[shakeel.faiz]

Hi,

What aspose.cells does is same with the behavior of Ms Excel. In Excel when you copy a cell range, you cannot get the cell size (column width) be copied too.

To get what you expected, we think you can reset the column width in given column range of the new sheet. The sample code:

Java

Cells cellsS = worksheets.get(0).getCells();

Cells cellsD = worksheets.get(1).getCells();

Range rS = cellsS.createRange(“A2:B3”);

Range rD = cellsD.createRange(“B5:C6”);

rD.copy(rS);

int colS = rS.getFirstColumn();

int colD = rD.getFirstColumn();

int count = rS.getColumnCount();

for(int i=0; i<count; i++)

{

cellsD.setColumnWidth(colD+i, cellsS.getColumnWidth(colS+i));

}

[mwirzbicki]

Hi,

Thanks for the sample source code. It did help to further me along but I had and interesting effect. While it did expand each column row it did so at a larger size than the original.

For instance Orginal WorkSheet A and New WorkSheet B

Column A_Src = 28.89 but B_Des = 37.33,

Column B_Src = 26.78 but B_Des = 34.67

Column C_Src = 8.89 but B_Des = 11.67

Question: There shouldn't be any type of autoformating going on right? Or is it possible that it's taking the max length of text and not just column width?

Getting closer to the solution so thanks so far!

Mike W..

[shakeel.faiz]

Hi,

With code like
cellsD.setColumnWidth(colD+i, cells.getColumnWidth(colS+i));
we cannot find the issue.

Would you please provide us your code and template file to reproduce the issue? And another notable thing about column width: with different default font of the workbook, the column width will be measured as different in pixels/points with same width in characters. And vice vice versa for same width in pixels/points to different value in characters. So please make sure you are using the same unit for getting/setting column width.

For autoformatting for column width, we think you should use Worksheet.autoFitColumn(int) or Worksheet.autoFitColumns().

[mwirzbicki]

Greetings, Below is a code snipit that I am using to get the column size. I have also given you the xcell spreadsheet that I am coping all the information from into a new spreadsheet. Any other questions just ask. Again Thanks in Advance for all the help!

============ code below the line ===============================

com.aspose.cells.Workbook mysrcwork = new com.aspose.cells.Workbook (srcFilePath);

Workbook workbook = new AsposeWorkbook(srcFilePath);

Cells cellsS = mysrcwork.getWorksheets().get(0).getCells();

logger.warn("[SrcFile] - Column 0 width: |" + cellsS.getColumnWidth(0)+"|");

[mwirzbicki]

Oh btw... The way I determine column with size is to open the xcell spreadsheet with MS Excel 2007, select column A, right click on it and select "column widh:" which displays 28.89. Apose getColumnWidth returns 37.43.

Thanks Again!

[Amjad_Sahi]

Hi,

I am using v7.0.4.7 (Please download and try: Aspose.Cells for Java v7.0.4.7). It works fine. I got 28.95 for the first column in your attached template file. Here is my sample code.

Sample code:

com.aspose.cells.Workbook mysrcwork = new com.aspose.cells.Workbook (“ASPOSE_TEST_FILE.xls”);

Cells cellsS = mysrcwork.getWorksheets().get(0).getCells();

System.out.println(“Column 0 width: |” + cellsS.getColumnWidth(0)+"|"); // Column 0 width: |28.95|

[mwirzbicki]

Hi,

I realize that I used Xcell 2007 to view the file before I sent it out to you. I inadvertanly save it which reformated it. This second version acts like I stated earlier. Run Aspose against it and you will get the 37.43 width. Open it will xcell (without saving it) and your width is 28.*

I appologize again for the confusion. And Thank-you again in advance!

[shakeel.faiz]

Hi,

In fact your attached xls file is an html format file. In this file there is no workbook default font defined.

When ms excel opens such kind of file, system’s default font will be used to measure the column width in characters unit. So, when you view this template file in ms excel in different environment, different width value(in characters) will be shown(at my end here I got 26.00 in ms excel which is different from yours).

For Aspose.Cells, we cannot get the system default font when reading such kind of template file. So we just use Arial 10 as the default font and determine the column width in characters. If the default font used by ms excel or your Xcell2007 is not Arial 10, then the shown column width will be different from what you can get by aspose.cells.

In your template file, the column width is defined in points. So, with aspose.cells, you can get the column width by pixels, points or inches which does not depend on the default font of workbook and the returned value by aspose.cells should be same with what you get in msexcel or Xcell2007.

[mwirzbicki]

Now things are beginning to make sense. So I went and tried using getColumnWidthPixels method expecting that the column width would be the same as with xcell but instead I recieved simular results (37.33) as before. Btw: my font and size used in the template is Ariel 8.

Call used below:

cellsD.setColumnWidthPixel(colD+i, cellsS.getColumnWidthPixel(colS+i));

What html syntax is apose call looking for to pickup the default font/size from wthin the src file.

Thanks-again all this has been good information!

Mike W.

[shakeel.faiz]

<!–[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

<![endif]–>

Hi,

When you use:

cellsD.setColumnWidthPixel(colD+i, cellsS.getColumnWidthPixel(colS+i));

the value of

cellsD.getColumnWidthPixel(colD+i);

should be same with

cellsS.getColumnWidthPixel(colS+i);

and also be same with what shown in ms excel or Xcell(in unit of pixels). However, because of the different default font used by aspose.cells and ms excel or Xcell, the values of

cellsD.getColumnWidth(colD+i);

cellsS.getColumnWidth(colD+i);

will both be different from the value shown in ms excel or Xcell.

For ms excel saved html file, the default font defined in “.style0” in element. When you save an excel file(such as, create one new workbook then save) to htm by ms excel, you can find such a definition in the htm file.

[mwirzbicki]

Thank you for your response. I added the html tags that I got from xcel after saving the sample file to XML format. I added the following to my ASPOSE_TEST_2.XLS file into section.

(was inital xcekk value before I changed)

ASPOSE calls now return the column values the way I expected (28.89) But now xcell (when used) change the size of it's column width because it's "Styles Default" changed.

Question: "The Tool" (which is Eclipse-BIRT) seem to be standard between itself and xcell without the "Style Default". Why can't ASPOSE use the same standard/rules to figure out column width (or again am I missing something).

thanks in advance again!

[shakeel.faiz]

Hi,

For eclipse related tools, we think maybe they are using apis such as org.eclipse.swt.* to get the system default font.

We are afraid there is no direct way in JDK’s common API and the implementation for detecting system font are complicated, such as org.eclipse.swt.*, there are many native methods need to be used.

So we are afraid we cannot support to detect system font automatically.

However, if it can fit your requirement, we can provide a method for users to set system default font when loading such kind of files, so when users specify the correct font, the column width should be set correctly.