C#

using System;

using System.IO;

using System.Data;

using NPOI.SS.UserModel;

using NPOI.HSSF.UserModel;

using NPOI.XSSF.UserModel;

namespace cfx.office

{

public class   tExcelWriter

{

private string   myFileName;

private DataTable   myData;

private string   mySheetName;

private int   myBeginRow;

private int   myBeginColumn;

private bool   myWriteColumnName;

//

private   tExcelWriter() { }

//

public static   tExcelWriter Create(string filename,

DataTable data,   string sheetName = null,

int beginRow =   1, int beginCol = 1,

bool   writeColumnName = true)

{

return new   tExcelWriter()

{

myFileName =   filename,

myData =   data,

mySheetName   = sheetName,

myBeginRow =   beginRow - 1,

myBeginColumn = beginCol - 1,

myWriteColumnName = writeColumnName

};

}

//

public bool Write()

{

string ext =   Path.GetExtension(myFileName).ToLower();

if (ext ==   ".xls") return WriteXls();

else return   WriteXlsx();

}

// 将DataTable对象数据写入.xls文件指定工作表的指定位置

private bool   WriteXls()

{

try

{

if (myData   == null || myData.Columns.Count < 1)

return   false;

//

using   (HSSFWorkbook wb = new HSSFWorkbook())

{

if   (mySheetName == null || mySheetName.Trim().Length == 0)

mySheetName = "数据";

ISheet   sheet = wb.CreateSheet(mySheetName);

// 写入工作表

int   curRow = myBeginRow;

// 写入列名

if   (myWriteColumnName)

{

IRow   sheetRow = sheet.CreateRow(curRow);

for   (int col = 0; col < myData.Columns.Count; col++)

{

ICell cell = sheetRow.CreateCell(col + myBeginColumn);

cell.SetCellType(CellType.String);

cell.SetCellValue(myData.Columns[col].ColumnName);

}

curRow++;

}

// 日期和时间格式

IDataFormat dataFormat = wb.CreateDataFormat();

ICellStyle datetimeStyle = wb.CreateCellStyle();

datetimeStyle.DataFormat =

dataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");

// 写入数据行

for (int   row = 0; row < myData.Rows.Count; row++)

{

IRow   sheetRow = sheet.CreateRow(curRow++);

for   (int col = 0; col < myData.Columns.Count; col++)

{

ICell cell = sheetRow.CreateCell(col + myBeginColumn);

if (myData.Rows[row][col] == DBNull.Value)

{

cell.SetCellValue("");

}

else

{

Type dataType = myData.Columns[col].DataType;

if (Comm.IsNumeric(dataType))

{

cell.SetCellType(CellType.Numeric);

double result;

if (Obj.TryToDbl(myData.Rows[row][col], out result))

cell.SetCellValue(result);

}

else if (dataType.Name == "DateTime")

{

cell.CellStyle = datetimeStyle;

DateTime result;

if (Obj.TryToDate(myData.Rows[row][col], out result))

cell.SetCellValue(result);

}

else if (dataType.Name == "Boolean")

{

cell.SetCellType(CellType.Boolean);

cell.SetCellValue(Obj.ToBool(myData.Rows[row][col]));

}

else

{

cell.SetCellType(CellType.String);

cell.SetCellValue(Obj.ToStr(myData.Rows[row][col]));

}

}

}

}

//

using   (FileStream fs = new FileStream(myFileName,

FileMode.Create, FileAccess.Write))

{

wb.Write(fs);

}

return   true;

}

}

catch

{

return   false;

}

}

// 将DataTable对象数据写入.xlsx文件指定工作表的指定位置

private bool   WriteXlsx()

{

try

{

if (myData   == null || myData.Columns.Count < 1)

return   false;

//

using   (XSSFWorkbook wb = new XSSFWorkbook())

{

if   (mySheetName == null || mySheetName.Trim().Length == 0)

mySheetName = "数据";

ISheet   sheet = wb.CreateSheet(mySheetName);

// 写入工作表

int   curRow = myBeginRow;

// 写入列名

if   (myWriteColumnName)

{

IRow   sheetRow = sheet.CreateRow(curRow);

for   (int col = 0; col < myData.Columns.Count; col++)

{

ICell cell = sheetRow.CreateCell(col + myBeginColumn);

cell.SetCellType(CellType.String);

cell.SetCellValue(myData.Columns[col].ColumnName);

}

curRow++;

}

// 日期和时间格式

IDataFormat dataFormat = wb.CreateDataFormat();

ICellStyle datetimeStyle = wb.CreateCellStyle();

datetimeStyle.DataFormat =

dataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");

// 写入数据行

for (int   row = 0; row < myData.Rows.Count; row++)

{

IRow   sheetRow = sheet.CreateRow(curRow++);

for   (int col = 0; col < myData.Columns.Count; col++)

{

ICell cell = sheetRow.CreateCell(col + myBeginColumn);

if (myData.Rows[row][col] == DBNull.Value)

{

cell.SetCellValue("");

}

else

{

Type dataType = myData.Columns[col].DataType;

if (Comm.IsNumeric(dataType))

{

cell.SetCellType(CellType.Numeric);

double result;

if (Obj.TryToDbl(myData.Rows[row][col], out result))

cell.SetCellValue(result);

}

else if (dataType.Name == "DateTime")

{

cell.CellStyle = datetimeStyle;

DateTime result;

if (Obj.TryToDate(myData.Rows[row][col], out result))

cell.SetCellValue(result);

}

else if (dataType.Name == "Boolean")

{

cell.SetCellType(CellType.Boolean);

cell.SetCellValue(Obj.ToBool(myData.Rows[row][col]));

}

else

{

cell.SetCellType(CellType.String);

cell.SetCellValue(Obj.ToStr(myData.Rows[row][col]));

}

}

}

}

//

using   (FileStream fs = new FileStream(myFileName,

FileMode.Create, FileAccess.Write))

{

wb.Write(fs);

}

return   true;

}

}

catch

{

return   false;

}

}

// 其它代码

}

}

