Click here to Skip to main content
15,845,469 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am generating a excel file using c#, a date time column is set General by default but requirement is for custom. Values are fine but type is general. I added the code

<pre>
C#
ICellStyle dateStyle = (ICellStyle)workbook.CreateCellStyle();
    dateStyle.DataFormat = format.GetFormat("dd-mm-yyyy hh:mm");
 
    
     if (j == 2 || j == 4)
             { currentCell.CellStyle = dateStyle; }


Kindly help to fix this.

What I have tried:

public static byte[] CreateExcelFile(DataTable dt, string fileNameWithPath)
          {
              byte[] excelByte = null;
              try
              {

                  IWorkbook workbook = new HSSFWorkbook();
                  IDataFormat format = workbook.CreateDataFormat();
                  ISheet ws = workbook.CreateSheet("Sheet1");
                  ICellStyle amountStyle = (ICellStyle)workbook.CreateCellStyle();
                  amountStyle.DataFormat = format.GetFormat("#.00");
                  amountStyle.Alignment = HorizontalAlignment.Right;

                  ICellStyle dateStyle = (ICellStyle)workbook.CreateCellStyle();
                  dateStyle.DataFormat = format.GetFormat("dd-mm-yyyy hh:mm");


                  IRow HeaderRow = ws.CreateRow(0);
                  HSSFFont xlFont = (HSSFFont)workbook.CreateFont();
                  xlFont.FontHeightInPoints = 10;
                  xlFont.FontName = "Calibri";
                  HSSFCellStyle hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                  hStyle.SetFont(xlFont);
                  amountStyle.SetFont(xlFont);

                  HSSFCellStyle rightStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                  rightStyle.Alignment = HorizontalAlignment.Right;

                  rightStyle.SetFont(xlFont);

                  HSSFFont xlboldFont = (HSSFFont)workbook.CreateFont();
                  xlboldFont.FontHeightInPoints = 10;
                  xlboldFont.FontName = "Calibri";
                  xlboldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                  HSSFCellStyle hStylebold = (HSSFCellStyle)workbook.CreateCellStyle();
                  hStylebold.SetFont(xlboldFont);



                  IRow HeaderRowData = ws.CreateRow(0);

                  for (int j = 0; j < dt.Columns.Count; j++)
                  {

                      ICell Cell1 = HeaderRowData.CreateCell(j);
                      Cell1.SetCellValue(dt.Columns[j].ColumnName);
                      Cell1.CellStyle = hStyle;

                  }
                  for (int i = 0; i < dt.Rows.Count; i++)
                  {

                      IRow CurrentRow = ws.CreateRow(i + 1);
                      for (int j = 0; j < dt.Columns.Count; j++)
                      {

                          ICell currentCell = CurrentRow.CreateCell(j);
                          currentCell.CellStyle = hStyle;
                          if (j == 0 || j == 2 || j == 3 || j == 5)
                          {
                              currentCell.CellStyle = rightStyle;
                              if (dt.Rows[i][j].ToString() != "" && dt.Rows[i][j] != null)
                              {
                                  var cellValue = Convert.ToDouble(dt.Rows[i][j]);
                                  currentCell.SetCellValue(cellValue);
                              }
                              else
                                  currentCell.SetCellValue(dt.Rows[i][j].ToString());
                              if (j == 2 || j == 3)
                              {
                                  currentCell.CellStyle = amountStyle;

                              }
                              if (j == 2 || j == 4)
                              {
                                  currentCell.CellStyle = dateStyle;
                              }
                          }
                          else
                          {
                              currentCell.SetCellValue(dt.Rows[i][j].ToString());
                          }
                      }
                  }

                  using (MemoryStream ms = new MemoryStream())
              {
                  workbook.Write(ms);
                  excelByte = ms.ToArray();
              }
              var reportDirectory = Path.GetDirectoryName(fileNameWithPath);
              if (!Directory.Exists(reportDirectory))
              {
                  Directory.CreateDirectory(reportDirectory);
              }

              //workbook.Write(fs);
              File.WriteAllBytes(fileNameWithPath, excelByte);
                  //}
                  CreateLogFiles objCreateLogFiles = new CreateLogFiles();
                  objCreateLogFiles.FTPUpload(fileNameWithPath);
              }
              catch (Exception ex)
              {
                  //Error while generating file
                  CreateLogFiles objCreateLogFiles = new CreateLogFiles();
          objCreateLogFiles.UpdateErrorLog("Error while creating Excel file: " + ex.Message);

              }
              return excelByte;
          }
Posted
Updated 11-May-23 0:24am
v2
Comments
Ralf Meier 11-May-23 5:21am    
I'm sorry ... which part of the code isn't working well ?
Or better which is the result you get what isn't like you expected it ?
Shailendra Mishra 2023 15-May-23 2:35am    
Thank Ralf Meier for your response.
All the code is working fine, I am able to generate the excel using the below code.
But the requirement is like, I have to set the Format of cell as Custom, By default it is General.
I need help, what change we have to do to achieve this.

Kindly refer the image link for more clarity.

https://photos.app.goo.gl/THYi31kh3rBhxmNg6

1 solution

to the following part of your code I added comments :
C#
if (j == 0 || j == 2 || j == 3 || j == 5)    // <= here you exclude the 4
                          {
                              currentCell.CellStyle = rightStyle;
                              if (dt.Rows[i][j].ToString() != "" && dt.Rows[i][j] != null)
                              {
                                  var cellValue = Convert.ToDouble(dt.Rows[i][j]);
                                  currentCell.SetCellValue(cellValue);
                              }
                              else
                                  currentCell.SetCellValue(dt.Rows[i][j].ToString());
                              if (j == 2 || j == 3)
                              {
                                  currentCell.CellStyle = amountStyle;

                              }
                              if (j == 2 || j == 4)  // <= but here you need it 
                              {
                                  currentCell.CellStyle = dateStyle;
                              }
                          }
 
Share this answer
 
v2
Comments
Shailendra Mishra 2023 11-May-23 5:47am    
I have to set the Format of cell as Custom, By default it is General.
I need help, what change we have to do to achieve this.
Ralf Meier 12-May-23 18:12pm    
PLease answer the questions I asked as comment to your question.
Without those answers it's impossible (for me) to give you any advice ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900