I need to take the content of a C# datatable and deposit it (...) in a new Excel spreadsheet. I have seen some good generic code to do this, however, my problem is that I need to be specific, column by column, as to the format of the cells. For example, I need to enforce a rule that certain columns are TEXT only - so that numbers (without leading text) are NOT treated as numeric values.

Typically, when Excel sees '01', it will load the cell as '1' with the leading zero truncated, and, further, right justified as numeric data would be. However, in my example, this would be incorrect, since '01' is a STRING and not a number, and must, therefore, retain the "leading" zero and be left justified.

Therefore, how do I go about specifying the data types for columns in a spreadsheet generated from a C# datatable?



Recommended Answers

All 3 Replies

You do something along these lines

Range rg = worksheet.get_Range("A1","E1");
rg.Font.Bold = true;
rg.Font.Name = "Arial";
rg.Font.Size = 8;
rg.WrapText = true;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 6;
rg.Borders.Weight = 3;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;

It's a good answer, but not what I'm trying to do. I need to make specific columns to act as TEXT.

It is what you are trying to do. NumberFormat = "@".

Sometimes you need to look for the answer, instead of waiting for it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.