C# 操作Excel

公告

View Post

两种读取Excel方法

--------------------------------------------------------------------方法一

/// <summary>
/// 解析Excel，返回DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
int a = ws.Rows.Count;
int b = ws.Columns.Count;
string name = ws.Name;
for (int i = 1; i <RowCount; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= ColumnsCount; j++)
{
Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
dr[j - 1] = app.ActiveCell.Text.ToString();
}
dt.Rows.Add(dr);
}
KillProcess(app);
return dt;
}
catch (Exception ex)
{
MessageBox.Show("数据绑定Excel失败! 失败原因：Excel格式不正确！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return dt;
}
}
<SPAN style="COLOR: #ff0000">-------------------------------------------<SPAN style="FONT-SIZE: 24px">方法二</SPAN></SPAN>
/// <summary>
/// 解析Excel，根据OleDbConnection直接连Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="name"></param>
/// <returns></returns>
public static DataSet LoadDataFromExcel(string filePath, string name)
{
try
{
string strConn;
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=No\"";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称，比如sheet2，等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, name);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败! 失败原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}

 /// <summary>
    /// 解析Excel，返回DataTable
    /// </summary>
    /// <param name="fileName"></param>
    /// <returns></returns>
    public static System.Data.DataTable ImpExcel(string fileName)
    {
      System.Data.DataTable dt = new System.Data.DataTable();
      try
      {
        Microsoft.Office.Interop.Excel.Application app;
        Workbooks wbs;
        Worksheet ws;
        app = new Microsoft.Office.Interop.Excel.Application();
        wbs = app.Workbooks;
        wbs.Add(fileName);
        ws = (Worksheet)app.Worksheets.get_Item(1);
        int a = ws.Rows.Count;
        int b = ws.Columns.Count;
        string name = ws.Name;

          for (int i = 1; i <RowCount; i++)
          {
            DataRow dr = dt.NewRow();
            for (int j = 1; j <= ColumnsCount; j++)
            {
              Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
              range.Select();
              dr[j - 1] = app.ActiveCell.Text.ToString();
            }
            dt.Rows.Add(dr);
           }
        KillProcess(app);
        return dt;
      }
      catch (Exception ex)
      {
        MessageBox.Show("数据绑定Excel失败! 失败原因：Excel格式不正确！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return dt;
      }
    }
-------------------------------------------方法二
    /// <summary>
    /// 解析Excel，根据OleDbConnection直接连Excel
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="name"></param>
    /// <returns></returns>
    public static DataSet LoadDataFromExcel(string filePath, string name)
    {
      try
      {
        string strConn;
        //  strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
        strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=No\"";
       OleDbConnection OleConn = new OleDbConnection(strConn);
        OleConn.Open();
        string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称，比如sheet2，等等 
        OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
        DataSet OleDsExcle = new DataSet();
        OleDaExcel.Fill(OleDsExcle, name);
        OleConn.Close();
        return OleDsExcle;
      }
      catch (Exception err)
      {
        MessageBox.Show("数据绑定Excel失败! 失败原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return null;
      }
    }

——————————————————插入Excel

[csharp] view plain copy print ?

/// <summary>
/// 写入Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
public bool SaveFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection =conn;
for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
{
if(fp2.Sheets [0].Cells[i,0].Text!="")
{
cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
cmd.ExecuteNonQuery ();
}
}
conn.Close ();
return true;
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
}
return false;
}

/// <summary>
  /// 写入Excel文档
  /// </summary>
  /// <param name="Path">文件名称</param>
  public bool SaveFP2toExcel(string Path)
  {
   try
   {
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
    OleDbConnection conn = new OleDbConnection(strConn);
    conn.Open();  
    System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
    cmd.Connection =conn;
    for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
    {
     if(fp2.Sheets [0].Cells[i,0].Text!="")
     {
      cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
       fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
       "','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
      cmd.ExecuteNonQuery ();
     }
    }
    conn.Close ();
    return true;
   }
   catch(System.Data.OleDb.OleDbException ex)
   {
    System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
   }
   return false;
  }

