C#操作EXCEL类
using System;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MySql.Data.Common;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Data.Common;
/// <summary>
/// excel 2003 helper
/// </summary>
public sealed class ExcelHelper : IDisposable
{
public static readonly object Missing = Type.Missing;
public string FileName { get; set; }
public Application App { get; set; }
public Workbooks Wbs { get; set; }
public Workbook Wb { get; set; }
public Worksheets Wss { get; set; }
public Worksheet Ws { get; set; }
public Range Rng { get; set; }
//宋吉峰使用EXCEL变量定义开始
public Microsoft.Office.Interop.Excel.Application excelApp = null;
public Microsoft.Office.Interop.Excel.Workbook book = null;
public Microsoft.Office.Interop.Excel.Worksheet sheet = null;
public Microsoft.Office.Interop.Excel.Range range = null;
public String Input_StrArr = "";
//宋吉峰使用EXCEL变量定义结束
public ExcelHelper()
{
}
/// <summary>
/// 创建一个Excel对象
/// </summary>
public void Create()
{
App = new Application() { Visible = false };
Wbs = App.Workbooks;
Wb = Wbs.Add(Missing);
}
/// <summary>
/// //打开一个Excel文件
/// </summary>
/// <param name="fileName"></param>
public void Open(string fileName)
{
App = new Application();
Wbs = App.Workbooks;
Wb = Wbs.Add(fileName);
FileName = fileName;
}
/// <summary>
/// 获取一个工作表
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public Worksheet GetSheet(string sheetName)
{
return (Worksheet)Wb.Worksheets[sheetName];
}
public Worksheet GetSheet(int i)
{
return (Worksheet)Wb.Worksheets[i];
}
/// <summary>
/// 添加一个工作表
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public Worksheet AddSheet(string sheetName)
{
var s = (Worksheet)Wb.Worksheets.Add(Missing, Missing, Missing, Missing);
s.Name = sheetName;
return s;
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="sheetName"></param>
public void DeleteSheet(string sheetName)
{
((Worksheet)Wb.Worksheets[sheetName]).Delete();
}
/// <summary>
/// 重命名一个工作表
/// </summary>
/// <param name="oldSheetName"></param>
/// <param name="newSheetName"></param>
/// <returns></returns>
public Worksheet ReNameSheet(string oldSheetName, string newSheetName)
{
var s = (Worksheet)Wb.Worksheets[oldSheetName];
s.Name = newSheetName;
return s;
}
/// <summary>
/// 重命名一个工作表
/// </summary>
/// <param name="sheet"></param>
/// <param name="newSheetName"></param>
/// <returns></returns>
public Worksheet ReNameSheet(Worksheet sheet, string newSheetName)
{
sheet.Name = newSheetName;
return sheet;
}
/// <summary>
/// 设定单元格的值[Text]
/// </summary>
/// <param name="ws">要设值的工作表</param>
/// <param name="x">X行</param>
/// <param name="y">Y列</param>
/// <param name="value">值</param>
public Range SetCellValue(Worksheet ws, int x, int y, object value)
{
((Range)ws.Cells[x, y]).Value2 = value;
return (Range)ws.Cells[x, y];
}
/// <summary>
/// 设定单元格的值[Text]
/// </summary>
/// <param name="sheetName">要设值的工作表的名称</param>
/// <param name="x">X行</param>
/// <param name="y">Y列</param>
/// <param name="value">值</param>
public Range SetCellValue(string sheetName, int x, int y, object value)
{
((Range)GetSheet(sheetName).Cells[x, y]).Value2 = value;
return (Range)GetSheet(sheetName).Cells[x, y];
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="ws"></param>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
public void MergeCells(Worksheet ws, int x1, int y1, int x2, int y2)
{
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Missing);
}
/// <summary>
/// //合并单元格
/// </summary>
/// <param name="sheetName"></param>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
public void MergeCells(string sheetName, int x1, int y1, int x2, int y2)
{
GetSheet(sheetName).get_Range(GetSheet(sheetName).Cells[x1, y1], GetSheet(sheetName).Cells[x2, y2]).Merge(Missing);
}
/// <summary>
/// 保存文档
/// </summary>
public void Save()
{
Wb.Save();
}
/// <summary>
/// 文档另存为
/// </summary>
/// <param name="fileName"></param>
public void SaveAs(string fileName)
{
Wb.SaveAs(fileName, XlFileFormat.xlWorkbookNormal,
Missing, Missing, Missing, Missing,
XlSaveAsAccessMode.xlNoChange,
Missing, Missing, Missing, Missing, Missing);
}
/// <summary>
/// 关闭一个Excel对象，销毁对象
/// </summary>
public void Close()
{
// 明确的释放非托管的COM资源,调用Marshal.FinalReleaseComObject对所有的访问其对象.
#region
if (Rng != null)
{
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Rng);
Rng = null;
}
if (Ws != null)
{
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Ws);
Ws = null;
}
if (Wb != null)
{
if (App != null && App.DisplayAlerts)
{
App.DisplayAlerts = false;
}
Wb.Close(Missing, Missing, Missing);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Wb);
Wb = null;
}
if (Wbs != null)
{
if (App != null && App.DisplayAlerts)
{
App.DisplayAlerts = false;
}
Wbs.Close();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Wbs);
Wbs = null;
}
if (App != null)
{
if (App.DisplayAlerts)
{
App.DisplayAlerts = false;
}
//当Excel或通过程序调用启动，并且Application.Visible = false时，Application.UserControl 的值是false。
//如果UserControl为false，且还有未完成的调用时，Excel进程将会继续保留。
//可以设置UserControl为true，强迫Quit方法调用时，应用程序被终止，而不管未完成的调用。
App.UserControl = true;
App.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(App);
App = null;
}
#endregion
#region GC
// Clean up the unmanaged Excel COM resources by forcing a garbage
// collection as soon as the calling function is off the stack (at
// which point these objects are no longer rooted).
GC.Collect();
GC.WaitForPendingFinalizers();
// GC needs to be called twice in order to get the Finalizers called
// - the first time in, it simply makes a list of what is to be
// finalized, the second time in, it actually is finalizing. Only
// then will the object do its automatic ReleaseComObject.
GC.Collect();
GC.WaitForPendingFinalizers();
#endregion
}
public void Dispose()
{
Close();
}
/*****************************************************************************************
*
* 宋吉峰添加EXCEL操作函数开始
*
* ***************************************************************************************/
/// <summary>
/// 将数据写入Excel
/// </summary>
/// <param name="data">要写入的字符串</param>
/// <param name="starRow">写入的行</param>
/// <param name="startColumn">写入的列</param>
public void WriteData(string data, Int64 row, int column)
{
sheet.Cells[row, column] = data;
}
/// 读取指定单元格数据
/// </summary>
/// <param name="row">行序号</param>
/// <param name="column">列序号</param>
/// <returns>该格的数据</returns>
public string ReadData(Int64 row, int column)
{
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(sheet.Cells[row, column], sheet.Cells[row, column]);
return range.Text.ToString();
}
/// <summary>
/// 填充EXCEL内容函数
/// </summary>
/// <param name="fileName">EXCEL文件路径</param>
/// <param name="str_Arr">填充内容以","(半角)为分割符号例如： 侧耳，水电厂，友，中小学，大</param>
public String ExcelWriteData(string fileName, string str_Arr)
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false; //若为true，删除瞬间可以看见 office excel界面
//打开excel文件
book = excelApp.Workbooks.Add(fileName);
//获取sheet1
sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1];
//获取编辑范围
// str = ReadData(3, 4);// sheet.Cells[3, 4].ToString();
string[] Str_Arr;
if (str_Arr.Length > 0)
{
Str_Arr = str_Arr.Split(',');
}
else
{
Str_Arr = Input_StrArr.Split(',');
}
//"第一个填充" //产品品番
if (Str_Arr.Length > 0)
{
WriteData(Str_Arr[0], 4, 3);
}
else
{
WriteData("", 4, 3);
}
//"第2个填充" //产品品名
if (Str_Arr.Length > 1)
{
WriteData(Str_Arr[1], 6, 3);
}
else
{
WriteData("", 6, 3);
}
//"第3个填充" //式样规格
if (Str_Arr.Length > 2)
{
WriteData(Str_Arr[2], 8, 3);
}
else
{
WriteData("", 8, 3);
}
//"第4个填充" //投入数量
if (Str_Arr.Length > 3)
{
WriteData(Str_Arr[3], 11, 5);
}
else
{
WriteData("", 11, 5);
}
//"第5个填充" //批量号
if (Str_Arr.Length > 4)
{
WriteData(Str_Arr[4], 4, 12);
}
else
{
WriteData("", 4, 12);
}
//"第6个填充" //原始批量号
if (Str_Arr.Length > 6)
{
WriteData(Str_Arr[6], 6, 12);
}
else
{
WriteData("", 6, 12);
}
////"第7个填充" //作业手顺
//if (Str_Arr.Length > 6)
//{
// WriteData(Str_Arr[6], 8, 12);
//}
//获取sheet2
sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[2];
//获取编辑范围
// str = ReadData(3, 4);// sheet.Cells[3, 4].ToString();
//"第一个填充" //产品品番
if (Str_Arr.Length > 0)
{
WriteData(Str_Arr[0], 4, 3);
}
else
{
WriteData("", 4, 3);
}
//"第2个填充" //产品品名
if (Str_Arr.Length > 1)
{
WriteData(Str_Arr[1], 6, 3);
}
else
{
WriteData("", 6, 3);
}
//"第3个填充" //式样规格
if (Str_Arr.Length > 2)
{
WriteData(Str_Arr[2], 8, 3);
}
else
{
WriteData("", 8, 3);
}
//"第4个填充" //投入数量
if (Str_Arr.Length > 3)
{
WriteData(Str_Arr[3], 11, 5);
}
else
{
WriteData("", 11, 5);
}
//"第5个填充" //批量号
if (Str_Arr.Length > 4)
{
WriteData(Str_Arr[4], 4, 12);
}
else
{
WriteData("", 4, 12);
}
//"第6个填充" //原始批量号
if (Str_Arr.Length > 5)
{
WriteData(Str_Arr[5], 6, 12);
}
else
{
WriteData("", 6, 12);
}
//"第7个填充" //作业手顺
if (Str_Arr.Length > 6)
{
WriteData(Str_Arr[6], 8, 12);
}
else
{
WriteData("", 8, 12);
}
string str = "";
string s = System.DateTime.Now.Date.Year.ToString() + "-" + System.DateTime.Now.Date.Month.ToString() + "-" + System.DateTime.Now.Date.Day.ToString() + "-" + System.DateTime.Now.Date.Hour.ToString() + "-" + System.DateTime.Now.Date.Minute.ToString() + "-" + System.DateTime.Now.Second.ToString() + "-" + System.DateTime.Now.Millisecond.ToString();
s = s + ".xls";
str = fileName.ToString().ToLower().Replace(".xls", s);
//保存编辑
// SaveAs();
book.SaveAs(str, XlFileFormat.xlWorkbookNormal,
Missing, Missing, Missing, Missing,
XlSaveAsAccessMode.xlNoChange,
Missing, Missing, Missing, Missing, Missing);
//关闭book
book.Close(Missing, Missing, Missing);
//退出excel application，可以将前面的excelApp.Visible = false改为excelApp.Visible = true看看;
excelApp.Workbooks.Close();
excelApp.Quit();
return str;
}
public void ExcelWritePrintData(string fileName, string str_Arr)
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false; //若为true，删除瞬间可以看见 office excel界面
//打开excel文件
book = excelApp.Workbooks.Add(fileName);
//获取sheet1
sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1];
//获取编辑范围
// str = ReadData(3, 4);// sheet.Cells[3, 4].ToString();
string[] Str_Arr;
if (str_Arr.Length > 0)
{
Str_Arr = str_Arr.Split(',');
}
else
{
Str_Arr = Input_StrArr.Split(',');
}
//"第一个填充" //产品品番
if (Str_Arr.Length > 0)
{
WriteData(Str_Arr[0], 4, 3);
}
else
{
WriteData("", 4, 3);
}
//"第2个填充" //产品品名
if (Str_Arr.Length > 1)
{
WriteData(Str_Arr[1], 6, 3);
}
else
{
WriteData("", 6, 3);
}
//"第3个填充" //式样规格
if (Str_Arr.Length > 2)
{
WriteData(Str_Arr[2], 8, 3);
}
else
{
WriteData("", 8, 3);
}
//"第4个填充" //投入数量
if (Str_Arr.Length > 3)
{
WriteData(Str_Arr[3], 11, 5);
}
else
{
WriteData("", 11, 5);
}
//"第5个填充" //批量号
if (Str_Arr.Length > 4)
{
WriteData(Str_Arr[4], 4, 12);
}
else
{
WriteData("", 4, 12);
}
//"第6个填充" //原始批量号
if (Str_Arr.Length > 6)
{
WriteData(Str_Arr[6], 6, 12);
}
else
{
WriteData("", 6, 12);
}
////"第7个填充" //作业手顺
//if (Str_Arr.Length > 6)
//{
// WriteData(Str_Arr[6], 8, 12);
//}
//获取sheet2
//sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[2];
////获取编辑范围
//// str = ReadData(3, 4);// sheet.Cells[3, 4].ToString();
////"第一个填充" //产品品番
//if (Str_Arr.Length > 0)
//{
// WriteData(Str_Arr[0], 4, 3);
//}
//else
//{
// WriteData("", 4, 3);
//}
////"第2个填充" //产品品名
//if (Str_Arr.Length > 1)
//{
// WriteData(Str_Arr[1], 6, 3);
//}
//else
//{
// WriteData("", 6, 3);
//}
////"第3个填充" //式样规格
//if (Str_Arr.Length > 2)
//{
// WriteData(Str_Arr[2], 8, 3);
//}
//else
//{
// WriteData("", 8, 3);
//}
////"第4个填充" //投入数量
//if (Str_Arr.Length > 3)
//{
// WriteData(Str_Arr[3], 11, 5);
//}
//else
//{
// WriteData("", 11, 5);
//}
////"第5个填充" //批量号
//if (Str_Arr.Length > 4)
//{
// WriteData(Str_Arr[4], 4, 12);
//}
//else
//{
// WriteData("", 4, 12);
//}
////"第6个填充" //原始批量号
//if (Str_Arr.Length > 5)
//{
// WriteData(Str_Arr[5], 6, 12);
//}
//else
//{
// WriteData("", 6, 12);
//}
////"第7个填充" //作业手顺
//if (Str_Arr.Length > 6)
//{
// WriteData(Str_Arr[6], 8, 12);
//}
//else
//{
// WriteData("", 8, 12);
//}
string str = "";
string s = System.DateTime.Now.Date.Year.ToString() + "-" + System.DateTime.Now.Date.Month.ToString() + "-" + System.DateTime.Now.Date.Day.ToString() + "-" + System.DateTime.Now.Date.Hour.ToString() + "-" + System.DateTime.Now.Date.Minute.ToString() + "-" + System.DateTime.Now.Second.ToString() + "-" + System.DateTime.Now.Millisecond.ToString();
s = s + ".xls";
str = fileName.ToString().ToLower().Replace(".xls", s);
//保存编辑
// SaveAs();
book.SaveAs(str, XlFileFormat.xlWorkbookNormal,
Missing, Missing, Missing, Missing,
XlSaveAsAccessMode.xlNoChange,
Missing, Missing, Missing, Missing, Missing);
//关闭book
book.Close(Missing, Missing, Missing);
//退出excel application，可以将前面的excelApp.Visible = false改为excelApp.Visible = true看看;
excelApp.Workbooks.Close();
excelApp.Quit();
ExcelPrint(str);
}
/// <summary>
/// 通过文件路径和XLS文件中SHEET的索引来打印EXCEL的SHEET文档
/// </summary>
/// <param name="strFilePath">XLS文件路径</param>
/// <param name="SheetIndex">XLS文件中SHEET的索引</param>
public void ExcelPrint(string strFilePath, int SheetIndex)
{
Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks xlWorkbooks;
Microsoft.Office.Interop.Excel.Workbook xlWorkbook;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
System.Type tyWorkbooks;
System.Reflection.MethodInfo[] methods;
object objFilePath;
object oMissing = System.Reflection.Missing.Value;
//strFilePath = Server.MapPath(strFilePath);
if (!System.IO.File.Exists(strFilePath))
{
throw new System.IO.FileNotFoundException();
return;
}
try
{
xlApp.Visible = true;
xlWorkbooks = xlApp.Workbooks;
tyWorkbooks = xlWorkbooks.GetType();
methods = tyWorkbooks.GetMethods();
objFilePath = strFilePath;
object Nothing = System.Reflection.Missing.Value;
xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[SheetIndex];
xlWorksheet.PrintPreview(true);
xlWorkbook.Close(oMissing, oMissing, oMissing);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (xlApp != null)
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
}
}
/// <summary>
/// 打印EXCEL中所有SHEET
/// </summary>
/// <param name="strFilePath">EXCEL文件路径</param>
public void ExcelPrint(string strFilePath)
{
int SheetIndex;
SheetIndex = 1;
Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks xlWorkbooks;
Microsoft.Office.Interop.Excel.Workbook xlWorkbook;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
System.Type tyWorkbooks;
System.Reflection.MethodInfo[] methods;
object objFilePath;
object oMissing = System.Reflection.Missing.Value;
//strFilePath = Server.MapPath(strFilePath);
if (!System.IO.File.Exists(strFilePath))
{
throw new System.IO.FileNotFoundException();
return;
}
try
{
xlApp.Visible = true;
xlWorkbooks = xlApp.Workbooks;
tyWorkbooks = xlWorkbooks.GetType();
methods = tyWorkbooks.GetMethods();
objFilePath = strFilePath;
object Nothing = System.Reflection.Missing.Value;
xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
for (SheetIndex = 0; SheetIndex < xlWorkbook.Sheets.Count; SheetIndex++)
{
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[SheetIndex + 1];
xlWorksheet.PrintPreview(true);
}
xlWorkbook.Close(oMissing, oMissing, oMissing);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (xlApp != null)
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
}
}
public void ExcelPrint_pdf(string strFilePath)
{
int SheetIndex;
SheetIndex = 1;
Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks xlWorkbooks;
Microsoft.Office.Interop.Excel.Workbook xlWorkbook;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
System.Type tyWorkbooks;
System.Reflection.MethodInfo[] methods;
object objFilePath;
object oMissing = System.Reflection.Missing.Value;
//strFilePath = Server.MapPath(strFilePath);
if (!System.IO.File.Exists(strFilePath))
{
throw new System.IO.FileNotFoundException();
return;
}
try
{
xlApp.Visible = true;
xlWorkbooks = xlApp.Workbooks;
tyWorkbooks = xlWorkbooks.GetType();
methods = tyWorkbooks.GetMethods();
objFilePath = strFilePath;
object Nothing = System.Reflection.Missing.Value;
xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
for (SheetIndex = 0; SheetIndex < xlWorkbook.Sheets.Count; SheetIndex++)
{
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[SheetIndex + 1];
xlWorksheet.PrintOut(1, 1, false, false, "Acrobat Distiller", true, false, strFilePath.ToString().ToLower().Replace(".xls",(SheetIndex.ToString()+".pdf")));
//xlWorksheet.PrintPreview(true);
}
xlWorkbook.Close(oMissing, oMissing, oMissing);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (xlApp != null)
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
}
}
/*****************************************************************************************
*
* 宋吉峰添加EXCEL操作函数结束
*
* ***************************************************************************************/
public String ExcelWriteData_C(string fileName)
{
string ColumsIndex = "";
string RowIndex = "";
String letter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false; //若为true，删除瞬间可以看见 office excel界面
//打开excel文件
book = excelApp.Workbooks.Add(fileName);
//获取sheet1
sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1];
//获取编辑范围
// str = ReadData(3, 4);// sheet.Cells[3, 4].ToString();
string str = "SELECT TABLE_SCHEMA,COLUMN_NAME,concat(TABLE_NAME,'.',COLUMN_NAME) as columnss FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA='mts2' and column_name<>'CO_CODE'";
System.Data.DataTable dt = new System.Data.DataTable();
MYSQL_SQLDB SQLDB = new MYSQL_SQLDB();
Int64 bigno = 0;
WriteData("数据库名:MTS2", 1, 1);
range = (Range)sheet.get_Range("A1", "A1");
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0,255,255).ToArgb();
bigno = 1;
//数据填充开始
str = "show tables";
dt = SQLDB.Get_DataTable_By_ExecuteSQL(str);
for (int i = 0; i < dt.Rows.Count; i++)
{
bigno = bigno + 3;
WriteData("表名:",bigno, 1);
WriteData(dt.Rows[i][0].ToString(), bigno, 2);
ColumsIndex = "B" + bigno.ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 255, 255).ToArgb();
System.Data.DataTable dt_Keywords = new System.Data.DataTable();
str = "SELECT TABLE_SCHEMA,COLUMN_NAME,concat(TABLE_NAME,'.',COLUMN_NAME) as columnss FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA='mts2' and column_name<>'CO_CODE' and TABLE_NAME='" + dt.Rows[i][0].ToString() + "'";
dt_Keywords = SQLDB.Get_DataTable_By_ExecuteSQL(str);
// Response.Write("<BR>**************************************************************************************<BR>");
// Response.Write("<BR>表KeyWords名称：<BR>");
// Response.Write("<BR>**************************************************************************************<BR>");
bigno=bigno+2;
WriteData("关键字:", bigno, 1);
ColumsIndex = "B" + bigno.ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 255, 255).ToArgb();
for (int j = 0; j < dt_Keywords.Rows.Count; j++)
{
// Response.Write("<BR>KeyWords Name：<BR>" + dt_Keywords.Rows[j]["COLUMN_NAME"].ToString());
bigno = bigno + 1;
WriteData(dt_Keywords.Rows[j]["COLUMN_NAME"].ToString(), bigno, 2);
ColumsIndex = "B"+bigno.ToString();
range.WrapText = true;
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
range.Font.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
System.Data.DataTable dt_Table_Related = new System.Data.DataTable();
str = "SELECT TABLE_SCHEMA,COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA='mts2' and column_name<>'CO_CODE' and column_name='" + dt_Keywords.Rows[j]["COLUMN_NAME"].ToString() + "'";
dt_Table_Related.Clear();
dt_Table_Related = SQLDB.Get_DataTable_By_ExecuteSQL(str);
//Response.Write("<BR>Related_Table:");
bigno = bigno + 1;
WriteData("相关表名：", bigno, 2);
ColumsIndex = "B" + bigno.ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 255).ToArgb();
range.WrapText = true;
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
range.Font.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
WriteData("链接条件：", bigno+1, 2);
ColumsIndex = "B" + (bigno+1).ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.WrapText = true;
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255,0, 255).ToArgb();
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
range.Font.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
int k = 0;
for ( k = 0; k < dt_Table_Related.Rows.Count; k++)
{
WriteData(dt_Table_Related.Rows[k]["table_name"].ToString(), bigno, 3 + k);
ColumsIndex = letter.Substring(3 + k, 1) + (bigno ).ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.WrapText = true;
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 255).ToArgb();
range.Font.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
string ss =dt.Rows[i][0].ToString()+"."+dt_Keywords.Rows[j]["COLUMN_NAME"].ToString()+"="+dt_Table_Related.Rows[k]["table_name"].ToString() + "." + dt_Table_Related.Rows[k]["COLUMN_NAME"].ToString();
WriteData(ss, bigno + 1, 3 + k);
ColumsIndex = letter.Substring(3+k,1) + (bigno + 1).ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.WrapText = true;
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 255).ToArgb();
range.Font.Color = System.Drawing.Color.FromArgb(255,0, 0 ).ToArgb();
range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
// Response.Write(dt_Table_Related.Rows[k]["table_name"].ToString() + ",");
}
if (k == 0)
{
WriteData("无关键字", bigno, 3);
}
else
{
bigno = bigno + 1;
}
}
// Response.Write("<BR>**************************************************************************************<BR>");
System.Data.DataTable dt_Table_colunms_name = new System.Data.DataTable();
str = "show columns from " + dt.Rows[i][0].ToString() + " from MTS2 ";
dt_Table_colunms_name.Clear();
dt_Table_colunms_name = SQLDB.Get_DataTable_By_ExecuteSQL(str);
bigno = bigno + 2;
WriteData("字段名称", bigno, 1);
ColumsIndex = "A" + bigno.ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0).ToArgb();
WriteData("字段类型", bigno, 2);
ColumsIndex = "B" + bigno.ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0).ToArgb();
WriteData("是否可为空", bigno, 3);
ColumsIndex = "C" + bigno.ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0).ToArgb();
WriteData("是否为关键值", bigno, 4);
ColumsIndex = "D" + bigno.ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0).ToArgb();
WriteData("默认值", bigno, 5);
ColumsIndex = "E" + bigno.ToString();
range = (Range)sheet.get_Range(ColumsIndex, ColumsIndex);
range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0).ToArgb();
for (int y = 0; y < dt_Table_colunms_name.Rows.Count; y++)
{
bigno = bigno + 1;
WriteData(dt_Table_colunms_name.Rows[y]["Field"].ToString(), bigno, 1);
WriteData(dt_Table_colunms_name.Rows[y]["type"].ToString(), bigno, 2);
WriteData(dt_Table_colunms_name.Rows[y]["NULL"].ToString(), bigno, 3);
WriteData(dt_Table_colunms_name.Rows[y]["Key"].ToString(), bigno, 4);
WriteData(dt_Table_colunms_name.Rows[y]["Default"].ToString(), bigno, 5);
// Response.Write("<BR>" + dt_Table_colunms_name.Rows[y]["Field"].ToString());
}
}
//数据填充结束
string s = System.DateTime.Now.Date.Year.ToString() + "-" + System.DateTime.Now.Date.Month.ToString() + "-" + System.DateTime.Now.Date.Day.ToString() + "-" + System.DateTime.Now.Date.Hour.ToString() + "-" + System.DateTime.Now.Date.Minute.ToString() + "-" + System.DateTime.Now.Second.ToString() + "-" + System.DateTime.Now.Millisecond.ToString();
s = s + ".xls";
str = fileName.ToString().ToLower().Replace(".xls", s);
//保存编辑
// SaveAs();
book.SaveAs(str, XlFileFormat.xlWorkbookNormal,
Missing, Missing, Missing, Missing,
XlSaveAsAccessMode.xlNoChange,
Missing, Missing, Missing, Missing, Missing);
//关闭book
book.Close(Missing, Missing, Missing);
//退出excel application，可以将前面的excelApp.Visible = false改为excelApp.Visible = true看看;
excelApp.Workbooks.Close();
excelApp.Quit();
return str;
}
}
