NPOI日期格式单元格 [英] NPOI Date Format Cell

问题描述

我正在使用NPOI在Sheet1中创建固定的工作表模板，并且需要来自Sheet2的日期格式的数据.我从数据库生成DataTable以在Sheet2中设置数据.这是我的代码:

I'm using NPOI to create fixed worksheet template in Sheet1, and need data from Sheet2 that's in date format.I generate DataTable from database to set data in Sheet2. It's my code :

private DataTable getWeek()
{    
  strConn = WebConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
  conn = new OdbcConnection(strConn);
  conn.Open();
  sql = "SELECT week, sunday, saturday FROM tb_weekreferences";     
  da = new OdbcDataAdapter(sql, conn);
  dt = new DataTable();
  da.Fill(dt);
  conn.Close();        

  return dt;

}

，然后将DataTable导出到Excel:

and then export DataTable to Excel :

private int ExportDataTableToExcel(DataTable sourceTable, ISheet sheet)
    {
        IRow headerRow = sheet.CreateRow(0);
        ICell headerCell;
        ICell cell = null;
        Dictionary<String, ICellStyle> styles = CreateExcelStyles(hssfwb);

        //handling value
        int rowIdx = 1;
        foreach (DataRow row in sourceTable.Rows)
        {
            IRow dataRow = sheet.CreateRow(rowIdx);            
            foreach (DataColumn column in sourceTable.Columns)
            {
                cell = dataRow.CreateCell(column.Ordinal);                
                cell.SetCellValue(row[column].ToString());
                cell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("");
                cell.CellStyle = styles["cell"]; 
            }            
            rowIdx++;
        }

        //handling header
        foreach (DataColumn column in sourceTable.Columns)
        {
            headerCell = headerRow.CreateCell(column.Ordinal);
            headerCell.SetCellValue(column.ColumnName);
            headerCell.CellStyle = styles["header"];
            sheet.AutoSizeColumn(column.Ordinal);
            sheet.SetColumnWidth(column.Ordinal, (sheet.GetColumnWidth(column.Ordinal) + (5 * 256))); // set width = autofit() + (5 * 256)
        }
        return rowIdx;
    }

在Sheet1中，我使用vlookup公式从Sheet2获取星期日"和星期六".但这是行不通的，因为Sheet2中的周，周日和周六的值似乎像字符串(左对齐单元格).生成Excel数据时如何设置日期的单元格格式?请为此提供解决方案.

In Sheet1 i use vlookup formula to get 'sunday' and 'saturday' from Sheet2. But it doesn't work, because value of week, sunday, and saturday in Sheet2 seems like string (left alignment cell). How to set cell format in date when generate data to excel ? Please give me solution for this.

谢谢.

推荐答案

第一个问题是这行

cell.SetCellValue(row[column].ToString());

您正在将值转换为字符串，但是您可能会将其转换为DateTime格式，因此NPOI知道这是日期时间.

You are converting value to string, but you probably convert it to DateTime format, so NPOI knows this is datetime.

或者尝试一下是否可行:

or try if this works:

cell.SetCellValue(DateTime.Now);

第二，首先尝试为您的单元格设置样式，然后更改其DataFormat属性，但使用这样的自定义格式:

Second, try first set the style for your cell and then change it DataFormat attribute, but with custom format like this:

IDataFormat dataFormatCustom = workbook.CreateDataFormat();
cell.CellStyle = styles["cell"]; 
cell.CellStyle.DataFormat = dataFormatCustom.GetFormat("yyyyMMdd HH:mm:ss");

这会将您的DateTime值格式化为人类可读的格式.

which will format your DateTime value to human readable format.

这篇关于NPOI日期格式单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！