Jump to content

Formatting cells in Excel to be text.

- - - - -
Started by Blue Indian, Jul 18 2011 12:27 PM

  • Please log in to reply
3 replies to this topic

#1
Blue Indian
Posted 18 July 2011 - 12:27 PM

Blue Indian

    Learning Programmer

  • Members
  • PipPipPip
  • 67 posts
Alright, here's the deal. When you export data to an excel spreadsheet using C# and OpenXml, Excel auto converts numerical text to the number format. Thus, I am losing leading zeros which will mess me up when I import data back. This could also mess stuff up like phone numbers that have leading zeros etc.

I have searched the Internet like crazy to no avail. I am trying to update the below function to format each cell as text. There may not be a way, but if anybody has a solution I will be very happy.

[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff] 
[SIZE=2][FONT=Consolas][COLOR=#0000ff]public[/COLOR][/FONT][/SIZE]
[/COLOR]
[/SIZE]
[/FONT]
[/COLOR]
[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]Cell[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] InsertCellInWorksheet([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] columnName, [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]uint[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] rowIndex, [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] sheetName, [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] text)[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]        {[/FONT][/SIZE]
 
 
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000]// Get the SharedStringTablePart. If it does not exist, create a new one.[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]SharedStringTablePart[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] shareStringPart = _workbookElement.GetPartsOfType<[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]SharedStringTablePart[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]>().Count() > 0 [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]? _workbookElement.GetPartsOfType<[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2] 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]SharedStringTablePart[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]>().First[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]                                                              ()[/FONT][/SIZE]
 
[SIZE=2][FONT=Consolas]                                                        : _workbookElement.AddNewPart<[/FONT][/SIZE]
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]SharedStringTablePart[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]>();[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2] 
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]if[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2](shareStringPart == [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]null[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2])[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]            {[/FONT][/SIZE]

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]return[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]null[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2];[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]            }[/FONT][/SIZE]
 
 
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]WorksheetPart[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] worksheetPart = findWorksheetPartFromSheetName(sheetName);[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2] 
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]Cell[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] textCell = insertCellInWorksheet(columnName, rowIndex, worksheetPart);[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2] 
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]if[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2](textCell == [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]null[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2])[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]            {[/FONT][/SIZE]
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]return[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]null[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2];[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]            }[/FONT][/SIZE]
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] sharedStringIndex = insertSharedStringItem(text, shareStringPart);[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]            textCell.CellValue = [/FONT][/SIZE]
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]CellValue[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2](sharedStringIndex.ToString());[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]            textCell.DataType = [/FONT][/SIZE]

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]EnumValue[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]<[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]CellValues[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]>([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]CellValues[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].SharedString);[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]            worksheetPart.Worksheet.Save();[/FONT][/SIZE]
 
 
 

[/SIZE]
[/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]return[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] textCell;[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][SIZE=2][FONT=Consolas]        }[/FONT][/SIZE]

[/SIZE]
[/FONT]

Sorry the formatting is so bad.

#2
WingedPanther
Posted 18 July 2011 - 01:17 PM

WingedPanther

    A spammer's worst nightmare

  • Moderators
  • 16,831 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others
What if you prepend the string for the cell with a single quote? That's how you do it when typing directly into Excel.
Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

#3
Roger
Posted 18 July 2011 - 08:38 PM

Roger

    If nothing goes right, go left.

  • Administrators
  • 718 posts
  • Programming Language:C, PHP
  • Learning:Python
Yes, I was going to suggest what WP said as well.. output the text number '00123 in quotes "'00123",
Check out our update Guidelines/FAQ. When posting code, remember to use code tags - Posted Image.

#4
fayyazlodhi
Posted 20 July 2011 - 01:03 PM

fayyazlodhi

    Programming Expert

  • Members
  • PipPipPipPipPipPip
  • 403 posts
or you could manually change the type of a specific column from numeric to string or whatever required. Generally, there is a default width selected, but if you edit the excel document from format tab, you can find almost entire data that you would have created using your software.

I have seen this many times in our products where we generate different types of string values resulting into excel files.
Today is the first day of the rest of my life
Back to C# Programming


1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users



  1. CODECALL Programming Forum
  2. → Software Development
  3. → C# Programming
IPB skins by Skinbox