Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Sun Nov 06, 2016 5:29 pm

I am evaluating Spire XLS for a WPF project. One of my requirements is to add a textbox to a worksheet. From an example on your site, I am using the following code


ITextBox myTextBox = mySheet.TextBoxes.AddTextBox(3, 2, 150, 300);

StringBuilder sb = new StringBuilder();
sb.AppendLine(headerInfo.BorrowerName);
sb.AppendLine(headerInfo.BorrowerAddressLine1);
if (!string.IsNullOrEmpty(headerInfo.BorrowerAddressLine2))
{
sb.AppendLine(headerInfo.BorrowerAddressLine2);
}
sb.AppendLine(headerInfo.BorrowerCity + ", " + headerInfo.BorrowerState + " " + headerInfo.BorrowerZip);
if (!string.IsNullOrEmpty(headerInfo.Contact))
{
sb.AppendLine("c/o " + headerInfo.Contact);
}
sb.AppendLine();
sb.AppendLine("Ph. " + headerInfo.BorrowerPhone);
sb.AppendLine("Fax " + headerInfo.BorrowerFax);
sb.AppendLine(headerInfo.BorrowerEmail);
myTextBox.Text = sb.ToString();

No errors are encountered, but when the resulting xlsx file is opened I receive the following message
"Excel found unreadable content in ....xlsx. do you want to recover the contents of this workbook?

when I click yes I get the message
"Excel was able to open the file by repairing or removing the unreadable content"
Repaired Records: Drawing from /xl/drawings/drawing2.xml part (Drawing shape)

The resulting xlsx file has the Textbox with the correct text.

What am I doing wrong?

thanks

Dan

dchillman
 
Posts: 4
Joined: Thu Nov 03, 2016 2:56 pm

Mon Nov 07, 2016 3:38 am

Hi Dan,

Thanks for your inquiry.
I created a new workbook and then insert a textbox with your code, but didn't find any issue when open result file. Did you add a textbox into an existing file ? If so, please provide us the sample file.
Here is my testing code.
Code: Select all
            Workbook workbook = new Workbook();
            Worksheet mySheet = workbook.Worksheets[0];
            ITextBox myTextBox = mySheet.TextBoxes.AddTextBox(3, 2, 150, 300);
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("123");
            sb.AppendLine("456");
            sb.AppendLine("789");
            myTextBox.Text = sb.ToString();
            workbook.SaveToFile("9005.xlsx", ExcelVersion.Version2010);


Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Mon Nov 07, 2016 1:22 pm

I have attached the template file I use, which is version 2007.

i have references to Spire.License and Spire.XLS.Wpf (file version 7.11.1.7240)
these are the using statements in use;

using Spire.Xls;
using Spire.Xls.Core;

here is my code

private void PopulateIDCSheet(string idcFileName, IDCHeaderInfo headerInfo, List<DisbursalRecords> lstDisbursalRecords )
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(idcFileName,ExcelVersion.Version2007);
Worksheet mySheet = workbook.Worksheets[0];
mySheet.Range["A1"].Value = headerInfo.ProjectName;
mySheet.Range["F3"].Value = headerInfo.ContractNumber;
mySheet.Range["F4"].Value2 = headerInfo.LoanApprovalAmount.Value;
mySheet.Range["F5"].Value2 = headerInfo.LoanAmount.Value;
mySheet.Range["F6"].Value2 = headerInfo.LoanServiceFeeRate.Value;
if (headerInfo.LoanServiceFeeRate == null)
{
mySheet.Range["F7"].Value2 = headerInfo.LoanServiceFee.Value;
}
else
{
mySheet.Range["F7"].Formula = "=F5*F6";
}
mySheet.Range["F9"].Value2 = headerInfo.InterestRate.Value;
mySheet.Range["F10"].Value2 = headerInfo.ContractTermYears;
mySheet.Range["F12"].DateTimeValue = headerInfo.IDCCalculationDate.Value;
if(headerInfo.SubstantialCompletion == null)
{
mySheet.Range["D13"].Value = "Open";
}
else
{
mySheet.Range["D13"].DateTimeValue = headerInfo.SubstantialCompletion.Value;
}
mySheet.Range["F12"].DateTimeValue = headerInfo.IDCCalculationDate.Value;
mySheet.Range["H13"].DateTimeValue = headerInfo.ContractExpirationDate.Value;
//txtBoxBorrower
ITextBox myTextBox = mySheet.TextBoxes[0];

