C# 怎样读写EXCEL文件

[已解决问题] C# 怎样读写EXCEL文件

提问时间: 2009-05-22 16:11

悬赏分：100 浏览:380 次

能提供个例子连接什么的最好  谢谢啊

提问者：飘云 - 初学一级

所有回答(6)

http://www.cnblogs.com/gaoyuchuanIT/articles/1412898.html

1个月前   回答者：游子 - 菜鸟二级

一直用这个串。

 

 sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + dataFile + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";
            try
            {
                m_connExcel = new OleDbConnection(sConnString);

                if (m_connExcel != null)
                {
                    m_connExcel.Open(); 
                    fRet = true;
                }
            }
            catch (Exception e)
            {
                fRet = false;
                Trace.WriteLine("COleDbExcelWrapper.Open: Open excel file failed! " + e.Message);
            }

 

1个月前   回答者：winzheng - 老鸟四级

   DataSet ds = new DataSet();
        OleDbCommand oleCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", new OleDbConnection(EXCELConnString + Path));
        OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);

        oleAdapter.Fill(ds, "[Sheet1$]");
        // 数据绑定
        return ds.Tables[0];

这是把读取excel文件的方式。读入dataset内后，怎么操作就省略了。

1个月前   回答者：邢少 - 老鸟四级

            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + addr + ";Extended Properties=Excel 8.0";///建立连接,地址为str传递的地址
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [sheet1$]";///SQL操作语句,就是说:取得所有数据从Sheet1

            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            myDataSet = new DataSet();///建立新的数据集myDataSet
            myCommand.Fill(myDataSet, "[sheet1$]");///填充数据集
            myConn.Close();
           
            //读取
            String str;
            str=myDataSet.Tables[0].Rows[0][0].ToString();
            return str;

1个月前   回答者：黄毛怪 - 菜鸟二级

