前台
<%@ Page MasterPageFile="~/Site.Master" Language="C#" AutoEventWireup="true" CodeBehind="ExcelTest.aspx.cs" Inherits="WebService.WebFormSystem.ExcelTest" %>
<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
<link href="../Content/bootstrap.min.css" rel="stylesheet" />
<div>
<h3>Excel模版写入操作</h3>
<asp:TextBox ID="TextBox1" runat="server" Height="73px" TextMode="MultiLine"
Width="338px" CssClass="form-control">要写要到Excel的数据,请不要同时点两种方法</asp:TextBox>
<br />
<asp:Button ID="btxls" runat="server" OnClick="btxls_Click" CssClass="btn btn-success" Text="输出到Excel" />
<asp:Button ID="btxls0"
runat="server" Text="快速批量输出到Excel" CssClass="btn btn-warning" OnClick="btxls0_Click" />
<asp:Button ID="btExcelRead" runat="server" Text="读取Excel" CssClass ="btn btn-primary" OnClick="btExcelRead_Click"/>
<br />
<asp:Label ID="Label1" runat="server" ForeColor="Red" Text="Label"></asp:Label>
<br />
<asp:Label ID="Label2" runat="server" ForeColor="Red" Text="Label"></asp:Label>
<br />
</div>
</asp:Content>
后台
using System;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace WebService.WebFormSystem
{
public partial class ExcelTest : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btxls_Click(object sender, EventArgs e)
{
string sPathModel = Server.MapPath("test.xls");
string sPathExport = Server.MapPath("test1.xls");
string sData = TextBox1.Text;
this.FunExcelExport(sPathModel, sPathExport, sData);
Label1.Text = "模版位置:" + sPathModel;
Label2.Text = "输出Excel:" + sPathExport;
Response.Redirect("test1.xls");
}
//快速批量输出到Excel
protected void btxls0_Click(object sender, EventArgs e)
{
string sPathModel = Server.MapPath("test.xls");
string sPathExport = Server.MapPath("test2.xls");
string sData = TextBox1.Text;
this.FunAllExcelExport(sPathModel, sPathExport, sData);
Label1.Text = "模版位置:" + sPathModel;
Label2.Text = "输出Excel:" + sPathExport;
Response.Redirect("test2.xls");
}
protected void btExcelRead_Click(object sender, EventArgs e)
{
string sPathModel = Server.MapPath("test1.xls");
ExcelRead(sPathModel);
}
/// <summary>
/// excel读取
/// </summary>
/// <param name="sPathModel"></param>
public void ExcelRead(string sPathModel)
{
Workbook myBook = null;
try
{
Application myApp = new ApplicationClass();
Worksheet mySheet;
Range myRange;
object oMissiong = System.Reflection.Missing.Value;
myBook = myApp.Workbooks.Open(sPathModel);
mySheet = (Worksheet)myBook.Worksheets[3];//读sheet3内容
int rowsint = mySheet.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数
//单个单元格
myRange = ((Range)mySheet.Cells[1, 1]);
Label1.Text = myRange.Value.ToString();
//多个单元格
myRange = mySheet.get_Range("A1", "D5");
object[,] arryItem = (object[,])myRange.Value2; //get range's value
string sValue = "";
for (int i = 1; i <= rowsint; i++)
{
for (int j = 1; j <= columnsint; j++)
{
sValue += arryItem[i, j].ToString();
}
sValue += "\n";
}
TextBox1.Text = sValue;
Label2.Text = sPathModel;
//myBook.Save();
}
catch (Exception)
{
throw;
}
finally
{
myBook.Close(true, sPathModel, true);
myBook = null;
KillExcelProcess();
}
}
/// <summary>
/// 批量输出模版到Excel
/// </summary>
/// <param name="sPathModel">模版所在的位置</param>
/// <param name="sPathExport">要输出的位置</param>
/// <param name="sData">要写入的数据</param>
public void FunAllExcelExport(string sPathModel, string sPathExport, string sData)
{
Application myApp = null;
Workbook myBook = null;
Worksheet mySheet = null;
//Sheets mySheets;
Range myRange;
object oMissiong = System.Reflection.Missing.Value;
try
{
FileInfo myFileInfo = new FileInfo(sPathModel);
myFileInfo.Attributes = FileAttributes.Normal;//设为可写
myFileInfo.CopyTo(sPathExport, true);
myApp = new ApplicationClass();
//myApp.Visible = false;
myBook = myApp.Workbooks.Open(sPathExport, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
mySheet = (Worksheet)myBook.Worksheets[1];
myRange = mySheet.get_Range("A2", "E2");//要操作的单元格
myRange.Merge(0);//合并设置的单元格
//
myRange = mySheet.get_Range("A3", "A7");//要操作的单元格
myRange.Merge(0);//合并设置的单元格
myRange = mySheet.get_Range("A8", oMissiong);//要操作的单元格
myRange = myRange.get_Resize(10, 10);//合并10行10列的单元格
myRange.Merge(0);//合并设置的单元格
myRange = mySheet.get_Range("A2", "E2"); 要操作的单元格
object[] objHeader = { "标题1", "标题2", "标题3", "标题4", "标题5" };//要写入的行列内容
myRange.Value = objHeader;
myRange = mySheet.get_Range("A3", "E3"); 要操作的单元格
object[] objHeader2 = { "标题11", "标题12", "标题13", "标题14", "标题15" };//要写入的行列内容
myRange.Value = objHeader2;
mySheet = (Worksheet)myBook.Worksheets[2];//放在第二个sheet中写入内容
myRange = mySheet.get_Range("A4", oMissiong);
int ih = 100;// 行
int il = 20; //列
object[,] objData = new Object[ih, il];//建立一个同样行列的数组
for (int i = 0; i < ih; i++)
{
for (int j = 0; j < il; j++)
{
objData[i, j] = (i + 1).ToString() + "行" + (j + 1).ToString() + "列";
}
}
myRange = myRange.get_Resize(ih, il);//设置输出的行数和列数
myRange.Value = objData;
//myRange.EntireColumn.AutoFit(); //自动调整列宽
myBook.Save();
}
catch (Exception)
{
throw;
}
finally
{
myBook.Close(true, sPathExport, true);
myBook = null;
KillExcelProcess();
}
}
/// <summary>
/// 输出模版到Excel
/// </summary>
/// <param name="sPathModel">模版所在的位置</param>
/// <param name="sPathExport">要输出的位置</param>
/// <param name="sData">要写入的数据</param>
public void FunExcelExport(string sPathModel, string sPathExport, string sData)
{
Workbook myBook = null;
object oMissiong = System.Reflection.Missing.Value;
try
{
FileInfo myFileInfo = new FileInfo(sPathModel);
myFileInfo.Attributes = FileAttributes.Normal;//设为可写
myFileInfo.CopyTo(sPathExport, true);
Application myApp = new ApplicationClass();
Worksheet mySheet;
//myApp.Visible = false;
myBook = myApp.Workbooks.Open(sPathExport);
mySheet = (Worksheet)myBook.Worksheets[1];
mySheet.Cells[1, 1] = sData;
mySheet.Name = "a";
mySheet = (Worksheet)myBook.Worksheets[2];
mySheet.Cells[1, 1] = sData;
mySheet.Name = "b";
myBook.Save();
}
catch (Exception)
{
throw;
}
finally
{
myBook.Close(true, sPathExport, true);
myBook = null;
KillExcelProcess();
}
}
/// <summary>
/// 释放EXCEL资源
/// </summary>
/// <param name="excelFile">EXCEL文件路径</param>
private void NAR(Object o)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
o = null;
}
private void KillExcelProcess()
{
System.Diagnostics.Process[] p = System.Diagnostics.Process.GetProcessesByName("EXCEL");
for (int i = 0; i < p.Length; i++)
{
p[i].Kill();
}
}
}
}
使用说明
需要引用与当前安装版本一致的Excel.dll 可以从网上下。
嵌入交互文件类型改为 “False”。
在目录中建立一个test.xls文件
按引入Excel.dll编译通过即可。