CODEWITHSUNDEEP
c#excel
whytheq
whytheq

Reputation: 35577

Can I preserve an Excel template's formatting

I have an Excel template in my Solution with various formatting applied to it for Dates (dd-mmm-yy), text (left aligned) and numbers (#,##0.0)

If I now use code to move data from SQL-Server into this template I'm using code such as the following:

Input parameter of DataTable dt feeds into a method which ultimately executes the following:

string data = null;
int i = 0;
int j = 0;
for(i = 0; i <= dt.Rows.Count - 1; i++) {
   for(j = 0; j <= dt.Columns.Count - 1; j++) {
        data = dt.Rows[i].ItemArray[j].ToString();
        xlWorkSheet.Cells[i + 1, j + 1] = data;
   }
}

The problem with the above is that all the numbers and dates are being cast to strings and then moved into the template - so the templates formatting will be lost.

Is there a way of moving the data and preserving the template's formatting ?
Or do I need to move the data as strings and then apply formatting after the data has moved?

Upvotes: 1

Views: 149

Answers (1)

stuartd
stuartd

Reputation: 73293

If you set the cells' value explicitly that should preserve the formatting: 

xlWorkSheet.Cells[i + 1, j + 1].Value = data;

Upvotes: 1

Related Questions