http://www.cnblogs.com/litianfei/archive/2008/03/21/1116906.html 写的很详细
一、操作Excel 首先导出Excel
（1）示例：
/// <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可视
}
（2）实例：
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);
}
}
（3）主要参数（引用上面链接内容）：
Excel的处理：如果在你已知Excel格式的情况下，可以控制Excel,如下：
1、 显示当前窗口：ExcelApp.Visible := True;
2、 更改 Excel 标题栏：ExcelApp.Caption := '标题内容';
3、 添加新工作簿：ExcelApp.WorkBooks.Add;
4、 设置第2个工作表为活动工作表：ExcelApp.WorkSheets[2].Activate;
5、 给单元格赋值：ExcelApp.Cells[1,1].Value := '第一行第一列';
6、 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth
:= 5;
7、 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelApp.ActiveSheet.Rows[2].RowHeight
:= 1/0.035; // 1厘米
8、文字水平居中：Excelid.worksheets[1].Rows[1].HorizontalAlignment :=
$FFFFEFF4; 文字垂直居中：Excelid.worksheets[1].Rows[1].VerticalAlignment := $FFFFEFF4;
9、 插入一行或一列：a. ExcelApp.ActiveSheet.Rows[2].Insert;b.
ExcelApp.ActiveSheet.Columns[1].Insert;
10、 删除一行或一列：a. ExcelApp.ActiveSheet.Rows[2].Delete;b.
ExcelApp.ActiveSheet.Columns[1].Delete;
11、合并单元格：ExcelApp.worksheets[1].range[A1:F8'].Merge(abc);注：要声明变量abc: Variant;
12、竖行显示文字：ExcelApp.worksheets[1].Cells.Item[1,1].Orientation:=
xlVertical;
13、单元格加边线：ExcelApp.worksheets[1].Range[A1:F8].Borders.LineStyle := 1;
14、在第8行之前插入分页符：ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
15、在第4列之前删除分页符：ExcelApp.ActiveSheet.Columns[4].PageBreak := 0;
16、指定边框线宽度：ExcelApp.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
17、拷贝操作：a.拷贝整个工作表：ExcelApplication1.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;从A1位置开始粘贴：ExcelApplication1.ActiveSheet.Range.[ 'A1'
].PasteSpecial;
d.从文件尾部开始粘贴：ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18、清除第一行第四列单元格公式：ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
19、工作表保存：if not ExcelApp.ActiveWorkBook.Saved then ExcelApp.ActiveSheet.PrintPreview;
20、工作表另存为：ExcelApp.SaveAs( 'C:\Excel\Demo1.xls' );
21、放弃存盘：ExcelApp.ActiveWorkBook.Saved := True;
22、关闭工作簿：ExcelApp.WorkBooks.Close;
23、退出 Excel：ExcelApp.Quit;
下面是有关打印页面控制的语句：
24、设置第一行字体属性：ExcelApp.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Color
:= clBlue;
ExcelApp.ActiveSheet.Rows[1].Font.Bold
:= True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine
:= True;
ExcelApp.ActiveSheet.Rows[1].Font.size:=10;
25、进行页面设置：a.页眉：ExcelApp.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：ExcelApp.ActiveSheet.PageSetup.CenterFooter := '共&N页 第&P页';
c.页眉到顶端边距2cm：ExcelApp.ActiveSheet.PageSetup.HeaderMargin :=
2/0.035;
d.页脚到底端边距3cm：ExcelApp.ActiveSheet.PageSetup.HeaderMargin :=
3/0.035;
e.顶边距2cm：ExcelApp.ActiveSheet.PageSetup.TopMargin :=
2/0.035;
f.底边距2cm：ExcelApp.ActiveSheet.PageSetup.BottomMargin :=
2/0.035;
g.左边距2cm：ExcelApp.ActiveSheet.PageSetup.LeftMargin :=
2/0.035;
h.右边距2cm：ExcelApp.ActiveSheet.PageSetup.RightMargin :=
2/0.035;
i.页面水平居中：ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：ExcelApp.ActiveSheet.PageSetup.PrintGridLines := True;
26、打印预览工作表：ExcelApp.ActiveSheet.PrintPreview;
27、打印输出工作表：ExcelApp.ActiveSheet.PrintOut;
对Excel的其他控制：
28、excel的多单元格合计功能：ExcelApp..Cells[ARow, ACol].Formula
:= '=
SUM($+IntToStr(BeginRow) +:$ + IntToStr(EndRow) +');
注：声明变量ARow, ACol: Integer;
29、打开已经存在的Excel文件： ExcelApplication1.Workbooks.Open (c:\a.xls
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
二、 读取Excel
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;
}
}