- #region DataGridView数据显示到Excel
-
-
-
-
-
-
-
-
-
-
- publicbool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
- {
- if (dgv.Rows.Count == 0)
- returnfalse;
-
- Excel.Application excel = new Excel.Application();
- excel.Application.Workbooks.Add(true);
- excel.Visible = isShowExcle;
-
- for (int i = 0; i < dgv.ColumnCount; i++)
- {
- excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
- }
-
- for (int i = 0; i < dgv.RowCount - 1; i++)
- {
- for (int j = 0; j < dgv.ColumnCount; j++)
- {
- if (dgv[j, i].ValueType == typeof(string))
- {
- excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
- }
- else
- {
- excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
- }
- }
- }
- returntrue;
- }
- #endregion
- #region DateGridView导出到csv格式的Excel
-
-
-
-
-
-
-
- privatevoid DataGridViewToExcel(DataGridView dgv)
- {
- SaveFileDialog dlg = new SaveFileDialog();
- dlg.Filter = "Execl files (*.xls)|*.xls";
- dlg.FilterIndex = 0;
- dlg.RestoreDirectory = true;
- dlg.CreatePrompt = true;
- dlg.Title = "保存为Excel文件";
-
- if (dlg.ShowDialog() == DialogResult.OK)
- {
- Stream myStream;
- myStream = dlg.OpenFile();
- StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
- string columnTitle = "";
- try
- {
-
- for (int i = 0; i < dgv.ColumnCount; i++)
- {
- if (i > 0)
- {
- columnTitle += "\t";
- }
- columnTitle += dgv.Columns[i].HeaderText;
- }
- sw.WriteLine(columnTitle);
-
-
- for (int j = 0; j < dgv.Rows.Count; j++)
- {
- string columnValue = "";
- for (int k = 0; k < dgv.Columns.Count; k++)
- {
- if (k > 0)
- {
- columnValue += "\t";
- }
- if (dgv.Rows[j].Cells[k].Value == null)
- columnValue += "";
- else
- columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
- }
- sw.WriteLine(columnValue);
- }
- sw.Close();
- myStream.Close();
- }
- catch (Exception e)
- {
- MessageBox.Show(e.ToString());
- }
- finally
- {
- sw.Close();
- myStream.Close();
- }
- }
- }
- #endregion
- #region DataGridView导出到Excel,有一定的判断性
-
-
-
-
-
-
-
-
-
- publicstaticvoid DataGridViewToExcel(DataGridView dgv)
- {
- #region 验证可操作性
-
-
- SaveFileDialog dlg = new SaveFileDialog();
-
- dlg.DefaultExt = "xls ";
-
- dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
-
- dlg.InitialDirectory = Directory.GetCurrentDirectory();
-
- if (dlg.ShowDialog() == DialogResult.Cancel) return;
-
- string fileNameString = dlg.FileName;
-
- if (fileNameString.Trim() == " ")
- { return; }
-
- int rowscount = dgv.Rows.Count;
- int colscount = dgv.Columns.Count;
-
- if (rowscount <= 0)
- {
- MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
-
-
- if (colscount <= 0)
- {
- MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
-
-
- if (rowscount > 65536)
- {
- MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
-
-
- if (colscount > 255)
- {
- MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
-
-
- FileInfo file = new FileInfo(fileNameString);
- if (file.Exists)
- {
- try
- {
- file.Delete();
- }
- catch (Exception error)
- {
- MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
- return;
- }
- }
- #endregion
- Excel.Application objExcel = null;
- Excel.Workbook objWorkbook = null;
- Excel.Worksheet objsheet = null;
- try
- {
-
- objExcel = new Microsoft.Office.Interop.Excel.Application();
- objWorkbook = objExcel.Workbooks.Add(Missing.Value);
- objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
-
- objExcel.Visible = false;
-
-
- int displayColumnsCount = 1;
- for (int i = 0; i <= dgv.ColumnCount - 1; i++)
- {
- if (dgv.Columns[i].Visible == true)
- {
- objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
- displayColumnsCount++;
- }
- }
-
-
-
-
-
-
-
- for (int row = 0; row <= dgv.RowCount - 1; row++)
- {
-
-
- displayColumnsCount = 1;
- for (int col = 0; col < colscount; col++)
- {
- if (dgv.Columns[col].Visible == true)
- {
- try
- {
- objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
- displayColumnsCount++;
- }
- catch (Exception)
- {
-
- }
-
- }
- }
- }
-
-
-
- objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
- Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
- Missing.Value, Missing.Value);
- }
- catch (Exception error)
- {
- MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
- return;
- }
- finally
- {
-
- if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
- if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
- if (objExcel != null) objExcel.Quit();
-
- objsheet = null;
- objWorkbook = null;
- objExcel = null;
- }
- MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
-
- }
- #endregion
posted on
2014-01-20 17:53
乖乖宝贝娃娃
阅读(
305)
评论()
编辑
收藏
举报