1.ExcelHelper源碼
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
//using cfg = System.Configuration;
/// <summary>
/// 說 明：Excel輸出列印子產品
/// 暫時不提供操作Excel對象樣式方法，樣式可以在Excel模闆中設定好
/// </summary>
public class ExcelHelper
{
#region 成員變量
private string templetFile = null;
private string outputFile = null;
private object missing = Missing.Value;
private DateTime beforeTime; //Excel啟動之前時間
private DateTime afterTime; //Excel啟動之後時間
Excel.Application app;
Excel.Workbook workBook;
Excel.Worksheet workSheet;
Excel.Range range;
Excel.Range range1;
Excel.Range range2;
Excel.TextBox textBox;
private int sheetCount = 1; //WorkSheet數量
private string sheetPrefixName = "頁";
#endregion
#region 公共屬性
/// <summary>
/// WorkSheet字首名，比如：字首名為“頁”，那麼WorkSheet名稱依次為“頁-1，頁-2...”
/// </summary>
public string SheetPrefixName
{
set { this.sheetPrefixName = value; }
}
/// <summary>
/// WorkSheet數量
/// </summary>
public int WorkSheetCount
{
get { return workBook.Sheets.Count; }
}
/// <summary>
/// Excel模闆檔案路徑
/// </summary>
public string TempletFilePath
{
set { this.templetFile = value; }
}
/// <summary>
/// 輸出Excel檔案路徑
/// </summary>
public string OutputFilePath
{
set { this.outputFile = value; }
}
#endregion
#region 公共方法
#region ExcelHelper
/// <summary>
/// 構造函數，将一個已有Excel工作簿作為模闆，并指定輸出路徑
/// </summary>
/// <param name="templetFilePath">Excel模闆檔案路徑</param>
/// <param name="outputFilePath">輸出Excel檔案路徑</param>
public ExcelHelper(string templetFilePath, string outputFilePath)
{
if (templetFilePath == null)
throw new Exception("Excel模闆檔案路徑不能為空！");
if (outputFilePath == null)
throw new Exception("輸出Excel檔案路徑不能為空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路徑的Excel模闆檔案不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
//建立一個Application對象并使其可見
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打開模闆檔案，得到WorkBook對象
workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet對象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 構造函數，打開一個已有的工作簿
/// </summary>
/// <param name="fileName">Excel檔案名</param>
public ExcelHelper(string fileName)
{
if (!File.Exists(fileName))
throw new Exception("指定路徑的Excel檔案不存在！");
//建立一個Application對象并使其可見
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打開一個WorkBook
workBook = app.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//得到WorkSheet對象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 構造函數，建立一個工作簿
/// </summary>
public ExcelHelper()
{
//建立一個Application對象并使其可見
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
//app.Visible = true; 不自動打開
afterTime = DateTime.Now;
//建立一個WorkBook
workBook = app.Workbooks.Add(Type.Missing);
//得到WorkSheet對象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
#endregion
#region Data Export Methods
/// <summary>
/// 将DataTable資料寫入Excel檔案（自動分頁）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每個WorkSheet寫入多少行資料</param>
/// <param name="top">表格資料起始行索引</param>
/// <param name="left">表格資料起始列索引</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left)
{
int rowCount = dt.Rows.Count; //DataTable行數
int colCount = dt.Columns.Count; //DataTable列數
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet個數
// StringBuilder sb;
//複制sheetCount-1個WorkSheet對象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //記錄起始行索引
int endRow = i * rows; //記錄結束行索引
//若是最後一個WorkSheet，那麼記錄結束行索引為源DataTable行數
if (i == sheetCount)
endRow = rowCount;
//擷取要寫入資料的WorkSheet對象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的資料寫入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二維數組批量寫入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
#region 利用Windwo粘貼闆批量拷貝資料（在Web下面行不通）
#endregion
}
}
/// <summary>
/// 将DataTable資料寫入Excel檔案（不分頁）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="top">表格資料起始行索引</param>
/// <param name="left">表格資料起始列索引</param>
public void DataTableToExcel(DataTable dt, int top, int left)
{
int rowCount = dt.Rows.Count; //DataTable行數
int colCount = dt.Columns.Count; //DataTable列數
//利用二維數組批量寫入
string[,] arr = new string[rowCount+1, colCount+1];
for (int i = 0; i < colCount;i++ )
{
arr[0, i] = dt.Columns[i].ColumnName.ToString();
}
for (int j = 0; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
arr[j+1, k] = dt.Rows[j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount+1, colCount+1);
range.Value = arr;
}
/// <summary>
/// 将DataTable資料寫入Excel檔案（自動分頁，并指定要合并的列索引）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每個WorkSheet寫入多少行資料</param>
/// <param name="top">表格資料起始行索引</param>
/// <param name="left">表格資料起始列索引</param>
/// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引，從0開始</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left, int mergeColumnIndex)
{
int rowCount = dt.Rows.Count; //源DataTable行數
int colCount = dt.Columns.Count; //源DataTable列數
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet個數
// StringBuilder sb;
//複制sheetCount-1個WorkSheet對象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //記錄起始行索引
int endRow = i * rows; //記錄結束行索引
//若是最後一個WorkSheet，那麼記錄結束行索引為源DataTable行數
if (i == sheetCount)
endRow = rowCount;
//擷取要寫入資料的WorkSheet對象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的資料寫入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二維數組批量寫入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}
/// <summary>
/// 将二維數組資料寫入Excel檔案（自動分頁）
/// </summary>
/// <param name="arr">二維數組</param>
/// <param name="rows">每個WorkSheet寫入多少行資料</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(string[,] arr, int rows, int top, int left)
{
int rowCount = arr.GetLength(0); //二維數組行數（一維長度）
int colCount = arr.GetLength(1); //二維資料列數（二維長度）
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet個數
//複制sheetCount-1個WorkSheet對象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
//将二維數組資料寫入Excel
for (int i = sheetCount; i >= 1; i--)
{
int startRow = (i - 1) * rows; //記錄起始行索引
int endRow = i * rows; //記錄結束行索引
//若是最後一個WorkSheet，那麼記錄結束行索引為源DataTable行數
if (i == sheetCount)
endRow = rowCount;
//擷取要寫入資料的WorkSheet對象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将二維數組中的資料寫入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = arr[startRow + j,k];
// }
// }
//利用二維數組批量寫入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = arr[startRow + j, k];
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
}
}//end ArrayToExcel
/// <summary>
/// 将二維數組資料寫入Excel檔案（不分頁）
/// </summary>
/// <param name="arr">二維數組</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(string[,] arr, int top, int left)
{
int rowCount = arr.GetLength(0); //二維數組行數（一維長度）
int colCount = arr.GetLength(1); //二維資料列數（二維長度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.FormulaArray = arr;
}//end ArrayToExcel
/// <summary>
/// 将二維數組資料寫入Excel檔案（不分頁）
/// </summary>
/// <param name="arr">二維數組</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="isFormula">填充的資料是否需要計算</param>
public void ArrayToExcel(string[,] arr, int top, int left, bool isFormula)
{
int rowCount = arr.GetLength(0); //二維數組行數（一維長度）
int colCount = arr.GetLength(1); //二維資料列數（二維長度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
//注意：使用range.FormulaArray寫合并的單元格會出問題
if (isFormula)
range.FormulaArray = arr;
else
range.Value = arr;
}//end ArrayToExcel
/// <summary>
/// 将二維數組資料寫入Excel檔案（不分頁），合并指定列的相同行
/// </summary>
/// <param name="arr">二維數組</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="isFormula">填充的資料是否需要計算</param>
/// <param name="mergeColumnIndex">需要合并行的列索引</param>
public void ArrayToExcel(string[,] arr, int top, int left, bool isFormula, int mergeColumnIndex)
{
int rowCount = arr.GetLength(0); //二維數組行數（一維長度）
int colCount = arr.GetLength(1); //二維資料列數（二維長度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
//注意：使用range.FormulaArray寫合并的單元格會出問題
if (isFormula)
range.FormulaArray = arr;
else
range.Value = arr;
this.MergeRows(workSheet, mergeColumnIndex, top, rowCount);
}//end ArrayToExcel
/// <summary>
/// 将二維數組資料寫入Excel檔案（不分頁）
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="arr">二維數組</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(int sheetIndex, string[,] arr, int top, int left)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
// 改變目前工作表
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
int rowCount = arr.GetLength(0); //二維數組行數（一維長度）
int colCount = arr.GetLength(1); //二維資料列數（二維長度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}//end ArrayToExcel
/// <summary>
/// 将二維數組資料寫入Excel檔案（自動分頁，并指定要合并的列索引）
/// </summary>
/// <param name="arr">二維數組</param>
/// <param name="rows">每個WorkSheet寫入多少行資料</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="mergeColumnIndex">數組的二維索引，相當于DataTable的列索引，索引從0開始</param>
public void ArrayToExcel(string[,] arr, int rows, int top, int left, int mergeColumnIndex)
{
int rowCount = arr.GetLength(0); //二維數組行數（一維長度）
int colCount = arr.GetLength(1); //二維資料列數（二維長度）
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet個數
//複制sheetCount-1個WorkSheet對象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
//将二維數組資料寫入Excel
for (int i = sheetCount; i >= 1; i--)
{
int startRow = (i - 1) * rows; //記錄起始行索引
int endRow = i * rows; //記錄結束行索引
//若是最後一個WorkSheet，那麼記錄結束行索引為源DataTable行數
if (i == sheetCount)
endRow = rowCount;
//擷取要寫入資料的WorkSheet對象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将二維數組中的資料寫入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
workSheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
//利用二維數組批量寫入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = arr[startRow + j, k];
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}//end ArrayToExcel
#endregion
#region WorkSheet Methods
/// <summary>
/// 改變目前工作表
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
public void ChangeCurrentWorkSheet(int sheetIndex)
{
//若指定工作表索引超出範圍，則不改變目前工作表
if (sheetIndex < 1)
return;
if (sheetIndex > this.WorkSheetCount)
return;
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
}
/// <summary>
/// 隐藏指定名稱的工作表
/// </summary>
/// <param name="sheetName">工作表名稱</param>
public void HiddenWorkSheet(string sheetName)
{
try
{
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
sheet = workSheet;
}
if (sheet != null)
sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
else
{
this.KillExcelProcess();
throw new Exception("名稱為/"" + sheetName + "/"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 隐藏指定索引的工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void HiddenWorkSheet(int sheetIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
Excel.Worksheet sheet = null;
sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);
sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 在指定名稱的工作表後面拷貝指定個數的該工作表的副本，并重命名
/// </summary>
/// <param name="sheetName">工作表名稱</param>
/// <param name="sheetCount">工作表個數</param>
public void CopyWorkSheets(string sheetName, int sheetCount)
{
try
{
Excel.Worksheet sheet = null;
int sheetIndex = 0;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
sheetIndex = workSheet.Index;
}
}
if (sheet != null)
{
for (int i = sheetCount; i >= 1; i--)
{
sheet.Copy(this.missing, sheet);
}
//重命名
for (int i = sheetIndex; i <= sheetIndex + sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
workSheet.Name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
}
}
else
{
this.KillExcelProcess();
throw new Exception("名稱為/"" + sheetName + "/"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 将一個工作表拷貝到另一個工作表後面，并重命名
/// </summary>
/// <param name="srcSheetIndex">拷貝源工作表索引</param>
/// <param name="aimSheetIndex">參照位置工作表索引，新工作表拷貝在該工作表後面</param>
/// <param name="newSheetName"></param>
public void CopyWorkSheet(int srcSheetIndex, int aimSheetIndex, string newSheetName)
{
if (srcSheetIndex > this.WorkSheetCount || aimSheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
Excel.Worksheet srcSheet = (Excel.Worksheet)workBook.Sheets.get_Item(srcSheetIndex);
Excel.Worksheet aimSheet = (Excel.Worksheet)workBook.Sheets.get_Item(aimSheetIndex);
srcSheet.Copy(this.missing, aimSheet);
//重命名
workSheet = (Excel.Worksheet)aimSheet.Next; //擷取新拷貝的工作表
workSheet.Name = newSheetName;
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 根據名稱删除工作表
/// </summary>
/// <param name="sheetName"></param>
public void DeleteWorkSheet(string sheetName)
{
try
{
Excel.Worksheet sheet = null;
//找到名稱位sheetName的工作表
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
}
}
if (sheet != null)
{
sheet.Delete();
}
else
{
this.KillExcelProcess();
throw new Exception("名稱為/"" + sheetName + "/"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 根據索引删除工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void DeleteWorkSheet(int sheetIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
Excel.Worksheet sheet = null;
sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);
sheet.Delete();
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
#endregion
#region TextBox Methods
/// <summary>
/// 向指定文本框寫入資料，對每個WorkSheet操作
/// </summary>
/// <param name="textboxName">文本框名稱</param>
/// <param name="text">要寫入的文本</param>
public void SetTextBox(string textboxName, string text)
{
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
try
{
textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
textBox.Text = text;
}
catch
{
this.KillExcelProcess();
throw new Exception("不存在ID為/"" + textboxName + "/"的文本框！");
}
}
}
/// <summary>
/// 向指定文本框寫入資料，對指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="textboxName">文本框名稱</param>
/// <param name="text">要寫入的文本</param>
public void SetTextBox(int sheetIndex, string textboxName, string text)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
try
{
textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
textBox.Text = text;
}
catch
{
this.KillExcelProcess();
throw new Exception("不存在ID為/"" + textboxName + "/"的文本框！");
}
}
/// <summary>
/// 向文本框寫入資料，對每個WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的鍵值對儲存文本框的ID和資料</param>
public void SetTextBoxes(Hashtable ht)
{
if (ht.Count == 0) return;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
foreach (DictionaryEntry dic in ht)
{
try
{
textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
textBox.Text = dic.Value.ToString();
}
catch
{
this.KillExcelProcess();
throw new Exception("不存在ID為/"" + dic.Key.ToString() + "/"的文本框！");
}
}
}
}
/// <summary>
/// 向文本框寫入資料，對指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的鍵值對儲存文本框的ID和資料</param>
public void SetTextBoxes(int sheetIndex, Hashtable ht)
{
if (ht.Count == 0) return;
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
foreach (DictionaryEntry dic in ht)
{
try
{
textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
textBox.Text = dic.Value.ToString();
}
catch
{
this.KillExcelProcess();
throw new Exception("不存在ID為/"" + dic.Key.ToString() + "/"的文本框！");
}
}
}
#endregion
#region Cell Methods
/// <summary>
/// 向單元格寫入資料，對目前WorkSheet操作
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="text">要寫入的文本值</param>
public void SetCells(int rowIndex, int columnIndex, string text)
{
try
{
workSheet.Cells[rowIndex, columnIndex] = text;
}
catch
{
this.KillExcelProcess();
throw new Exception("向單元格[" + rowIndex + "," + columnIndex + "]寫資料出錯！");
}
}
/// <summary>
/// 向單元格寫入資料，對指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="text">要寫入的文本值</param>
public void SetCells(int sheetIndex, int rowIndex, int columnIndex, string text)
{
try
{
this.ChangeCurrentWorkSheet(sheetIndex); //改變目前工作表為指定工作表
workSheet.Cells[rowIndex, columnIndex] = text;
}
catch
{
this.KillExcelProcess();
throw new Exception("向單元格[" + rowIndex + "," + columnIndex + "]寫資料出錯！");
}
}
/// <summary>
/// 向單元格寫入資料，對每個WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的鍵值對儲存單元格的位置索引（行索引和列索引用“,”隔開）和資料</param>
public void SetCells(Hashtable ht)
{
int rowIndex;
int columnIndex;
string position;
if (ht.Count == 0) return;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
foreach (DictionaryEntry dic in ht)
{
try
{
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
workSheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch
{
this.KillExcelProcess();
throw new Exception("向單元格[" + dic.Key + "]寫資料出錯！");
}
}
}
}
/// <summary>
/// 向單元格寫入資料，對指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的鍵值對儲存單元格的位置索引（行索引和列索引用“,”隔開）和資料</param>
public void SetCells(int sheetIndex, Hashtable ht)
{
int rowIndex;
int columnIndex;
string position;
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
if (ht.Count == 0) return;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
foreach (DictionaryEntry dic in ht)
{
try
{
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
workSheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch
{
this.KillExcelProcess();
throw new Exception("向單元格[" + dic.Key + "]寫資料出錯！");
}
}
}
/// <summary>
/// 設定單元格為可計算的
/// </summary>
/// <remarks>
/// 如果Excel的單元格格式設定為數字，日期或者其他類型時，需要設定這些單元格的FormulaR1C1屬性，
/// 否則寫到這些單元格的資料将不會按照預先設定的格式顯示
/// </remarks>
/// <param name="arr">儲存單元格的位置索引（行索引和列索引用“,”隔開）和資料</param>
public void SetCells(int sheetIndex, string[] arr)
{
int rowIndex;
int columnIndex;
string position;
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
if (arr.Length == 0) return;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
for (int i = 0; i < arr.Length; i++)
{
try
{
position = arr[i];
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
Excel.Range cell = (Excel.Range)workSheet.Cells[rowIndex, columnIndex];
cell.FormulaR1C1 = cell.Text;
}
catch
{
this.KillExcelProcess();
throw new Exception(string.Format("計算單元格{0}出錯！", arr[i]));
}
}
}
/// <summary>
/// 向單元格寫入資料，對指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的鍵值對儲存單元格的位置索引（行索引和列索引用“,”隔開）和資料</param>
public void SetCells(string sheetName, Hashtable ht)
{
int rowIndex;
int columnIndex;
string position;
Excel.Worksheet sheet = null;
int sheetIndex = 0;
if (ht.Count == 0) return;
try
{
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
sheetIndex = workSheet.Index;
}
}
if (sheet != null)
{
foreach (DictionaryEntry dic in ht)
{
try
{
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
sheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch
{
this.KillExcelProcess();
throw new Exception("向單元格[" + dic.Key + "]寫資料出錯！");
}
}
}
else
{
this.KillExcelProcess();
throw new Exception("名稱為/"" + sheetName + "/"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 合并單元格，并指派，對每個WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">開始行索引</param>
/// <param name="beginColumnIndex">開始列索引</param>
/// <param name="endRowIndex">結束行索引</param>
/// <param name="endColumnIndex">結束列索引</param>
/// <param name="text">合并後Range的值</param>
public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不會出錯
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
}
/// <summary>
/// 合并單元格，并指派，對指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">開始行索引</param>
/// <param name="beginColumnIndex">開始列索引</param>
/// <param name="endRowIndex">結束行索引</param>
/// <param name="endColumnIndex">結束列索引</param>
/// <param name="text">合并後Range的值</param>
public void MergeCells(int sheetIndex, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不會出錯
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
#endregion
#region Row Methods
/// <summary>
/// 将指定索引列的資料相同的行合并，對每個WorkSheet操作
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">開始行索引</param>
/// <param name="endRowIndex">結束行索引</param>
public void MergeRows(int columnIndex, int beginRowIndex, int endRowIndex)
{
if (endRowIndex - beginRowIndex < 1)
return;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
for (int j = beginRowIndex; j <= endRowIndex; j++)
{
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //設定開始合并行索引
count = 0; //計數器清0
}
}
}
}
/// <summary>
/// 将指定索引列的資料相同的行合并，對指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">開始行索引</param>
/// <param name="endRowIndex">結束行索引</param>
public void MergeRows(int sheetIndex, int columnIndex, int beginRowIndex, int endRowIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
if (endRowIndex - beginRowIndex < 1)
return;
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
for (int j = beginRowIndex; j <= endRowIndex; j++)
{
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //設定開始合并行索引
count = 0; //計數器清0
}
}
}
/// <summary>
/// 插行（在指定行上面插入指定數量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int rowIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 插行（在指定WorkSheet指定行上面插入指定數量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 複制行（在指定行下面複制指定數量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 複制行（在指定WorkSheet指定行下面複制指定數量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void DeleteRows(int rowIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Delete(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void DeleteRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Delete(Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
#endregion
#region Column Methods
/// <summary>
/// 插列（在指定列右邊插入指定數量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void InsertColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 插列（在指定WorkSheet指定列右邊插入指定數量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void InsertColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 複制列（在指定列右邊複制指定數量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
// range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 複制列（在指定WorkSheet指定列右邊複制指定數量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
// range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++)
{
range.Delete(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++)
{
range.Delete(Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
#endregion
#region Range Methods
/// <summary>
/// 将指定範圍區域拷貝到目标區域
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="startCell">要拷貝區域的開始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷貝區域的結束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标區域的開始Cell位置（比如：H10）</param>
public void RangeCopy(int sheetIndex, string startCell, string endCell, string targetCell)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出範圍，WorkSheet索引不能大于WorkSheet數量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range1 = workSheet.get_Range(startCell, endCell);
range2 = workSheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 将指定範圍區域拷貝到目标區域
/// </summary>
/// <param name="sheetName">WorkSheet名稱</param>
/// <param name="startCell">要拷貝區域的開始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷貝區域的結束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标區域的開始Cell位置（比如：H10）</param>
public void RangeCopy(string sheetName, string startCell, string endCell, string targetCell)
{
try
{
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
}
}
if (sheet != null)
{
for (int i = sheetCount; i >= 1; i--)
{
range1 = sheet.get_Range(startCell, endCell);
range2 = sheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
}
else
{
this.KillExcelProcess();
throw new Exception("名稱為/"" + sheetName + "/"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 自動填充
/// </summary>
public void RangAutoFill()
{
Excel.Range rng = workSheet.get_Range("B4", Type.Missing);
rng.Value2 = "星期一 ";
rng.AutoFill(workSheet.get_Range("B4", "B9"),
Excel.XlAutoFillType.xlFillWeekdays);
rng = workSheet.get_Range("C4", Type.Missing);
rng.Value = "一月";
rng.AutoFill(workSheet.get_Range("C4", "C9"),
Excel.XlAutoFillType.xlFillMonths);
rng = workSheet.get_Range("D4", Type.Missing);
rng.Value2 = "1";
rng.AutoFill(workSheet.get_Range("D4", "D9"),
Excel.XlAutoFillType.xlFillSeries);
rng = workSheet.get_Range("E4", Type.Missing);
rng.Value2 = "3";
rng = workSheet.get_Range("E5", Type.Missing);
rng.Value2 = "6";
rng = workSheet.get_Range("E4", "E5");
rng.AutoFill(workSheet.get_Range("E4", "E9"),
Excel.XlAutoFillType.xlFillSeries);
}
/// <summary>
/// 應用樣式
/// </summary>
public void ApplyStyle()
{
object missingValue = Type.Missing;
Excel.Range rng = workSheet.get_Range("B3", "L23");
Excel.Style style;
try
{
style = workBook.Styles["NewStyle"];
}
// Style doesn't exist yet.
catch
{
style = workBook.Styles.Add("NewStyle", missingValue);
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Color = 255;
style.Interior.Color = (200 << 16) | (200 << 8) | 200;
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
}
rng.Value2 = "'Style Test";
rng.Style = "NewStyle";
rng.Columns.AutoFit();
}
#endregion
#region ExcelHelper Kit
/// <summary>
/// 将Excel列的字母索引值轉換成整數索引值
/// </summary>
/// <param name="letter"></param>
/// <returns></returns>
public int LetterToInt(string letter)
{
int n = 0;
if (letter.Trim().Length == 0)
throw new Exception("不接受空字元串！");
if (letter.Length >= 2)
{
char c1 = letter.ToCharArray(0, 2)[0];
char c2 = letter.ToCharArray(0, 2)[1];
if (!char.IsLetter(c1) || !char.IsLetter(c2))
{
throw new Exception("格式不正确，必須是字母！");
}
c1 = char.ToUpper(c1);
c2 = char.ToUpper(c2);
int i = Convert.ToInt32(c1) - 64;
int j = Convert.ToInt32(c2) - 64;
n = i * 26 + j;
}
if (letter.Length == 1)
{
char c1 = letter.ToCharArray()[0];
if (!char.IsLetter(c1))
{
throw new Exception("格式不正确，必須是字母！");
}
c1 = char.ToUpper(c1);
n = Convert.ToInt32(c1) - 64;
}
if (n > 256)
throw new Exception("索引超出範圍，Excel的列索引不能超過256！");
return n;
}
/// <summary>
/// 将Excel列的整數索引值轉換為字元索引值
/// </summary>
/// <param name="n"></param>
/// <returns></returns>
public string IntToLetter(int n)
{
if (n > 256)
throw new Exception("索引超出範圍，Excel的列索引不能超過256！");
int i = Convert.ToInt32(n / 26);
int j = n % 26;
char c1 = Convert.ToChar(i + 64);
char c2 = Convert.ToChar(j + 64);
if (n > 26)
return c1.ToString() + c2.ToString();
else if (n == 26)
return "Z";
else
return c2.ToString();
}
#endregion
#region Output File(注意：如果目标檔案已存在的話會出錯)
/// <summary>
/// 輸出Excel檔案并退出
/// </summary>
public void OutputExcelFile()
{
if (this.outputFile == null)
throw new Exception("沒有指定輸出檔案路徑！");
try
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
}
/// <summary>
/// 輸出指定格式的檔案（支援格式：HTML，CSV，TEXT，EXCEL）
/// </summary>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void OutputFile(string format)
{
if (this.outputFile == null)
throw new Exception("沒有指定輸出檔案路徑！");
try
{
switch (format)
{
case "HTML":
{
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
case "CSV":
{
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
case "TEXT":
{
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
//
// }
default:
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
}
/// <summary>
/// 儲存檔案
/// </summary>
public void SaveFile()
{
try
{
workBook.Save();
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
}
/// <summary>
/// 另存檔案
/// </summary>
public void SaveAsFile()
{
if (this.outputFile == null)
throw new Exception("沒有指定輸出檔案路徑！");
try
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
}
/// <summary>
/// 将Excel檔案另存為指定格式
/// </summary>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void SaveAsFile(string format)
{
if (this.outputFile == null)
throw new Exception("沒有指定輸出檔案路徑！");
try
{
switch (format)
{
case "HTML":
{
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
case "CSV":
{
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
case "TEXT":
{
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
// }
default:
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
}
/// <summary>
/// 另存檔案
/// </summary>
/// <param name="fileName">檔案名</param>
public void SaveFile(string fileName)
{
try
{
workBook.SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
}
/// <summary>
/// 将Excel檔案另存為指定格式
/// </summary>
/// <param name="fileName">檔案名</param>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void SaveAsFile(string fileName, string format)
{
try
{
switch (format)
{
case "HTML":
{
workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
case "CSV":
{
workBook.SaveAs(fileName, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
case "TEXT":
{
workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(fileName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
// }
default:
{
workBook.SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
}
#endregion
#endregion
#region 私有方法
/// <summary>
/// 合并單元格，并指派，對指定WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">開始行索引</param>
/// <param name="beginColumnIndex">開始列索引</param>
/// <param name="endRowIndex">結束行索引</param>
/// <param name="endColumnIndex">結束列索引</param>
/// <param name="text">合并後Range的值</param>
private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
if (sheet == null)
return;
range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不會出錯
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
/// <summary>
/// 将指定索引列的資料相同的行合并，對指定WorkSheet操作
/// </summary>
/// <param name="columnIndex">要合并的列索引</param>
/// <param name="beginRowIndex">合并開始行索引</param>
/// <param name="rows">要合并的行數</param>
private void MergeRows(Excel.Worksheet sheet, int columnIndex, int beginRowIndex, int rows)
{
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
if (sheet == null)
return;
for (int j = beginRowIndex; j < beginRowIndex + rows; j++)
{
range1 = (Excel.Range)sheet.Cells[j, columnIndex];
range2 = (Excel.Range)sheet.Cells[j + 1, columnIndex];
text1 = range1.Text.ToString();
text2 = range2.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(sheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //設定開始合并行索引
count = 0; //計數器清0
}
}
}
/// <summary>
/// 計算WorkSheet數量
/// </summary>
/// <param name="rowCount">記錄總行數</param>
/// <param name="rows">每WorkSheet行數</param>
public int GetSheetCount(int rowCount, int rows)
{
int n = rowCount % rows; //餘數
if (n == 0)
return rowCount / rows;
else
return Convert.ToInt32(rowCount / rows) + 1;
}
/// <summary>
/// 結束Excel程序
/// </summary>
public void KillExcelProcess()
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel程序ID，暫時隻能判斷程序啟動時間
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
private void Dispose()
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (range1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
if (range2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range2 = null;
}
if (textBox != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
textBox = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
this.KillExcelProcess();
}//end Dispose
#endregion
}//end class
2。類的調用
private void BindData(DataTable dt)
{
repData.DataSource = dt;
repData.DataBind();
}
protected void btnLoadDt_Click(object sender, EventArgs e)
{
BindData(LoadDataTable());
}
//建立DataTable
private DataTable LoadDataTable()
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn("userName");
dt.Columns.Add(dc);
dc = new DataColumn("userSex");
dt.Columns.Add(dc);
DataRow dr = dt.NewRow();
dr[0] = "fengyan";
dr[1] = "male";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "efly";
dr[1] = "male";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "楚旋";
dr[1] = "male";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "51aspx";
dr[1] = "male";
dt.Rows.Add(dr);
return dt;
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
//得到需要導入Excel的DataTable
DataTable dt = LoadDataTable();
//将其列名添加進去！ （這一步注意是為了友善以後将該Excel導入記憶體表中 自動建立列名用。）
//DataRow dr = dt.NewRow();
//dr[0] = "userName";
//dr[1] = "userSex";
//dt.Rows.InsertAt(dr, 0);
//執行個體化一個Excel助手工具類
ExcelHelper ex = new ExcelHelper();
//導入所有！（從第一行第一列開始）
ex.DataTableToExcel(dt, 1, 1);
//導出Excel儲存的路徑！
ex.OutputFilePath = txtExcelPath.Text;
ex.OutputExcelFile();
}
protected void btnExcelToDataTable_Click(object sender, EventArgs e)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFromExcel.Text + ";Extended Properties=Excel 8.0";
//連結Excel
OleDbConnection cnnxls = new OleDbConnection(strConn);
//讀取Excel裡面有 表Sheet1
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet ds = new DataSet();
//将Excel裡面有表内容裝載到記憶體表中！
oda.Fill(ds);
DataTable dt = ds.Tables[0];
BindData(dt);
}
![對于VS中scanf報錯永久解決，建立源檔案自動添加#define _CRT_SECURE_NO_WARNINGS 1[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)