private void button1_Click(object sender, EventArgs e){ #region 驗證可操作性 //申明保存對話框 SaveFileDialog dlg = new SaveFileDialog(); //默然文件后綴 dlg.DefaultExt = "xls "; //文件后綴列表 dlg.Filter = "EXCEL文件(*.XLS)|*.xls "; //默然路徑是系統當前路徑 dlg.InitialDirectory = Directory.GetCurrentDirectory(); string fileNameString = dlg.InitialDirectory + "\\printExcel.xls";
//打開保存對話框 if (dlg.ShowDialog() == DialogResult.Cancel) return; //返回文件路徑 fileNameString = dlg.FileName;
//驗證strFileName是否為空或值無效 if (fileNameString.Trim() == " ") { return; } //定義表格內數據的行數和列數 int rowscount = dataGridView1.Rows.Count; int colscount = dataGridView1.Columns.Count - 1; //行數必須大于0 if (rowscount <= 0) { MessageBox.Show("沒有數據可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; }
//列數必須大于0 if (colscount <= 0) { MessageBox.Show("沒有數據可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; }
//行數不可以大于65536 if (rowscount > 65536) { MessageBox.Show("數據記錄數太多(最多不能超過65536條)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; }
//列數不可以大于255 if (colscount > 255) { MessageBox.Show("數據記錄行數太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; }
//驗證以fileNameString命名的文件是否存在，如果存在刪除它 FileInfo file = new FileInfo(fileNameString); if (file.Exists) { try { file.Delete(); } catch (Exception error) { MessageBox.Show(error.Message, "刪除失敗 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } } #endregion
#region 導出Excel var dataTableTemp = DataGridViewToDataTable(dataGridView1); ToExcel(dataTableTemp, fileNameString); #endregion
MessageBox.Show(fileNameString + "\n\n導出完畢! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);}
/// <summary>/// DataGridView轉DataTable/// </summary>/// <param name="dataGridView"></param>/// <returns></returns>public static DataTable DataGridViewToDataTable(DataGridView dataGridView){ DataTable dt = new DataTable(); foreach (DataGridViewColumn column in dataGridView.Columns) { dt.Columns.Add(column.HeaderText, typeof(string)); // 假設類型以string的方式添加 } foreach (DataGridViewRow row in dataGridView.Rows) { if (!row.IsNewRow) // 忽略添加空行（通常最后一行是空行） { DataRow dataRow = dt.NewRow(); for (int i = 0; i < dataGridView.Columns.Count; i++) { dataRow[i] = row.Cells[i].Value; } dt.Rows.Add(dataRow); } } return dt;}
#region 數據表保存至Excel/// <summary>/// DataTable數據表保存至Excel/// </summary>/// <param name="dt">數據源</param>/// <param name="filePath">文件完整路徑</param>public static void ToExcel(DataTable dt, string filePath){ string subTitle = string.Empty;
//新建工作簿 Workbook wb = new Workbook(); //新建工作表 Worksheet ws = wb.Worksheets[0];
ws.Name = dt.TableName; //dt.Columns.Remove("Id");
int rowIndex = 0; int colIndex = 0; int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count;
ws.Cells.SetRowHeight(rowIndex, 25);//設置行高
//創建樣式 Style style = wb.Styles[wb.Styles.Add()];//新增樣式 style.HorizontalAlignment = TextAlignmentType.Center; //單元格內容的水平對齊方式文字居中 style.Font.Name = "宋體"; //字體 //style.Font.IsBold = true; //設置粗體 //style.Font.Color = Color.White;//設置字體顏色 style.Font.Size = 10; //設置字體大小 //style.ForegroundColor = Color.FromArgb(0, 196, 180); //背景色 style.Pattern = BackgroundType.Solid;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.TopBorder].Color = Color.Black; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].Color = Color.Black; style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.LeftBorder].Color = Color.Black; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].Color = Color.Black;
//列名的處理 for (int i = 0; i < colCount; i++) { ws.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName); ws.Cells[rowIndex, colIndex].SetStyle(style);//給單元格關聯樣式 colIndex++; }
Style style2 = wb.Styles[wb.Styles.Add()];//新增樣式 style2.Font.Name = "宋體";//文字字體 style2.Font.Size = 10;//文字大小 style2.ShrinkToFit = true; style2.VerticalAlignment = TextAlignmentType.Center;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.TopBorder].Color = Color.Black; style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.BottomBorder].Color = Color.Black; style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.LeftBorder].Color = Color.Black; style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.RightBorder].Color = Color.Black;
rowIndex++;
for (int i = 0; i < rowCount; i++) { ws.Cells.SetRowHeight(rowIndex, 25);//設置行高 colIndex = 0; for (int j = 0; j < colCount; j++) { if (dt.Columns[j].ColumnName == "凈重(T)" || dt.Columns[j].ColumnName == "扣雜(T)" || dt.Columns[j].ColumnName == "標準水分%" || dt.Columns[j].ColumnName == "實測水分%" || dt.Columns[j].ColumnName == "扣水噸位(T)" || dt.Columns[j].ColumnName == "標準灰分%" || dt.Columns[j].ColumnName == "實測灰分%" || dt.Columns[j].ColumnName == "扣灰噸位(T)" || dt.Columns[j].ColumnName == "扣雜后凈重(T)" || dt.Columns[j].ColumnName == "結算重量(T)" || dt.Columns[j].ColumnName == "結算單價(元/T)" || dt.Columns[j].ColumnName == "結算金額(元)") { ws.Cells[rowIndex, colIndex].PutValue((dt.Rows[i][j].ToString() == "" ? 0 : Math.Round(decimal.Parse(dt.Rows[i][j].ToString()), 2)).ToString("0.00")); } else { ws.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString() == "" ? null : dt.Rows[i][j].ToString()); } if (i == rowCount - 1) { //style2.ForegroundColor = Color.Gray; style2.Pattern = BackgroundType.Solid; ws.Cells[rowIndex, colIndex].SetStyle(style2);//給單元格關聯樣式 } else { style2.ForegroundColor = Color.White; style2.Pattern = BackgroundType.Solid; ws.Cells[rowIndex, colIndex].SetStyle(style2);//給單元格關聯樣式 } colIndex++; } rowIndex++; }
//設置所有列為自適應列寬 ws.AutoFitColumns();
for (int col = 0; col < colCount; col++) { ws.Cells.SetColumnWidthPixel(col, ws.Cells.GetColumnWidthPixel(col) + 20); }
if (System.IO.File.Exists(filePath)) System.IO.File.Delete(filePath); System.IO.FileStream fs = System.IO.File.Create(filePath); fs.Close(); fs.Dispose(); wb.Save(filePath);}#endregion
閱讀原文：原文鏈接
該文章在 2025/2/21 12:26:34 編輯過
400 186 1886
