Excel - Set Cell format using VB.NET

Asked By Hug on 14-Feb-08 12:07 PM
Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()




HelpFrom replied on 14-Feb-08 12:58 PM
I believe this will do the trick for you - just add these lines of code to
what you already have displayed here.  You'll need to change the sheet name
and column affected based on what's actually in your workbook, along with the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberFormat = "m/d/yyyy;@"
Hug replied on 14-Feb-08 02:46 PM
Hi JLanthan,

Thank you very much for your help.  Your code works fine.  I was stuck at
syntex of numeric format.  It looks you are the expert.  Can I ask one more
trick?  The column width can not be saved and went back to default width
after the file reopen.  Any trick?    Thank you again.
HelpFrom replied on 14-Feb-08 03:17 PM
Arrrgh... serious FatFinger exercise on the keyboard at my end if you see a
partial response!

I'll try again.  The reason it's not saving the width, I think, is because
it's a .CSV file and not a .XLS file.  In reality a .CSV file is an ASCII
text file and it has no format storing ability.  You'd need to SAVE AS and
choose to save it as an Excel Workbook to preserve the formatting.

The easiest way to accomplish this could be by adding this line of code
after the formatting is done:
wb.SaveAs Filename:=wb.FullName & ".xls", FileFormat:=xlNormal

if it balks at xlNormal, try using a value of -4143, as:
wb.SaveAs Filename:=wb.FullName & ".xls", FileFormat:=-4143

I say this is easiest instead of stripping off the .csv and replacing it
with .xls.  The filename you'll end up with would be something like
myFile.csv.xls if it started out as myFile.csv
The .FullName property returns the entire drive/path/filename.xls string, so
the copy of the file would be saved to the same location that the .csv file
is at.

That may be undesirable, and you may want to save it in a location you
designate.  You could deal with that by creating a String variable and
assigning it to the path and name you want.  Something like this:

Dim newLocation As String
newLocation = "X:\MySavedCSVFiles\" & wb.Name & ".xls"
so if your file started out as myFile.csv it would end up saved in
X:\MySavedCSVFiles as a file named myFile.csv.xls
the .Name property just returns the name of the workbook without drive/path
info.

Hope that helps.
David Biddulph replied on 14-Feb-08 03:27 PM
In what format did you save the file? If you saved as CSV, remember that CSV
is just comma separated text, so has no formatting data.  If you want to
save things like column width, save as xls.
--
David Biddulph
Hug replied on 14-Feb-08 04:09 PM
Thanks again, Latha.  I could see the width was changed by the code and could
not be saved.  I suspected the reason was csv file but was not sure.  Thanks
for your great help.
Hug replied on 14-Feb-08 04:10 PM
Hi Daved,

I guess that I have to sacrifice the column width stuff because I want to
keep it in csv format.

Thanks again.
Harlan Grove replied on 15-Feb-08 03:35 AM
...

Picky: the OP could save the file in SLK file format, which does save
formatting information but is also plain text though structured.
Mehdi Anis replied on 01-Oct-08 12:27 PM
If you need 15 char long values in CSV, you can add SPACEs as FILLER to your EXCEL Cell Value, then save it as CSV.



Unfortunately you have to loop through each row to make the changes. High level logic is like :-



For Each MyRow as ExcelRow in MySheet.Rows

MyRow(Col).Value &= "      " 'u put 15 spaces

MyRow(Col).Value = MyRow(Col).Value.substring(0, 15)

Next



The above code will give you LEFT JUSTIFIED 15 char long CSV Field. If you want RIGHT JUSTIFIED Text in CSV, then CODE will be like (for the filler only)



MyRow(Col).Value = MyRow(Col).Value.Reverse()

MyRow(Col).Value &= "      "

MyRow(Col).Value = MyRow(Col).Value.substring(0, 15)

MyRow(Col).Value = MyRow(Col).Value.Reverse()



Looping and string operation will take toll on performance - depending on how many rows you have and computer's power. Thnaks you.
bolton ss replied on 09-Oct-08 08:17 AM
if you need more help on format worksheet through vb.net , just refer this.

http://vb.net-informations.com/excel-2007/vb.net_excel_page_format.htm

bolton
babu p replied to Hug on 04-Mar-10 06:06 AM
Hi



I want to change Column data format as text of excel file in c#
SathishChandar Rajalingam replied to HelpFrom on 06-Aug-10 02:08 AM
hi,

I am exporting a datagrid contenet to excel. I want the excel to store numbers as string like 0001 and not as 1.

Can you help me?

My code is

string attachment = "attachment; filename=InventoryCompletionMonitor.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter stwCallinCompletion = new StringWriter();

HtmlTextWriter htextwCallinCompletion = new HtmlTextWriter(stwCallinCompletion.FormatProvider.GetFormat(String ));

dgMaterialQuantityDataGrid.RenderControl(htextwCallinCompletion);

Response.Write(stwCallinCompletion.ToString());

Response.End();
SathishChandar Rajalingam replied to HelpFrom on 06-Aug-10 02:09 AM
hi,

I am exporting a datagrid contenet to excel. I want the excel to store numbers as string like 0001 and not as 1.

Can you help me?

My code is

string attachment = "attachment; filename=InventoryCompletionMonitor.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter stwCallinCompletion = new StringWriter();

HtmlTextWriter htextwCallinCompletion = new HtmlTextWriter(stwCallinCompletion.FormatProvider.GetFormat(String ));

dgMaterialQuantityDataGrid.RenderControl(htextwCallinCompletion);

Response.Write(stwCallinCompletion.ToString());

Response.End();
Jerry Latham replied to SathishChandar Rajalingam on 06-Aug-10 08:38 AM
You could use this code on the Excel side to convert numbers in a given column to text.  This assumes the numbers are in column A and begin in row 2.



This code could be in your Personal.xls file or any other workbook.  You would select the sheet with the numbers to be converted and then run this macro.



Sub NumbersToText()

Const numCol = "A"

Const firstRow = 2

'change this to determine how many

'digits to show left of the decimal

Const numDigits = 4



Dim numbersList As Range

Dim anyEntry As Range

Dim anyText As String



Set numbersList = ActiveSheet.Range(numCol & firstRow & ":" _

& ActiveSheet.Range(numCol & Rows.Count).End(xlUp).Address)

Application.ScreenUpdating = False

For Each anyEntry In numbersList

anyText = ""

If Not IsEmpty(anyEntry) And IsNumeric(anyEntry) Then

anyText = Trim(Str(anyEntry))

If Len(anyText) < numDigits Then

anyText = String(numDigits - Len(anyText), "0") & anyText

End If

With anyEntry

.NumberFormat = "@" ' format as text

.HorizontalAlignment = xlRight

.Value = anyText

End With

End If

Next

Set numbersList = Nothing

End Sub