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