C#操作Excel（读/写）

/// <summary>
/// 操作Excel
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
Application excel = new Application();//引用Excel对象
excel.Application.Workbooks.Add(true);//引Excel工作薄
excel.Cells[, ] = "First Row First Column";
excel.Cells[, ] = "First Row Second Column";
excel.Cells[, ] = "Second Row First Column";
excel.Cells[, ] = "Second Row Second Column";
excel.Visible = true;//使Excel可视
}

private void InputEx()
{
try
{
String Pid=Request.QueryString[“id”];
if (Pid != "")
{
string STime = " 1990-01-01 00:00:00";
string Etime = DateTime.Now.ToString();
if (!string.IsNullOrEmpty(begintime.Value))
{
STime = begintime.Value + " 00:00:00";
}
if (!string.IsNullOrEmpty(endtime.Value))
{
Etime = endtime.Value + " 23:59:59";
}
IList<Model.Renew> List = new BLL.Service().GetInputEx(Pid,STime,Etime);
#region 导出
if (List.Count > )
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
// 创建Excel应用程序对象的一个实例，相当于我们从开始菜单打开Excel应用程序
if (xlApp == null)
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script type=\"text/javascript\">alert('您未安装Office');</script>");
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[];
Microsoft.Office.Interop.Excel.Range range;
string[] NameS = { "序号?", "姓名", "性别", "电话", "手机型号", "电脑型号", "汽车品牌", "购买时间" };
for (int i = ; i < NameS.Length; i++)
{
worksheet.Cells[, i + ] = NameS[i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, i + ];
range.Interior.ColorIndex = ;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
range.ColumnWidth = 15.5;
range.Font.Bold = true;
}
for (int r = ; r < List.Count; r++)
{
worksheet.Cells[r + , ] = (r + );
worksheet.Cells[r + , ] = “张三”;
worksheet.Cells[r + , ] = “男”;
worksheet.Cells[r + , ] = “”;
worksheet.Cells[r + , ] =”三星”;
worksheet.Cells[r + , ] =“攒的”; worksheet.Cells[r + , ] =“大挂”;
worksheet.Cells[r + , ] = “--”;
Range rng01 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + , ];
Range rng02 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + , ];
rng01.ColumnWidth = ; //第八列变宽
rng02.HorizontalAlignment = XlHAlign.xlHAlignLeft; //第二列居左
}
xlApp.Visible = true;
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script type=\"text/javascript\">alert('根据条件未查询到数据');</script>");
return;
}
#endregion }
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script type=\"text/javascript\">alert(请选择查询条件?');</script>");
return;
}
}
catch (Exception ex)
{
Common.Log.Error("导出信息页面错误信息：" + ex.Message);
}
}

List<Model.it> List = new List<Model.it>(); /// <summary>
/// 导入按钮时间，将Excel数据导入列表里
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void GetExcel_Click(object sender, EventArgs e)
{
string fullpath = string.Empty;
SaveFile(ref fullpath);
if (string.IsNullOrEmpty(fullpath))
{
MessageBox("文件格式错误", "", Page.ClientScript);
return;
}
List.Clear();
ExcelToList(fullpath);
} /// <summary>
/// 保存按钮事件，将excel数据保存到数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void Save_Click(object sender, EventArgs e)
{
int id = Request.QueryString[“id”];
string log = string.Empty;
int errorcount = ;
DateTime dt = DateTime.Now;
string tag = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Millisecond.ToString();
int count = List.Count;
for (int i = ; i < count; i++)
{
string error = string.Empty;
var model = List[i];
try
{
if (Cis.IsHasid(model))// 已存在，不可插入
{
error += "第" + (i + ) + "行报错：该id:" + model.id + "已存在";
}
}
catch (Exception ex)
{
error += "第" + (i + ) + "行报错： id=" + model.id + "错误原因：" + ex.Message;
}
if (!string.IsNullOrEmpty(error))
{
log += error;
errorcount++;
FileIO.WriteFile(Server.MapPath("~/Log/UploadExcelPath") + "/logs " + "_" + tag + ".txt", error);
}
}
List.Clear();
btn_Save.Visible = true;
if (!string.IsNullOrEmpty(log))
{
ScriptManager.RegisterStartupScript(this, this.GetType(), Guid.NewGuid().ToString(), "alert(‘计划导入" + count + "行记录，其中" + errorcount + "行错，" + (count - errorcount) + "行成功，<a target=\"_blank\" href=\"" + "/Log/UploadExcelPath" + "/logs" + "_" + tag + ".txt" + "\">查看错误地址</a>');");
}
else
MessageBox("全部导入成功", "", Page.ClientScript);
} /// <summary>
///上传提交的文件
/// </summary>
public void SaveFile(ref string fullpath)
{
string fileName = this.fileUpExcel.FileName;
string path = Server.MapPath("~/Log/UploadExcelPath");
string fileType = fileUpExcel.PostedFile.ContentType;
if ((fileType == "application/vnd.ms-excel" ||
fileType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") ||
(fileType == "application/octet-stream" &&
(Path.GetExtension(fileName) == ".xls" || Path.GetExtension(fileName) == ".xlsx")))
{
DateTime dt = DateTime.Now;
string tag = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Millisecond.ToString();
string name = "nit_" + tag + "_" + fileName;
//判断是否有该目录?
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(path);
if (!dir.Exists)
{
dir.Create();
}
fullpath = path + "//" + name;
//如果存在,删除文件
if (File.Exists(fullpath))
{
File.Delete(fullpath);
}
this.fileUpExcel.SaveAs(fullpath);//上传
}
}/// <summary>
/// 导出Excel到List并展现出来
/// </summary>
/// <param name="fullpath"></param>
/// 引用using System.Data.OleDb;
public void ExcelToList(string fullpath)
{
DataTable dtExcel = null;
// using (OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"; Data Source=" + fullpath + ";"))//32位?
using (OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"; Data Source=" + fullpath + ";"))//64位?
{
oleCon.Open();
DataTable dtNames = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取excel表名
oleCon.Close();
var rows = dtNames.Rows;
if (rows.Count > )
{
var sheetname = rows[][].ToString();
OleDbDataAdapter oldAdapter = new OleDbDataAdapter("select * from [" + sheetname + "]", oleCon);//获取表数据
DataSet ds = new DataSet();
oldAdapter.Fill(ds, "table");
oleCon.Close();
dtExcel = ds.Tables["table"];
var count = dtExcel.Rows.Count;
if (count > )
{
for (int i = ; i < count; i++)
{
var now = DateTime.Now;
List.Add(new Model. it { id = dtExcel.Rows[i][].ToString(), Tid = dtExcel.Rows[i][].ToString(), State = , Ontime = now, Status = , Update_time = now });
}
clist.DataSource = List;
clist.DataBind();
btn_Save.Visible = true;
}
else
{
MessageBox("Excel表格无数据,请重新导入", "", Page.ClientScript);
return;
}
}
else
{
MessageBox("Excel表格无数据,请重新导入", "", Page.ClientScript);
return;
}
}
if (dtExcel == null)
{
//读取错误
MessageBox("Excel读取错误,请重新导入", "", Page.ClientScript);
return;
}
}