#region 变量
Application xlApp = null;
Workbooks wbs = null;
Workbook wb = null;
private int _rowindex = 0;//全局行索引(使用时加n行)
//要导出的表集合
private List<System.Data.DataTable> _tables = new List<System.Data.DataTable>();
private string _saveFile = null;
#endregion
#region 属性
/// <summary>
/// 设置EXCEL列数组样式()
/// </summary>
public static string SetColumns { get; set; }
#endregion
#region 构造方法
public Excel()
{
}
public Excel(System.Data.DataTable table)
{
_tables = new List<System.Data.DataTable>() { table };
}
public Excel(List<System.Data.DataTable> tables, string SaveFile)
{
_tables = tables;
_saveFile = SaveFile;
}
#endregion
//保存Table导入到那个Sheet表(打印时可以判断sheet是否有数据,没有数据则不打印)
private Dictionary<string, System.Data.DataTable> SheetTable = new Dictionary<string, System.Data.DataTable>();
/// <summary>
/// 导入excel模板
/// </summary>
public static void ExcelFile(System.Data.DataTable dataTable)
{
RegistryKey folders;
folders = OpenRegistryPath(Registry.CurrentUser, @"\software\microsoft\windows\currentversion\explorer\shell folders");
// Windows用户我的文档路径
string personalPath = folders.GetValue("Personal").ToString();
object misValue = Missing.Value;
Application exapp = new Application();
exapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\DataFile\报表_销售统计.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Workbook mybook = exapp.Workbooks[1];
Worksheet exs = (Worksheet)mybook.Worksheets[1];
try
{
int rowCount = dataTable.Rows.Count;
int colCount = dataTable.Columns.Count;
object[,] dataArray = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[i, j] = dataTable.Rows[i][j];
}
}
exs.Range[exapp.Cells[3, 1], exapp.Cells[rowCount, colCount]].Value2 = dataArray;
mybook.SaveCopyAs(personalPath + @"\报表_销售统计.xls");
}
finally
{
exapp.Quit();
GC.Collect();//强行销毁
PublicMethod.Kill(exapp);//调用kill当前excel进程
releaseObject(exs);
releaseObject(mybook);
releaseObject(exapp);
}
}
private static RegistryKey OpenRegistryPath(RegistryKey root, string s)
{
s = s.Remove(0, 1) + @"\";
while (s.IndexOf(@"\") != -1)
{
root = root.OpenSubKey(s.Substring(0, s.IndexOf(@"\")));
s = s.Remove(0, s.IndexOf(@"\") + 1);
}
return root;
}
/// <summary>
/// 获取Workbook
/// </summary>
/// <param name="table"></param>
/// <param name="filename"></param>
public void GetExcelWorkbook()
{
if (_tables.Count == 0)
{
throw new Exception("Tables集合必须大于零!");
}
if (_rowindex < 0)
{
_rowindex = 0;
}
object misValue = Missing.Value;
xlApp = new Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
wbs = xlApp.Workbooks;
wb = wbs.Add(misValue); //添加一个工作簿
//添加Sheet表,新建一个Excel文件时候,一般会默认有3个Sheet表,所以用[table.Count - wb.Sheets.Count]
int tabcount = _tables.Count;
int sheets = wb.Worksheets.Count; //获取默认Sheet表个数,一般默认3个
if (tabcount > sheets)
{
wb.Worksheets.Add(misValue, misValue, tabcount - sheets, misValue);
}
// 删除多余Sheet表
//((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[index]).Delete();
//写入Excel
WriteExcelSheet(wb, _saveFile, misValue, xlApp, wb);
wb.Close(true, misValue, misValue);
xlApp.Quit();
PublicMethod.Kill(xlApp);
releaseObject(wb);
releaseObject(xlApp);
}
/// <summary>
/// 写入Excel的Sheet表
/// </summary>
/// <param name="wb"></param>
/// <param name="tables"></param>
private void WriteExcelSheet(Workbook wb, string SaveFile, object misValue, Application excel, Workbook workBook)
{
List<string> SheetNames = GetSheetsName(wb); //Sheet表名字集合,防止相同名称,出现错误!
for (int i = 0; i < _tables.Count; i++)
{
System.Data.DataTable table = _tables[i];
int rows = table.Rows.Count;
int cols = table.Columns.Count;
//获取Sheet表
Worksheet wsheet = (Worksheet)wb.Worksheets[i + 1]; //wb.Worksheets 所以从1开始
//选中表
wsheet.Select();
//设置表名字
wsheet.Name = GetCorrectSheetName(SheetNames, table.TableName, (i + 1)); //Name要特别注意
//保存DataTable和Sheet表的绑定
SheetTable.Add(wsheet.Name, table);
DataTableToSheet(table, SaveFile, misValue, excel, workBook, wsheet);
//保存名字
//SheetNames.Add(wsheet.Name);
// SheetNames[i] = wsheet.Name;
}
//选中第一个表
((Worksheet)wb.Worksheets[1]).Select();
}
/// <summary>
/// 获取一个合法Sheet名称
/// </summary>
/// <returns></returns>
private string GetCorrectSheetName(List<string> existnames, string tabname, int tabindex)
{
string name = String.Empty;
//是否为空,是否大于31,是否有特殊字符
//验证名字是否合法
if (!VerifySheetName(tabname))
{
tabname = "Table" + tabindex;
}
bool isexist = existnames.Exists(n => n.Equals(tabname)); //是否存在
if (isexist)
{
tabindex++;
name = GetCorrectSheetName(existnames, "Table" + tabindex, tabindex);
}
else
{
name = tabname;
}
return name;
}
/// <summary>
/// 判断是否合法
/// </summary>
private bool VerifySheetName(string sheetname)
{
if (string.IsNullOrEmpty(sheetname) || sheetname.Length > 31)
{
return false;
}
if (sheetname.Contains("\\") || sheetname.Contains("/"))
{
return false;
}
if (sheetname.Contains(":") || sheetname.Contains(":"))
{
return false;
}
if (sheetname.Contains("?") || sheetname.Contains("?"))
{
return false;
}
if (sheetname.Contains("[") || sheetname.Contains("]"))
{
return false;
}
return true;
}
/// <summary>
/// 获取Workbook中所有Sheet表的名称
/// </summary>
/// <param name="wb"></param>
/// <returns></returns>
private List<string> GetSheetsName(Workbook wb)
{
List<string> names = new List<string>();
foreach (var sheet in wb.Worksheets)
{
string name = ((Worksheet)sheet).Name;
if (!String.IsNullOrEmpty(name))
{
names.Add(name);
}
}
return names;
}
/// <summary>
/// 数据库转为excel表格
/// </summary>
/// <param name="dataTable">数据库数据</param>
/// <param name="SaveFile">导出的excel文件</param>
private void DataTableToSheet(System.Data.DataTable dataTable, string SaveFile, object misValue, Application excel, Workbook workBook, Worksheet workSheet)
{
int rowCount = dataTable.Rows.Count;
int colCount = dataTable.Columns.Count;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] dataArray = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[i, j] = "'" + dataTable.Rows[i][j];
}
}
if (rowCount > 0)
excel.Range["A2"].Resize[rowCount, colCount].Value2 = dataArray;
excel.Visible = false;
workBook.SaveAs(SaveFile, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
releaseObject(workSheet);
}
/// <summary>
/// 数据库转为excel表格
/// </summary>
/// <param name="dataTable">数据库数据</param>
/// <param name="SaveFile">导出的excel文件</param>
public static void DataTableToExcel(System.Data.DataTable dataTable, string SaveFile)
{
Application excel;
_Workbook workBook;
_Worksheet workSheet;
object misValue = System.Reflection.Missing.Value;
excel = new Application();//new ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (_Worksheet)workBook.ActiveSheet;
//设置单元格样式
string[] arrary;
if (!string.IsNullOrEmpty(SetColumns))
{
if (SetColumns.Contains(','))
arrary = SetColumns.Split(',');
else
{
arrary = new string[1];
arrary[0] = SetColumns;
}
for (int i = 0; i < arrary.Length; i++)
{
Range range = null;
range = workSheet.Columns.get_Range(arrary[i], misValue).Resize[dataTable.Rows.Count, 1];
range.NumberFormatLocal = "#,##0.00";
}
}
int rowIndex = 1;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//取得表格中的数据
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();
}
}
excel.Visible = false;
workBook.SaveAs(SaveFile, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
dataTable = null;
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.Kill(excel);//调用kill当前excel进程
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
Application xlApp = null;
Workbooks wbs = null;
Workbook wb = null;
private int _rowindex = 0;//全局行索引(使用时加n行)
//要导出的表集合
private List<System.Data.DataTable> _tables = new List<System.Data.DataTable>();
private string _saveFile = null;
#endregion
#region 属性
/// <summary>
/// 设置EXCEL列数组样式()
/// </summary>
public static string SetColumns { get; set; }
#endregion
#region 构造方法
public Excel()
{
}
public Excel(System.Data.DataTable table)
{
_tables = new List<System.Data.DataTable>() { table };
}
public Excel(List<System.Data.DataTable> tables, string SaveFile)
{
_tables = tables;
_saveFile = SaveFile;
}
#endregion
//保存Table导入到那个Sheet表(打印时可以判断sheet是否有数据,没有数据则不打印)
private Dictionary<string, System.Data.DataTable> SheetTable = new Dictionary<string, System.Data.DataTable>();
/// <summary>
/// 导入excel模板
/// </summary>
public static void ExcelFile(System.Data.DataTable dataTable)
{
RegistryKey folders;
folders = OpenRegistryPath(Registry.CurrentUser, @"\software\microsoft\windows\currentversion\explorer\shell folders");
// Windows用户我的文档路径
string personalPath = folders.GetValue("Personal").ToString();
object misValue = Missing.Value;
Application exapp = new Application();
exapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\DataFile\报表_销售统计.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Workbook mybook = exapp.Workbooks[1];
Worksheet exs = (Worksheet)mybook.Worksheets[1];
try
{
int rowCount = dataTable.Rows.Count;
int colCount = dataTable.Columns.Count;
object[,] dataArray = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[i, j] = dataTable.Rows[i][j];
}
}
exs.Range[exapp.Cells[3, 1], exapp.Cells[rowCount, colCount]].Value2 = dataArray;
mybook.SaveCopyAs(personalPath + @"\报表_销售统计.xls");
}
finally
{
exapp.Quit();
GC.Collect();//强行销毁
PublicMethod.Kill(exapp);//调用kill当前excel进程
releaseObject(exs);
releaseObject(mybook);
releaseObject(exapp);
}
}
private static RegistryKey OpenRegistryPath(RegistryKey root, string s)
{
s = s.Remove(0, 1) + @"\";
while (s.IndexOf(@"\") != -1)
{
root = root.OpenSubKey(s.Substring(0, s.IndexOf(@"\")));
s = s.Remove(0, s.IndexOf(@"\") + 1);
}
return root;
}
/// <summary>
/// 获取Workbook
/// </summary>
/// <param name="table"></param>
/// <param name="filename"></param>
public void GetExcelWorkbook()
{
if (_tables.Count == 0)
{
throw new Exception("Tables集合必须大于零!");
}
if (_rowindex < 0)
{
_rowindex = 0;
}
object misValue = Missing.Value;
xlApp = new Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
wbs = xlApp.Workbooks;
wb = wbs.Add(misValue); //添加一个工作簿
//添加Sheet表,新建一个Excel文件时候,一般会默认有3个Sheet表,所以用[table.Count - wb.Sheets.Count]
int tabcount = _tables.Count;
int sheets = wb.Worksheets.Count; //获取默认Sheet表个数,一般默认3个
if (tabcount > sheets)
{
wb.Worksheets.Add(misValue, misValue, tabcount - sheets, misValue);
}
// 删除多余Sheet表
//((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[index]).Delete();
//写入Excel
WriteExcelSheet(wb, _saveFile, misValue, xlApp, wb);
wb.Close(true, misValue, misValue);
xlApp.Quit();
PublicMethod.Kill(xlApp);
releaseObject(wb);
releaseObject(xlApp);
}
/// <summary>
/// 写入Excel的Sheet表
/// </summary>
/// <param name="wb"></param>
/// <param name="tables"></param>
private void WriteExcelSheet(Workbook wb, string SaveFile, object misValue, Application excel, Workbook workBook)
{
List<string> SheetNames = GetSheetsName(wb); //Sheet表名字集合,防止相同名称,出现错误!
for (int i = 0; i < _tables.Count; i++)
{
System.Data.DataTable table = _tables[i];
int rows = table.Rows.Count;
int cols = table.Columns.Count;
//获取Sheet表
Worksheet wsheet = (Worksheet)wb.Worksheets[i + 1]; //wb.Worksheets 所以从1开始
//选中表
wsheet.Select();
//设置表名字
wsheet.Name = GetCorrectSheetName(SheetNames, table.TableName, (i + 1)); //Name要特别注意
//保存DataTable和Sheet表的绑定
SheetTable.Add(wsheet.Name, table);
DataTableToSheet(table, SaveFile, misValue, excel, workBook, wsheet);
//保存名字
//SheetNames.Add(wsheet.Name);
// SheetNames[i] = wsheet.Name;
}
//选中第一个表
((Worksheet)wb.Worksheets[1]).Select();
}
/// <summary>
/// 获取一个合法Sheet名称
/// </summary>
/// <returns></returns>
private string GetCorrectSheetName(List<string> existnames, string tabname, int tabindex)
{
string name = String.Empty;
//是否为空,是否大于31,是否有特殊字符
//验证名字是否合法
if (!VerifySheetName(tabname))
{
tabname = "Table" + tabindex;
}
bool isexist = existnames.Exists(n => n.Equals(tabname)); //是否存在
if (isexist)
{
tabindex++;
name = GetCorrectSheetName(existnames, "Table" + tabindex, tabindex);
}
else
{
name = tabname;
}
return name;
}
/// <summary>
/// 判断是否合法
/// </summary>
private bool VerifySheetName(string sheetname)
{
if (string.IsNullOrEmpty(sheetname) || sheetname.Length > 31)
{
return false;
}
if (sheetname.Contains("\\") || sheetname.Contains("/"))
{
return false;
}
if (sheetname.Contains(":") || sheetname.Contains(":"))
{
return false;
}
if (sheetname.Contains("?") || sheetname.Contains("?"))
{
return false;
}
if (sheetname.Contains("[") || sheetname.Contains("]"))
{
return false;
}
return true;
}
/// <summary>
/// 获取Workbook中所有Sheet表的名称
/// </summary>
/// <param name="wb"></param>
/// <returns></returns>
private List<string> GetSheetsName(Workbook wb)
{
List<string> names = new List<string>();
foreach (var sheet in wb.Worksheets)
{
string name = ((Worksheet)sheet).Name;
if (!String.IsNullOrEmpty(name))
{
names.Add(name);
}
}
return names;
}
/// <summary>
/// 数据库转为excel表格
/// </summary>
/// <param name="dataTable">数据库数据</param>
/// <param name="SaveFile">导出的excel文件</param>
private void DataTableToSheet(System.Data.DataTable dataTable, string SaveFile, object misValue, Application excel, Workbook workBook, Worksheet workSheet)
{
int rowCount = dataTable.Rows.Count;
int colCount = dataTable.Columns.Count;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] dataArray = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[i, j] = "'" + dataTable.Rows[i][j];
}
}
if (rowCount > 0)
excel.Range["A2"].Resize[rowCount, colCount].Value2 = dataArray;
excel.Visible = false;
workBook.SaveAs(SaveFile, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
releaseObject(workSheet);
}
/// <summary>
/// 数据库转为excel表格
/// </summary>
/// <param name="dataTable">数据库数据</param>
/// <param name="SaveFile">导出的excel文件</param>
public static void DataTableToExcel(System.Data.DataTable dataTable, string SaveFile)
{
Application excel;
_Workbook workBook;
_Worksheet workSheet;
object misValue = System.Reflection.Missing.Value;
excel = new Application();//new ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (_Worksheet)workBook.ActiveSheet;
//设置单元格样式
string[] arrary;
if (!string.IsNullOrEmpty(SetColumns))
{
if (SetColumns.Contains(','))
arrary = SetColumns.Split(',');
else
{
arrary = new string[1];
arrary[0] = SetColumns;
}
for (int i = 0; i < arrary.Length; i++)
{
Range range = null;
range = workSheet.Columns.get_Range(arrary[i], misValue).Resize[dataTable.Rows.Count, 1];
range.NumberFormatLocal = "#,##0.00";
}
}
int rowIndex = 1;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//取得表格中的数据
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();
}
}
excel.Visible = false;
workBook.SaveAs(SaveFile, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
dataTable = null;
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.Kill(excel);//调用kill当前excel进程
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}