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; }

转载于:https://www.cnblogs.com/MiLu/p/6664872.html