C#

using System;

namespace cfx

{

public static class Obj

{

// 其它代码

//

public static string   ToStr(object obj, string defVal = "")

{

if (obj != null)

return   obj.ToString();

else

return   defVal;

}

//

public static bool   ToBool(object obj)

{

try { return   Convert.ToBoolean(obj); }

catch { return   false; }

}

// 尝试转换类型

public static bool   TryToDbl(object obj,out double result)

{

result = 0D;

return obj !=   null && double.TryParse(obj.ToString(), out result);

}

public static bool   TryToDate(object obj, out DateTime result)

{

result =   DateTime.MinValue;

return obj !=   null && DateTime.TryParse(obj.ToString(), out result);

}

}

}

C#

using System;

using System.Data;

using cfx.office;

namespace csfx_demo

{

class Program

{

static void   Main(string[] args)

{

DataTable data =   tApp.DbJet.GetTable(@"select * from t1");

bool result =   tExcelWriter.Create(@"d:\t1.xls", data).Write();

Console.WriteLine(result);

}

}

}

C#

using System;

using System.IO;

using System.Data;

using NPOI.SS.UserModel;

using NPOI.HSSF.UserModel;

using NPOI.XSSF.UserModel;

namespace cfx.office

{

public class   tExcelWriter

{

// 其它代码

// 写入已存在的文件

public bool   WriteExisted()

{

string ext =   Path.GetExtension(myFileName).ToLower();

if (ext ==   ".xls") return WriteXlsExisted();

else return   WriteXlsxExisted();

}

// 写入已存在的xls文件

private bool   WriteXlsExisted()

{

try

{

if (myData   == null || myData.Columns.Count < 1 ||

File.Exists(myFileName) == false)

return   false;

//

using   (FileStream fs = new FileStream(myFileName,

FileMode.Open, FileAccess.Read))

{

using   (HSSFWorkbook wb = new HSSFWorkbook(fs))

{

int   sheetIndex = 0;

if   (mySheetName != null && mySheetName.Trim().Length > 0)

sheetIndex = wb.GetSheetIndex(mySheetName);

if   (sheetIndex == -1) sheetIndex = 0;

ISheet sheet = wb.GetSheetAt(sheetIndex);

//

int   curRow = myBeginRow;

// 写入列名

if   (myWriteColumnName)

{

IRow sheetRow = sheet.GetRow(curRow);

if (sheetRow == null) 

sheetRow = sheet.CreateRow(curRow);

for (int col = 0; col < myData.Columns.Count; col++)

{

int colIndex = col + myBeginColumn;

ICell cell = sheetRow.GetCell(colIndex);

if (cell == null) 

cell = sheetRow.CreateCell(colIndex);

cell.SetCellType(CellType.String);

cell.SetCellValue(myData.Columns[col].ColumnName);

}

curRow++;

}

// 日期和时间格式

IDataFormat dataFormat = wb.CreateDataFormat();

ICellStyle datetimeStyle = wb.CreateCellStyle();

datetimeStyle.DataFormat =

dataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");

// 写入数据行

for   (int row = 0; row < myData.Rows.Count; row++)

{

IRow sheetRow = sheet.GetRow(curRow);

if (sheetRow == null)

sheetRow = sheet.CreateRow(curRow);

for (int col = 0; col < myData.Columns.Count; col++)

{

int colIndex = col + myBeginColumn;

ICell cell = sheetRow.GetCell(colIndex);

if (cell == null) 

cell = sheetRow.CreateCell(colIndex);

if (myData.Rows[row][col] == DBNull.Value)

{

cell.SetCellValue("");

}

else

{

Type dataType = myData.Columns[col].DataType;

if (Comm.IsNumeric(dataType))

{

cell.SetCellType(CellType.Numeric);

double result;

if   (Obj.TryToDbl(myData.Rows[row][col], out result))

cell.SetCellValue(result);

}

else if (dataType.Name == "DateTime")

{

cell.CellStyle = datetimeStyle;

DateTime result;

if   (Obj.TryToDate(myData.Rows[row][col], out result))

cell.SetCellValue(result);

}

else if (dataType.Name == "Boolean")

{

cell.SetCellType(CellType.Boolean);

cell.SetCellValue(Obj.ToBool(myData.Rows[row][col]));

}

else

{

cell.SetCellType(CellType.String);

cell.SetCellValue(Obj.ToStr(myData.Rows[row][col]));

}

}

}

curRow++;

}

//

using (FileStream fs1 =

new FileStream(myFileName, FileMode.Create, FileAccess.Write))

{

wb.Write(fs1);

}

}

return   true;

}

}

catch

{

return   false;

}

}

// 写入已存在的xls文件

private bool   WriteXlsxExisted()

{

try

{

if (myData   == null || myData.Columns.Count < 1 ||

File.Exists(myFileName) == false)

return   false;

//

using   (FileStream fs = new FileStream(myFileName,

FileMode.Open, FileAccess.Read))

{

using   (XSSFWorkbook wb = new XSSFWorkbook(fs))

{

int   sheetIndex = 0;

if   (mySheetName != null && mySheetName.Trim().Length > 0)

sheetIndex = wb.GetSheetIndex(mySheetName);

if   (sheetIndex == -1) sheetIndex = 0;

ISheet sheet = wb.GetSheetAt(sheetIndex);

//

int   curRow = myBeginRow;

// 写入列名

if   (myWriteColumnName)

{

IRow sheetRow = sheet.GetRow(curRow);

if (sheetRow == null)

sheetRow = sheet.CreateRow(curRow);

for (int col = 0; col < myData.Columns.Count; col++)

{

int colIndex = col + myBeginColumn;

ICell cell = sheetRow.GetCell(colIndex);

if (cell == null)

cell = sheetRow.CreateCell(colIndex);

cell.SetCellType(CellType.String);

cell.SetCellValue(myData.Columns[col].ColumnName);

}

curRow++;

}

// 日期和时间格式

IDataFormat dataFormat = wb.CreateDataFormat();

ICellStyle datetimeStyle = wb.CreateCellStyle();

datetimeStyle.DataFormat =

dataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");

// 写入数据行

for   (int row = 0; row < myData.Rows.Count; row++)

{

IRow sheetRow = sheet.GetRow(curRow);

if (sheetRow == null)

sheetRow = sheet.CreateRow(curRow);

for (int col = 0; col < myData.Columns.Count; col++)

{

int colIndex = col + myBeginColumn;

ICell cell = sheetRow.GetCell(colIndex);

if (cell == null)

cell = sheetRow.CreateCell(colIndex);

if (myData.Rows[row][col] == DBNull.Value)

{

cell.SetCellValue("");

}

else

{

Type dataType = myData.Columns[col].DataType;

if (Comm.IsNumeric(dataType))

{

cell.SetCellType(CellType.Numeric);

double result;

if   (Obj.TryToDbl(myData.Rows[row][col], out result))

cell.SetCellValue(result);

}

else if (dataType.Name == "DateTime")

{

cell.CellStyle = datetimeStyle;

DateTime result;

if   (Obj.TryToDate(myData.Rows[row][col], out result))

cell.SetCellValue(result);

}

else if (dataType.Name == "Boolean")

{

cell.SetCellType(CellType.Boolean);

cell.SetCellValue(Obj.ToBool(myData.Rows[row][col]));

}

else

{

cell.SetCellType(CellType.String);

cell.SetCellValue(Obj.ToStr(myData.Rows[row][col]));

}

}

}

curRow++;

}

//

using (FileStream fs1 =

new FileStream(myFileName, FileMode.Create, FileAccess.Write))

{

wb.Write(fs1);

}

}

return   true;

}

}

catch

{

return   false;

}

}

}

}

