.NET对Excel的读写

最新推荐文章于 2019-10-04 17:50:34 发布

liuxiuk

最新推荐文章于 2019-10-04 17:50:34 发布

阅读量1k

点赞数

文章标签： excel .net properties string 数据库 null

在.net中读Excel可以通过两种方式读取

1.以数据库的方式，这种方式简单明了，推荐。

参考代码如下：

注意修改两个地方Data Source=C:/Book.xls指Excel文件的位置，string.Format("select * from [{0}$]", p_tagName)中p_tagName指Sheet标签页的名称

Code

//构建连接字符串
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Book.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
OleDbConnection Conn = new OleDbConnection(ConnStr);
Conn.Open();
//填充数据
string sql = string.Format("select * from [{0}$]", p_tagName);
OleDbDataAdapter da = new OleDbDataAdapter(sql, ConnStr);
da.Fill(ds);

2.以OLE方式读取

Code

//读取excel
int indexSheet = 1;

Excel.ApplicationClass Myexcel = new Excel.ApplicationClass();
if (Myexcel == null)
{
    MessageBox.Show("EXCEL无法启动!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
    return;
}

Myexcel.Visible = true;
Myexcel.UserControl = true;
Excel.Workbooks Myworkbooks = Myexcel.Workbooks;
Excel.Workbook Myworkbook = Myworkbooks.Open(@"C:\Book.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet Myworksheet = (Excel.Worksheet)Myworkbook.Worksheets[indexSheet];
Excel.Range rng3 = Myworksheet.get_Range("E1",Missing.Value);

最后通过rng3的属性Value2可以获取到指定Cell的值

在.net中写Excel可以通过两种方式读取

1.通过数据库方式批量更新

2.通过OLE方式更新

从以下片段可以看出和读取没有什么区别

Code

if (sBhName.IndexOf(infoArr[0]) >= 0 || sDmisName.IndexOf(infoArr[0]) >= 0)
{
    Excel.Range rng3 = null;
    bEmsByqId = GetNumberInt(infoArr[3]);
    if (bEmsByqId == bDmisByqId)
    {
        rng3 = Myworksheet.get_Range(string.Format("E{0}", Convert.ToString(i + 1)), Missing.Value);
        rng3.Value2 = infoArr[1];
        rng3.Interior.ColorIndex = 4; //设置Range的背景色 

        rng3 = Myworksheet.get_Range(string.Format("F{0}", Convert.ToString(i + 1)), Missing.Value);
        rng3.Value2 = infoArr[0];
        rng3.Interior.ColorIndex = 6; //设置Range的背景色 

        rng3 = Myworksheet.get_Range(string.Format("G{0}", Convert.ToString(i + 1)), Missing.Value);
        rng3.Value2 = infoArr[3];
        rng3.Interior.ColorIndex = 8; //设置Range的背景色
    }
}

Myworksheet.SaveAs(@"C:\date.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);

最后不要忘记保存...