using System;
using System.IO;
using System.Text;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
//using cfg = System.Configuration;
namespace GDBI.Common
{
/// <summary>
/// 说 明：Excel输出打印模块
/// 暂时不提供操作Excel对象样式方法，样式可以在Excel模板中设置好
/// 作 者：lingyun_k
/// 创建日期：2005－7－12
/// </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下面行不通）
/*sb = new StringBuilder();
for(int j=0;j<endRow-startRow;j++)
{
for(int k=0;k<colCount;k++)
{
sb.Append( dt.Rows[startRow + j][k].ToString() );
sb.Append("\t");
}
sb.Append("\n");
}
System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());
range = (Excel.Range)workSheet.Cells[top,left];
workSheet.Paste(range,false);*/
#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,colCount];
for(int j=0;j<rowCount;j++)
{
for(int k=0;k<colCount;k++)
{
arr[j,k] = dt.Rows[j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top,left];
range = range.get_Resize(rowCount,colCount);
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
}//end namespace
分享到：
发表评论
-
ipchange
2009-11-23 21:00 656public string ipChange(u ... -
遍历类的属性
2009-08-12 11:23 735Response.Write(getProperties(l ... -
SqliteHelper
2009-07-27 14:46 1344using System; using Syst ... -
网址收藏
2009-07-23 14:50 696http://www.cnblogs.com/arte ... -
。net常用工具
2009-07-23 14:48 1745ildasm：反编译.net程序； ilasm：编译 ... -
c#生成Excel
2009-07-23 14:45 2538using System;using System.Colle ... -
C#操作Word!
2009-07-23 14:44 3775using System; using System.Com ... -
验证控件网页代码分析2
2009-07-23 14:43 935还有一个子程序是更新整个页面的验证状态： //更新整个页面 ... -
验证控件网页代码分析
2009-07-23 14:38 1527如果我们在文本框中什么都没有输入，直接点击按钮，则激发Butt ... -
ASP.Net 向xml文件中添加数据(insert操作)
2009-07-23 14:33 1004try { ... -
xml操作
2009-07-23 14:32 695using System; using System.Xml ... -
ip地址转换
2009-07-23 14:30 1101public String getIp(long ip10) ... -
BASE64加密解密
2009-07-23 14:28 786string base64EncodeChars = & ... -
winform Splash加载窗口
2009-07-23 14:28 1867static ApplicationContext conte ... -
C#实现快捷键(系统热键)响应
2009-07-23 14:27 1879C#实现快捷键(系统热键)响应 在应用中，我们可能会需要 ... -
WinForm的一些常用技巧
2009-07-23 14:22 2176在任务栏不显示 将WinForm的ShowInT ... -
去除文本中的标签
2009-07-23 14:10 734/// <summary> / ... -
C#验证码
2009-07-23 14:03 1239using System; using System.Dra ... -
des和RSA两种加密方式的例子
2009-07-23 13:50 1306static public string RSA_Encry ...
相关推荐
C# excel类C# excel类C# excel类C# excel类C# excel类
C#excel转图片(亲自测试过可以用)，学习使用
c# Excel 操作类，可实现EXCEL的大部分操作，并且可以把数据库查询出的结果datatable直接导到EXCEL里面，注释详细！ /// /// 将内存中数据表格插入到Excel指定工作表的指定位置 /// /// 数据表 /// 工作表...
C# Excel 导入到Access数据库
c#ExceL导入支持2003和2007两种导入，里面有说明和案例，还有遇到问题的解决方法。
C#Excel大量数据快速导入数据库.
C# Excel导入导出多sheet页
C# 编写的操作Excel读取/写入动态库(COM/NPOI两种方式)，个人推荐使用NPOI方式，因为COM方式存在无法关闭Excel进程的情况(个人体验COM方式效率有点低)。。。已设置0分，资源自取。
C# Excel 导入 DataGridView
本文先将Excel文件转换成DataTable，然后再循环将记录插入到数据库表中，这种方式可以任由程序员来选择将哪列数据导入到数据表的哪个字段中
C# excel自动添加数据和绘图，excel界面可见，亲测可用
C#Excel.dll引用
C# excel数据导入 DataGridView
最近在做个小东西的时候用到的，网上有很多但大多是文档，所以自己照着改了改，开发环境:vs2013 excel版本：office excel 2013，可能写的比较糙，有什么有问题的地方希望能够指出来，共同进步。
C# EXCEL导入导出类 /// /// 导入EXCEL到DataSet /// /// <param name="fileName">Excel全路径文件名 /// 导入成功的DataSet public DataSet ImportExcel(string fileName) /// /// 把DataTable导出...
C#写的Excel操作类示例，希望对大家学习有帮助
C# Excel表格数据的导出和导入 VS2010版
c# excel采用三种方式上传数据 c# excel采用三种方式上传数据
C# 操作 Excel 导入导出,很方便的.
这份源码包括了基本的Office Excel编程，通过C#语言提供的强大类库，可以在程序中直接操作Excel里面的表格和数据，对刚学Excel编程的人将有不少的帮助！