/// <summary>
/// DataTable数据导出为xls
/// </summary>
/// <param name="grid">DataGridView</param>
/// <param name="saveFileDialog">SaveFileDialog instance</param>
/// <param name="visibleOnly">只导出可见的列</param>
/// <param name="includeNewRow">是否导出新行</param>
public static bool OutputXLSFromDataGridView(DataGridView grid, SaveFileDialog saveFileDialog, bool visibleOnly, bool includeNewRow)
{
if (grid == null || grid.Rows.Count == 0)
{
return(false);
}
DialogResult rs = saveFileDialog.ShowDialog();
if (rs != DialogResult.OK)
{
return(false);
}
try
{
ExcelWriter excel = new ExcelWriter(saveFileDialog.FileName);
excel.BeginWrite();
short cols = 0;
foreach (DataGridViewColumn col in grid.Columns)
{
if (visibleOnly && !col.Visible)
{
continue;
}
string column = col.HeaderText;
excel.WriteString(0, cols, column);
cols++;
}
short rows = 1;
foreach (DataGridViewRow gr in grid.Rows)
{
if (!includeNewRow && gr.IsNewRow)
{
continue;
}
cols = 0;
foreach (DataGridViewCell cell in gr.Cells)
{
if (visibleOnly && !cell.Visible)
{
continue;
}
excel.WriteString(rows, cols, cell.Value == null ? string.Empty : cell.Value.ToString());
cols++;
}
rows++;
}
excel.EndWrite();
Process.Start(saveFileDialog.FileName);
return(true);
}
catch
{
return(false);
}
}
/// <summary>
/// 导出DataTable为XLS，并打开生成的XLS
/// </summary>
/// <param name="columns">列名</param>
/// <param name="dt">表</param>
/// <param name="saveFileDialog">对话框</param>
/// <returns></returns>
public static bool OutputXLSFromDataTable(string[] columns, DataTable dt, SaveFileDialog saveFileDialog)
{
if (Common.DataTableIsEmpty(dt))
{
return(false);
}
DialogResult rs = saveFileDialog.ShowDialog();
if (rs != DialogResult.OK)
{
return(false);
}
try
{
ExcelWriter excel = new ExcelWriter(saveFileDialog.FileName);
excel.BeginWrite();
short cols = 0;
if (columns == null || columns.Length == 0)
{ //若没有传列名，则以dt的列名做为Excel的列名
foreach (DataColumn col in dt.Columns)
{
excel.WriteString(0, cols, col.ColumnName);
cols++;
}
}
else
{
foreach (string column in columns)
{
excel.WriteString(0, cols, column);
cols++;
}
}
short rows = 1;
foreach (DataRow dr in dt.Rows)
{
cols = 0;
foreach (DataColumn col in dt.Columns)
{
excel.WriteString(rows, cols, dr[col].ToString());
cols++;
}
rows++;
}
excel.EndWrite();
Process.Start(saveFileDialog.FileName);
return(true);
}
catch
{
return(false);
}
}
/// <summary>
/// 导出DataTable为XLS，并打开生成的XLS
/// </summary>
/// <param name="columns">列名</param>
/// <param name="dt">表</param>
/// <param name="saveFileDialog">对话框</param>
/// <returns></returns>
public static bool OutputXLSFromDataTable(string[] columns, DataTable dt, string strPath)
{
if (Common.DataTableIsEmpty(dt))
{
return(false);
}
try
{
if (File.Exists(strPath))
{
File.Delete(strPath);
}
ExcelWriter excel = new ExcelWriter(strPath);
excel.BeginWrite();
short cols = 0;
if (columns == null || columns.Length == 0)
{//若没有传列名，则以dt的列名做为Excel的列名
foreach (DataColumn col in dt.Columns)
{
excel.WriteString(0, cols, col.ColumnName);
cols++;
}
}
else
{
foreach (string column in columns)
{
excel.WriteString(0, cols, column);
cols++;
}
}
short rows = 1;
foreach (DataRow dr in dt.Rows)
{
cols = 0;
foreach (DataColumn col in dt.Columns)
{
excel.WriteString(rows, cols, dr[col].ToString());
cols++;
}
rows++;
}
excel.EndWrite();
return(true);
}
catch
{
return(false);
}
}
/// <summary>
/// 导出Array为XLS，并打开生成的XLS
/// </summary>
/// <param name="array">二维数组</param>
/// <param name="saveFileDialog">对话框</param>
/// <returns></returns>
public static void OutputXLSFromArray(string[,] array, SaveFileDialog saveFileDialog)
{
DialogResult rs = saveFileDialog.ShowDialog();
if (rs != DialogResult.OK)
{
return;
}
//try
//{
ExcelWriter excel = new ExcelWriter(saveFileDialog.FileName);
excel.BeginWrite();
short cols = 0;
short rows = 0;
int rowLength = array.GetUpperBound(0) + 1;
int columnLength = array.GetUpperBound(1) + 1;
while (rows < rowLength)
{
cols = 0;
while (cols < columnLength)
{
excel.WriteString(rows, cols, array[rows, cols]);
cols++;
}
rows++;
}
excel.EndWrite();
Process.Start(saveFileDialog.FileName);
//}
//catch
//{
// return false;
//}
}
/// <summary>
/// 导出Array为XLS，并打开生成的XLS
/// </summary>
/// <param name="array">数组</param>
/// <param name="saveFileDialog">对话框</param>
/// <returns></returns>
public static void OutputXLSFromArray(string[] array, SaveFileDialog saveFileDialog)
{
DialogResult rs = saveFileDialog.ShowDialog();
if (rs != DialogResult.OK)
{
return;
}
ExcelWriter excel = new ExcelWriter(saveFileDialog.FileName);
excel.BeginWrite();
short cols = 0;
while (cols < array.Length)
{
excel.WriteString(0, cols, array[cols]);
cols++;
}
excel.EndWrite();
Process.Start(saveFileDialog.FileName);
}
/// <summary>
/// 导出DataReader为XLS，并打开生成的XLS
/// </summary>
/// <param name="columns">列名</param>
/// <param name="dr">DataReader</param>
/// <param name="saveFileDialog">对话框</param>
/// <returns></returns>
public static void OutputXLSFromDataReader(string[] columns, IDataReader dr, SaveFileDialog saveFileDialog)
{
DialogResult rs = saveFileDialog.ShowDialog();
if (rs != DialogResult.OK)
{
return;
}
ExcelWriter excel = new ExcelWriter(saveFileDialog.FileName);
excel.BeginWrite();
short cols = 0;
foreach (string column in columns)
{
excel.WriteString(0, cols, column);
cols++;
}
short rows = 1;
using (dr)
{
while (dr.Read())
{
cols = 0;
while (cols < dr.FieldCount)
{
excel.WriteString(rows, cols, dr[cols].ToString());
cols++;
}
rows++;
}
}
excel.EndWrite();
Process.Start(saveFileDialog.FileName);
}