[ad_1]
C#を使用してExcelファイルを生成しています。日付時刻列はデフォルトで「一般」に設定されていますが、要件はカスタムです。 値は問題ありませんが、タイプは一般的です。 コードを追加しました
<pre>
C#
ICellStyle dateStyle = (ICellStyle)workbook.CreateCellStyle(); dateStyle.DataFormat = format.GetFormat("dd-mm-yyyy hh:mm"); if (j == 2 || j == 4) { currentCell.CellStyle = dateStyle; }
これを修正するために協力してください。
私が試したこと:
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; }
解決策 1
コードの次の部分にコメントを追加しました。
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; } }
[ad_2]
コメント