新增、修改

[csharp] view plain copy print ?

string path="你的excel的路径";
workbook book=new workbook(path);
worksheet sheet=book["sheet3"];
sheet[行，列]=“你想存的值";
workbook.saveas(newpath);

string path="你的excel的路径";
workbook book=new workbook(path);
worksheet sheet=book["sheet3"];
sheet[行，列]=“你想存的值";
workbook.saveas(newpath);

——————————————————修改Excel的值

[csharp] view plain copy print ?

修改第一行Name的值为张三
string strComm = "update [Sheet1$] set Name='张三' WHERE 工号='132'";
OleDbConnection myConn = new OleDbConnection(strConn);
myConn.Open();
OleDbCommand com = new OleDbCommand(strComm, myConn);
com.ExecuteNonQuery();
myConn.Close();

修改第一行Name的值为张三
string strComm = "update [Sheet1$] set Name='张三' WHERE 工号='132'";
OleDbConnection myConn = new OleDbConnection(strConn);
myConn.Open();
OleDbCommand com = new OleDbCommand(strComm, myConn);
com.ExecuteNonQuery();
myConn.Close();

------------------------------------------------导出————————————————————

[csharp] view plain copy print ?

using _Excel = Microsoft.Office.Interop.Excel;
using System.Drawing;
using System.Reflection;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
/// <summary>
/// DataTable直接导出Excel,此方法会把DataTable的数据用Excel打开,再自己手动去保存到确切的位置
/// </summary>
/// <param name="dt">要导出Excel的DataTable</param>
/// <returns></returns>
public static void ExportExcel(System.Data.DataTable table, string savePath, bool isExit)
{
if (!isExit)//保存路径是否存在
File.Copy(System.Windows.Forms.Application.StartupPath + @"\Excel\Excel.xls", savePath);
_Excel.Application app = new _Excel.ApplicationClass();
if (app == null)
{
throw new Exception("Excel无法启动");
}
app.Visible = false;
_Excel.Workbook book = app.Workbooks.Open(savePath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
_Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;
for (int k = 1; k < ds.Tables[1].Rows.Count + 1; k++)
{
_Excel.Sheets xlSheets = book.Sheets as Sheets;
// 添加 Sheet
sheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
}
for (int j = 1; j < ds.Tables[1].Rows.Count + 1; j++)
{
_Excel.Range range = null;
sheet = (_Excel.Worksheet)book.Sheets[j];
if (!istrue)
sheet.Name ="123";
else
sheet.Name = "345";
range = sheet.get_Range("A1", "C3");
range.MergeCells = true;//合并，将1-3行和1-3列合并为一个单元格
range.WrapText = true; //自动换行
range.EntireRow.AutoFit();//行高根据内容自动调整
sheet.get_Range("A1", "C3").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = BorderStyle.FixedSingle;//将1-3行和1-3列合并的单元格划下划线，根据[]内属性，可以设置文本上、下、左、右的的边框框
sheet.get_Range("A1", "C3").Borders.LineStyle = BorderStyle.FixedSingle;//给整个合并的单元格加上边框
sheet.get_Range("A1", "C3").Font.Name = "Times New Roman";//设置字体.
sheet.get_Range("A1", "C3").Font.Size = 22;//设置字体大小
range.Font.Bold = true;//加粗
range.RowHeight = 22;//调行高
rang.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();//加背景色
range = sheet.get_Range(string.Format("D{0}", 1), string.Format("F{0}", 1));
range.MergeCells = true;//合并，将3-5列合并为一个单元格
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//合并后文本水平居中
range.VerticalAlignment = XlVAlign.xlVAlignCenter;//合并后文本竖直居中
單個sheet里求和：
Excel.Range range2 = sheet.get_Range("B25", Type.Missing);
range2.Formula = "=SUM(B2:B24)";
range2.Calculate();
rang = (_Excel.Range)sheet.get_Range(string.Format("G{0}", 7), string.Format("G{0}", table.Rows.Count + 7));
rang.NumberFormatLocal = "$#,##0.00"; //设置单元格格式为货币格式
跨sheet求和：
Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);
Excel.Range range3 =wsheet1.get_Range("A23", Type.Missing);
range3.Formula = "=Sheet3!B8+Sheet3!B12";
range3.Calculate();
sheet.Cells[1, 4] = ds.Tables[1].Rows[0]["FeightCode"].ToString();//给合并的列赋值
//循环加载数据
int startIndex = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[1].Rows[j - 1]["BoxCode"].ToString() == ds.Tables[0].Rows[i]["BoxCode"].ToString())
{
range = sheet.get_Range("b" + (15 + startIndex) + "", "e" + (15 + startIndex) + "");
range.MergeCells = true;
sheet.Cells[15 + startIndex, 2] = ds.Tables[0].Rows[i]["TypeName"].ToString();
range = (_Excel.Range)sheet.Cells[15 + startIndex, 2];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Bold = true;
range.RowHeight = 22;
sheet.Cells[15 + startIndex, 6] = ds.Tables[0].Rows[i]["Qty"].ToString();
range = (_Excel.Range)sheet.Cells[15 + startIndex, 6];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Bold = true;
range.RowHeight = 22;
sheet.Cells[15 + startIndex, 7] = ds.Tables[0].Rows[i]["UnitPrice"].ToString();
range = (_Excel.Range)sheet.Cells[15 + startIndex, 7];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Bold = true;
range.RowHeight = 22;
sheet.Cells[15 + startIndex, 8] = ds.Tables[0].Rows[i]["Subtotal"].ToString();
range = (_Excel.Range)sheet.Cells[15 + startIndex, 8];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Bold = true;
range.RowHeight = 22;
Image image = Picture.ReadPicture(ds.Tables[0].Rows[row][col].ToString());
range = sheet.get_Range(string.Format("A{0}", row * num + 29), string.Format("A{0}", row * num + 48));
sheet.Shapes.AddPicture(ds.Tables[0].Rows[row][col].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(range.Left) + 15 + image.Width, Convert.ToSingle(range.Top) + 5, image.Width, image.Height); //导出图片
range.EntireColumn.AutoFit();
System.Windows.Forms.Application.DoEvents();
startIndex++;
}
}