#region 最简单的导出Excel
        public void CreateExcel(DataTable _table, string FileName)
        {
            //FileName = Server.UrlEncode(FileName);
            HttpResponse response = Page.Response;
            response.Clear();
            response.Buffer = true;
            response.Charset = "GB2312";
            response.HeaderEncoding = System.Text.Encoding.GetEncoding("GB2312");
            response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
            response.AddHeader("Content-Type", "application/octet-stream;charset=GB2312");
            response.ContentType = "application/ms-excel";
            string ls_item = "";

            ls_item = "编号\t险种\t保单号\t手续费\t手续费率\t佣金\t备注\t分组序号\n";
            response.Write(ls_item);
            ls_item = "";
            int i = 1;
            foreach (DataRow row in _table.Rows)
            {
                ls_item = i.ToString() + "\t" + row[3] + "\t" + row[4] + "\t" + row[7] + "\t" + row[8] + "\t" + row[9] + "\t" + row[10] + "\t" + i.ToString() + "\n";
response.Write(ls_item);
                i++;
            }
            //写缓冲区中的数据到HTTP头文件中
            response.Flush();
            response.Clear();
            response.End();
        }
        #endregion

        #region 引用了OWC11组件，比较方便，好控制
        private void ExpoertExcel(DataTable _table)
        {
            //请在项目中引用OWC11(COM组件)
            OWC11.SpreadsheetClass xlsheet = new OWC11.SpreadsheetClass();
            ////合并单元格
            //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 8]).set_MergeCells(true);
            //xlsheet.ActiveSheet.Cells[1, 1] = "保险劳务费";
            ////字体加粗
            //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 8]).Font.set_Bold(true);
            ////单元格文本水平居中对齐
            //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 8]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            ////设置字体大小
            //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 8]).Font.set_Size(14);
            //设置列宽
            xlsheet.get_Range(xlsheet.Cells[1,1], xlsheet.Cells[1, 1]).set_ColumnWidth(5);
            xlsheet.get_Range(xlsheet.Cells[1, 2], xlsheet.Cells[1,2]).set_ColumnWidth(12);
            xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(25);
            xlsheet.get_Range(xlsheet.Cells[1, 4], xlsheet.Cells[1, 4]).set_ColumnWidth(15);
            xlsheet.get_Range(xlsheet.Cells[1, 5], xlsheet.Cells[1, 5]).set_ColumnWidth(10);
            xlsheet.get_Range(xlsheet.Cells[1, 6], xlsheet.Cells[1, 6]).set_ColumnWidth(15);
            xlsheet.get_Range(xlsheet.Cells[1, 7], xlsheet.Cells[1, 7]).set_ColumnWidth(10);
            xlsheet.get_Range(xlsheet.Cells[1, 8], xlsheet.Cells[1, 8]).set_ColumnWidth(8);
            //设置行高
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_table.Rows.Count, 8]).set_RowHeight(20);
           
            //画边框线
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_table.Rows.Count, 8]).Borders.set_LineStyle(OWC11.XlLineStyle.xlContinuous);
            //设置字体大小
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_table.Rows.Count, 8]).Font.set_Size(10);
            //写入数据 (这里可根据需要由DS生成)
            int i = 1,j=0;
            int groupID = 0;
            double t1 = 0.0, t2 = 0.0;
            foreach (DataRow row in _table.Select("佣金<1333 and 险种<>''", "佣金 desc")) //注意写入数据时,必须从第一行开始写EXCEL里没有第"0"行
            {
                if (j % 10 == 0)
                {
                    t1 = 0.0;
                    t2 = 0.0;
                    SetTitle(xlsheet, i);
                    i++; i++; groupID++;
                }
                xlsheet.ActiveSheet.Cells[i, 1] = j%10+1;
                xlsheet.ActiveSheet.Cells[i, 2] = row["险种"];
                xlsheet.ActiveSheet.Cells[i, 3] = row["保单号"].ToString()+" ";
                xlsheet.ActiveSheet.Cells[i, 4] = row["手续费"];
                xlsheet.ActiveSheet.Cells[i, 5] = row[8];//"手续费率"
                xlsheet.ActiveSheet.Cells[i, 6] = row["佣金"];
                xlsheet.ActiveSheet.Cells[i, 7] = row["备注"];
                xlsheet.ActiveSheet.Cells[i, 8] = groupID;//分组
                t1 += Convert.ToDouble(row["手续费"]);
                t2 += Convert.ToDouble(row["佣金"]);
                if (j % 10 == 9)
                {
                    SetFoot(xlsheet, ++i, t1, t2);
                    i++;                  
                }
                i++; j++;
            }
            try
            {
                //格式化 Selection.NumberFormatLocal = "0;[红色]0"
                xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[_table.Rows.Count, 3]).set_NumberFormat("0");
                xlsheet.get_Range(xlsheet.Cells[1, 4], xlsheet.Cells[_table.Rows.Count, 4]).set_NumberFormat("￥#,##0.00");
                xlsheet.get_Range(xlsheet.Cells[1, 5], xlsheet.Cells[_table.Rows.Count, 5]).set_NumberFormat("￥#,##0.00");
                xlsheet.get_Range(xlsheet.Cells[1, 6], xlsheet.Cells[_table.Rows.Count, 6]).set_NumberFormat("￥#,##0.00");
                if (_table != null) _table.Dispose();
                xlsheet.Export(System.AppDomain.CurrentDomain.BaseDirectory + "计算并导出后的Excel.xls", OWC11.SheetExportActionEnum.ssExportActionNone, OWC11.SheetExportFormat.ssExportXMLSpreadsheet);
                GC.Collect();
                lblMsg.Text = "导出成功!!!";
            }
            catch
            {
                lblMsg.Text = "请关闭Excel文件，再重试!!!";
                return;
            }
            Response.Redirect("计算并导出后的Excel.xls");          
        }
        private void SetTitle(Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet,int row)       
        {
            //合并单元格
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[row, 1] = "保险劳务费";
            //字体加粗
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).Font.set_Bold(true);
            //单元格文本水平居中对齐
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            //设置字体大小
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).Font.set_Size(14);
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 1]).set_RowHeight(35);

            row++;

            xlsheet.ActiveSheet.Cells[row , 1] = "编号";
            xlsheet.ActiveSheet.Cells[row , 2] = "险种";
            xlsheet.ActiveSheet.Cells[row , 3] = "保单号";
            xlsheet.ActiveSheet.Cells[row , 4] = "手续费";
            xlsheet.ActiveSheet.Cells[row, 5] = "手续费率";
            xlsheet.ActiveSheet.Cells[row, 6] = "佣金";
            xlsheet.ActiveSheet.Cells[row , 7] = "备注";
            xlsheet.ActiveSheet.Cells[row , 8] = "分组序号";//分组

            //字体加粗
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).Font.set_Bold(true);
            //单元格文本水平居中对齐
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            //设置字体大小8
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).Font.set_Size(10);

        }
        private void SetFoot(Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet, int row,double t1,double t2)
        {
            row++;
            xlsheet.ActiveSheet.Cells[row, 1] = "合计";
            //字体加粗
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 1]).Font.set_Bold(true);
            //合并单元格
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 3]).set_MergeCells(true);
            //单元格文本水平居中对齐
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 3]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            xlsheet.ActiveSheet.Cells[row, 4] = t1;
            xlsheet.ActiveSheet.Cells[row, 6] = t2;
        }
        #endregion
Response导出的文件，中文文件名乱码的问题未解决，你自己小心！

1个月前   回答者：月光小提琴 - 初学一级

 

using System.Reflection;
using Microsoft.Office.Interop.Excel;

            Application myExcel = new ApplicationClass();
            Workbook myWorkbook = null;
            Worksheet mySheet = null;
            string excelFileName = "test.xls";

            try
            {
                // 打开Excel文件
                myWorkbook = myExcel.Workbooks.Open(excelFileName, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                // 获取第一个sheet
                mySheet = (Worksheet)myWorkbook.Sheets[1];

                // 读取第一行第一列的数据
                string firstString = (string)((Range)mySheet.Cells[1, "A"]).Formula.ToString();

                // 修改第一行第一列的数据
                ((Range)mySheet.Cells[1, "A"]).Formula = "test";

                // 保存Excel文件
                myWorkbook.SaveAs(mappingFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                // 关闭Excel文件
                myWorkbook.Close(false, Missing.Value, Missing.Value);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                // 释放资源
                mySheet = null;
                myWorkbook = null;
                myExcel.Quit();
                myExcel = null;
            }

 

1个月前   回答者：轩辕枯藤 - 菜鸟二级

评论

1个月前   月光小提琴 : 读取Excel: string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + ViewState["file"]; string sql = "SELECT * FROM [Sheet1$]"; DataTable _table = new DataTable(); OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr); da.Fill(_table); ViewState["table"] = _table; lblMsg.Text = "总共: "+_table.Rows.Count+" 条记录"; _table.Dispose(); 欢迎访问：C# Excel http://www.cnblogs.com/qiaojun/articles/1486040.html

   您需要登录以后才能回答！