StringBuilder sb = new StringBuilder();
sb.AppendLine(headerInfo.BorrowerName);
sb.AppendLine(headerInfo.BorrowerAddressLine1);
if (!string.IsNullOrEmpty(headerInfo.BorrowerAddressLine2))
{
sb.AppendLine(headerInfo.BorrowerAddressLine2);
}
sb.AppendLine(headerInfo.BorrowerCity + ", " + headerInfo.BorrowerState + " " + headerInfo.BorrowerZip);
if (!string.IsNullOrEmpty(headerInfo.Contact))
{
sb.AppendLine("c/o " + headerInfo.Contact);
}
sb.AppendLine();
sb.AppendLine("Ph. " + headerInfo.BorrowerPhone);
sb.AppendLine("Fax " + headerInfo.BorrowerFax);
sb.AppendLine(headerInfo.BorrowerEmail);
myTextBox.Text = sb.ToString();

//first row
mySheet.Range["F15"].Formula = "=F5";
int cRow = 0;
int iRow = 16;
if(lstDisbursalRecords.Count > 0)
{
//add a line for every record
for(int i = 0;i < lstDisbursalRecords.Count; i++)
{
cRow = iRow + i;
mySheet.Range[cRow, 1].Value = (i + 1).ToString();

mySheet.Range[cRow, 2].DateTimeValue = lstDisbursalRecords[i].DisbursedDate;
mySheet.Range[cRow, 3].Value = lstDisbursalRecords[i].DisbursalDescription;
mySheet.Range[cRow, 4].Value2= lstDisbursalRecords[i].DisbursedAmount;
mySheet.Range[cRow, 5].Formula = "=E" + (cRow - 1).ToString() + "+" + "D" + cRow.ToString();//cumulative loan balance
mySheet.Range[cRow, 9].Formula = "=E" + cRow.ToString() + @"/$F$15";//%complete
mySheet.Range[cRow, 10].Formula = "=$F$12-B" + cRow.ToString() ; //days
mySheet.Range[cRow, 6].Formula = "=$F$15-E" + cRow.ToString(); //balance available to loan
mySheet.Range[cRow, 7].Formula = "=D" + cRow.ToString() + "*$F$9*(J" + cRow.ToString() + @"/365)";//interest
mySheet.Range[cRow, 8].Formula = "=H" + (cRow - 1).ToString() + "+" + "G" + cRow.ToString();

mySheet.Range[cRow, 1, cRow, 8].Borders.LineStyle = LineStyleType.Thin;
mySheet.Range[cRow, 1, cRow, 8].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
mySheet.Range[cRow, 1, cRow, 8].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
mySheet.Range[cRow, 1, cRow, 8].Borders.Color = System.Drawing.Color.Black;
}
}



workbook.CalculateAllValue();
workbook.SaveToFile(idcFileName, ExcelVersion.Version2007);
}
Attachments
IDC Template.zip
this is zipped version of the xlsx template file i use to create new files
(12.42 KiB) Downloaded 295 times

dchillman
 
Posts: 4
Joined: Thu Nov 03, 2016 2:56 pm

Tue Nov 08, 2016 7:21 am

Hi Dan,

