test

DataTable dt = new DataTable();
DataColumn[] cols = new DataColumn[] {
new DataColumn("name",typeof(string)),
new DataColumn ("birthday",typeof(DateTime)),
new DataColumn ("score",typeof(int))
};
dt.Columns.AddRange(cols);
Random rnd = new Random();

for (int i = 0; i < 5; i++)
{
DataRow row = dt.NewRow();
object[] items = new object[] {
"小明",
DateTime.Now ,
rnd.Next(100)
};
row.ItemArray = items;
dt.Rows.Add(row);
}

LwNpoiHelper.RenderDataTableToExcel(dt, "TR.xls");

 

 

public partial class LwNpoiHelper
{
public static Stream RenderDataTableToExcel(DataTable SourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);

// handling header.
foreach (DataColumn column in SourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

// handling value.
int rowIndex = 1;

foreach (DataRow row in SourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);

foreach (DataColumn column in SourceTable.Columns)
{
if (column.DataType == typeof(int))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((int)row[column]);
}
else if (column.DataType == typeof(float))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((float)row[column]);
}
else if (column.DataType == typeof(double))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((double)row[column]);
}
else if (column.DataType == typeof(Byte))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((byte)row[column]);
}
else if (column.DataType == typeof(UInt16))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt16)row[column]);
}
else if (column.DataType == typeof(UInt32))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt32)row[column]);
}
else if (column.DataType == typeof(UInt64))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt64)row[column]);
}
else if (column.DataType == typeof(DateTime))
{
//dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)row[column]);

IDataFormat dataformat = workbook.CreateDataFormat();
ICellStyle style = workbook.CreateCellStyle();

dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)row[column]);

style.DataFormat = dataformat.GetFormat("yyyy-MM-dd");
dataRow.GetCell(column.Ordinal).CellStyle = style;

}
else
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}

// dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}

rowIndex++;
}

workbook.Write(ms);
ms.Flush();
ms.Position = 0;

sheet = null;
headerRow = null;
workbook = null;

return ms;
}