This project has moved. For the latest updates, please go here.

Leading Zero preservation for whole workbook?

Oct 9, 2012 at 4:37 PM

First I'd like to say AMAZING library. Good job and keep up the great work! This is how working with spreadsheets should be!

Ok, so I am generating a workbook that has a bunch of worksheets in it. I am trying to set it so basically the default DataType is Text for all columns. This is a template and I need the users to be able to type in "0100" into the cell and have it preserve the leading 0. I have seen a lot of great examples in the discussions but their solutions do not seem to work for me. Even if I opt to not enter and value in and just simply set the column type. I have tried doing 1 column and I have tried doing a bunch but it still shows "General" and removed the leading zeros once typed in. Here is the code I have, basically I am providing it a bunch of Types and having it generate the data from there. I know I have it setting type 2x which is mainly example to show you but even doing it alone I cannot get it to stick.

public static void CreateFile()
        {

            var assembly = Assembly.GetAssembly(typeof(tblPreFERClientMap));

            var types = assembly.GetTypes().Where(t => t.Name.StartsWith("tbl"));

            using(var book = new XLWorkbook())
            {
                foreach (var type in types)
                {
                    var name = type.Name.Length > 31 ? type.Name.Substring(0, 31) : type.Name;

                    using(var sheet = book.Worksheets.Add(name))
                    {
                        var props = type.GetProperties();

                        for (var i = 0; i < props.Count(); i++)
                        {
                            var x = i;
                            var cell = sheet.Cell(1, x + 1);
                            cell.SetValue(props[i].Name);
                        }
                        
                        sheet.Columns().AdjustToContents();
                        sheet.Column(1).Cells().SetDataType(XLCellValues.Text);
                        book.Worksheet(name).Column(1).Cells().DataType = XLCellValues.Text;
                    }
                }

                book.SaveAs(@"c:\testing.xlsx");
            }
        }
Maybe you could create a method SetDefaultDataType for the workbook or worksheet objects. Any helps is appreciated. Again thanks and keep up great work!

Coordinator
Oct 9, 2012 at 4:44 PM

Let's simplify the issue, does the following put "0123" on cell A1?

var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("0123");
wb.SaveAs(targetFile);

Don't worry abut General vs Text formatting. The Text format is for numbers to be treated as text. Here you're adding a pure text so there's no need to format it as text. They're just a bunch of characters which also happen to be digits.

Oct 9, 2012 at 5:14 PM

Correct that does work. 

Just to specify, this issue is post generation when the user is entering data under the generate column names.

 

btw, thanks for fast response! :)

Coordinator
Oct 9, 2012 at 5:27 PM

Ah, now I get it. You have to format the cell(s) so Excel displays what is entered "as is" and not try to cast it.

            var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("Sheet1");
            ws.FirstCell().Style.NumberFormat.Format = "@";
            wb.SaveAs(filePath);
Oct 9, 2012 at 5:37 PM

Ok great that did work! So I have tried several variation to try and set the columns that have headers to that type but when I do the example below I find it is only setting the top row and not the others below it. Basically, I have column names at the top of the sheet and then they enter data below it. I only need the columns that have the text header to have this formatting. What is the best way you would recommend me do this? Here is the code I have that is only doing the top row.

public static void CreateFile()
        {

            var assembly = Assembly.GetAssembly(typeof(tblPreFERClientMap));

            var types = assembly.GetTypes().Where(t => t.Name.StartsWith("tbl"));

            using(var book = new XLWorkbook())
            {
                foreach (var type in types)
                {
                    var name = type.Name.Length > 31 ? type.Name.Substring(0, 31) : type.Name;

                    using(var sheet = book.Worksheets.Add(name))
                    {
                        var props = type.GetProperties();

                        for (var i = 0; i < props.Count(); i++)
                        {
                            var x = i;
                            var cell = sheet.Cell(1, x + 1);
                            cell.SetValue(props[i].Name);
                        }
                        
                        sheet.Columns().AdjustToContents();
                        sheet.Column(1).Cells().Style.NumberFormat.Format = "@";
                    }
                }
                book.SaveAs(@"c:\testing.xlsx");
            }
        }

What I thought would have works is below but it just errors out due to memory. Just FYI, this is creating about 50 sheets.

sheet.Columns(1,sheet.ColumnsUsed().Count()).Cells().Style.NumberFormat.Format = "@";

Coordinator
Oct 9, 2012 at 5:54 PM

You're calling all (1 million) cells of the column and then applying the format to all individually. Do this instead:

sheet.Column(1).Style.NumberFormat.Format = "@";
Oct 9, 2012 at 6:08 PM

Works PERFECTLY. I really cannot express how impressed I am. I have almost completely reprogrammed a major piece of functionality that we did initially months ago. It took many lines of code and many weeks and was still not perfect. Think I have been working on it for 3 hrs and its almost completely replaced and improved thanks to you :) Kudos!