using _Excel = Microsoft.Office.Interop.Excel;
using System.Drawing;
using System.Reflection;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
        /// <summary>
        /// DataTable直接导出Excel,此方法会把DataTable的数据用Excel打开,再自己手动去保存到确切的位置
        /// </summary>
        /// <param name="dt">要导出Excel的DataTable</param>
        /// <returns></returns>
        public static void ExportExcel(System.Data.DataTable table, string savePath, bool isExit)
        {
            if (!isExit)//保存路径是否存在
                File.Copy(System.Windows.Forms.Application.StartupPath + @"\Excel\Excel.xls", savePath);
            _Excel.Application app = new _Excel.ApplicationClass();
            if (app == null)
            {
                throw new Exception("Excel无法启动");
            }
            app.Visible = false;
            _Excel.Workbook book = app.Workbooks.Open(savePath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
            _Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;

        for (int k = 1; k < ds.Tables[1].Rows.Count + 1; k++)
                {
                    _Excel.Sheets xlSheets = book.Sheets as Sheets;
                    //  添加 Sheet
                    sheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
                }
          for (int j = 1; j < ds.Tables[1].Rows.Count + 1; j++)
            {
   
		_Excel.Range range = null;             
	           sheet = (_Excel.Worksheet)book.Sheets[j];        
               if (!istrue)                
	         sheet.Name ="123";          
	      else                
	         sheet.Name = "345";
                range = sheet.get_Range("A1", "C3");
                range.MergeCells = true;//合并，将1-3行和1-3列合并为一个单元格
                range.WrapText = true;  //自动换行
                range.EntireRow.AutoFit();//行高根据内容自动调整
                sheet.get_Range("A1", "C3").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = BorderStyle.FixedSingle;//将1-3行和1-3列合并的单元格划下划线，根据[]内属性，可以设置文本上、下、左、右的的边框框
                sheet.get_Range("A1", "C3").Borders.LineStyle = BorderStyle.FixedSingle;//给整个合并的单元格加上边框
                sheet.get_Range("A1", "C3").Font.Name = "Times New Roman";//设置字体.
                 sheet.get_Range("A1", "C3").Font.Size = 22;//设置字体大小
                 range.Font.Bold = true;//加粗
                 range.RowHeight = 22;//调行高
                rang.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();//加背景色
                range = sheet.get_Range(string.Format("D{0}", 1), string.Format("F{0}", 1));
                range.MergeCells = true;//合并，将3-5列合并为一个单元格
               range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//合并后文本水平居中
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;//合并后文本竖直居中 
		 單個sheet里求和：
		Excel.Range range2 = sheet.get_Range("B25", Type.Missing);
		range2.Formula = "=SUM(B2:B24)";
		range2.Calculate();

  rang = (_Excel.Range)sheet.get_Range(string.Format("G{0}", 7), string.Format("G{0}", table.Rows.Count + 7));
  rang.NumberFormatLocal = "$#,##0.00"; //设置单元格格式为货币格式 
		跨sheet求和：
		Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);
		Excel.Range range3 =wsheet1.get_Range("A23", Type.Missing);
		range3.Formula = "=Sheet3!B8+Sheet3!B12";
		range3.Calculate();
		 sheet.Cells[1, 4] = ds.Tables[1].Rows[0]["FeightCode"].ToString();//给合并的列赋值
		//循环加载数据
		 int startIndex = 0;
			  for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
				{
				    if (ds.Tables[1].Rows[j - 1]["BoxCode"].ToString() == ds.Tables[0].Rows[i]["BoxCode"].ToString())
				    {
					range = sheet.get_Range("b" + (15 + startIndex) + "", "e" + (15 + startIndex) + "");
					range.MergeCells = true;
					sheet.Cells[15 + startIndex, 2] = ds.Tables[0].Rows[i]["TypeName"].ToString();
					range = (_Excel.Range)sheet.Cells[15 + startIndex, 2];
					range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
					range.Font.Bold = true;
					range.RowHeight = 22;
					sheet.Cells[15 + startIndex, 6] = ds.Tables[0].Rows[i]["Qty"].ToString();
					range = (_Excel.Range)sheet.Cells[15 + startIndex, 6];
					range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
					range.Font.Bold = true;
					range.RowHeight = 22;
					sheet.Cells[15 + startIndex, 7] = ds.Tables[0].Rows[i]["UnitPrice"].ToString();
					range = (_Excel.Range)sheet.Cells[15 + startIndex, 7];
					range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
					range.Font.Bold = true;
					range.RowHeight = 22;
					sheet.Cells[15 + startIndex, 8] = ds.Tables[0].Rows[i]["Subtotal"].ToString();
					range = (_Excel.Range)sheet.Cells[15 + startIndex, 8];
					range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
					range.Font.Bold = true;
					range.RowHeight = 22;
					 Image image = Picture.ReadPicture(ds.Tables[0].Rows[row][col].ToString());
						range = sheet.get_Range(string.Format("A{0}", row * num + 29), string.Format("A{0}", row * num + 48));
						sheet.Shapes.AddPicture(ds.Tables[0].Rows[row][col].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(range.Left) + 15 + image.Width, Convert.ToSingle(range.Top) + 5, image.Width, image.Height);   //导出图片
					range.EntireColumn.AutoFit();
					System.Windows.Forms.Application.DoEvents();
					startIndex++;
				    }
				}

[csharp] view plain copy print ?

rang = sheet.get_Range("a" + (table.Rows.Count + 3) + "", "r" + (table.Rows.Count + 3) + "");
rang.Font.Bold = true;
rang.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();//设置单元格背景颜色

                 rang = sheet.get_Range("a" + (table.Rows.Count + 3) + "", "r" + (table.Rows.Count + 3) + "");
                 rang.Font.Bold = true;
                 rang.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();//设置单元格背景颜色

[csharp] view plain copy print ?

sheet.Columns.AutoFit();
sheet.Cells.EntireColumn.AutoFit();
sheet.Columns.EntireColumn.AutoFit();//列宽自适应。
// sheet.Cells.Borders.LineStyle =BorderStyle.Fixed3D;//设置边框
sheet = null;
book.Save();
}
book.Close(sheet, savePath, System.Type.Missing);
app.Quit();
app.DisplayAlerts=false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
System.GC.Collect();
KillProcess(app);
/// <summary>
/// 导出Excel后，杀死Excel进程
/// </summary>
/// <param name="app"></param>
private static void KillProcess(_Excel.Application app)
{
IntPtr t = new IntPtr(app.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

			    sheet.Columns.AutoFit();
			    sheet.Cells.EntireColumn.AutoFit();
			    sheet.Columns.EntireColumn.AutoFit();//列宽自适应。
			    //  sheet.Cells.Borders.LineStyle =BorderStyle.Fixed3D;//设置边框
				sheet = null;
				book.Save();
			    }
			    book.Close(sheet, savePath, System.Type.Missing);
			    app.Quit();
			    app.DisplayAlerts=false;     //保存Excel的时候，不弹出是否保存的窗口直接进行保存
			    System.GC.Collect();
			    KillProcess(app);       
 
         /// <summary>
        /// 导出Excel后，杀死Excel进程
        /// </summary>
        /// <param name="app"></param>
        private static void KillProcess(_Excel.Application app)
        {
            IntPtr t = new IntPtr(app.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();
        }
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);

C#导出写保护的Excel，设置某些区域可以编辑
对Excel操作时，由于使用权限的不同，可能对表格的操作权限也不一样。EXCEL提供了保护工作表以及允许编辑单元格功能。相应的在C#中就可以对Excel表格进行操作。
有两种方法可以实现：
第一种：

[csharp] view plain copy print ?

主要用Protect（）方法保护工作表，Worksheet.Protection.AllowEditRanges设置允许编辑的单元格。
public void CreateExcel()
{
//创建一个Excel文件
Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet excelSheet = null;
myExcel.Application.Workbooks.Add(true);
//让Excel文件可见
myExcel.Visible = true;
myExcel.Cells[1, 4] = "普通报表";
//逐行写入数据
for (int i = 0; i < 11; i++)
{
for (int j = 0; j < 7; j++)
{
//以单引号开头，表示该单元格为纯文本
myExcel.Cells[2 + i, 1 + j] = "'" + i;
}
}
try
{
string excelTemp ="c:\\a.xls";
//excelWorkbook = myExcel.Workbooks[1];
excelWorkbook = myExcel.ActiveWorkbook;
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.ActiveSheet;
//设定允许操作的单元格
Microsoft.Office.Interop.Excel.AllowEditRanges ranges = excelSheet.Protection.AllowEditRanges;
ranges.Add("Information", myExcel.Application.get_Range("B2", "B2"), Type.Missing);
//保护工作表
excelSheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, true, Type.Missing, Type.Missing);
//Realease the com object
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);
excelSheet = null;
//Save the result to a temp path
excelWorkbook.SaveAs(excelTemp, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing,Type.Missing,Type.Missing);
}
catch (Exception ex)
{
throw;
}
finally
{
if (excelWorkbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;
}
if (myExcel != null)
{
myExcel.Workbooks.Close();
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myExcel = null;
}
GC.Collect();
}
}
PS：借用此方法我写了个循环来设定单元格保护，没想到一直在报HRESULT:0x800A03EC 的一个异常，郁闷。
经过一番折腾，发现 AllowEditRanges.Add方法的第一个参数名是不能够重复的，写循环的时候没注意。

