/// <summary> /// 关闭Excel /// </summary> public void Close() { myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); }
private void QuitExcel(ref Excel.Application eps, ref Workbook wkCur) { try { if (wkCur != null) { wkCur.Save(); wkCur.Close(false); } //无论是否成功,都退出 if (eps != null) { PrintLog("Convert Excel file To PDF end"); eps.Quit(); } } catch (Exception ex) { PrintLog(ex.Message.ToString()); //m_nConvertStatus = EErrorType.OTP_EXCEPTION_FAILED; } finally { eps = null; wkCur = null; } }
/// <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文件操作失败"); } }
private void btnTest2_Click(object sender, EventArgs e) { Excel.Application m_excel; Excel.Workbook m_workbook; m_excel = new Excel.Application();//对象实例化 m_workbook = m_excel.Workbooks.Open("D:\\test.xlsx", 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); //m_workbook = m_excel.Workbooks.Open("D:\\test.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); m_excel.Visible = true;//显示Excel内容(为false时,excel.exe进程在后台运行,未找到关闭方法) Range rng; object obj; String str; rng = (Excel.Range)m_excel.Cells[2, 2]; obj = rng.Value2; System.Diagnostics.Debug.WriteLine(obj.ToString()); //输出调试信息 str = rng.NumberFormatLocal; System.Diagnostics.Debug.WriteLine(str); //输出调试信息 this.txtTest2.Text = obj.ToString(); m_excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框 m_excel.AlertBeforeOverwriting = false; m_excel.Workbooks.Close(); //object oV = System.Reflection.Missing.Value; //反复用到 //System.Runtime.InteropServices.Marshal.ReleaseComObject(rng); //System.Runtime.InteropServices.Marshal.ReleaseComObject(m_excel); //GC.Collect(); m_excel.Quit(); }
public void CreateExcel(IDictionary <string, System.Data.DataTable> iDicDT, string FilePath, bool Visible) { Excel.Application excel = new Excel.Application(); //引用Excel对象 Excel.Workbook book = excel.Application.Workbooks.Add(System.Reflection.Missing.Value); //引用 Excel.Worksheet sheet3 = (Excel.Worksheet)book.Worksheets["Sheet3"];//选中 object nothing = System.Reflection.Missing.Value; book.Worksheets.Add(nothing, sheet3, iDicDT.Count - 3, nothing); int index = 0; foreach (KeyValuePair <string, System.Data.DataTable> val in iDicDT) { index++; Excel.Worksheet tempExcel = (Excel.Worksheet)book.Worksheets["Sheet" + index];//选中 tempExcel.Name = val.Key; System.Data.DataTable tempdt = val.Value; for (int i = 0; i < tempdt.Columns.Count; i++) { tempExcel.Cells[1, i + 1] = tempdt.Columns[i].ColumnName; } for (int i = 0; i < tempdt.Rows.Count; i++) { for (int j = 0; j < tempdt.Columns.Count; j++) { try { tempExcel.Cells[i + 2, j + 1] = tempdt.Rows[i][j].ToString(); } catch { } } } } //保存 if (!string.IsNullOrEmpty(FilePath)) { SaveExcelFile(excel, FilePath); } //打开显示 excel.Visible = Visible; excel.Quit(); excel = null; GC.Collect();//垃圾回收 }
//关闭Excel public void CloseExcel() { wb.Close(); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; GC.Collect(); }
//关闭一个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(); }
private void createExcel() { Excel.Application excelApp = null; Excel.Workbook workbook = null; Excel.Sheets sheets = null; Excel.Worksheet newSheet = null; try { FileInfo file = new FileInfo(fileLoc); if (file.Exists) { excelApp = new Excel.Application(); workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); sheets = workbook.Sheets; //check columns exist foreach (Excel.Worksheet sheet in sheets) { Console.WriteLine(sheet.Name); sheet.Select(Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); } newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); newSheet.Name = "My New Sheet"; newSheet.Cells[1, 1] = "BOO!"; workbook.Save(); workbook.Close(null, null, null); excelApp.Quit(); } } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); newSheet = null; sheets = null; workbook = null; excelApp = null; GC.Collect(); } }
/// <summary> /// 直接创建Excel表 /// </summary> /// <param name="dtSource">数据源DataTable</param> /// <param name="filePath">保存路径</param> /// <param name="drTitle">标题DataRow</param> /// <param name="TableName">Excel表头</param> /// <returns></returns> public static bool Save(System.Data.DataTable dtSource, string filePath, System.Data.DataRow drTitle, string TableName) { Excel.Application app = new Excel.Application();//EXCEL实例 try { app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; if (dtSource.Rows.Count > 0) { int row = dtSource.Rows.Count + 1; int col = dtSource.Columns.Count + 1; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = dtSource.Rows[i][j].ToString(); wSheet.Cells[i + 3, j + 1] = str; } } } int size = dtSource.Columns.Count; for (int i = 0; i < size; i++) { wSheet.Cells[2, 1 + i] = drTitle[i].ToString(); } wSheet.get_Range(wSheet.Cells[1, 0], wSheet.Cells[1, size]).Merge(Type.Missing); wSheet.Cells[1, 1] = TableName; //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(filePath); app.SaveWorkspace(filePath); app.Quit(); app = null; return(true); } catch { return(false); } finally { } }
/// <summary> /// 关闭当前Excel文档 /// </summary> public void CloseControl() { if (workbook != null) { deactivateevents = true; object dummy = null; object dummyFalse = (object)false; ((Excel._Workbook)workbook).Close(dummy, dummy, dummy); xlApp.Quit(); Kill(); deactivateevents = false; } }
//字母-〉列号 Z-〉26 //public void SaveAsExcel() //{ // if (xlsSaveFileName == "") // { // throw new Exception("未指定要保存的文件名"); // } // try // { // xlsApp.Visible = true; // xlsWb.PrintPreview(false); // xlsWb = null; // xlsApp.Quit(); // xlsApp = null; // //xlsWs.SaveAs(xlsSaveFileName, Excel.XlFileFormat.xlExcel7, // // Type.Missing, Type.Missing, // // Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, // // Type.Missing, Type.Missing); // } // catch (Exception e) // { // CloseExcelApplication(); // throw new Exception(e.Message); // } //} public void SaveAsExcel(bool IsView) { try { if (File.Exists(xlsSaveFileName)) { File.Delete(xlsSaveFileName); } if (IsView) { xlsApp.Visible = true; xlsWb.PrintPreview(false); } xlsWb.Close(true, xlsSaveFileName, Type.Missing); xlsWb = null; xlsApp.Quit(); xlsApp = null; } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } }
public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath) { Excel.Application app = new Excel.Application();//对象实例 try { app.Visible = true; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; if (excelTable.Rows.Count > 0) { int row = 0; row = excelTable.Rows.Count; int col = excelTable.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = excelTable.Rows[i][j].ToString(); wSheet.Cells[i + 2, j + 1] = str; } } } int size = excelTable.Columns.Count; for (int i = 0; i < size; i++) { wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName; } app.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框 app.AlertBeforeOverwriting = false; wBook.SaveAs(filePath); //保存工作簿(当不指定FileFormat时,保存为默认格式,新建的文档默认格式为xlsx,即使filePath后缀为xls,内部数据格式仍为xlsx) wBook.Close(); //关闭工作簿 //app.Save(filePath);//保存excel文件 //app.SaveWorkspace(filePath); app.Quit(); return(true); } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return(false); } }
/// <summary> /// 关闭当前Excel文档 /// </summary> public void CloseControl() { if (workbook != null) { deactivateevents = true; object dummy = null; object dummyFalse = (object)false; xlApp.WorkbookBeforeClose -= new Excel.AppEvents_WorkbookBeforeCloseEventHandler(OnClose); xlApp.NewWorkbook -= new Excel.AppEvents_NewWorkbookEventHandler(OnNewXls); xlApp.WorkbookOpen -= new Excel.AppEvents_WorkbookOpenEventHandler(OnOpenXls); ((Excel._Workbook)workbook).Close(dummyFalse, dummy, dummy); xlApp.Quit(); Kill(); deactivateevents = false; workbook = null; } }
//Exports a DataView to Excel. The following steps are carried out //in order to export the DataView to Excel //Create Excel Objects And Open Template File //Select All Used Cells //Create Headers/Footers //Set Status Finished //Save workbook & Tidy up all objects //@param path : The path to save/open the EXCEL file to/from public void UseTemplate(string path, string templatePath, string[,] myTemplateValues) { try { this.myTemplateValues = myTemplateValues; //create new EXCEL application EXL = new Excel.ApplicationClass(); //Yes file exists, so open the file workbook = EXL.Workbooks.Open(templatePath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //get the workbook sheets collection sheets = workbook.Sheets; //get the new sheet worksheet = (Worksheet)sheets.get_Item(1); //Change its name to that requested worksheet.Name = "ATemplate"; //Fills the Excel Template File Selected With A 2D Test Array fillTemplate_WithTestValues(); //Select all used cells within current worksheet SelectAllUsedCells(); try { workbook.Close(true, path, Type.Missing); EXL.UserControl = false; EXL.Quit(); EXL = null; //kill the EXCEL process as a safety measure killExcel(); // Show that processing is finished ProgressEventArgs pe = new ProgressEventArgs(100); OnProgressChange(pe); MessageBox.Show("Finished adding test values to Template", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (COMException) { Console.WriteLine("User closed Excel manually, so we don't have to do that"); } } catch (Exception ex) { MessageBox.Show("Error " + ex.Message); } }
public static System.Data.DataTable LoadDataFromExcel(string filePath) { Excel.Application m_excel; Excel.Workbook m_workbook; Excel.Worksheet worksheet; m_excel = new Excel.Application();//对象实例化 m_workbook = m_excel.Workbooks.Open(filePath, 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); worksheet = (Worksheet)m_workbook.Worksheets[1]; m_excel.Visible = true;//显示Excel内容(为false时,excel.exe进程在后台运行,未找到关闭方法) int rowCount = worksheet.UsedRange.Rows.Count; int colCount = worksheet.UsedRange.Columns.Count; Excel.Range range1; System.Data.DataTable dt = new System.Data.DataTable(); for (int i = 0; i < colCount; i++) { //range1 = worksheet.get_Range(worksheet.Cells[1, i + 1], worksheet.Cells[1, i + 1]); range1 = (Excel.Range)m_excel.Cells[1, i + 1]; dt.Columns.Add(range1.Value2.ToString()); } for (int j = 1; j < rowCount; j++) { DataRow dr = dt.NewRow(); for (int i = 0; i < colCount; i++) { //range1 = worksheet.get_Range(worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]); range1 = (Excel.Range)m_excel.Cells[j + 1, i + 1]; dr[i] = range1.Value2.ToString(); } dt.Rows.Add(dr); } m_excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框 m_excel.AlertBeforeOverwriting = false; m_excel.Workbooks.Close(); m_excel.Quit(); return(dt); }
public static bool SaveDataToExcel(System.Data.DataTable dt, string filePath) //将System.Data.DataTable内容存储到指定路径的Excel中(存储默认格式xlsx) { Excel.Application excel = new Excel.Application(); //对象实例化; try { excel.Visible = false;//不显示Excel内容 Workbook workbook = excel.Workbooks.Add(true); Worksheet worksheet = workbook.Worksheets[1] as Worksheet; if (dt.Rows.Count > 0) { int row = row = dt.Rows.Count; int col = dt.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = dt.Rows[i][j].ToString(); worksheet.Cells[i + 2, j + 1] = str; } } } int size = dt.Columns.Count; for (int i = 0; i < size; i++) { worksheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName; } excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框 excel.AlertBeforeOverwriting = false; workbook.SaveAs(filePath); //保存工作簿(当不指定FileFormat时,保存为默认格式,新建的文档默认格式为xlsx,即使filePath后缀为xls,内部数据格式仍为xlsx) workbook.Close(); //关闭工作簿 excel.Quit(); //退出Excel程序 return(true); } catch (Exception ex)//导出Excel出错 { //throw new Exception(ex.Message); //MessageBox.Show(ex.Message); return(false); } }
public static System.Data.DataTable LoadDataFromExcel(string filePath) //从指定文件路径中的Excel读取所有内容到System.Data.DataTable(xls和xlsx格式均可以打开) { Excel.Application excel = new Excel.Application(); //对象实例化; try { Excel.Workbook workbook = excel.Workbooks.Open(filePath, 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); Excel.Worksheet worksheet = (Worksheet)workbook.Worksheets[1]; excel.Visible = false; //不显示Excel内容 int rowCount = worksheet.UsedRange.Rows.Count; //定义Excel文件中的行数 int colCount = worksheet.UsedRange.Columns.Count; //定义Excel文件中的列数 Excel.Range range; System.Data.DataTable dt = new System.Data.DataTable(); for (int i = 0; i < colCount; i++)//循环列 { range = (Excel.Range)excel.Cells[1, i + 1]; dt.Columns.Add(range.Value2.ToString()); } for (int j = 1; j < rowCount; j++)//循环行 { DataRow dr = dt.NewRow(); for (int i = 0; i < colCount; i++) { range = (Excel.Range)excel.Cells[j + 1, i + 1]; dr[i] = range.Value2.ToString(); } dt.Rows.Add(dr); } excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框 excel.AlertBeforeOverwriting = false; excel.Workbooks.Close(); //关闭工作簿 excel.Quit(); //退出Excel程序 return(dt); } catch (Exception ex) { //throw new Exception(ex.Message); //MessageBox.Show(ex.Message); return(null); } }
public static void Main() { Excel.Application ex = new Excel.Application(); ex.Visible = true; Excel.Workbook wb = ex.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Range rg = ex.ActiveCell; rg.set_Item(1, 1, "Competência"); rg.set_Item(1, 2, "Nota"); rg.set_Item(2, 1, "C++"); rg.set_Item(2, 2, "5"); rg.set_Item(3, 1, "UML"); rg.set_Item(3, 2, "6"); rg.set_Item(4, 1, "OOP"); rg.set_Item(4, 2, "5"); rg.set_Item(5, 1, "DB"); rg.set_Item(5, 2, "9"); rg.set_Item(6, 1, ".NET"); rg.set_Item(6, 2, "4"); rg.set_Item(7, 1, "XML"); rg.set_Item(7, 2, "4"); rg.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic3, true, true, true, true, true, true); Excel.Chart ch = (Excel.Chart)ex.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); ch.ChartType = Excel.XlChartType.xlRadarFilled; Excel.Worksheet ws = (Excel.Worksheet)ex.Worksheets["Plan1"]; Excel.Range lrg = ws.get_Range("A1", "B7"); ch.SetSourceData(lrg, Excel.XlRowCol.xlColumns); ch.Location(Excel.XlChartLocation.xlLocationAsObject, "Plan1"); ex.ActiveChart.HasLegend = false; ex.ActiveChart.HasTitle = true; ex.ActiveChart.ChartTitle.Text = "Gráfico de Competência"; Console.Write("Pressionar ENTER para sair do EXCEL"); Console.ReadLine(); ex.Quit(); }
/// <summary> /// Excel转PDF /// </summary> /// <param name="wpsFilename">文件名</param> /// <param name="pdfFilename"></param> public void ExcelToPdf(string wpsFilename, string pdfFilename = null) { Excel.Application eps = new Excel.Application(); try { if (wpsFilename == null) { throw new ArgumentNullException("wpsFilename"); } //保存路径为空时,保存在原始文件目录 if (pdfFilename == null) { pdfFilename = Path.ChangeExtension(wpsFilename, "pdf"); } //忽略警告提示 eps.Visible = false; eps.DisplayAlerts = false; eps.Workbooks.Open(wpsFilename); eps.ActiveWorkbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, pdfFilename, null, null, true); eps.ActiveWorkbook.Close(); } catch (Exception ex) { } finally { //无论是否成功,都退出 if (eps != null) { eps.Quit(); //强制关闭所有et进程 System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("EXCEL"); foreach (System.Diagnostics.Process prtemp in process) { prtemp.Kill(); } } } }
private void button1_Click(object sender, EventArgs e) { saveFileDialog1.Title = "序列号生成工具----百思威科技"; saveFileDialog1.Filter = "Excel(*.xls)|*.xls"; saveFileDialog1.FileName = string.Format("序列号_{0}", DateTime.Now.ToString("yyyyMMdd")); DialogResult result = saveFileDialog1.ShowDialog(); Excel._Application xlapp = new Excel.Application(); Excel.Workbook xlbook = xlapp.Workbooks.Add(true); Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1]; int RowCount = Convert.ToInt32(len.Text); int RowIndex = 0; for (int i = 0; i < RowCount; i++) { RowIndex++; xlsheet.Cells[RowIndex, 1] = "SN:" + (Convert.ToInt32(sarial.Text) + i).ToString(); } xlbook.Saved = true; xlbook.SaveCopyAs(saveFileDialog1.FileName); xlapp.Quit(); MessageBox.Show("导出成功!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public static void Main(string[] argv) { string command = argv[0]; ArrayList filenames = new ArrayList(); for (int i = 1; i < argv.Length; i++) { string excelname = argv[i]; if (!System.IO.Path.IsPathRooted(excelname)) { string cwd = System.Environment.CurrentDirectory; excelname = System.IO.Path.Combine(cwd, excelname); } filenames.Add(excelname); } Excel.Application app = new Excel.Application(); doit(app, filenames, command); app.Quit(); }
public void ExportData(DataGridView srcDgv, string fileName) //导出数据,传入一个datagridview和一个文件路径 { string type = fileName.Substring(fileName.IndexOf(".") + 1); //获得数据类型 if (type.Equals("xls", StringComparison.CurrentCultureIgnoreCase)) //Excel文档 { Excel.Application excel = new Excel.Application(); try { excel.DisplayAlerts = false; excel.Workbooks.Add(true); excel.Visible = false; for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题 { excel.Cells[2, i + 1] = srcDgv.Columns[i].HeaderText; } for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据 { for (int j = 0; j < srcDgv.Columns.Count; j++) { if (srcDgv[j, i].ValueType.ToString() == "System.Byte[]") { excel.Cells[i + 3, j + 1] = "System.Byte[]"; } else { excel.Cells[i + 3, j + 1] = srcDgv[j, i].Value; } } } excel.Workbooks[1].SaveCopyAs(fileName);//保存 } finally { excel.Quit(); } return; } //保存Word文件 if (type.Equals("doc", StringComparison.CurrentCultureIgnoreCase)) { object path = fileName; Object none = System.Reflection.Missing.Value; Word.Application wordApp = new Word.Application(); Word.Document document = wordApp.Documents.Add(ref none, ref none, ref none, ref none); //建立表格 Word.Table table = document.Tables.Add(document.Paragraphs.Last.Range, srcDgv.Rows.Count + 1, srcDgv.Columns.Count, ref none, ref none); try { for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题 { table.Cell(1, i + 1).Range.Text = srcDgv.Columns[i].HeaderText; } for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据 { for (int j = 0; j < srcDgv.Columns.Count; j++) { string a = srcDgv[j, i].ValueType.ToString(); if (a == "System.Byte[]") { PictureBox pp = new PictureBox(); byte[] pic = (byte[])(srcDgv[j, i].Value); //将数据库中的图片转换成二进制流 MemoryStream ms = new MemoryStream(pic); //将字节数组存入到二进制流中 pp.Image = Image.FromStream(ms); //二进制流Image控件中显示 pp.Image.Save(@"C:\22.bmp"); //将图片存入到指定的路径 object aaa = table.Cell(i + 2, j + 1).Range; wordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter; wordApp.Selection.InlineShapes.AddPicture(@"C:\22.bmp", ref none, ref none, ref aaa); pp.Dispose(); } else { table.Cell(i + 2, j + 1).Range.Text = srcDgv[j, i].Value.ToString(); } } } document.SaveAs(ref path, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none); document.Close(ref none, ref none, ref none); if (File.Exists(@"C:\22.bmp")) { File.Delete(@"C:\22.bmp"); } } finally { wordApp.Quit(ref none, ref none, ref none); } } }
/// <summary> /// 读取并保存Excel中的信息到数据库中 /// </summary> private void ReadAndSaveExcelInfo() { //Workbook wBook = excel.Workbooks.Add(true); Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); //Worksheet wSheet = (Excel._Worksheet)wBook.ActiveSheet; //Worksheet worksheet = (Excel.Worksheet)wBook.ActiveSheet; app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; string strTmpTable, strTmpField, strTmpValue; string val1 = ""; string val2 = ""; string val3 = ""; string val4 = ""; string val5 = ""; string val6 = ""; string val7 = ""; string val8 = ""; string val9 = ""; string val10 = ""; string val11 = ""; int num = 0; Excel.Range range = worksheet.get_Range("A2", "P1000");//A2、O1000表示到Excel从第A列的第2行到第O列的第1000-1行 A2、P65535表示到Excel从第A列的第2行到第P列的第65535-1行 System.Array values = (System.Array)range.Formula; num = values.GetLength(0); for (int i = 1; i <= num; i++) { if (values.GetValue(i, 1).ToString().Trim() == "" || values.GetValue(i, 2).ToString().Trim() == "") { break; } else { val1 = values.GetValue(i, 1).ToString().Trim(); //记帐日期 val2 = values.GetValue(i, 2).ToString().Trim(); //订单号 val3 = values.GetValue(i, 3).ToString().Trim(); //订单行项目号 val4 = values.GetValue(i, 4).ToString().Trim(); //物料编码 val5 = values.GetValue(i, 5).ToString().Trim(); //物料名称 val6 = values.GetValue(i, 6).ToString().Trim(); //批号 val7 = values.GetValue(i, 7).ToString().Trim(); //收货数量 val8 = values.GetValue(i, 8).ToString().Trim(); //计量单位 val9 = values.GetValue(i, 9).ToString().Trim(); //工厂 val10 = values.GetValue(i, 10).ToString().Trim(); //库存地点 val11 = values.GetValue(i, 11).ToString().Trim(); //抬头文本 string strLRR = CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName(); string strLRD = CoreFS.SA06.CoreUserInfo.UserInfo.GetDepartment(); strTmpTable = "DT_SAP261"; strTmpField = "FS_WEIGHTNO,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_MATERIAL,FS_MATERIALNAME,FS_STOVENO," + "FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,FS_HEADER,FS_WEIGHTTYPE,FS_DRDW,FS_AUDITOR"; strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + val1 + "','" + val2 + "','" + val3 + "','" + val4 + "','" + val5 + "','" + val6 + "'," + val7 + ",'" + val9 + "','" + val10 + "','" + val11 + "'," + "'261',"; /* if ((CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC1001") || (CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC1002")) * { * strTmpValue += "'棒线厂一作',"; * } * if (CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC2001") * { * strTmpValue += "'棒线厂二作',"; * }*/ strTmpValue += "'" + strLRD + "','" + strLRR + "'"; CoreClientParam ccp = new CoreClientParam(); ccp.ServerName = "ygjzjl.base.QueryData"; ccp.MethodName = "insertDataInfo"; ccp.ServerParams = new object[] { strTmpTable, strTmpField, strTmpValue }; try { this.ExecuteNonQuery(ccp, CoreInvokeType.Internal); } catch (Exception ex1) { lstHint1.Items.Add(ex1.Message + "第" + i.ToString() + "行信息导入成功!"); } } } app.Quit(); app = null; lstHint1.Items.Add("信息导入成功!"); System.Diagnostics.Process[] myProcesses; myProcesses = System.Diagnostics.Process.GetProcesses(); foreach (System.Diagnostics.Process myProcess in myProcesses) { if (myProcess.ProcessName == "EXCEL") { myProcess.Kill(); } } }
/// <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 } }
/// <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 List <StatisticalGraph> BuildGraphList(string file, bool gif) { List <StatisticalGraph> sg_collection = new List <StatisticalGraph>(); Excel.Workbook xl_workbook; try { xl_workbook = this.xl_app.Workbooks.Open(file, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); } catch { return(null); /*returning the null list; handled in iGraphMain*/ } /** Loop over every worksheet and every ChartObject in the worksheet. * The ChartObject object acts as a container for a Chart object. * Properties and methods for the ChartObject object control the * appearance and size of the embedded chart on the worksheet, * see: http://msdn.microsoft.com/en-us/library/aa173258(office.11).aspx */ foreach (Worksheet sheet in xl_workbook.Worksheets) { foreach (ChartObject curr_chart in (ChartObjects)sheet.ChartObjects(Type.Missing)) { sg = new StatisticalGraph(); problem = false; Chart c = curr_chart.Chart; // c is the chart in ChartObject if ((int)c.ChartType == 68 || (int)c.ChartType == 71) { log.Warn("iGraph does not process pie charts... Skipping."); continue; } sg.Prologue = new GraphPrologue(file, "MS Excel", curr_chart.Index, sheet.Index, (int)c.ChartType, curr_chart.Height, curr_chart.Width); log.Debug("Graph ID: " + sg.Prologue.GetGraphName()); sg.PlotArea = GetXLPlotArea(c); sg.Textboxes = GetXLTextBoxes(c); sg.Series = GetXLSeries(c); sg.MainTitle = GetXLChartTitle(c); sg.ValueAxis = GetXLValueAxis(c); sg.CategoryAxis = GetXLCategoryAxis(c); // add to the returned statgraph collection if (!problem) { sg_collection.Add(sg); // if flagged, export graph as gif in same argument directory if (gif) { c.Export(sg.Prologue.GetGifName(), "GIF", false); log.Info("Saved " + sg.Prologue.GetGifName()); } } } } // Excel has to be closed. xl_app.Workbooks.Close(); xl_app.Quit(); // the return value if all goes well... return(sg_collection); }
private void button1_Click(object sender, EventArgs e) { string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = ""; int NumRows = 1000; int NumColumns = 150; int startsum = 0; int currow = 0; bool firstTimeSum = true; Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet = null; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (int row = 0; row < NumRows; row++) { for (int col = 0; col < NumColumns; col++) { if (row < 2) { xlWorkSheet.Cells[row + 1, col + 1] = new Random().Next(1, NumRows).ToString(); } else { if (firstTimeSum) { if (row - currow == 2) { currow = row; startsum = 0; firstTimeSum = false; } else { startsum = 1; } } else { if (row - currow == 3) { currow = row; startsum = 0; } } if (startsum == 0) { strColName = GenerateColumnText(col); strImmediateOneUp = strColName + ((row + 1) - 1).ToString(); strImmediateTwoUp = strColName + ((row + 1) - 2).ToString(); strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp); xlWorkSheet.Cells[row + 1, col + 1] = strSum; } else { xlWorkSheet.Cells[row + 1, col + 1] = new Random().Next(1, NumRows).ToString(); } } } startsum = 1; } if (System.IO.File.Exists(@"d:\pop.xls")) { System.IO.File.Delete(@"d:\pop.xls"); } xlWorkBook.SaveAs(@"d:\pop.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); xlApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); stopwatch.Stop(); TimeSpan timeSpan = stopwatch.Elapsed; MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds)); }
public void reportForm() { int rowCount = dataGridView1.Rows.Count; //获取dataGridView1的行数 int columnsCount = dataGridView1.Columns.Count; //获取dataGridView1的列数 //判断是否存在值 if (rowCount == 0) { MessageBox.Show("没有数据可供导出。。。", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } //定义个变量接收文件名字 string fileName = string.Empty; //new 一个对象用于提示用户选择保存文件位置 SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "导出Excel (*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; //筛选器的索引 saveFileDialog.RestoreDirectory = true; //对话框关闭前是否还原当前目录 saveFileDialog.CreatePrompt = true; //指定文件不存在,提示用户是否允许创建该文件 saveFileDialog.Title = "导出文件保存路径"; //设置对话框标题 //当前时间 DateTime now = DateTime.Now; saveFileDialog.FileName = now.Year.ToString().PadLeft(2) + now.Month.ToString().PadLeft(2, '0') + now.Day.ToString().PadLeft(2, '0') + "-" + now.Hour.ToString().PadLeft(2, '0') + now.Minute.ToString().PadLeft(2, '0') + now.Second.ToString().PadLeft(2, '0'); if (saveFileDialog.ShowDialog() == DialogResult.OK) { fileName = saveFileDialog.FileName; Missing miss = Missing.Value; Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = false;//若是true,则在导出的时候会显示EXcel界面。 if (excel == null) { MessageBox.Show("Excel无法启动", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } Workbooks workbooks = excel.Workbooks; Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet worksheet = (Worksheet)workbook.Worksheets[1]; //取得sheet1 //写入标题 for (int i = 0; i < dataGridView1.ColumnCount; i++) { worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; } #region 导出一页 ////写入数值 //for (int r = 0; r < dataGridView1.Rows.Count; r++) //{ // for (int i = 0; i < dataGridView1.ColumnCount; i++) // { // worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value; // } // System.Windows.Forms.Application.DoEvents(); //} #endregion ResultModelOfArrayOfModel_Waybill_Based4FqxSXX waybill = cs.GetWaybillList(waybillNumber, null, startTime, endTime, customerId, true, 3, true); if (waybill.Code != 0) { MessageBox.Show(waybill.Message); } else { //写入数值 for (int r = 0; r < waybill.Data.Length; r++) { //在字段前加单引号是为了防止自动转化格式,如057410007009导出后转化格式前面的0不会消失 worksheet.Cells[r + 2, 1] = "'" + waybill.Data[r].Numberk__BackingField; worksheet.Cells[r + 2, 2] = waybill.Data[r].ReceiverOrgk__BackingField; worksheet.Cells[r + 2, 3] = waybill.Data[r].ReceiverPersonk__BackingField; worksheet.Cells[r + 2, 4] = waybill.Data[r].ReceiverTelk__BackingField; worksheet.Cells[r + 2, 5] = waybill.Data[r].ReceiverAddressk__BackingField; worksheet.Cells[r + 2, 6] = waybill.Data[r].BeginAtk__BackingField; worksheet.Cells[r + 2, 7] = waybill.Data[r].BillingCountk__BackingField; System.Windows.Forms.Application.DoEvents(); } } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 if (fileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(fileName); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } } excel.Quit(); GC.Collect();//强行销毁 MessageBox.Show(fileName + "的简明资料保存成功", "提示", MessageBoxButtons.OK); } }
public void Export2DatasetDSPhongThi(System.Data.DataSet i_DataSet, string i_TableName, int i_iSheetStartRow) { try { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); m_objExcelApp = new Excel.Application(); m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null); m_objExcelApp.Workbooks[1].Worksheets.Select(1); m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]); int i_iExcelRow = 0; bool v_bol_stop = false; while (!v_bol_stop) { int i_iExcelCol = 0; System.Data.DataRow v_iDataRow = default(System.Data.DataRow); v_iDataRow = i_DataSet.Tables[i_DataSet.Tables[i_TableName].TableName].NewRow(); v_iDataRow[i_iExcelCol] = i_iExcelCol + 1; for (i_iExcelCol = 0; i_iExcelCol <= i_DataSet.Tables[i_TableName].Columns.Count - 2; i_iExcelCol++) { if (!object.ReferenceEquals(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, 4]/*.Value()*/, null)) { if (!(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/ == null)) { v_iDataRow[i_iExcelCol + 1] = m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/; } } else { v_bol_stop = true; } } if (!v_bol_stop) { i_DataSet.Tables[i_TableName].Rows.InsertAt(v_iDataRow, i_iExcelRow); i_iExcelRow++; } } m_objExcelApp.DisplayAlerts = false; m_objExcelApp.Workbooks.Close(); m_objExcelApp.DisplayAlerts = true; m_objExcelApp.Quit(); Unmount(); } catch (Exception v_e) { m_objExcelApp.DisplayAlerts = false; m_objExcelApp.Workbooks.Close(); m_objExcelApp.DisplayAlerts = true; m_objExcelApp.Quit(); Unmount(); throw (v_e); } }
/// <summary> /// 将DataTable的数据导出显示为报表 /// </summary> /// <param name="dt">要导出的数据</param> /// <param name="strTitle">导出报表的标题</param> /// <param name="FilePath">保存文件的路径</param> /// <returns></returns> public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath) { beforeTime = DateTime.Now; Excel.Application excel; Excel._Workbook xBk; Excel._Worksheet xSt; int rowIndex = 4; int colIndex = 1; excel = new Excel.Application(); xBk = excel.Workbooks.Add(true); xSt = (Excel._Worksheet)xBk.ActiveSheet; //取得列标题 foreach (DataColumn col in dt.Columns) { colIndex++; excel.Cells[4, colIndex] = col.ColumnName; //设置标题格式为居中对齐 xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true; xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select(); xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色,共计有56种 } //取得表格中的数据 foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 1; foreach (DataColumn col in dt.Columns) { colIndex++; if (col.DataType == System.Type.GetType("System.DateTime")) { excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐 } else if (col.DataType == System.Type.GetType("System.String")) { excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString(); xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 } else { excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } } } //加载一个合计行 int rowSum = rowIndex + 1; int colSum = 2; excel.Cells[rowSum, 2] = "合计"; xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //设置选中的部分的颜色 xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select(); //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色,共计有56种 //取得整个报表的标题 excel.Cells[2, 2] = strTitle; //设置整个报表的标题格式 xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true; xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22; //设置报表表格为最适应宽度 xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select(); xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit(); //设置整个报表的标题为跨列居中 xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select(); xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //绘制边框 xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1; xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗 xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗 xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗 xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗 afterTime = DateTime.Now; //显示效果 //excel.Visible=true; //excel.Sheets[0] = "sss"; ClearFile(FilePath); string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls"; excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); //wkbNew.SaveAs strBookName; //excel.Save(strExcelFileName); #region 结束Excel进程 //需要对Excel的DCOM对象进行配置:dcomcnfg //excel.Quit(); //excel=null; xBk.Close(null, null, null); excel.Workbooks.Close(); excel.Quit(); //注意:这里用到的所有Excel对象都要执行这个操作,否则结束不了Excel进程 // if(rng != null) // { // System.Runtime.InteropServices.Marshal.ReleaseComObject(rng); // rng = null; // } // if(tb != null) // { // System.Runtime.InteropServices.Marshal.ReleaseComObject(tb); // tb = null; // } if (xSt != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xSt = null; } if (xBk != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); xBk = null; } if (excel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } GC.Collect();//垃圾回收 #endregion return filename; }
/// <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中,并在服务器端AppData文件夹中生成xls文件 * </summary> * <param name="dt">要导出数据的DataTable</param> * <param name="head">题头数据</param> * <param name="absFileName">文件的绝对路径</param> * <returns></returns> */ public static void ExportToExcelDiagramfor9(System.Data.DataTable dt, string[] head, string absFileName) { //设置多少行为一个Sheet int RowsToDivideSheet = 65535; //计算Sheet数 int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1; GC.Collect(); string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径 string tempEFilePath = "\\TempFiles"; //EXCEL临时文件保存服务器物理存放路径 string tempEFileXPath = "/TempFiles"; //EXCEL临时文件保存服务器虚拟存放路径 Excel.Application excel = null; Excel._Workbook xBk = null; Excel._Worksheet xSt = null; Excel.Workbooks workbooks; //工作簿集合 Excel.Sheets sheets; //SHEET页集合 #region //读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称 excelTemplateDPath = System.Web.HttpContext.Current.Server.MapPath("~/moban"); tempEFileXPath = "~/moban" + tempEFileXPath; tempEFilePath = excelTemplateDPath + tempEFilePath; excelTemplateDPath += "\\TaskTemplet9.xls"; //验证EXCEL临时文件夹是否存在 if (!File.Exists(tempEFilePath)) { Directory.CreateDirectory(tempEFilePath); } #endregion try { #region //启动excel进程并加载模板 //启动EXCEL进程 excel = new Excel.Application(); excel.Visible = false; excel.UserControl = true; excel.DisplayAlerts = false; //加载读取模板 workbooks = excel.Workbooks; xBk = workbooks.Add(excelTemplateDPath); sheets = xBk.Worksheets; xSt = (Excel._Worksheet)sheets.get_Item(1); #endregion //循环中要使用的变量 int dvRowStart; int dvRowEnd; //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { //计算起始行 dvRowStart = sheetIndex * RowsToDivideSheet; dvRowEnd = dvRowStart + RowsToDivideSheet - 1; if (dvRowEnd > dt.Rows.Count - 1) { dvRowEnd = dt.Rows.Count - 1; } //创建一个Sheet if (null == xSt) { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName xSt.Name = "Excel"; if (sheetCount > 1) { xSt.Name += ((int)(sheetIndex + 1)).ToString(); } //题头导出 int rowCount = head.Length; int colCount = 1; object[,] dataArray = new object[colCount, rowCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[j, i] = head[i]; } } xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10; //数据导出 System.Data.DataTable SheetTable = new System.Data.DataTable(); foreach (DataColumn dc in dt.Columns) { DataColumn newdc = new DataColumn(); newdc.ColumnName = dc.ColumnName; newdc.DataType = dc.DataType; SheetTable.Columns.Add(newdc); } for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++) { SheetTable.ImportRow(dt.Rows[drvIndex]); } //保存数据 rowCount = SheetTable.Rows.Count; colCount = SheetTable.Columns.Count; object[,] dataArray1 = new object[rowCount, colCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray1[i, j] = SheetTable.Rows[i][j]; } } xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10; //单元格边框 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐 xSt.Columns.AutoFit(); //自适应宽度 //设置图标的标题 string Charttitle = dt.Rows[0][0].ToString(); } //删除Sheet1 excel.DisplayAlerts = false; //注意一定要加上这句 ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete(); excel.DisplayAlerts = true; //注意一定要加上这句 object objOpt = System.Reflection.Missing.Value; excel.Visible = false; xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } catch (Exception e) { //throw (e); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } }
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); }
public void Export2Grid(C1FlexGrid i_fg, int i_iSheetStartRow, int i_iSheetCol, int i_iGridCol) { try { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); m_objExcelApp = new Excel.Application(); m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null); m_objExcelApp.Workbooks[1].Worksheets.Select(1); m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]); int v_iGridRow = 0; for (v_iGridRow = i_fg.Rows.Fixed; v_iGridRow <= i_fg.Rows.Count - 1; v_iGridRow++) { i_fg[v_iGridRow, i_iGridCol] = m_objExcelWorksheet.Cells[i_iSheetStartRow + v_iGridRow - i_fg.Rows.Fixed, i_iSheetCol];//.Value } m_objExcelApp.Workbooks.Close(); m_objExcelApp.Quit(); Unmount(); } catch (Exception v_e) { m_objExcelApp.Workbooks.Close(); m_objExcelApp.Quit(); Unmount(); throw (v_e); } }