• 查看excel文件和导出


       public System.Data.DataTable dsmdata()//查看
            { 
            public static string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =d:\\csd.xls;Extended Properties=Excel 8.0";
                OleDbConnection conn = new OleDbConnection(strCon);
                string sql = "select * from [Sheet1$]";
                conn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strCon);
                ds = new DataSet();
                myCommand.Fill(ds, "[Sheet1$]");
                conn.Close();
                return ds.Tables["[Sheet1$]"];
                //datagrid1.DataMember = "[Sheet1$]";
                //datagrid1.DataSource = ds;
                //datagrid1.DataBind();
            }

     public static bool ExportForDataGridview(DataGridView gridView, string fileName, bool isShowExcle)

    //此方法需要加excel引用
            {

                //建立Excel对象

                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                try
                {
                    if (app == null)
                    {
                        return false;
                    }

                    app.Visible = isShowExcle;
                    Workbooks workbooks = app.Workbooks;
                    _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                    Sheets sheets = workbook.Worksheets;
                    _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
                    if (worksheet == null)
                    {
                        return false;
                    }
                    string sLen = "";
                    //取得最后一列列名
                    char H = (char)(64 + gridView.ColumnCount / 26);
                    char L = (char)(64 + gridView.ColumnCount % 26);
                    if (gridView.ColumnCount < 26)
                    {
                        sLen = L.ToString();
                    }
                    else
                    {
                        sLen = H.ToString() + L.ToString();
                    }


                    //标题
                    string sTmp = sLen + "1";
                    Range ranCaption = worksheet.get_Range(sTmp, "A1");
                    string[] asCaption = new string[gridView.ColumnCount];
                    for (int i = 0; i < gridView.ColumnCount; i++)
                    {
                        asCaption[i] = gridView.Columns[i].HeaderText;
                    }
                    ranCaption.Value2 = asCaption;

                    //数据
                    object[] obj = new object[gridView.Columns.Count];
                    for (int r = 0; r < gridView.RowCount - 1; r++)
                    {
                        for (int l = 0; l < gridView.Columns.Count; l++)
                        {
                            if (gridView[l, r].ValueType == typeof(DateTime))
                            {
                                obj[l] = gridView[l, r].Value.ToString();
                            }
                            else
                            {
                                obj[l] = gridView[l, r].Value;
                            }
                        }
                        string cell1 = sLen + ((int)(r + 2)).ToString();
                        string cell2 = "A" + ((int)(r + 2)).ToString();
                        Range ran = worksheet.get_Range(cell1, cell2);
                        ran.Value2 = obj;
                    }
                    //保存
                    workbook.SaveCopyAs(fileName);
                    workbook.Saved = true;
                }
                finally
                {
                    //关闭
                    app.UserControl = false;
                    app.Quit();
                }
                return true;

            }

     public  void SaveAs(DataGridView dgvAgeWeekSex)
        
            {

                SaveFileDialog saveFileDialog = new SaveFileDialog();

                saveFileDialog.Filter = "Execl files (*.xls)|*.xls";

                saveFileDialog.FilterIndex = 0;

                saveFileDialog.RestoreDirectory = true;

                saveFileDialog.CreatePrompt = true;

                saveFileDialog.Title = "Export Excel File To";


                saveFileDialog.ShowDialog();


                Stream myStream;

                myStream = saveFileDialog.OpenFile();

                //StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));

                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));

                string str = "";

                try
                {

                    //写标题

                    for (int i = 0; i < dgvAgeWeekSex.ColumnCount; i++)
                    {

                        if (i > 0)
                        {

                            str += "\t";

                        }

                        str += dgvAgeWeekSex.Columns[i].HeaderText;

                    }


                    sw.WriteLine(str);

                    //写内容

                    for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)
                    {

                        string tempStr = "";

                        for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++)
                        {

                            if (k > 0)
                            {

                                tempStr += "\t";

                            }

                            tempStr += dgvAgeWeekSex.Rows[j].Cells[k].Value.ToString();

                        }

                        sw.WriteLine(tempStr);

                    }

                    sw.Close();

                    myStream.Close();

                }

                catch (Exception e)
                {

                    MessageBox.Show(e.ToString());

                }

                finally
                {

                    sw.Close();

                    myStream.Close();

                }

            }
           public void SaveAsdata(System.Data.DataTable dgvAgeWeekSex)
           {

               SaveFileDialog saveFileDialog = new SaveFileDialog();

               saveFileDialog.Filter = "Execl files (*.xls)|*.xls";

               saveFileDialog.FilterIndex = 0;

               saveFileDialog.RestoreDirectory = true;

               saveFileDialog.CreatePrompt = true;

               saveFileDialog.Title = "Export Excel File To";


               saveFileDialog.ShowDialog();


               Stream myStream;

               myStream = saveFileDialog.OpenFile();

               //StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));

               StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));

               string str = "";

               try
               {

                   //写标题

                   for (int i = 0; i < dgvAgeWeekSex.Columns.Count; i++)
                   {

                       if (i > 0)
                       {

                           str += "\t";

                       }

                       str += dgvAgeWeekSex.Columns[i].ColumnName;

                   }


                   sw.WriteLine(str);

                   //写内容

                   for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)
                   {

                       string tempStr = "";

                       for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++)
                       {

                           if (k > 0)
                           {

                               tempStr += "\t";

                           }

                           tempStr += dgvAgeWeekSex.Rows[j][k].ToString();

                       }

                       sw.WriteLine(tempStr);

                   }

                   sw.Close();

                   myStream.Close();

               }

               catch (Exception e)
               {

                   MessageBox.Show(e.ToString());

               }

               finally
               {

                   sw.Close();

                   myStream.Close();

               }

           }

  • 相关阅读:
    备忘链接执行js时注意target必须是_self或者_top
    windows2003 Server远程连接Event Error;event id:12517,12503,1111
    网站配置了Url重写的Handler会导致虚拟目录找不到dll
    SEO 搜索引擎优化技巧
    11款开源Wiki管理系统
    收集了几个优秀的设计公司网站
    8款开源聊天系统和程序/Open Chat
    制作Web应用程序安装程序的方法
    转:推荐21套非常棒的网页设计图标素材
    14款开源文档管理系统
  • 原文地址:https://www.cnblogs.com/jinyuttt/p/2103359.html
Copyright © 2020-2023  润新知