主要用Protect（）方法保护工作表，Worksheet.Protection.AllowEditRanges设置允许编辑的单元格。
public void CreateExcel()
    {
        //创建一个Excel文件
        Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
        Microsoft.Office.Interop.Excel.Worksheet excelSheet = null;
        myExcel.Application.Workbooks.Add(true);
        //让Excel文件可见
        myExcel.Visible = true;
        myExcel.Cells[1, 4] = "普通报表";
        //逐行写入数据
        for (int i = 0; i < 11; i++)
        {
            for (int j = 0; j < 7; j++)
            {
                //以单引号开头，表示该单元格为纯文本
                myExcel.Cells[2 + i, 1 + j] = "'" + i;
            }
        }
        try
        {
            string excelTemp ="c:\\a.xls";       
            //excelWorkbook = myExcel.Workbooks[1];
            excelWorkbook = myExcel.ActiveWorkbook;
            excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.ActiveSheet;
            
            //设定允许操作的单元格
            Microsoft.Office.Interop.Excel.AllowEditRanges ranges = excelSheet.Protection.AllowEditRanges;
            ranges.Add("Information",  myExcel.Application.get_Range("B2", "B2"),  Type.Missing);
            //保护工作表
            excelSheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,
              Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
              Type.Missing, true, Type.Missing, Type.Missing);
            //Realease the com object
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);
            excelSheet = null;

            //Save the result to a temp path
            excelWorkbook.SaveAs(excelTemp, Excel.XlFileFormat.xlWorkbookNormal,  null, null, false, false,
                                 Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
                                 Type.Missing, Type.Missing,Type.Missing,Type.Missing);
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (excelWorkbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                excelWorkbook = null;
            }
            if (myExcel != null)
            {
                myExcel.Workbooks.Close();
                myExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
                myExcel = null;
            }
            GC.Collect();
        }
    }
 PS：借用此方法我写了个循环来设定单元格保护，没想到一直在报HRESULT:0x800A03EC 的一个异常，郁闷。
        经过一番折腾，发现 AllowEditRanges.Add方法的第一个参数名是不能够重复的，写循环的时候没注意。

