locked
Preserve Excel Formatting RRS feed

  • Question

  • User-1251924073 posted

     Hi Guys,

     I have an excel template file properly set with necessary formattings. For example: All amount cells are formatted with Currency type and 2 descimal places, etc.., I use this template to write the user data and have it downloaded by the client. I use the following method to write data to excel cells.

    INSERT INTO [sheet1$A29:B29] (F1, F2) VALUES ('Hemant', 200)

     From the above insert statement you can see that I 'm inserting in the Row 29 values such as Hemant (String type) and 200 (Currency type). Row 28 holds the Heading. After writing data in Row 29, i see that 200 is not formatted in currency format. But if i write the same data at Row 30 (leaving Row 29 empty), i see the data are correctly formatted. The issue is related only with the immediate row after the heading and there is no issue with other rows following.

    I thought of leaving an empty row purposly while writing the data and that i would remove the empty row after all rows are populated. But it seems that this is not supported. So, guys i really need your help in fixing the formatting issue.

    I' m using the following connection string:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=template.xls;Extended Properties=" + Convert.ToChar(0x22).ToString() + "Excel 12.0;" + ConnectionOption + Convert.ToChar(0x22).ToString()



     

    Saturday, September 26, 2009 11:10 AM

All replies

  • User-1251924073 posted

     Hi Guys,

     Is there any possible workaround for this problem. Appreciate your thoughts.

     

    Monday, September 28, 2009 2:01 AM