C#

using System;

using System.Data;

using cfx.office;

namespace csfx_demo

{

class Program

{

static void   Main(string[] args)

{

DataTable data =   tApp.DbJet.GetTable(@"select * from t1");

bool result = 

tExcelWriter.Create(@"d:\t1.xls", data,"数据1").WriteExisted();

Console.WriteLine(result);

}

}

}

C#

using System;

using System.IO;

using System.Data;

using NPOI.SS.UserModel;

using NPOI.HSSF.UserModel;

using NPOI.XSSF.UserModel;

namespace cfx.office

{

public class   tExcelWriter

{

// 其它代码

// 按模板写入，参数为模板文件路径

public bool   WriteByTemplate(string template)

{

try

{

if   (File.Exists(template) == false) 

return   false;

File.Copy(template, myFileName, true);

return   WriteExisted();

}

catch { return   false; }

}

//

}

}

C#

using System;

using System.Data;

using cfx.office;

namespace csfx_demo

{

class Program

{

static void   Main(string[] args)

{

DataTable data =   tApp.DbJet.GetTable(@"select * from t1");

bool result =

tExcelWriter.Create(@"d:\t1.xlsx", data, null, 5, 3, false)

.WriteByTemplate(@"d:\template.xlsx");

Console.WriteLine(result);

}

}

}