第二种：
用locked属性，设置Locked = false 的区域就可编辑的区域

[csharp] view plain copy print ?

worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[10, 10]).Locked = false;
//保护工作表
worksheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, true, true, true);
///
/// 在工作表中插入行，并调整其他行以留出空间
///
///
当前工作表
///
欲插入的行索引
private void InsertRows(Excel.Worksheet sheet, int rowIndex)
{
range = (Excel.Range)sheet.Rows[rowIndex, missing];
//object Range.Insert(object shift, object copyorigin);
//shift: Variant类型，可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一：
//xlShiftToRight 或 xlShiftDown。如果省略该参数，Microsoft Excel 将根据区域形状确定调整方式。
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
}
///
/// 在工作表中删除行
///
///
当前工作表
///
欲删除的行索引
private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
{
range = (Range)sheet.Rows[rowIndex, missing];
range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
///
/// 退出Excel，并且释放调用的COM资源
///
private void Dispose()
{
book.Close(missing, missing, missing);
app.Workbooks.Close();
app.Quit();
}

worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[10, 10]).Locked = false;
   //保护工作表
  worksheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,
           Type.Missing, Type.Missing, Type.Missing, Type.Missing,
           Type.Missing, Type.Missing, Type.Missing, Type.Missing,
           Type.Missing, true, true, true);


  ///     
      ///   在工作表中插入行，并调整其他行以留出空间   
      ///     
      ///     
  当前工作表   
      ///     
  欲插入的行索引   
      private   void   InsertRows(Excel.Worksheet   sheet,   int   rowIndex)   
      {   
        range   =   (Excel.Range)sheet.Rows[rowIndex,   missing];         
        //object   Range.Insert(object   shift,   object   copyorigin);     
        //shift:   Variant类型，可选。指定单元格的调整方式。可以为下列   XlInsertShiftDirection   常量之一：   
        //xlShiftToRight   或   xlShiftDown。如果省略该参数，Microsoft   Excel   将根据区域形状确定调整方式。   
        range.Insert(Excel.XlInsertShiftDirection.xlShiftDown,   missing);       
      }   
    
      ///     
      ///   在工作表中删除行   
      ///     
      ///     
  当前工作表   
      ///     
  欲删除的行索引   
      private   void   DeleteRows(Excel.Worksheet   sheet,   int   rowIndex)   
      {   
        range   =   (Range)sheet.Rows[rowIndex,   missing];   
        range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);   
      }   
    
      ///     
      ///   退出Excel，并且释放调用的COM资源   
      ///     
      private   void   Dispose()   
      {   
        book.Close(missing,   missing,   missing);   
        app.Workbooks.Close();   
        app.Quit();   
	}

