/// <summary>
/// Excel文件打印预览
/// </summary>
/// <param name="ExcelFile">文件路径包含文件名称</param>
public static void ExcelPreview(string ExcelFile)
{
try
{
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
throw new Exception("无法创建Excel对象，可能您的计算机未安装Excel");
}
Excel.Workbooks xlsWbs = xlsApp.Workbooks;
Excel.Workbook xlsWb = xlsWbs.Open(
ExcelFile, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlsApp.Visible = true;
xlsWb.PrintPreview(false);
xlsWb = null;
xlsApp.Quit();
xlsApp = null;
}
catch (Exception ex)
{
throw new Exception(ex.Message + "Excel文件操作失败");
}
}
/// <summary>
/// 将datatable的数据导出到excel
/// </summary>
/// <param name="mdt"></param>
private void DataToExcel(DataTable mdt)
{
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
oBook = (Excel._Workbook)(oExcel.Workbooks.Add(true));// 引用excel工作薄
for (int i = 0; i < mdt.Columns.Count; i++)
{
oExcel.Cells[2, i + 1] = mdt.Columns[i].ColumnName.ToString();// m_DataView.Columns[i].HeaderText.ToString();
}
for (int i = 0; i < mdt.Rows.Count; i++)
{
for (int j = 0; j < mdt.Columns.Count; j++)
{
oExcel.Cells[i + 3, j + 1] = mdt.Rows[i][j].ToString();
}
}
oExcel.Visible = true;
object Missing = System.Reflection.Missing.Value;
// oExcel.Run("Sheet1.printdoc", Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);
oBook.Application.DisplayAlerts = false;
}
/// <summary>
/// 功能：实现Excel应用程序的打开
/// </summary>
/// <param name="TemplateFilePath">模板文件物理路径</param>
public void Open(string TemplateFilePath)
{
//打开对象
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
m_objExcel.Visible = false;
m_objExcel.DisplayAlerts = false;
if (m_objExcel.Version != "11.0")
{
MessageBox.Show("您的Excel 版本不是11.0 （Office 2003），操作可能会出现问题。");
m_objExcel.Quit();
return;
}
m_objBooks = (Workbooks)m_objExcel.Workbooks;
if (TemplateFilePath.Equals(String.Empty))
{
m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt));
}
else
{
m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
m_objSheets = (Sheets)m_objBook.Worksheets;
m_objSheet = (_Worksheet)(m_objSheets.get_Item(1));
m_objExcel.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
}
/// <summary>
/// 以excelOpenFileName为模板新建Excel文件
/// </summary>
public bool OpenExcelFile()
{
if (xlsApp != null)
{
//检查文件是否存在
if (xlsOpenFileName == "")
{
throw new Exception("请选择文件!");
}
}
if (!File.Exists(xlsOpenFileName))
{
throw new Exception(xlsOpenFileName + "该文件不存在!");
}
try
{
xlsApp = new Excel.ApplicationClass();
xlsWbs = xlsApp.Workbooks;
xlsWb = ((Excel.Workbook)xlsWbs.Open(xlsOpenFileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value));
xlsWs = (Excel.Worksheet)xlsWb.Worksheets[excelActiveWorkSheetIndex];
xlsApp.Visible = false;
return(true);
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception("(1)没有安装Excel 2003;\n(2)或没有安装Excel 2003 .NET 可编程性支持;\n详细信息:\n" + e.Message);
}
}
//--------------------------------------------------------------------------------------------------------
/// <summary>
/// 关闭Excel文件，释放对象；最后一定要调用此函数，否则会引起异常
/// </summary>
/// <param></param>
public void CloseExcelApplication()
{
try
{
xlsWbs = null;
xlsWb = null;
xlsWs = null;
xlsRg = null;
if (xlsApp != null)
{
xlsApp.ActiveWorkbook.Close(false, null, null);
xlsApp.Workbooks.Close();
//Object missing = Type.Missing;
xlsApp.Quit();
xlsApp = null;
//ReleaseAllRef(excelApplication);//Error
}
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
// 运行excel的vba宏
public object RunExcelMacro(string strMacroName, object args)
{
LuaTable ltArgs = args as LuaTable;
if (ltArgs == null)
{
MessageBox.Show("调用RunExcelMacro(string strMacroName, object args)时参数为空！");
return(null);
}
object[] oArgArr = new object[ltArgs.Values.Count];
for (int i = 0; i < oArgArr.Length; i++)
{
oArgArr[i] = ltArgs[i];
}
object[] ArgArr30 = new object[30];
for (int i = 0; i < oArgArr.Length; i++)
{
ArgArr30[i] = oArgArr[i];
}
for (int i = oArgArr.Length; i < ArgArr30.Length; i++)
{
ArgArr30[i] = Missing.Value;
}
if (excelApp == null) // 未初始化成功
{
MessageBox.Show("运行Excel的vba宏失败!\n\n请检查是否已在机器上装有Excel.");
return(null); // 脚本已经对null值的处理
}
object result_macro = null;
try
{
result_macro = excelApp.Run(strMacroName,
ArgArr30[0], ArgArr30[1], ArgArr30[2], ArgArr30[3], ArgArr30[4], ArgArr30[5],
ArgArr30[6], ArgArr30[7], ArgArr30[8], ArgArr30[9], ArgArr30[10], ArgArr30[11],
ArgArr30[12], ArgArr30[13], ArgArr30[14], ArgArr30[15], ArgArr30[16], ArgArr30[17],
ArgArr30[18], ArgArr30[19], ArgArr30[20], ArgArr30[21], ArgArr30[22], ArgArr30[23],
ArgArr30[24], ArgArr30[25], ArgArr30[26], ArgArr30[27], ArgArr30[28], ArgArr30[29]);
}
catch (Exception ex)
{
MessageBox.Show("宏运行错误,请不要保存所作修改！");
}
return(result_macro as string[]);
#if false
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
oExcel.Visible = true;
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
string strXL = "C:/Documents and Settings/kuangsihao/桌面/剑网3-NPC数值.xlsm";
oBook = oBooks.Open(strXL, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
RunMacro(oExcel, new Object[] { "矩形1_Click" });
public ClsAllExcel()
{
xlsApp = null;
xlsWbs = null;
xlsWb = null;
xlsWs = null;
ActiveSheetIndex = 1;
}
public void Open(string FileName)//打开一个Excel文件
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
}
public Base()
{
App = new Application();
App.Visible = true;
App.DisplayAlerts = false;
Books = App.Workbooks;
Book = Books.Add(Missing.Value);
Sheet = Book.ActiveSheet;
}
//关闭一个Excel对象，销毁对象
public void Close()
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
public void Quit()
{
Book.Close();
App.Quit();
Marshal.ReleaseComObject(Book);
Marshal.ReleaseComObject(Books);
Book = null;
Books = null;
rng = null;
App = null;
Sheet = null;
GC.Collect();
}
private void Dispose(bool disposing)
{
if (disposing)
{
// Dispose managed resources.
Marshal.FinalReleaseComObject(m_objExcel);
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBooks = null;
m_objBook = null;
m_objExcel = null;
}
}
private void ExportExcel(DataTable dt)
{
if (dt == null)
{
return;
}
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = transColumnName(dt.Columns[i].ColumnName);
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
xlApp.Visible = true;
}
/// <summary>
/// 新建一个Excel实例
/// </summary>
/// <param name="strTitle">Excel表头上的文字</param>
public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle)
{
m_objExcel = new Excel.ApplicationClass();
m_objExcel.Visible = true;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
if (intSheetTotalSize <= 3)
{
if (this.dbTotalSize <= this.dbSheetSize)
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
return;
}
else if (this.dbTotalSize <= this.dbSheetSize * 2)
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
return;
}
else
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle);
return;
}
}
for (int i = 3; i < intSheetTotalSize; i++)
{
m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
}
ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
for (int i = 2; i <= m_objSheets.Count; i++)
{
ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle);
}
}
static void Main(string[] args)
{
Console.WriteLine("Interop Performance Test - 10.000 Cells.");
/*
* start excel and disable messageboxes and screen updating
*/
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.ScreenUpdating = false;
//excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);
/*
* create new empty worksheet
*/
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook book = books.Add(Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
/*
* do the test
* we collect all references and release after time recording
* the 2 latebind libs release the references at end automaticly and we want a fair test
*/
List <object> comReferenesList = new List <object>();
DateTime timeStart = DateTime.Now;
for (int i = 1; i <= 10000; i++)
{
string rangeAdress = "$A" + i.ToString();
Range cellRange = (Range)sheet.Range[rangeAdress];
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = false;
Comment sampleComment = cellRange.AddComment("Sample Comment");
comReferenesList.Add(cellRange);
comReferenesList.Add(sampleComment);
}
DateTime timeEnd = DateTime.Now;
TimeSpan timeElapsed = timeEnd - timeStart;
foreach (var item in comReferenesList)
{
Marshal.ReleaseComObject(item);
}
/*
* display for user
*/
string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);
Console.WriteLine(outputConsole);
Console.Read();
/*
* write result in logfile
*/
string logFile = Path.Combine(Environment.CurrentDirectory, "Interop.log");
string logFileAppend = timeElapsed.ToString() + Environment.NewLine;
File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);
/*
* release & quit
*/
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
}
/// <summary>
/// 将datatable导出到excel
/// </summary>
/// <param name="dt"></param>
/// <param name="saveasfilepath"></param>
/// <param name="WorkbookName"></param>
public void ExportExcel(System.Data.DataTable dt, string saveasfilepath, string WorkbookName)
{
if (dt == null)
{
return;
}
string saveFileName = saveasfilepath; // "d:\\333.xlsx";
bool fileSaved = false;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的机子未安装Excel");
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
if (!string.IsNullOrEmpty(WorkbookName))
{
Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
ws.Name = WorkbookName;
}
Excel.Range range;
//string oldCaption = DateTime.Today.ToString("yy-MM-dd");
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
// worksheet.Cells[1, 1] = DateTime.Today.ToString("yy-MM-dd");
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
//this.lbl_process.Text = "正在导出数据[" + percent.ToString("0.00") + "%]..."; //这里可以自己做一个label用来显示进度.
System.Windows.Forms.Application.DoEvents();
}
//this.lbl_process.Visible = false;
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
if (saveFileName != "")
{
try
{
//xlApp.Visible = true;
//workbook.PrintPreview(false);
workbook.Close(true, saveFileName, Type.Missing);
workbook = null;
//SaveAs方法会截断超过255个字符的字符串
//workbook.Saved = true;
//workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel7, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing,
// Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing);
//fileSaved = true;
}
catch (Exception ex)
{
xlApp.Quit();
GC.Collect();
fileSaved = false;
throw new Exception("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
xlApp = null;
GC.Collect();//强行销毁
if (fileSaved && File.Exists(saveFileName))
{
//System.Diagnostics.Process.Start(saveFileName);
}
}
/// <summary>
/// 把DataTable中的数据导出到Excel
/// </summary>
/// <param name="dt"></param>
public static void DataTableToExcel(System.Data.DataTable srcDt, string savename)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt = srcDt;
if (dt == null)
{
return;
}
string saveFileName = "";
bool fileSaved = false;
/*SaveFileDialog saveDialog = new SaveFileDialog();
* saveDialog.DefaultExt = "xlsx";
* saveDialog.Filter = "Excel文件|*.xlsx";
* saveDialog.FileName = "导出文件";
* saveDialog.ShowDialog();
* saveFileName = saveDialog.FileName;
* if (saveFileName.IndexOf(":") < 0) return; //被点了取消 */
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth = 40;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight = 50;
//worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
for (int i = 2; i < 12; i++)
{
if (dt.Rows[i - 2][3].ToString() == "E")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3;
}
if (dt.Rows[i - 2][3].ToString() == "W")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6;
}
}
//写入字段
worksheet.Cells[1, 1] = "参数名称";
worksheet.Cells[1, 2] = "参数值";
worksheet.Cells[1, 3] = "推荐值";
worksheet.Cells[1, 4] = "报警值";
worksheet.Cells[1, 5] = "详情";
worksheet.Cells[1, 6] = "参数类型";
worksheet.Cells[1, 7] = "重要级别";
worksheet.Cells[1, 8] = "历史变化";
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
//worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
/*string md5 = "";
* for (int r = 0; r < 5; r++)
* {
* for (int i = 0; i < 8; i++)
* {
* md5 += dt.Rows[r][i].ToString();
* worksheet.Cells[2, 9] = md5;
* }
*
* }
*/
//worksheet.Cells.Width = 39;
//if (saveFileName != "")
//{
try
{
workbook.Saved = true;
saveFileName = savename;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
MessageBox.Show("导出完成！");
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
//}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
}
public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
{
//文件存在时先删除文件后再进行下一步操作
FileInfo file = new FileInfo(strFullFilePath);
if (file.Exists)
{
file.Delete();
}
int rowIndex = 3; //开始写入数据的单元格行
int colIndex = 0; //开始写入数据的单元格列
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.ApplicationClass mExcel = new Excel.ApplicationClass();
mExcel.Visible = false;
Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
Excel.Workbook mBook = (Excel.Workbook)(mBooks.Add(miss));
Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
er.Value2 = title;
try
{
foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
{
colIndex++;
mSheet.Cells[3, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows) //同样方法处理数据
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
if (colIndex == 2)
{
mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
}
else
{
mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//保存工作已写入数据的工作表
mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
// return true;
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
finally //finally中的代码主要用来释放内存和中止进程()
{
mBook.Close(false, miss, miss);
mBooks.Close();
mExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
GC.Collect();
}
//return false;
}
public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(filePath));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex));
_excelSheet.Name = sheetName;
}
public void OpenExcelApp(bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
}
public static string ProcesarArchivo(string pathArchivo)
{
string msgResul = "Migración realizada correctamente.";
ImportadorExcelBE infoImportar = new ImportadorExcelBE();
UIUtility.COMObjectAplication ObjCom = null;
Excel.Application xlApp = null;
try
{
ObjCom = new UIUtility.COMObjectAplication("Excel.Application", "EXCEL");
xlApp = (Excel.Application)ObjCom.ObjetoAplication;
Excel.Workbooks oBooks = xlApp.Workbooks;
oBooks.Open(pathArchivo, ReadOnly: true, IgnoreReadOnlyRecommended: true);
Excel.Workbook xlLibro = oBooks.Item[1];
Excel.Sheets oSheets = xlLibro.Worksheets;
Excel.Worksheet hojaConfig = (Excel.Worksheet)oSheets.Item[1];
DataTable dtConfig = ObtenerConfigPrincipal(infoImportar, hojaConfig);
int indiceHojaParaMigrar;
Excel.Worksheet hojaParaMigrar;
foreach (DataRow infoTabla in dtConfig.Rows)
{
indiceHojaParaMigrar = ObtenerIndiceHojaExcel(oSheets, infoTabla["Pestania"].ToString());
if (indiceHojaParaMigrar > 0)
{
hojaParaMigrar = (Excel.Worksheet)oSheets.Item[indiceHojaParaMigrar];
DataTable dtDefTablas = null;
dtDefTablas = ObtenerConfigColumnasSegunTabla(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());
DataTable dtDatos = null;
dtDatos = ObtenerRegistrosTablaSegunPestania(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());
}
else
{
throw new ImportadorExcelExceptionBE("No se ha encontrado la Hoja Excel: " + infoTabla["Pestania"].ToString());
}
}
ImportadorExcelBM objImportadorBM = new ImportadorExcelBM();
objImportadorBM.GuardarImportacion(infoImportar);
}
catch (Exception ex)
{
if (xlApp != null)
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
msgResul = ex.Message;
}
finally
{
if (ObjCom != null)
{
ObjCom.terminarProceso();
}
}
return(msgResul); // Si es "" (VACIO) entonces es PROCESO OK
}
public void Create()//创建一个Excel对象
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
private void ExportUpEmployee()
{
string strSql = @"select GetOrgName(GetStationOrgID(b.org_Id)) UnitName,
getworkshopname(b.org_id) WorkShopName,
case when c.level_num=4 then c.Short_Name else null end WorkGroupName,
Employee_Name, Identity_CardNo,
e.Train_Plan_Name,
d.Class_Name,f.Post_Name
from zj_train_plan_employee t
inner join Employee b on t.Employee_ID=b.Employee_ID
inner join Org c on b.Org_ID=c.Org_ID
inner join ZJ_Train_Plan_Post_Class d on t.Train_Plan_Post_Class_ID=d.Train_Plan_Post_Class_ID
inner join ZJ_Train_Plan e on d.Train_Plan_ID=e.Train_Plan_ID
inner join Post f on b.Post_ID = f.Post_ID
where t.Train_Plan_Post_Class_Org_ID=" + Request.QueryString.Get("classOrgID");
OracleAccess db = new OracleAccess();
DataSet ds = db.RunSqlDataSet(strSql);
if (ds.Tables[0].Rows.Count == 0)
{
Response.Write("<script>alert('没有已上报的员工信息！');window.close();</script>");
return;
}
string templateFileName = Server.MapPath("/RailExamBao/RandomExam/ProgressBar.htm");
StreamReader reader = new StreamReader(@templateFileName, System.Text.Encoding.GetEncoding("gb2312"));
string html = reader.ReadToEnd();
reader.Close();
Response.Write(html);
Response.Flush();
System.Threading.Thread.Sleep(200);
string jsBlock;
Excel.Application objApp = new Excel.ApplicationClass();
Excel.Workbooks objbooks = objApp.Workbooks;
Excel.Workbook objbook = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet objSheet = (Excel.Worksheet)objbook.Worksheets[1]; //取得sheet1
string filename = "";
string strName = "";
string strFileName = "";
try
{
int count = ds.Tables[0].Rows.Count;
strName = "Excel";
strFileName = ds.Tables[0].Rows[0]["UnitName"].ToString() + ds.Tables[0].Rows[0]["Class_Name"].ToString() + "上报人员";
filename = Server.MapPath("/RailExamBao/Excel/" + strName + ".xls");
objSheet.Cells.Font.Size = 10;
objSheet.Cells.Font.Name = "宋体";
int colIndex = 1;
objSheet.Cells[1, colIndex] = "序号";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "站段";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "车间";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "班组";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "姓名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "身份证号";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "职名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "上报培训计划名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "上报计划培训班名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(1 * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
int rowIndex = 2;
foreach (DataRow dr in ds.Tables[0].Rows)
{
colIndex = 1;
objSheet.Cells[rowIndex, colIndex] = rowIndex - 1;
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["UnitName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["WorkShopName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["WorkGroupName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Employee_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = "'" + dr["Identity_CardNo"];
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Post_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Train_Plan_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Class_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(rowIndex * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
rowIndex++;
}
objSheet.Cells.Columns.AutoFit();
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
// 处理完成
jsBlock = "<script>SetCompleted('处理完成。'); </script>";
Response.Write(jsBlock);
Response.Flush();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objbook.Close(Type.Missing, filename, Type.Missing);
objbooks.Close();
objApp.Application.Workbooks.Close();
objApp.Application.Quit();
objApp.Quit();
GC.Collect();
}
Response.Write("<script>top.returnValue='" + strName + "|" + strFileName + "';window.close();</script>");
}