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.

Fri Nov 29, 2013 12:58 am

I'm adding data to a worksheet using the InsertDataTable method. The DataTable includes columns of type double. When exported to XLS, these cells are general format and not number format. I've set transTypes to true.

Code: Select all
//worksheet.InsertDataTable(dataTable, columnHeaders, firstRow, firstColumn, transTypes);

worksheet.InsertDataTable(dataTable, true, 1, 1, true);

How can I make the InsertDataTable method set the format to number?

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Fri Nov 29, 2013 9:00 am

Hello Will,

Thanks for your feedback.
Sorry that the description for the transTypes parameter, which doesn't affect numeric format inside the cell formatting, but affects the value stored in the file's type. We have also tested it with the following code1, actually it is the number value you want(see screenshot), as you didn't set its number format, so they are general format , and you could refer to the following code2 to set the cells as number format you need .
Code: Select all
//code1
DataTable dt = new DataTable();
dt.Columns.Add("columns1", typeof(double));
dt.Columns.Add("columns2", Type.GetType("System.String"));
DataRow newRow = dt.NewRow();
newRow["columns1"] =88882.3232;
newRow["columns2"] = "ddddd";
dt.Rows.Add(newRow);
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.InsertDataTable(dt, true, 1, 1,true);
workbook.SaveToFile(@"..\..\Result.xlsx",ExcelVersion.Version2010);
//code2
sheet.Range["C4"].NumberFormat = "0.00";

If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
Attachments
PDFView080.png
PDFView080.png (1.47 KiB) Viewed 4511 times
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Sun Dec 01, 2013 10:51 pm

Thanks, I've set the number format for the appropriate cells. It would be nice if there was another option that applied formatting to the cell depending on the type e.g. double is number, datatime is date etc.

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Mon Dec 02, 2013 1:44 am

Hello Will,

Thanks for your feedback. Thanks for your suggestion, which would be actively considered.
If there are any questions, welcome to get it back to us.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Jan 06, 2014 8:08 am

here am using an button ,on every click of button my row should go down
ERROR:am not able to save the file even if the path is same.
Code: Select all
 static int n=2;
        Workbook workbook = new Workbook();//Initialize a new Workboook object
        public void button1_Click(object sender, EventArgs e)
        {
            try
            {
                button1.Text = Convert.ToString(n);//getting the count of click
                workbook.LoadFromFile(@"C:\Program Files\Warehouse\Sample1.xls");
                Worksheet sheet = workbook.Worksheets[0];//Get the first worksheet
                sheet.Range["A" + n].Text = "Party Name";//Write string values in a cell
                sheet.Range["B" + n].Text = "amount";
                sheet.Range["C" + n].Text = "crain";
                sheet.Range["D" + n].Text = "drain";
                sheet.Range["E" + n].Text = "pain";
                sheet.Range["F" + n].Text = "devel";
                workbook.SaveToFile(@"C:\Program Files\Warehouse\Sample1.xls");
                n++;
            }
            catch { }
        }


Please if you have any alternate program in which on click of the button my data should be entered in the excel sheet but the datat should be in the next line on every click made by the user/client.

shahkharsh
 
Posts: 1
Joined: Wed Jan 01, 2014 10:33 am

Mon Jan 06, 2014 8:13 am

Dear harsh shah,

Thanks for your interested in our Spire.xls component.
Your code is ok, but please kindly notice that the saved excel file will automatically create a sheet named Evaluation Warning without a license when it run the line workbook.SaveToFile(...); every time. And as a excel can't contain multiple sheets with the same name. So you got the error since your project didn't apply the license for Spire.xls.

We could provide you with an evaluation license to test without any restrictions for 30 days. If you need, please contact us(support@e-iceblue.com).

Please don't hesitate to contact us for further inquiry.

Best regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Return to Spire.XLS