C#

using System;

using System.Data;

using NPOI.SS.UserModel;

namespace cfx.office

{

public class   tExcelReader

{

// Excel文件名

private string   myFileName;

// 读取式作表索引

private int   mySheetIndex;

// 如果设置工作表名称，则优先使用

private string   mySheetName;

// 开始读取数据的行和列

private int   myBeginColumn;

private int   myBeginRow;

// 第一行作为列名

private bool   myFirstRowAsColumnName;

// 读取的行数，大于0时有效，否则读取全部

private int   myReadRowCount;

//

private   tExcelReader() { }

//

public static   tExcelReader Create(string filename,

int sheetIndex =   0, string sheetName = null,

int beginRow =   1, int beginCol = 1,

bool   firstRowAsColName = true, int readRowCount = -1)

{

return new   tExcelReader()

{

myFileName =   filename,

mySheetIndex   = sheetIndex,

mySheetName   = sheetName,

myBeginRow =   beginRow - 1,

myBeginColumn = beginCol - 1,

myFirstRowAsColumnName = firstRowAsColName,

myReadRowCount = readRowCount

};

}

// 执行读取操作

public DataTable   Read()

{

try

{

IWorkbook wb   = WorkbookFactory.Create(myFileName);

// 优先使用名称获取工作表

if   (mySheetName != null)

mySheetIndex = wb.GetSheetIndex(mySheetName);

if   (mySheetIndex < 0 || mySheetIndex > wb.NumberOfSheets - 1)

mySheetIndex = 0;

ISheet sheet   = wb.GetSheetAt(mySheetIndex);

// 读取第一行列数为全部行读取列数

int colCount   = sheet.GetRow(myBeginRow).LastCellNum;

int rowCount   = sheet.LastRowNum + 1;

// 读取数据并添加到DataTable对象

DataTable   tbl = new DataTable();

// 添加DataTable对象的列

for (int col   = myBeginColumn; col < colCount; col++)

tbl.Columns.Add();

// 

IRow   readRow;

// 读取行数据计数

int   readRowCounter = 0;

//

for (int row   = myBeginRow; row < rowCount; row++)

{

readRow   = sheet.GetRow(row);

DataRow   dataRow = tbl.NewRow();

for (int   col = myBeginColumn; col < colCount; col++)

{

int   tblColIndex = col - myBeginColumn;

try

{

ICell cell = readRow.GetCell(col);

if (cell == null)

{

dataRow[tblColIndex] = DBNull.Value;

}

else if (cell.CellType == CellType.Numeric)

{

// Excel中的日期时间值是Double类型

if (DateUtil.IsCellDateFormatted(cell))

dataRow[tblColIndex] = cell.DateCellValue;

else

dataRow[tblColIndex] = cell.NumericCellValue;

}

else if (cell.CellType == CellType.Boolean)

{

dataRow[tblColIndex] =

cell.BooleanCellValue ? 1 : 0;

}

else

{

// 其它格式以文本形式显示，纯空白字符设置为空值

string val = cell.StringCellValue.Trim();

if (val.Length == 0)

dataRow[tblColIndex] = DBNull.Value;

else

dataRow[tblColIndex] = val;

}

}

catch

{

dataRow[tblColIndex] = DBNull.Value;

}

}

tbl.Rows.Add(dataRow);

// 读取行

readRowCounter++;

if   (myReadRowCount > 0 && myReadRowCount == readRowCounter)

break;

}

// 数据第一行提升为标题，然后从数据行删除

if   (myFirstRowAsColumnName)

{

string   sName;

for (int   col = 0; col < tbl.Columns.Count; col++)

{

sName = tbl.Rows[0][col].ToString().Trim();

if   (sName != "")

tbl.Columns[col].ColumnName = sName;

}

tbl.Rows.RemoveAt(0);

}

//

return tbl;

}

catch

{

return null;

}

}

//

}

}

C#

using System;

using System.Data;

using cfx.office;

namespace csfx_demo

{

class Program

{

static void   Main(string[] args)

{

DataTable data =   tExcelReader.Create(@"d:\t1.xls").Read();

Console.WriteLine(data.Rows.Count);

Console.WriteLine(data.Columns.Count);

for(int col = 0;   col < data.Columns.Count; col++)

{

Console.WriteLine(data.Columns[col].ColumnName);

}

}

}

}