public static void DataReaderToExcelFile(IDataReader reader, string saveFileName, List<SheetOptions> optionsList)
{
if (reader == null) throw new ArgumentNullException("reader");
if (string.IsNullOrWhiteSpace(saveFileName)) throw new ArgumentException("字符串参数不允许为null或者空值.", saveFileName);
// 创建一个空Xls文档
XlsDocument xls = new XlsDocument();
int sheetIndex = 0;
int sheetDefaultNameIndex = 1;
do
{
// 创建一个工作表
SheetOptions options = (optionsList == null || sheetIndex >= optionsList.Count)
? options = new SheetOptions() { SheetName = SheetOptions.DefaultSheetName + (sheetDefaultNameIndex++).ToString() }
: optionsList[sheetIndex];
Worksheet sheet = xls.Workbook.Worksheets.Add(options.SheetName);
// 创建表格数据
if (reader.Read())
{
// 标题
ushort rowNo = 1,
columnNo = 1;
int fieldCnt = reader.FieldCount;
for (int i = 0; i < fieldCnt; i++, columnNo++)
{
sheet.Cells.Add(rowNo, columnNo, reader.GetName(i));
}
// 正文数据
do
{
rowNo++;
columnNo = 1;
for (int i = 0; i < fieldCnt; i++, columnNo++)
{
string value = Convert.ToString(reader[i]); // null值将显示为空
sheet.Cells.Add(rowNo, columnNo, value);
}
} while (reader.Read());
}
sheetIndex++;
} while (reader.NextResult());
trySaveXlsFile(xls, saveFileName);
}
public static void DataTableToExcelFile(DataTable dataTable, string saveFileName, SheetOptions options)
{
if (dataTable == null) throw new ArgumentNullException("dataTable");
if (string.IsNullOrWhiteSpace(saveFileName)) throw new ArgumentException("字符串参数不允许为null或者空值.", saveFileName);
if (options == null)
options = new SheetOptions();
ushort rowNo = 0, columnNo = 0;
int columnNum = dataTable.Columns.Count;
// 创建一个空Xls文档
XlsDocument xls = new XlsDocument();
// 创建一个工作表
Worksheet sheet = xls.Workbook.Worksheets.Add(options.SheetName);
// 创建表格标题
rowNo = 1;
columnNo = 1;
for(int i = 0;i< dataTable.Columns.Count; i++, columnNo++)
{
#if DEBUG
Console.WriteLine("{0},{1},{2}", rowNo, columnNo, dataTable.Columns[i].ColumnName);
#endif
sheet.Cells.Add(rowNo, columnNo, dataTable.Columns[i].ColumnName);
}
// 创建正文数据
rowNo++;
for (int i = 0; i < dataTable.Rows.Count; i++, rowNo++)
{
DataRow row = dataTable.Rows[i];
columnNo = 1;
for (int j = 0; j < columnNum; j++, columnNo++)
{
string value = Convert.ToString(row[j]);
#if DEBUG
Console.WriteLine("{0},{1},{2}", rowNo, columnNo, value);
#endif
sheet.Cells.Add(rowNo, columnNo, value);
}
}
// 保存文件
trySaveXlsFile(xls, saveFileName);
}
