Appending ="0xxxx" to excel cell while exporting using NPOI to maintain leading zero, or how to set cell as text

Question

I am trying to maintain the leading zeros while exporting a column holding phone numbers to excel using NPOI in an asp.net mvc 3 application. I have read here; http://creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel that I can append ="[some number beginning with 0]" to have excel maintain the zeros. I have also read that if I set the cell as text it will maintain the zero. I have been unsuccessful in my attempts to do this. Here is my code;

 public ActionResult Export(int page, string orderBy, string filter)
    {
        //Get the data representing the current grid state - page, sort and filter
        GridModel model = Model().ToGridModel(page, 10, orderBy, string.Empty, filter);
        var orders = model.Data.Cast<Advertiser>();

        //Create new Excel workbook
        var workbook = new HSSFWorkbook();


        //Create new Excel sheet
        var sheet = workbook.CreateSheet();


        //(Optional) set the width of the columns
        sheet.SetColumnWidth(0, 10 * 256);
        sheet.SetColumnWidth(1, 50 * 256);
        sheet.SetColumnWidth(2, 50 * 256);
        sheet.SetColumnWidth(3, 50 * 256);

        //Create a header row
        var headerRow = sheet.CreateRow(0);



        //Set the column names in the header row
        headerRow.CreateCell(0).SetCellValue("Name");
        headerRow.CreateCell(1).SetCellValue("Phone");
        headerRow.CreateCell(5).SetCellValue("Company Name");
        headerRow.CreateCell(7).SetCellValue("Address 1");
        headerRow.CreateCell(8).SetCellValue("Address 2");
        headerRow.CreateCell(9).SetCellValue("Address 3");
        headerRow.CreateCell(10).SetCellValue("Address 4");
        headerRow.CreateCell(11).SetCellValue("Post Code");
        headerRow.CreateCell(14).SetCellValue("Email");
        headerRow.CreateCell(16).SetCellValue("Website");
        headerRow.CreateCell(19).SetCellValue("Listing Type");

        //(Optional) freeze the header row so it is not scrolled
        sheet.CreateFreezePane(0, 1, 0, 1);

        int rowNumber = 1;

        //Populate the sheet with values from the grid data
        foreach (Advertiser order in orders)
        {
            //Create a new row
            var row = sheet.CreateRow(rowNumber++);

            //Set values for the cells
            row.CreateCell(0).SetCellValue(order.AdvertiserName);
            row.CreateCell(1).SetCellValue(order.Phone);
            row.CreateCell(3).SetCellValue(order.CompanyName);
            row.CreateCell(5).SetCellValue(order.Address1);
            row.CreateCell(6).SetCellValue(order.Address2);
            row.CreateCell(7).SetCellValue(order.Address3);
            row.CreateCell(8).SetCellValue(order.Address4);
            row.CreateCell(9).SetCellValue(order.Postcode);
            row.CreateCell(10).SetCellValue(order.AdvertiserEmail);
            row.CreateCell(11).SetCellValue(order.Website);
            row.CreateCell(12).SetCellValue(order.listing.type);



        }

        //Write the workbook to a memory stream
        MemoryStream output = new MemoryStream();
        workbook.Write(output);

        //Return the result to the end user

        return File(output.ToArray(),   //The binary data of the XLS file
            "application/vnd.ms-excel", //MIME type of Excel files
            "Advertisers.xls");     //Suggested file name in the "Save as" dialog which will be displayed to the end user
    }
}

I have tried the setCellTyoe method in various places with no luck.

I don't mind how it's done, I just want to maintain the leading zeros when the sheet is exported.

Answer 1

Changed the data type from string to int and NPOI set the cell as text, keeping the leading zero.