Thanks for the information.
I have tested the scenario with the Spire.XLS( WPF file version 7.11.1.7240). But I didn't have the data( IDCHeaderInfo headerInfo, List<DisbursalRecords> lstDisbursalRecords), so I set sample data in cells. And the result file doesn't have any issue when open. So I suppose the data maybe cause the issue. Please provide us the data or a sample project to help us reproduce this issue.
Also, I have attached my result document and here is my testing code.
Code: Select all
            string idcFileName = @"F:\testing\xls test form\sample document\IDC Template.xlsx";
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(idcFileName, ExcelVersion.Version2007);
            Worksheet mySheet = workbook.Worksheets[0];
            mySheet.Range["A1"].Value = "1";
            mySheet.Range["F3"].Value = "2";
            mySheet.Range["F4"].Value2 = "3";
            mySheet.Range["F5"].Value2 = "4";
            mySheet.Range["F6"].Value2 = "5";
            mySheet.Range["F7"].Formula = "=F5*F6";
            mySheet.Range["F9"].Value2 = "7";
            mySheet.Range["F10"].Value2 = "8";
            mySheet.Range["F12"].DateTimeValue = DateTime.Now;
            mySheet.Range["H13"].DateTimeValue = DateTime.Now;
            //txtBoxBorrower
            ITextBox myTextBox = mySheet.TextBoxes[0];
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("123");
            sb.AppendLine("456");
            sb.AppendLine("456");
            sb.AppendLine("456");
            sb.AppendLine("456");
            sb.AppendLine("456");
            myTextBox.Text = sb.ToString();
            //first row
            mySheet.Range["F15"].Formula = "=F5";
            int cRow = 0;
            int iRow = 16;
            if (10 > 0)
            {
                //add a line for every record
                for (int i = 0; i < 10; i++)
                {
                    cRow = iRow + i;
                    mySheet.Range[cRow, 1].Value = (i + 1).ToString();
                    mySheet.Range[cRow, 2].DateTimeValue = DateTime.Now;
                    mySheet.Range[cRow, 3].Value = "11";
                    mySheet.Range[cRow, 4].Value2 = "12";
                    mySheet.Range[cRow, 5].Formula = "=E" + (cRow - 1).ToString() + "+" + "D" + cRow.ToString();//cumulative loan balance
                    mySheet.Range[cRow, 9].Formula = "=E" + cRow.ToString() + @"/$F$15";//%complete
                    mySheet.Range[cRow, 10].Formula = "=$F$12-B" + cRow.ToString(); //days
                    mySheet.Range[cRow, 6].Formula = "=$F$15-E" + cRow.ToString(); //balance available to loan
                    mySheet.Range[cRow, 7].Formula = "=D" + cRow.ToString() + "*$F$9*(J" + cRow.ToString() + @"/365)";//interest
                    mySheet.Range[cRow, 8].Formula = "=H" + (cRow - 1).ToString() + "+" + "G" + cRow.ToString();

                    mySheet.Range[cRow, 1, cRow, 8].Borders.LineStyle = LineStyleType.Thin;
                    mySheet.Range[cRow, 1, cRow, 8].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
                    mySheet.Range[cRow, 1, cRow, 8].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
                    mySheet.Range[cRow, 1, cRow, 8].Borders.Color = System.Drawing.Color.Black;
                }
            }

            workbook.CalculateAllValue();
            workbook.SaveToFile("9005restule.xlsx", ExcelVersion.Version2007);


Sincerely,
Betsy
E-iceblue support team
Attachments
9005restule.zip
(13.44 KiB) Downloaded 304 times
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Thu Nov 10, 2016 7:16 am

Hi Dan,

How is the issue now ? Could you please give us some feedback at your convenience ?

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Thu Nov 10, 2016 1:01 pm

i still get the unreadable content message when opening the spreadsheet. due to project deadlines I have had to go another route. thanks for you time and assistance.

Dan
Attachments
Capture.JPG
screen message
Capture.JPG (30.88 KiB) Viewed 6295 times

dchillman
 
Posts: 4
Joined: Thu Nov 03, 2016 2:56 pm

Fri Nov 11, 2016 2:24 am

Hi Dan,

So sorry that the result document still has issue.
I opened it with Excel10 and Excel13 before, so didn't notice the issue, so sorry for this.
Now I open the result document with Excel07, and have noticed the issue, I have posted it to our Dev team. We will inform you when it is fixed.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Fri Jan 06, 2017 7:56 am

Hi Dan,

Thanks for your waiting.
Now the issue has been resolved in Spire.XLS Pack Hotfix Version:7.11.34, welcome to test it.
If there is any question, welcome to get it back to us.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Jan 10, 2017 7:59 am

Hi Dan,

Did you test the hotfix ? Has the issue been resolved ?

Thanks,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Jan 24, 2017 4:33 pm

Hello,

I am getting unreadable content as well and I took EVERYTHING out of my workbook.

Workbook wb = new Workbook();
wb.CreateEmptySheets(1);
Worksheet xlsWorkSheet = wb.Worksheets[0];
wb.SaveToStream(ms, FileFormat.Version2013);

I am trying to open the spreadsheet in Excel 2016.

Will the hotfix you posted resolve this issue? I just got the latest free spire from nuget and the version is 7.9.1

Thanks,

Karen

karen.healey@communitysoftwaregroup.com
 
Posts: 1
Joined: Tue Jan 24, 2017 4:25 pm

Wed Jan 25, 2017 2:36 am

Dear Karen,

Thanks for your inquiry.
I have tested the code with the latest Spire.XLS Pack Hotfix Version:7.11.49, and I can open the result file with Excel2016 succesfully. Please use this version.
If there is any question, please let me know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Sat Feb 04, 2017 6:17 am

Dear Karen,

Did you test the hotfix ? Has the issue been resolved ?

Thanks,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Return to Spire.XLS