using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Data;
using System.Diagnostics;
using System.IO;
using Aspose.Cells;
namespace DBHelper
{
public class ExcelHelper : IDisposable
{
private string templetFile = String.Empty;
private string outputFile = String.Empty;
private DateTime beforeTime; //Excel启动之前时间
private DateTime afterTime; //Excel启动之后时间
Workbook workBook;
public Worksheet workSheet;
Range range;
private int sheetCount = 1; //WorkSheet数量
private string sheetPrefixName = "Sheet";
#region 公共属性
public string SheetPrefixName
{
set { this.sheetPrefixName = value; }
}
public int WorkSheetCount
{
get { return workBook.Worksheets.Count; }//.Sheets.Count; }
}
public string OutputFilePath
{
set { this.outputFile = value; }
}
#endregion
#region CreateExcelFile
/// <summary>
/// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径
/// </summary>
/// <param name="templetFilePath">Excel模板文件路径</param>
/// <param name="outputFilePath">输出Excel文件路径</param>
public ExcelHelper(string templetFilePath, string outputFilePath)
{
if (templetFilePath == String.Empty)
throw new Exception("Excel模板文件路径不能为空!");
if (outputFilePath == String.Empty)
throw new Exception("输出Excel文件路径不能为空!");
if (!File.Exists(templetFilePath))
throw new Exception("指定路径的Excel模板文件不存在!");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
afterTime = DateTime.Now;
//打开模板文件,得到WorkBook对象
//workBook = new Workbook(templetFilePath, new LoadOptions(LoadFormat.Unknown));
workBook = new Workbook(templetFilePath, new LoadOptions());
workSheet = workBook.Worksheets[0];
}
/// <summary>
/// 构造函数,打开一个已有的工作簿
/// </summary>
/// <param name="fileName">Excel文件名</param>
public ExcelHelper(string fileName)
{
if (!File.Exists(fileName))
throw new Exception("指定路径的Excel文件不存在!");
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
afterTime = DateTime.Now;
//打开一个WorkBook
workBook = new Workbook(fileName, new LoadOptions());
workSheet = workBook.Worksheets[0];
}
/// <summary>
/// 构造函数,新建一个工作簿
/// </summary>
public ExcelHelper()
{
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
afterTime = DateTime.Now;
workBook = new Workbook();
workSheet = workBook.Worksheets[0];
}
#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 = workBook.Worksheets[sheetIndex];//(Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
}
/// <summary>
/// 隐藏指定名称的工作表
/// </summary>
/// <param name="sheetName">工作表名称</param>
public void HiddenWorkSheet(string sheetName)
{
try
{
Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Worksheet)workBook.Worksheets[i];//.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
sheet = workSheet;
}
if (sheet != null)
sheet.VisibilityType = VisibilityType.Hidden;//.Visible = Excel.XlSheetVisibility.xlSheetHidden;
else
{
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 隐藏指定索引的工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void HiddenWorkSheet(int sheetIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!");
}
try
{
Worksheet sheet = null;
sheet = (Worksheet)workBook.Worksheets[sheetIndex];//.Sheets.get_Item(sheetIndex);
sheet.VisibilityType = VisibilityType.Hidden;// Excel.XlSheetVisibility.xlSheetHidden;
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 在指定名称的工作表后面拷贝指定个数的该工作表的副本,并重命名
/// </summary>
/// <param name="sheetName">复制源【工作表名称】</param>
/// <param name="sheetCount">工作表个数</param>
public void CopyWorkSheets(string sheetName, int sheetCount)
{
try
{
Worksheet sheet = null;
int sheetIndex = 0;
if (!string.IsNullOrWhiteSpace(sheetName))
{
for (int i = 0; i < this.WorkSheetCount; i++)
{
workSheet = workBook.Worksheets[i];//.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
sheetIndex = workSheet.Index;
}
}
}
else
{
sheet = workSheet;
}
if (sheet != null)
{
int countWorkSheet = WorkSheetCount + sheetCount;
for (int i = WorkSheetCount; i < countWorkSheet; i++)
{
string name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
Worksheet sheetTMP = workBook.Worksheets.Add(name);
sheetTMP.Copy(sheet);
}
}
else
{
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e)
{
throw e;
}
}
public string lastErrorMSG = "";
/// <summary>
/// 将一个工作表拷贝到另一个工作表后面
/// </summary>
/// <param name="srcSheetIndex">拷贝源工作表索引</param>
/// <param name="newSheetName">新工作表名称</param>
/// <returns></returns>
public bool CopyWorkSheet(int srcSheetIndex, string newSheetName)
{
if (srcSheetIndex > this.WorkSheetCount || srcSheetIndex < 0)
{
lastErrorMSG = "索引位置错误";
return false;
}
try
{
foreach (Worksheet sheet in workBook.Worksheets)
{
if (sheet.Name == newSheetName)
{
lastErrorMSG = "工作表中已经存在该名称,请选择其他名称";
return false;
}
}
Worksheet srcSheet = (Worksheet)workBook.Worksheets[srcSheetIndex];//.Sheets.get_Item(srcSheetIndex);
Worksheet aimSheet = (Worksheet)workBook.Worksheets.Add(newSheetName);//[aimSheetIndex];//Sheets.get_Item(aimSheetIndex);
srcSheet.Copy(aimSheet);//(this.missing, aimSheet);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
}
return false;
}
/// <summary>
/// 根据名称删除工作表
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <returns></returns>
public bool DeleteWorkSheet(string sheetName)
{
try
{
Worksheet sheet = null;
bool isHave = false;
//找到名称位sheetName的工作表
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Worksheet)workBook.Worksheets[i];//.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
isHave = true;
}
}
if (!isHave)
{
return true;
}
if (sheet != null)
{
workBook.Worksheets.RemoveAt(sheetName);
return true;
}
else
{
return false;
}
}
catch (Exception e)
{
lastErrorMSG = e.Message;
}
return false;
}
/// <summary>
/// 根据索引删除工作表
/// </summary>
/// <param name="sheetIndex">工作薄索引</param>
/// <returns></returns>
public bool DeleteWorkSheet(int sheetIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
try
{
workBook.Worksheets.RemoveAt(sheetIndex);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
#endregion
/// <summary>
/// 将指定索引列的数据相同的行合并,对每个WorkSheet操作
/// </summary>
/// <param name="rowIndex">开始列索引</param>
/// <param name="columnIndex">开始行索引</param>
/// <param name="count">跨行个数</param>
/// <returns></returns>
public bool MergeRows(int rowIndex, int columnIndex, int count)
{
if (workSheet != null)
{
Cells cells = workSheet.Cells;
cells.Merge(rowIndex, columnIndex, 1, count);//(rowIndex, columnIndex, count, 1);//, 1, endRowIndex - columnIndex);
return true;
}
return false;
}
/// <summary>
/// 合并行
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="count">合并行数</param>
/// <returns></returns>
public bool MergeRows(int sheetIndex, int rowIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
MergeRows(rowIndex, columnIndex, count);
return true;
}
/// <summary>
/// 合并列
/// </summary>
/// <param name="rowIndex">开始行索引</param>
/// <param name="columnIndex">开始列索引</param>
/// <param name="count">合并列数</param>
/// <returns></returns>
public bool MergeColumns(int rowIndex, int columnIndex, int count)
{
if (workSheet != null)
{
Cells cells = workSheet.Cells;
cells.Merge(rowIndex, columnIndex, count, 1);
}
return false;
}
/// <summary>
/// 合并列
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="rowIndex">开始行索引</param>
/// <param name="columnIndex">开始列索引</param>
/// <param name="count">合并列数</param>
/// <returns></returns>
public bool MergeColumns(int sheetIndex, int rowIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return MergeColumns(rowIndex, columnIndex, count);
// true;
}
/// <summary>
///
/// </summary>
/// <param name="rowIndex">开始行索引</param>
/// <param name="columnIndex">开始列索引</param>
/// <param name="countRow">合并行数</param>
/// <param name="countColumn">合并列数</param>
/// <returns></returns>
public bool MergeRowsAndColumns(int rowIndex, int columnIndex, int countRow, int countColumn)
{
if (workSheet == null)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
if (countRow < 1 || countColumn < 1)
{
lastErrorMSG = "合并行和列数目设置有错";
return false;
}
Cells cells = workSheet.Cells;
cells.Merge(rowIndex, columnIndex, countRow, countColumn); //count, 1);
return true;
}
/// <summary>
/// 合并行和列
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="rowIndex">开始行索引</param>
/// <param name="columnIndex">开始列索引</param>
/// <param name="countRow">合并行数</param>
/// <param name="countColumn">合并列数</param>
/// <returns></returns>
public bool MergeRowsAndColumns(int sheetIndex, int rowIndex, int columnIndex, int countRow, int countColumn)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return MergeRowsAndColumns(rowIndex, columnIndex, countRow, countColumn);
}
/// <summary>
/// 插行(在指定行下面插入指定数量行)
/// </summary>
/// <param name="rowIndex">行号索引从0开始</param>
/// <param name="count">插入行数</param>
/// <returns></returns>
public bool InsertRows(int rowIndex, int count)
{
try
{
rowIndex = rowIndex + 1;
if (rowIndex < 0 || count < 0)
{
lastErrorMSG = "行号或行数错误";
return false;
}
workSheet.Cells.InsertRows(rowIndex, count);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 插行(在指定行下面插入指定数量行)
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
/// <returns></returns>
public bool InsertRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
try
{
workSheet = workBook.Worksheets[sheetIndex];
return InsertRows(rowIndex, count);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 复制行
/// </summary>
/// <param name="sourceRowIndex">源行号索引</param>
/// <param name="targetRowIndex">目标行索引</param>
public bool CopyRow(int sourceRowIndex, int targetRowIndex)
{
if (sourceRowIndex < 0 || (targetRowIndex) < 0)
{
lastErrorMSG = "源行索引或目标行索引错误";
return false;
}
Cells cells = workSheet.Cells;
workSheet.Cells.CopyRow(cells, sourceRowIndex, targetRowIndex);
return true;
}
/// <summary>
/// 复制多行
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="rowstartIndex">开始行索引</param>
/// <param name="rowCount">(从开始行索引 )行数</param>
/// <param name="targetRowIndex">目标行索引</param>
/// <returns></returns>
public bool CopyRows(int sheetIndex, int rowstartIndex, int rowCount, int targetRowIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
try
{
Cells cells = workSheet.Cells;
workSheet.Cells.CopyRows(cells, rowstartIndex, targetRowIndex, rowCount);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <returns></returns>
public bool DeleteRow(int rowIndex)
{
try
{
workSheet.Cells.DeleteRow(rowIndex);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
}
return false;
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="rowIndex">开始行索引</param>
/// <returns></returns>
public bool DeleteRow(int sheetIndex, int rowIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
try
{
workSheet = workBook.Worksheets[sheetIndex];
workSheet.Cells.DeleteRow(rowIndex);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
}
return false;
}
/// <summary>
/// 删除多行
/// </summary>
/// <param name="rowIndex">开始行索引</param>
/// <param name="count">行数</param>
/// <returns></returns>
public bool DeleteRows(int rowIndex, int count)
{
try
{
workSheet.Cells.DeleteRows(rowIndex, count);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
}
return false;
}
/// <summary>
/// 删除多行
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="rowIndex">开始行索引</param>
/// <param name="count">行数</param>
/// <returns></returns>
public bool DeleteRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
try
{
workSheet = workBook.Worksheets[sheetIndex];
return DeleteRows(rowIndex, count);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
}
return false;
}
/// <summary>
/// 插列 列的左侧
/// </summary>
/// <param name="columnIndex">插入索引</param>
/// <returns></returns>
public bool InsertColumn(int columnIndex)
{
try
{
workSheet.Cells.InsertColumn(columnIndex);//.DeleteColumn(columnIndex);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 插列 列的左侧
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="columnIndex">列索引</param>
/// <returns></returns>
public bool InsertColumn(int sheetIndex, int columnIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
try
{
workSheet.Cells.InsertColumn(columnIndex);//.DeleteColumn(columnIndex);
return InsertColumn(columnIndex);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 插列 列的左侧
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <param name="count">插入列数</param>
/// <returns></returns>
public bool InsertColumns(int columnIndex, int count)
{
try
{
workSheet.Cells.InsertColumns(columnIndex, count);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 插列 列的左侧
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="count">列个数</param>
/// <returns></returns>
public bool InsertColumns(int sheetIndex, int columnIndex, int count)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
//workSheet.Cells.InsertColumns(columnIndex, count);
return InsertColumns(columnIndex, count);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 复制列
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public bool CopyColumn(int columnIndex, int columnTargetIndex)
{
try
{
Cells cells = workSheet.Cells;
workSheet.Cells.CopyColumn(cells, columnIndex, columnTargetIndex);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 复制列
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="columnTargetIndex">复制目标列索引</param>
/// <returns></returns>
public bool CopyColumn(int sheetIndex, int columnIndex, int columnTargetIndex)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
return CopyColumn(columnIndex, columnTargetIndex);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 复制多列
/// </summary>
/// <param name="startIndex">列索引</param>
/// <param name="count">列数目</param>
/// <param name="targetIndex">目标索引</param>
/// <returns></returns>
public bool CopyColumns(int startIndex, int count, int targetIndex)
{
try
{
Cells cells = workSheet.Cells;
workSheet.Cells.CopyColumns(cells, startIndex, targetIndex, count);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 复制多列
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="startIndex">源列索引</param>
/// <param name="count">列数目(源列向后的列)</param>
/// <param name="targetIndex">目标列索引</param>
/// <returns></returns>
public bool CopyColumns(int sheetIndex, int startIndex, int count, int targetIndex)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return CopyColumns(startIndex, count, targetIndex);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <returns></returns>
public bool DeleteColumn(int columnIndex)
{
try
{
workSheet.Cells.DeleteColumn(columnIndex);
return true;
}
catch (Exception excelHelper)
{
lastErrorMSG = excelHelper.Message;
return false;
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="columnIndex">列索引</param>
/// <returns></returns>
public bool DeleteColumn(int sheetIndex, int columnIndex)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return DeleteColumn(columnIndex);
}
catch (Exception ex)
{
lastErrorMSG = ex.Message;
return false;
}
}
/// <summary>
/// 删除多列
/// </summary>
/// <param name="startIndex">列</param>
/// <param name="count">数目</param>
/// <returns></returns>
public bool DeleteColumns(int startIndex, int count)
{
try
{
workSheet.Cells.DeleteColumns(startIndex, count, false);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 删除多列
/// </summary>
/// <param name="sheetIndex">单元薄</param>
/// <param name="startIndex">列索引</param>
/// <param name="count">数目</param>
/// <returns></returns>
public bool DeleteColumns(int sheetIndex, int startIndex, int count)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return DeleteColumns(startIndex, count);
}
catch (Exception ex)
{
lastErrorMSG = ex.Message;
return false;
}
}
/// <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();
}
/// <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>
/// <param name="fileName">文件名</param>
/// <param name="format"></param>
/// <returns></returns>
public bool SaveAsFile(string fileName, SaveFormat format)
{
try
{
workBook.Save(fileName, format);
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 保存Excel
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public bool SaveAsFile(string fileName)
{
try
{
return SaveAsFile(fileName, SaveFormat.Xlsx);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
//throw;
}
}
public bool SaveAsFile()
{
if (!string.IsNullOrWhiteSpace(outputFile))
{
return SaveAsFile(outputFile);
}
else
{
return false;
}
}
/// <summary>
/// 将DataTable数据写入Excel文件(不分页)
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(DataTable dt, int rowIndex, int columnIndex)
{
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
for (int i = 0; i < dt.Columns.Count; i++)
{
workSheet.Cells[rowIndex, columnIndex + i].PutValue(dt.Columns[i].ColumnName);
// workSheet.Cells[top, left + i] = "'" + dt.Columns[i].ColumnName;
}
for (int j = 1; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
workSheet.Cells[j + rowIndex, k + columnIndex].PutValue(dt.Rows[j][k].ToString());
}
}
}
/// <summary>
/// 将DataTable数据写入Excel文件(自动分页)
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="count">每个WorkSheet写入多少行数据</param>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
public void DataTableToExcel(DataTable dt, int count, int rowIndex, int columnIndex)
{
int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = dt.Columns.Count; //源DataTable列数
sheetCount = this.GetSheetCount(rowCount, count); //WorkSheet个数
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Worksheet)workBook.Worksheets[0];//.get_Item(i);
string name = sheetPrefixName + "-" + i.ToString();
Worksheet sheetTMP = workBook.Worksheets.Add(name);
sheetTMP.Copy(workSheet);
}
int dataIndex = 0;
for (int i = 0; i < sheetCount; i++)
{
int startRow = i * count + rowIndex; //记录起始行索引
int endRow = (i + 1) * count + rowIndex; //记录结束行索引
int row = endRow - startRow;
workSheet = workBook.Worksheets[i];
//将dt中的数据写入WorkSheet
//for (int j = 0; j < dt.Columns.Count; j++)
//{
// workSheet.Cells[rowIndex, columnIndex + j].PutValue(dt.Columns[j].ColumnName);
//}
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
if (dataIndex < dt.Rows.Count)
{
workSheet.Cells[j + rowIndex + 1, k + columnIndex].PutValue(dt.Rows[dataIndex][k].ToString());
}
}
dataIndex++;
}
}
}
/// <summary>
/// 设置单元格值
/// </summary>
/// <param name="rowNum"></param>
/// <param name="colNum"></param>
/// <param name="value"></param>
public void SetCellValue(int rowIndex, int colIndex, string value)
{
workSheet.Cells[rowIndex, colIndex].PutValue(value);
}
/// <summary>
/// 设置单元薄为字符串
/// </summary>
public void SetSheetFormatString()
{
workSheet.Cells.PreserveString = true;
}
/// <summary>
/// 将二维数组数据写入Excel文件(不分页)
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <returns></returns>
public bool ArrayToExcel(string[,] arr, int rowIndex, int columnIndex)
{
int rowCount = arr.GetLength(0); //二维数组行数(一维长度)
int colCount = arr.GetLength(1); //二维数据列数(二维长度)
if (rowCount <= 0 || colCount <= 0)
{
lastErrorMSG = "二位数组长度设置有错";
return false;
}
try
{
for (int i = 0; i < rowCount; i++)//行
{
for (int k = 0; k < colCount; k++)//列
{
workSheet.Cells[rowIndex + i, columnIndex + k].PutValue(arr[i, k]);
}
}
return true;
}
catch (Exception ex)
{
lastErrorMSG = ex.Message;
return false;
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件(不分页)
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <returns></returns>
public bool ArrayObjectToExcel(object[,] arr, int rowIndex, int columnIndex)
{
int rowCount = arr.GetLength(0); //二维数组行数(一维长度)
int colCount = arr.GetLength(1); //二维数据列数(二维长度)
if (rowCount <= 0 || colCount <= 0)
{
lastErrorMSG = "二位数组长度设置有错";
return false;
}
try
{
for (int i = 0; i < rowCount; i++)//行
{
for (int k = 0; k < colCount; k++)//列
{
workSheet.Cells[rowIndex + i, columnIndex + k].PutValue(arr[i, k]);
}
}
return true;
}
catch (Exception ex)
{
lastErrorMSG = ex.Message;
return false;
}
}
/// <summary>
/// 将二维数组数据写入Excel文件(分页)
/// </summary>
/// <param name="arr">二位数组</param>
/// <param name="rowNumber">每页显示行数</param>
/// <param name="rowIndex">开始行索引</param>
/// <param name="columnIndex">开始列索引</param>
/// <returns></returns>
public bool ArrayToExcel(string[,] arr, int rowNumber, int rowIndex, int columnIndex)
{
int rowCount = arr.GetLength(0); //二维数组行数(一维长度)
int colCount = arr.GetLength(1); //二维数据列数(二维长度)
if (rowCount <= 0 || colCount <= 0)
{
lastErrorMSG = "二位数组错误";
return false;
}
sheetCount = this.GetSheetCount(rowCount, rowNumber); //WorkSheet个数
try
{
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Worksheet)workBook.Worksheets[0];//.get_Item(i);
string name = sheetPrefixName + "-" + i.ToString();
Worksheet sheetTMP = workBook.Worksheets.Add(name);
sheetTMP.Copy(workSheet);
}
int dataRow = 0;
//将二维数组数据写入Excel
for (int i = 0; i < sheetCount; i++)
{
workSheet = workBook.Worksheets[i];
for (int j = 0; j < rowNumber; j++)
{
for (int k = 0; k < colCount; k++)
{
if (dataRow < rowCount)
{
workSheet.Cells[j + rowIndex, k + columnIndex].PutValue(arr[dataRow, k]);
}
}
dataRow++;
}
}
return true;
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件(不分页)
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="arr">二维数组</param>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <returns></returns>
public bool ArrayToExcel(int sheetIndex, string[,] arr, int rowIndex, int columnIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
}
int rowCount = arr.GetLength(0); //二维数组行数(一维长度)
int colCount = arr.GetLength(1); //二维数据列数(二维长度)
if (rowCount <= 0 || colCount <= 0)
{
return false;
}
// 改变当前工作表
this.workSheet = (Worksheet)this.workBook.Worksheets[sheetIndex];
return ArrayToExcel(arr, rowIndex, columnIndex);
}//end ArrayToExcel
public void Dispose()
{
}
/// <summary>
/// 设置文字水平对齐方式
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetCellHAlignment(int rowIndex, int columnIndex, HorizontalAlignment sy)
{
Style styleTitle = workBook.Styles[workBook.Styles.Add()];
styleTitle.HorizontalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置垂直对齐方式
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="sy"></param>
/// <returns></returns>
public bool SetCellVAlignment(int rowIndex, int columnIndex, VerticalAlignment sy)
{
Style styleTitle = workBook.Styles[workBook.Styles.Add()];
styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置行的垂直对齐方式
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="sy"></param>
/// <returns></returns>
public bool SetRowVAlignment(int rowIndex, VerticalAlignment sy)
{
Style styleTitle = workBook.Styles[workBook.Styles.Add()];
styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置行的垂直对齐方式
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="rowIndex">行索引</param>
/// <param name="sy"></param>
/// <returns></returns>
public bool SetRowVAlignment(int sheetIndex, int rowIndex, VerticalAlignment sy)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return SetRowVAlignment(rowIndex, sy);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 设置列的充值对其方式
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <param name="sy"></param>
/// <returns></returns>
public bool SetColumnVAlignment(int columnIndex, VerticalAlignment sy)
{
Cells cells = workSheet.Cells;
Style styleTitle = workBook.Styles[workBook.Styles.Add()];
styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置文字水平对齐方式
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetCellHAlignment(int sheetIndex, int rowIndex, int columnIndex, HorizontalAlignment sy)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return SetCellHAlignment(rowIndex, columnIndex, sy);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 设置垂直对齐方式
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="sy"></param>
/// <returns></returns>
public bool SetCellVAlignment(int sheetIndex, int rowIndex, int columnIndex, VerticalAlignment sy)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return SetCellVAlignment(rowIndex, columnIndex, sy);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 设置行的S水平对齐方式
/// </summary>
/// <param name="sheetIndex">单元薄索引</param>
/// <param name="rowIndex">行索引</param>
/// <param name="sy"></param>
/// <returns></returns>
public bool SetRowHAlignment(int sheetIndex, int rowIndex, HorizontalAlignment sy)
{
try
{
if (sheetIndex > this.WorkSheetCount)
{
lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
return false;
}
workSheet = workBook.Worksheets[sheetIndex];
return SetRowHAlignment(rowIndex, sy);
}
catch (Exception e)
{
lastErrorMSG = e.Message;
return false;
}
}
/// <summary>
/// 设置行的水平对齐方式
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="sy"></param>
/// <returns></returns>
public bool SetRowHAlignment(int rowIndex, HorizontalAlignment sy)
{
Cells cells = workSheet.Cells;
Style styleTitle = workBook.Styles[workBook.Styles.Add()];
styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置列的水平对其方式
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <param name="sy"></param>
/// <returns></returns>
public bool SetColumnHAlignment(int columnIndex, VerticalAlignment sy)
{
Cells cells = workSheet.Cells;
Style styleTitle = workBook.Styles[workBook.Styles.Add()];
styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置单元格字体颜色
/// </summary>
/// <param name="color"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetCellFontColor(System.Drawing.Color color, int rowIndex, int columnIndex)
{
Style styleTitle = workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.Color = color;//.VerticalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置行的字体颜色
/// </summary>
/// <param name="color"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public bool SetRowFontColor(System.Drawing.Color color, int rowIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.Color = color;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置列的字体颜色
/// </summary>
/// <param name="color"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetColumnFontColor(System.Drawing.Color color, int columnIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.Color = color;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置字体名称
/// </summary>
/// <param name="name"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetCellFontName(string name, int rowIndex, int columnIndex)
{
Style styleTitle = GetStyle;
styleTitle.Font.Name = name;//.VerticalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置行字体名称
/// </summary>
/// <param name="name"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public bool SetRowFontName(string name, int rowIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.Name = name;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置列字体名称
/// </summary>
/// <param name="name"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetColumnFontName(string name, int columnIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.Name = name;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置字体大小
/// </summary>
/// <param name="size"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetCellFontSize(int size, int rowIndex, int columnIndex)
{
Style styleTitle = GetStyle;
styleTitle.Font.Size = size;//.VerticalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置行字体大小
/// </summary>
/// <param name="size"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public bool SetRowFontSize(int size, int rowIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.Size = size;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置列的字体大小
/// </summary>
/// <param name="size"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetColumnFontSize(int size, int columnIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.Size = size;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 字体是否加粗
/// </summary>
/// <param name="iSBold"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetCellFontIsBold(bool iSBold, int rowIndex, int columnIndex)
{
Style styleTitle = GetStyle;
styleTitle.Font.IsBold = iSBold;//.VerticalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置字体行是否加租
/// </summary>
/// <param name="iSBold"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public bool SetRowFontIsBold(bool iSBold, int rowIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.IsBold = iSBold;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置字体列是否加粗
/// </summary>
/// <param name="iSBold"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetColumnFontIsBold(bool iSBold, int columnIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.IsBold = iSBold;
StyleFlag styleFlag = flag;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
public StyleFlag flag
{
get
{
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
return styleFlag;
}
}
/// <summary>
/// 字体斜体
/// </summary>
/// <param name="iSItalic"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetCellIsItalic(bool iSItalic, int rowIndex, int columnIndex)
{
Style styleTitle = GetStyle;
styleTitle.Font.IsItalic = iSItalic;//.VerticalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置行 字体斜体
/// </summary>
/// <param name="iSItalic"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public bool SetRowIsItalic(bool iSItalic, int rowIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.IsItalic = iSItalic;
StyleFlag styleFlag = flag;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置列 字体是否为斜体
/// </summary>
/// <param name="iSItalic"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetColumnIsItali(bool iSItalic, int columnIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.IsItalic = iSItalic;
StyleFlag styleFlag = flag;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置删除线
/// </summary>
/// <param name="iStrike"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetCellIsStrikeOut(bool iStrike, int rowIndex, int columnIndex)
{
Style styleTitle = GetStyle;
styleTitle.Font.IsStrikeout = iStrike;//.VerticalAlignment = (TextAlignmentType)(int)sy;
Cells cells = workSheet.Cells;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置行是否删除线
/// </summary>
/// <param name="iStrike"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public bool SetRowIsStrikeOut(bool iStrike, int rowIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.IsStrikeout = iStrike;
StyleFlag styleFlag = flag;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置列是否删除线
/// </summary>
/// <param name="iStrike"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public bool SetColumnIsStrikeOut(bool iStrike, int columnIndex)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
styleTitle.Font.IsStrikeout = iStrike;
StyleFlag styleFlag = flag;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置边框是否实线
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="left">左侧是否为实线</param>
/// <param name="right">右侧是否为实线</param>
/// <param name="top">上部是否为实线</param>
/// <param name="bottom">下部是否为实线</param>
/// <returns></returns>
public bool SetCellBorder(int rowIndex, int columnIndex, bool left = false, bool right = false, bool top = false, bool bottom = false)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;
StyleFlag styleFlag = flag;
styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin;
cells[rowIndex, columnIndex].SetStyle(styleTitle);
return true;
}
/// <summary>
/// 设置行边框是否为实线
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="top"></param>
/// <param name="bottom"></param>
/// <returns></returns>
public bool SetRowBorder(int rowIndex, bool left = false, bool right = false, bool top = false, bool bottom = false)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;
StyleFlag styleFlag = flag;
styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置列的边框是否为实线
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="top"></param>
/// <param name="bottom"></param>
/// <returns></returns>
public bool SetColumnBorder(int columnIndex, bool left = false, bool right = false, bool top = false, bool bottom = false)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;
StyleFlag styleFlag = flag;
styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin;
styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置单元格背景色 为纯色
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="color"></param>
/// <returns></returns>
public bool SetCellBackColor(int rowIndex, int columnIndex, System.Drawing.Color color)
{
var s = workSheet.Cells[rowIndex, columnIndex].GetStyle();
s.ForegroundColor = color;
s.Pattern = BackgroundType.Solid;
workSheet.Cells[rowIndex, columnIndex].SetStyle(s);
return true;
}
/// <summary>
/// 设置行的背景色 为纯色
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="color"></param>
/// <returns></returns>
public bool SetRowBackColor(int rowIndex, System.Drawing.Color color)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;
styleTitle.ForegroundColor = color;
styleTitle.Pattern = BackgroundType.Solid;
StyleFlag styleFlag = flag;
cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
/// <summary>
/// 设置列的背景色 为纯色
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="color"></param>
/// <returns></returns>
public bool SetColumnBackColor(int columnIndex, System.Drawing.Color color)
{
Cells cells = workSheet.Cells;
Style styleTitle = GetStyle;
styleTitle.ForegroundColor = color;
styleTitle.Pattern = BackgroundType.Solid;
StyleFlag styleFlag = flag;
cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
return true;
}
public Style GetStyle
{
get
{
return workBook.Styles[workBook.Styles.Add()];
}
}
/// <summary>
/// DataTable 导出至模板excel中
/// </summary>
/// <param name="table">数据源</param>
/// <param name="dataRowNum">每页数据的行数(无表头表尾,只是数据)</param>
/// <param name="pageRowNum">每页总行数(加上表头与表尾)</param>
/// <param name="firstTitleRowNum">首页标题的行数(表头)</param>
/// <param name="pageTiletRowNum">表头+表尾行数(分页时中间的行数就是第一页表尾+第二页表头)</param>
/// <param name="columnNum">要输出的列数(前几列)</param>
/// <param name="MergeColumns">合并列例如(2,3合并,输入3即可,如果多行:2,3,4合并,则输入3,4)</param>'
/// <param name="pageColumnIndex">分页列,例如以班组分页就写班组所在的列id[必须以该列进行排序]</param>
/// <returns></returns>
public List<ExcelInfo> TableToExcel(DataTable table, int dataRowNum, int pageRowNum, int firstTitleRowNum, int pageTiletRowNum, int columnNum, int[] MergeColumns, int pageColumnIndex)
{
try
{
List<ExcelInfo> listBanzu = null;
//分页标识(例如 班组不同分页)
string pageflag = string.Empty;
if (pageColumnIndex > 0)
{
pageflag = table.Rows[0][pageColumnIndex].ToString();
}
//必须先分页.
//总页数
int pagecount = 1;
//分页行数
int fenge = firstTitleRowNum;
int fenye = -1;
for (int i = 0; i < table.Rows.Count; i++)
{
if (fenye == -1)
fenye = i;
//正常分页
if (fenye > 0 && fenye % dataRowNum == 0)
{
CopyRows(0, 0, pageRowNum, pageRowNum * pagecount);
pagecount++;
fenye = 0;
}
//遇到分页组分页
if (table.Rows[i][pageColumnIndex].ToString() != pageflag)
{
pageflag = table.Rows[i][pageColumnIndex].ToString();
CopyRows(0, 0, pageRowNum, pageRowNum * pagecount);
pagecount++;
fenye = 0;
}
fenye++;
}
if (pageColumnIndex > 0)
{
listBanzu = new List<ExcelInfo>();
pageflag = table.Rows[0][pageColumnIndex].ToString();
}
fenye = -1;
int id = 1;
//3.插入数据 每一页
for (int i = 0; i < table.Rows.Count; i++)
{
if (fenye == -1)
fenye = i;
//正常分页
if (fenye > 0 && fenye % dataRowNum == 0)
{
if (listBanzu != null)
listBanzu.Add(new ExcelInfo() { pageID = id, banzu = pageflag });
fenge += pageTiletRowNum;
fenye = 0;
id++;
}
//遇到分页组分页
if (table.Rows[i][pageColumnIndex].ToString() != pageflag)
{
if (listBanzu != null)
listBanzu.Add(new ExcelInfo() { pageID = id, banzu = pageflag });
pageflag = table.Rows[i][pageColumnIndex].ToString();
fenge += dataRowNum - fenye + pageTiletRowNum; //每页行数-已有行数+表头表尾
fenye = 0;
id++;
}
//合并行
int col = -1;
for (int t = 0; t < columnNum; t++)
{
if (col == -1)
{
col = t;
}
if (MergeColumns != null)
{
for (int m = 0; m < MergeColumns.Count(); m++)
{
if (t == MergeColumns[m])
{
col++;
}
}
}
SetCellValue(i + fenge, col, table.Rows[i][t].ToString());
col++;
}
fenye++;
}
if (listBanzu.Count < pagecount) {
//增加最后一页
listBanzu.Add(new ExcelInfo() { pageID = pagecount, banzu = pageflag });
}
#region
//int total = table.Rows.Count;
//int count = total / dataRowNum;
//if (total % dataRowNum > 0)
//{
// count += 1;
//}
2.分页复制模板
//for (int i = 0; i < count; i++)
//{
// CopyRows(0, 0, pageRowNum, i * pageRowNum);
//}
//int fenge = firstTitleRowNum;
//if (table.Columns.Count < columnNum)
//{
// columnNum = table.Columns.Count;
//}
3.插入数据 每一页
//for (int i = 0; i < table.Rows.Count; i++)
//{
// if (i > 0 && i % dataRowNum == 0)
// {
// fenge += pageTiletRowNum;
// }
// int col = -1;
// for (int t = 0; t < columnNum; t++)
// {
// if (col == -1)
// {
// col = t;
// }
// if (MergeColumns != null)
// {
// for (int m = 0; m < MergeColumns.Count(); m++)
// {
// if (t == MergeColumns[m])
// {
// col++;
// }
// }
// }
// SetCellValue(i + fenge, col, table.Rows[i][t].ToString());
// col++;
// }
//}
#endregion
return listBanzu;
}
catch (Exception ex)
{
lastErrorMSG = ex.Message;
return null;
}
}
}
public class ExcelInfo
{
public int pageID;
public string banzu;
public string date;
}
/// <summary>
/// 文字水平显示
/// </summary>
public enum HorizontalAlignment
{
Bottom = 0,
Center = 1,
CenterAcross = 2,
Distributed = 3,
Fill = 4,
General = 5,
Justify = 6,
Left = 7,
Right = 8,
Top = 9,
}
/// <summary>
/// 垂直显示
/// </summary>
public enum VerticalAlignment
{
Bottom = 0,
Center = 1,
CenterAcross = 2,
Distributed = 3,
Fill = 4,
General = 5,
Justify = 6,
Left = 7,
Right = 8,
Top = 9,
}
}
调用
var v = dt.DefaultView;
v.Sort = "班组";
ExcelHelper exhelp = new ExcelHelper("templet\\kq.xlsx", "1");
List<ExcelInfo> banzu = exhelp.TableToExcel(v.ToTable(), 20, 28, 5, 8, 35, new int[] { 3 }, 35);
if (banzu != null)
{
foreach (var item in banzu)
{
exhelp.SetCellValue((item.pageID - 1) * 28, 0, "考勤表");
exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 0, "部门名称(全称): xx名称");
exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 10, "班组:" + item.banzu);
exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 24, dt.Rows[0]["月份"].ToString());
exhelp.SetCellValue((item.pageID - 1) * 28 + 27, 0, "第" + item.pageID + "页,共" + banzu.Count + "页 o=缺勤 √ =出勤");
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.AddExtension = true;
sfd.DefaultExt = "xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
exhelp.SaveAsFile(sfd.FileName);
}
return true;
}
return false;