Question

C#如何将EXCEL中的数据导入

C# 中实现对excel的操作 只用C#自己带的类和方法能写吗?

Answer 1

看到很多关于使用Microsoft.Jet.OLEDB.4.0。首先这是一个非常常见的、很简单有效的和Excel进行数据交流的方式。另外补充一下这种方式有个缺点，就是如果Excel的Sheet名不规范，例如有空格、连字符(-)和过长的时候，这个方法是不能够正确获取数据的。这里提供一个用COM+来访问Excel的方法。COM+访问比较复杂，要求客户端上必须安装Excel。除非你经常要处理Sheet名不规范的Excel。否则我还是建议你使用OLEDB方法。using System;
using System.Data;
using Excel;
using System.IO;
namespace Test.ExcelCom
{
/// <summary>
/// 将DataView中的数据导入Excel文件中
/// 作者：Rexsp
/// 创建：2004-4-4
/// </summary>
public class OutputExcel
{
#region 私有成员
/// <summary>
/// 数据的DataView
/// </summary>
private DataView dv=null;
/// <summary>
/// 表格标题
/// </summary>
private string title=null;
/// <summary>
/// 输出文件路径
/// </summary>
private string outFilePath=null;
/// <summary>
/// 输入文件名
/// </summary>
private string inputFilePath=null;
#endregion #region 公共属性
/// <summary>
/// 数据的DataView
/// </summary>
public DataView DV
{
set{dv=value;}
}
/// <summary>
/// 表格标题
/// </summary>
public string Title
{
set{title=value;}
get{return title;}
}
/// <summary>
/// 输出文件路径
/// </summary>
public string OutFilePath
{
set{outFilePath=value;}
get{return outFilePath;}
}
/// <summary>
/// 输入文件路径
/// </summary>
public string InputFilePath
{
set{inputFilePath=value;}
get{return inputFilePath;}
}
#endregion

#region 构造函数
public OutputExcel()
{
}
public OutputExcel(DataView dv,string title)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#endregion #region 公共方法
public void CreateExcel()
{
int rowIndex=4;//行起始坐标
int colIndex=1;//列起始坐标 ApplicationClass myApp=null;
Workbook myBook=null;
Worksheet mySheet=null; //如果文件不存在，则将模板文件拷贝一份作为输出文件
//这里如果通过File.Create来创建文件是不行的，因为xls
//的空文件也有固定的格式，跟文本不一样的，也许有其它
//通过程序直接生成excel的方法，大家可以尝试尝试的
if(!File.Exists(outFilePath))
{
File.Copy(inputFilePath,outFilePath,true);
} myApp= new ApplicationClass();
myApp.Visible=false;
object oMissiong=System.Reflection.Missing.Value;
myApp.Workbooks.Open(outFilePath,oMissiong,oMissiong,oMissiong,oMissiong, oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
myBook=myApp.Workbooks[1];
mySheet=(Worksheet)myBook.ActiveSheet;
//
//取得标题
//
foreach(DataColumn col in dv.Table.Columns)
{
colIndex++;
mySheet.Cells[4,colIndex] = col.ColumnName;
mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题格式为居中对齐
} //
//取得表格中的数据
//
foreach(DataRowView row in dv)
{
rowIndex ++;
colIndex = 1;
foreach(DataColumn col in dv.Table.Columns)
{
colIndex ++;
if(col.DataType == System.Type.GetType("System.DateTime"))
{
mySheet.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
mySheet.get_Range(mySheet.Cells[rowIndex,colIndex],mySheet.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if(col.DataType == System.Type.GetType("System.String"))
{
mySheet.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
mySheet.get_Range(mySheet.Cells[rowIndex,colIndex],mySheet.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
mySheet.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
//
//加载一个合计行
//
int rowSum = rowIndex + 1;
int colSum = 2;
mySheet.Cells[rowSum,2] = "合计";
mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
//
//设置选中的部分的颜色
//
mySheet.get_Range(mySheet.Cells[rowSum,colSum],mySheet.Cells[rowSum,colIndex]).Select();
mySheet.get_Range(mySheet.Cells[rowSum,colSum],mySheet.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
//
//取得整个报表的标题
//
mySheet.Cells[2,2] = title;
//
//设置整个报表的标题格式
//
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Bold = true;
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Select();
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).Select();
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
myBook.Save();;
myBook.Close( true,outFilePath,true);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);
GC.Collect();
}
#endregion
}
}

Answer 2

最容易的办法就是直接针对Excel当作本地数据库，使用Odbc等方式操作：http://www.cnblogs.com/pony/archive/2007/02/06/641752.html

Answer 3

C#导入Excel中的数据，可以编写个引用，程序如下：
openFileDialog1.Filter = "Execl files (*.xls)|*.xls";
openFileDialog1.FilterIndex = 0;
openFileDialog1.RestoreDirectory = true;
openFileDialog1.Title = "导入文件保存路径";
openFileDialog1.FileName = null;
openFileDialog1.ShowDialog();
string FileName = openFileDialog1.FileName;
if (FileName != "")
{
String connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + "; Extended Properties=Excel 8.0;";
String sql = "SELECT * FROM [Sheet1$]"; OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{

string[] total = dr[0].ToString().Split('-');
string server = total[0];
string ah = total[1].Substring(0, 1);
string strSql = "select * from JMFServername_list where Servername_realm=@server and AH=@ah";
SqlConnection sqlconn = new SqlConnection(strsql);
sqlconn.Open();
SqlCommand sqlcmd = new SqlCommand(strSql, sqlconn);
sqlcmd.Parameters.AddWithValue("@server", server);
sqlcmd.Parameters.AddWithValue("@ah", ah);
SqlDataReader dtr = sqlcmd.ExecuteReader();
if (dtr.Read())
{
continue;
}
string sqlQuery = "insert into JMFServername_list(Servername_realm,AH)values(@server,@AH)"; sqlcmd = new SqlCommand(sqlQuery, sqlconn);
sqlcmd.Parameters.AddWithValue("@server",server);
sqlcmd.Parameters.AddWithValue("@AH", ah);
sqlcmd.ExecuteNonQuery();
}

myDGV.DataSource = ds.Tables[0];
}

Answer 4

自己写的一个 复制可以就可以用了openFileDialog1.Filter = "Execl files (*.xls)|*.xls";
openFileDialog1.FilterIndex = 0;
openFileDialog1.RestoreDirectory = true;
openFileDialog1.Title = "导入文件保存路径";
openFileDialog1.FileName = null;
openFileDialog1.ShowDialog();
string FileName = openFileDialog1.FileName;
if (FileName != "")
{
String connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + "; Extended Properties=Excel 8.0;";
String sql = "SELECT * FROM [Sheet1$]"; OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{

string[] total = dr[0].ToString().Split('-');
string server = total[0];
string ah = total[1].Substring(0, 1);
string strSql = "select * from JMFServername_list where Servername_realm=@server and AH=@ah";
SqlConnection sqlconn = new SqlConnection(strsql);
sqlconn.Open();
SqlCommand sqlcmd = new SqlCommand(strSql, sqlconn);
sqlcmd.Parameters.AddWithValue("@server", server);
sqlcmd.Parameters.AddWithValue("@ah", ah);
SqlDataReader dtr = sqlcmd.ExecuteReader();
if (dtr.Read())
{
continue;
}
string sqlQuery = "insert into JMFServername_list(Servername_realm,AH)values(@server,@AH)"; sqlcmd = new SqlCommand(sqlQuery, sqlconn);
sqlcmd.Parameters.AddWithValue("@server",server);
sqlcmd.Parameters.AddWithValue("@AH", ah);
sqlcmd.ExecuteNonQuery();
}

myDGV.DataSource = ds.Tables[0];
}