----------------------------NOPI导出Excel-------------------------------

改单元格样式点击打开链接

[csharp] view plain copy print ?

dll下载地址：<A href="http://download.csdn.net/detail/happy09li/4436160" target=_blank>点击打开链接</A>
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
public static void exportExcel(DataTable dtSource, string strFileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
//填充表头
HSSFRow dataRow = sheet.CreateRow(0);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
dataRow.GetCell(column.Ordinal).CellStyle = headStyle;
// dataRow.GetCell(column.Ordinal).c
//设置列宽
//sheet.SetColumnWidth(column.Ordinal,column.Caption.Length*20);
}
//填充内容
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
}
}</pre><pre name="code" class="csharp"> for (int m = 0; m < dtData.Columns.Count - 1; m++)
{
for (int n = 0; n < dtData.Rows.Count - 1; n++)
{
if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n + 1).GetCell(m).ToString() && !string.IsNullOrEmpty(sheet.GetRow(n).GetCell(m).ToString()))
{
sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(n, m, n + 1, m));
}
//else if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n).GetCell(m + 1).ToString())
//{
// sheet.AddMergedRegion(new Region(n, m, n, m + 1));
//}
}
}
//保存
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
workbook.Dispose();
}

dll下载地址：点击打开链接
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
public static void exportExcel(DataTable dtSource, string strFileName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet();
            //填充表头   
            HSSFRow dataRow = sheet.CreateRow(0);
            HSSFCellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = CellHorizontalAlignment.CENTER;
            HSSFFont font = workbook.CreateFont();
            font.FontHeightInPoints = 20;
            font.Boldweight = 700;
            headStyle.SetFont(font);
            
            foreach (DataColumn column in dtSource.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                dataRow.GetCell(column.Ordinal).CellStyle = headStyle;
              //  dataRow.GetCell(column.Ordinal).c
                //设置列宽   
               //sheet.SetColumnWidth(column.Ordinal,column.Caption.Length*20); 
            }
            //填充内容   
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                }
            }</pre><pre name="code" class="csharp"> for (int m = 0; m < dtData.Columns.Count - 1; m++)
        {
          for (int n = 0; n < dtData.Rows.Count - 1; n++)
          {
            if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n + 1).GetCell(m).ToString() && !string.IsNullOrEmpty(sheet.GetRow(n).GetCell(m).ToString()))
            {
              sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(n, m, n + 1, m));
            }
            //else if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n).GetCell(m + 1).ToString())
            //{
            //  sheet.AddMergedRegion(new Region(n, m, n, m + 1));
            //}
          }
        }
            //保存   
            using (MemoryStream ms = new MemoryStream())
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                }
            }
            workbook.Dispose();   
        }

posted on 2012-12-06 16:53 五蕴阅读(521) 评论(0) 收藏举报

刷新页面返回顶部