首页 新闻 搜索 专区 学院

C# 怎样读写EXCEL文件

0
悬赏园豆:100 [已关闭问题]

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

飘云的主页 飘云 | 初学一级 | 园豆:95
提问于:2009-05-22 16:11
< >
分享
其他回答(5)
0
游子 | 园豆:205 (菜鸟二级) | 2009-05-22 16:15
0

一直用这个串。

 

Code
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);
}
winzheng | 园豆:8797 (大侠五级) | 2009-05-22 16:32
0

   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内后,怎么操作就省略了。

邢少 | 园豆:10926 (专家六级) | 2009-05-22 16:37
0

            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;

mongg | 园豆:205 (菜鸟二级) | 2009-05-23 20:32
0

#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导出的文件,中文文件名乱码的问题未解决,你自己小心!

月光小提琴 | 园豆:44 (初学一级) | 2009-05-26 13:59
0

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;
}

轩辕枯藤 | 园豆:180 (初学一级) | 2009-05-27 18:13
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册