When Importing an Excel file with Zip and Zip +4- import errors if 1st row contains Zip (and not zip+4)

Aspose.Cells Product Family
1

When Importing an Excel file with Zip and Zip +4, import errors if 1st row contains Zip (and not zip+4)

For example, if the first row contains zip+4, Aspose imports all remaining records as text (which is fine).

However, if the first row contains only a numeric zip, then the first occurence of a alphanumeric field (ex. 21202-1234) will cause the import to fail.

Formatting the field in the Excel template as TEXT does not work. The only work around so far is to make the first row a zip+10. However, this is not a good work around because we are expecting end-user quality data (i.e. junk, zip, zip+4)!

Thank you in advance!

Error:

FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2755599
System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt) +117
System.Double.Parse(String s, NumberStyles style, NumberFormatInfo info) +189
System.Convert.ToDouble(String value, IFormatProvider provider) +43
System.String.System.IConvertible.ToDouble(IFormatProvider provider) +4
System.Data.Common.DoubleStorage.Set(Int32 record, Object value) +101
System.Data.DataColumn.set_Item(Int32 record, Object value) +37

[ArgumentException: Input string was not in a correct format.Couldn't store <27609-1234> in Column10 Column. Expected type is Double.]
System.Data.DataColumn.set_Item(Int32 record, Object value) +72
System.Data.DataRow.set_Item(DataColumn column, Object value) +194
System.Data.DataRow.set_Item(Int32 columnIndex, Object value) +25
Aspose.Cells.Cells.ExportDataTable(Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber) +1264
TransFile.ExcelImport(String FileLocation) in c:\inetpub\wwwroot\nowtg.com\wwwroot\Staging\App_Code\BLL\TransFile.cs:65
TransFile.ProcessFile(String FileName, String targetFolder) in c:\inetpub\wwwroot\nowtg.com\wwwroot\Staging\App_Code\BLL\TransFile.cs:117
User_Apps_SCMP01_Step2.ProcessFile(UploadedFile file, String targetFolder) in c:\inetpub\wwwroot\nowtg.com\wwwroot\Staging\User\Apps\FSI01\UserControls\Step2.ascx.cs:176
User_Apps_SCMP01_Step2.BindValidResults() in c:\inetpub\wwwroot\nowtg.com\wwwroot\Staging\User\Apps\FSI01\UserControls\Step2.ascx.cs:141
User_Apps_SCMP01_Step2.buttonSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\nowtg.com\wwwroot\Staging\User\Apps\FSI01\UserControls\Step2.ascx.cs:129
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

This message was posted using Page2Forum from Technical Articles - Aspose.Cells for .NET and Java
2

Hi,

We are not clear about the problem you mentioned.

Could you give us more details and do create a console application and post us here with all the files (input/output file, other files etc.) to show and reproduce the problem. We will check and fix it soon if found the problem.

Thank you,

3

Hi,

By the way, if you are exporting data to fill a datatable, I think you may try the attached version(4.5.1.24) using Cells.ExportDataTable(DataTable dataTable,int firstRow, int firstColumn,
int rowNumber, bool exportColumnName) method to export the data table.

This error might be caused by there are mixed value types in the column(s).

e.g..,

Workbook workbook = new Workbook();
workbook.Open(@"F:\test\MyBook.xls");
Cells cells = workbook.Worksheets[0].Cells;
//Create a datatable and add columns to it.
//NOTE: If there are mixed value types, please always use String data types for those columns.
DataTable dt = new DataTable();
dt.Columns.Add("column1", typeof(string));
dt.Columns.Add("column2", typeof(string));
dt.Columns.Add("column3", typeof(string));
dt.Columns.Add("column4", typeof(double));
dt.Columns.Add("column5", typeof(double));
dt.Columns.Add("column6", typeof(double));
dt.Columns.Add("column7", typeof(double));
//.............
cells.ExportDataTable(dt, 0, 0, 232, true);
workbook.Worksheets.Add("NewSheet");
workbook.Worksheets["NewSheet"].Cells.ImportDataTable(dt, true, "A1");
workbook.Save("f:\\test\\outMyBook.xls");

Kindly let us know if it works fine now, if you still could not figure it out, kindly do post your template file and sample codes (you may create a demo app too to post us here)

Thank you.