Excel 追加记录
最近要做个操作excel 文件追加的功能 ，分享一下 写的不好 请指教！！
-----------------------------------------------------------------
using System;
using System.Collections;
using System.Text;
using Excel;
using System.Collections.Generic;
namespace HappyAudio2008
{
public class ExcelAction
{
/// <summary>
//文件名称
/// </summary>
private string fileName;
private object miss = System.Type.Missing;
public string FileName
{
get { return fileName; }
set { fileName = value; }
}
private Font font;
public Font ExcelFont
{
get { return font; }
set { font = value; }
}
/// <summary>
/// 工作簿的INDEX
/// </summary>
private int workSheetsIndex;
public int WorkSheetsIndex
{
get { return workSheetsIndex; }
set { workSheetsIndex = value; }
}
/// <summary>
/// 追加到EXCEL
/// </summary>
/// <param name="list">添加的列表</param>
public void AppendText(List<Hashtable> list)
{
Excel.ApplicationClass excel = new ApplicationClass();
Workbooks workBooks = null;
excel.Workbooks.Open(fileName, miss, false, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
try
{
workBooks = excel.Workbooks;
Workbook curWorkBook = workBooks[1];
Worksheet curSheet = (Worksheet)curWorkBook.Worksheets[WorkSheetsIndex];
int cout = curSheet.UsedRange.Rows.Count;
for (int i = 0; i < list.Count; i++)
{
Hashtable ht = list[i];
int j = 1;
foreach (object key in ht.Keys)
{
curSheet.Cells[cout + 1 + i, j] = ht[key];
j++;
}
}
curWorkBook.Save();
workBooks.Close();
excel.Quit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (workBooks != null) { workBooks.Close(); }
if (excel != null) { excel.Quit(); }
}
}
/// <summary>
/// 追加记录
/// </summary>
/// <param name="list"></param>
public void AppendText(List<object[]> list)
{
ApplicationClass excel = new ApplicationClass();
object miss = System.Type.Missing;
excel.Workbooks.Open(fileName, miss, false, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
Workbooks workBooks = null;
try
{
workBooks = excel.Workbooks;
Workbook curWorkBook = workBooks[1];
Worksheet curSheet = (Worksheet)curWorkBook.Worksheets[WorkSheetsIndex];
int cout = curSheet.UsedRange.Rows.Count;
for (int i = 0; i < list.Count; i++)
{
Object[] oArray = list[i];
int j = 1;
foreach (object oValue in oArray)
{
curSheet.Cells[cout + 1 + i, j] = oValue;
j++;
}
}
curWorkBook.Save();
workBooks.Close();
excel.Quit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (workBooks != null) { workBooks.Close(); }
if (excel != null) { excel.Quit(); }
}
}
/// <summary>
/// 设定行的格式 可以自行添加格式
/// </summary>
public void SetRowStyle(Worksheet sheet, int x, int y, int cout)
{
Excel.Range range = sheet.get_Range(sheet.Cells[x, y], sheet.Cells[x + cout, y]);
range.Font.Size = font.Size;
range.Font.Bold = font.Bold;
//range.Font.Color = color;
range.Font.Name = font.Name;
range.Font.Italic = font.Italic;
range.Font.Underline = font.Underline;
}
/// <summary>
/// 设定列的格式 可以自行添加格式
/// </summary>
/// <param name="sheet"></param>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="cout"></param>
public void SetColStyle(Worksheet sheet, int x, int y, int cout)
{
Excel.Range range = sheet.get_Range(sheet.Cells[x, y], sheet.Cells[x, y + cout]);
range.Font.Size = font.Size;
range.Font.Bold = font.Bold;
// range.Font.Color = color;
range.Font.Name = font.Name;
range.Font.Italic = font.Italic;
range.Font.Underline = font.Underline;
// range.Width = 300;
// range.Height = 20;
}
/// <summary>
/// 设定格式
/// </summary>
/// <param name="fileName"></param>
public ExcelAction(string fileName)
{
this.fileName = fileName;
}
public ExcelAction() { }
}
}
/////////////////////////////////////////////////////////
///转载一个别人些的程序
/////////////////////////////////////////////////////////
// csc /r:Interop.Excel.dll
namespace Skyiv
{
using Excel;
public class Excel2000 : ApplicationClass
{
object TM = System.Type.Missing;
public Excel2000()
{
DisplayAlerts = false;
}
// 返回当前工作表的使用的行数
public int GetRowsCount()
{
return ((Worksheet)ActiveSheet).UsedRange.Rows.Count;
}
// 返回工作表 sheet 的使用的行数
public int GetRowsCount(int sheet)
{
return ((Worksheet)ActiveWorkbook.Sheets[sheet]).UsedRange.Rows.Count;
}
// 用模板创建工作簿
public void CreateFromTemplet(string templet, params string [] names)
{
foreach (string name in names)
{
(Workbooks.Add(templet)).SaveAs(name, TM, TM, TM, TM, TM, XlSaveAsAccessMode.xlNoChange, TM, TM, TM, TM);
}
}
// 打开工作簿
public void Open(params string [] names)
{
foreach (string name in names)
{
Workbooks.Open(name, TM, TM, TM, TM, TM, TM, TM, TM, TM, TM, TM, TM);
}
}
// 复制当前工作簿的 sheet0 到 sheet1 之前
public void CopySheetBefore(int sheet0, int sheet1)
{
((Worksheet)ActiveWorkbook.Sheets[sheet0]).Copy(ActiveWorkbook.Sheets[sheet1], TM);
}
// 复制当前工作簿的 sheet0 到 sheet1 之后
public void CopySheetAfter(int sheet0, int sheet1)
{
((Worksheet)ActiveWorkbook.Sheets[sheet0]).Copy(TM, ActiveWorkbook.Sheets[sheet1]);
}
// 复制工作簿 book 的 sheet0 到 sheet1 之后
public void CopySheetAfter(int book, int sheet0, int sheet1)
{
((Worksheet)Workbooks[book].Sheets[sheet0]).Copy(TM, Workbooks[book].Sheets[sheet1]);
}
// 把删除当前工作簿的工作表 sheet
public void DeleteSheet(int sheet)
{
((Worksheet)ActiveWorkbook.Sheets[sheet]).Delete();
}
// 返回工作表 sheet 的名称
public string GetSheetName(int sheet)
{
return ((Worksheet)ActiveWorkbook.Sheets[sheet]).Name;
}
// 把当前工作表改名为 name
public void SetSheetName(string name)
{
((Worksheet)ActiveSheet).Name = name;
}
// 把工作表 sheet 改名为 name
public void SetSheetName(int sheet, string name)
{
((Worksheet)ActiveWorkbook.Sheets[sheet]).Name = name;
}
// 把工作簿 book 的工作表 sheet 改名为 name
public void SetSheetName(int book, int sheet, string name)
{
((Worksheet)Workbooks[book].Sheets[sheet]).Name = name;
}
// 返回当前工作表的单元格的值
public object GetCellValue(object col, object row)
{
return ((Range)((Worksheet)ActiveSheet).Cells[row, col]).Value;
}
// 返回工作表sheet的单元格的值
public object GetCellValue(int sheet, object col, object row)
{
return ((Range)((Worksheet)ActiveWorkbook.Sheets[sheet]).Cells[row, col]).Value;
}
// 对当前工作表的单元格赋值
public void SetCellText(object col, object row, object txt)
{
Cells[row, col] = txt;
}
// 对book工作簿的sheet工作表的单元格赋值
public void SetCellText(object book, object sheet, object col, object row, object txt)
{
((Worksheet)Workbooks[book].Sheets[sheet]).Cells[row, col] = txt;
}
// 对book工作簿的sheet工作表, 在第row行插入一行(复制本行)
public void InsertCopyRowAt(object book, object sheet, object row)
{
((Range)((Worksheet)Workbooks[book].Sheets[sheet]).Rows[row, TM]).Copy(TM);
((Range)((Worksheet)Workbooks[book].Sheets[sheet]).Cells[row, 1]).EntireRow.Insert(0);
}
// 在第row行插入一行(复制本行)
public void InsertCopyRowAt(object row)
{
((Range)((Worksheet)ActiveSheet).Rows[row, TM]).Copy(TM);
((Range)((Worksheet)ActiveSheet).Cells[row, 1]).EntireRow.Insert(0);
}
// 在第col行插入一列(复制本列)
public void InsertCopyColumnAt(object col)
{
((Range)((Worksheet)ActiveSheet).Columns[col, TM]).Copy(TM);
((Range)((Worksheet)ActiveSheet).Cells[1, col]).EntireColumn.Insert(0);
}
// 对book工作簿的sheet工作表, 删除第row行
public void DeleteRowAt(object book, object sheet, object row)
{
((Range)((Worksheet)Workbooks[book].Sheets[sheet]).Rows[row, TM]).Delete(TM);
}
// 删除第row行
public void DeleteRowAt(object row)
{
((Range)((Worksheet)ActiveSheet).Rows[row, TM]).Delete(TM);
}
// 分类汇总
public void Subtotal(int r0, int c0, int r1, int c1, int GroupBy, int[] TotalList)
{
get_Range(Cells[r0,c0], Cells[r1,c1]).
Subtotal(GroupBy, XlConsolidationFunction.xlSum, TotalList, TM, TM, XlSummaryRow.xlSummaryBelow);
}
// 清除
public void Clear(int r0, int c0, int r1, int c1)
{
get_Range(Cells[r0,c0], Cells[r1,c1]).Clear();
}
// 保存所有工作簿
public void SaveAll()
{
foreach (Workbook book in Workbooks)
{
book.Save();
}
}
}
}
