Hi ,

Thanks for your Reply.

I am using LOB of SilverLight Netadvantage. Version is 10.3.20103.1006.
Now i am writing into MemoryStream and then writing back to File. But still Format is not applied.
I have attached the code below. please check and let me know the issue and resolution.

 string path = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + @"Export\" + "ABC.xls";
            FileStream file1 = new FileStream(path, FileMode.Create, FileAccess.Write);
            MemoryStream memStream = new MemoryStream();

            #region "Create a file using Stream"
                try
                {
                    WorkbookFormat workbookFormat = WorkbookFormat.Excel97To2003;
                    // create an Excel workbook in the specified format
                    Workbook dataWorkbook = new Workbook(workbookFormat);
                    // create the Excel worksheet to receive the export data
                    Worksheet worksheet = dataWorkbook.Worksheets.Add(worksheetName);
                    // freeze header row
                    worksheet.DisplayOptions.PanesAreFrozen = true;
                    worksheet.DisplayOptions.FrozenPaneSettings.FrozenRows = 1;

                    #region "Header Row"
                    worksheet.DefaultColumnWidth = 5000;
                    int currentColumn = 0;
                    foreach (DataColumn column in result.Columns)
                    {
                        string format = GetFormatForColumn(column.ColumnName);
                        worksheet.Rows[0].CellFormat.FormatString = format;
                        this.SetCellValue(worksheet.Rows[0].Cells[currentColumn], column.ColumnName, null);
                        currentColumn++;
                    }
                    #endregion

                    int currentRow = 1;
                    foreach (DataRow row in result.Rows)
                    {
                        int currentCell = 0;
                        foreach (DataColumn col in result.Columns)
                        {
                            this.SetCellValue(worksheet.Rows[currentRow].Cells[currentCell], row[col.ColumnName], col.DataType);
                            currentCell++;
                        }
                        currentRow++;
                    }
                    dataWorkbook.Save(memStream);
                    byte[] data = memStream.ToArray();
                    file1.Write(data, 0, data.Length);
                    file1.Close();
                }
                catch (Exception ex)
                {
                    string msg = ex.InnerException.Message;
                }
            #endregion

Thanks

Anil Kumar

Hello Anil,

Please provide the information Mike requested so that we may assist you.

Hi ,

Sorry for Late response.

There are many formats based on the Field. But mainly we are using following formats

1) #,##0;(#,##0);0

2) #0.0000

3) ###,###0

Mainly for amount Fields. If possible send me a sample or at least a code piece.

So I can solve quickly.

Thanks

Anil Kumar

Well I still don't know what your SetCellValue method does, so it is still difficult to say, but it looks like you are only setting the FormatString in one place. And you are setting it on the cell in the first row, which apparently contains header cells. I'm guessing you want to set the format string all cells in those columns, not just the header cells. If so, change that line to this:

worksheet.Columns[currentColumn].CellFormat.FormatString = format;