DataGridView导出成EXCEL文挡(是Winform,不是Web)

Question

代码越少越好!!在百度查了下代码太多了!!谁给点简洁的代码!!!谢谢.(自己测试好的代码发过来!)

Answer 1

给你一个思路吧，代码就不写了，我想应该是比较简单1.gridview的内容用excel打开，可以通过剪贴板来完成
2.如果要保存成excel文件，可以考虑将gridview存储为cvs格式（逗号分隔的文本），excel也是可以打开的如果你要直接存成xls文件，肯定会稍微复杂一些

Answer 2

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                strExcel = string.Format("select * from [{0}$]", sheetName);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, sheetName);                //如果目标表不存在则创建
                string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
                foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
                {
                    strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
                }
                strSql = strSql.Trim(',') + ")";                using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                {
                    sqlconn.Open();
                    System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                    command.CommandText = strSql;
                    command.ExecuteNonQuery();
                    sqlconn.Close();
                }
                //用bcp导入数据
                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                {
                    bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    bcp.BatchSize = 100;//每次传输的行数
                    bcp.NotifyAfter = 100;//进度提示的行数
                    bcp.DestinationTableName = sheetName;//目标表
                    bcp.WriteToServer(ds.Tables[0]);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

Answer 3

        /// <summary>
        /// 导出指定的Excel文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的Excel文件名</param>
        public void ExportToExcel(DataSet ds, string strExcelFileName)
        {
            if (ds.Tables.Count == 0 || strExcelFileName == "") return;
            doExport(ds, strExcelFileName);
        }        /// <summary>
        /// 导出用户选择的Excel文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToExcel(DataSet ds)
        {
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                doExport(ds, saveFileDlg.FileName);
        }        public void setRange(Microsoft.Office.Interop.Excel.Range sRange)
        {
            sRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
            sRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
            sRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
            sRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
            sRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
            sRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            sRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            sRange.Font.Size = 10;
            sRange.EntireColumn.AutoFit();
            sRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            sRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
        }
        /// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的文件名</param>
        private void doExport(DataSet ds, string strExcelFileName)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            int rowIndex = 1;
            int colIndex = 0;
            string alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            string colStr = "";
            excel.Application.Workbooks.Add(true);
            System.Data.DataTable table = ds.Tables[0];
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            if (colIndex < 27) colStr = alpha.Substring(colIndex - 1, 1);
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }
            Microsoft.Office.Interop.Excel.Workbook wbook = excel.Workbooks[1];
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)wbook.Worksheets[1];            Microsoft.Office.Interop.Excel.Range selectRange = (Microsoft.Office.Interop.Excel.Range)worksheet.get_Range("A1", colStr + "1");
            selectRange.Interior.ColorIndex = 37;
            setRange(selectRange);            selectRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            selectRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;            selectRange = (Microsoft.Office.Interop.Excel.Range)worksheet.get_Range("A2", colStr + rowIndex.ToString());
            setRange(selectRange);
            
            excel.Visible = false;
            excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();//垃圾回收
        }

Answer 4

http://blog.csdn.net/wf_car/archive/2009/03/13/3988283.aspx

Answer 5

 /// <summary>
        /// 将DataGridView列表数据导出到Excel
        /// </summary>
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="title">导到Excl显示的标题</param>
        public static void DataGridViewOutPutExcel(System.Windows.Forms.DataGridView dgv, string title)
        {
            try
            {
                int rowCount = dgv.RowCount;
                if (rowCount <= 0)
                {
                    MessageBox.Show("表格中没有数据，无法导出数据！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                int columnCount = 0;
                foreach (DataGridViewColumn dHeader in dgv.Columns)
                {
                    if (dHeader.Visible == true)
                        columnCount++;
                }
                Microsoft.Office.Interop.Excel.Application exc = new Microsoft.Office.Interop.Excel.Application();                if (exc == null)
                {
                    throw new Exception("Excel无法启动");
                }
                Workbooks workbooks = exc.Workbooks;
                _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Sheets sheets = exc.Sheets;
                _Worksheet worksheet = (_Worksheet)sheets[1];
                if (worksheet == null)
                {
                    throw new Exception("Worksheet error");
                }                Range r = worksheet.get_Range(exc.Cells[1, 1], exc.Cells[1, columnCount]);
                exc.Visible = false;
                r.MergeCells = true;
                if (r == null)
                {
                    MessageBox.Show("Range无法启动");
                    throw new Exception("Range error");
                }                //标题
                exc.ActiveCell.FormulaR1C1 = title;
                exc.ActiveCell.Font.Size = 12;
                exc.ActiveCell.Font.Bold = true;                //列头
                int ColIndex = 1;
                foreach (DataGridViewColumn dHeader in dgv.Columns)
                {
                    if (dHeader.Visible == true)
                        worksheet.Cells[2, ColIndex++] = dHeader.HeaderText;
                }                //填充单元格
                ColIndex = 0;
                foreach (DataGridViewColumn col in dgv.Columns)
                {
                    if (col.Visible == true)
                    {
                        ColIndex++;
                        for (int i = 0; i < rowCount; i++)
                        {
                            if (dgv.Rows[i].Cells[col.Index].FormattedValue.ToString() == null)
                                continue;
                            worksheet.Cells[i + 3, ColIndex] = dgv.Rows[i].Cells[col.Index].FormattedValue.ToString();                        }
                    }
                }
                exc.Cells.EntireColumn.AutoFit();
                exc.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                exc.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                exc.Visible = true;            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Answer 6

现在写的代码还有点小问题,就是报"索引超出了数组界限";加了try()catch也不行!!加上(saveFileDialog.ShowDialog() == DialogResult.OK)条件,要点击2次取消按扭才行.请问有什么好办法解决这个问题???

Answer 7

可是 怎么样 导成多个 sheet呢？