/// <summary> /// Закрыть документ /// </summary> /// <param name="save">C сохранением</param> public void CloseDocument(bool save) { if (_xlApp.ActiveWorkbook != null) { _xlWorkBook.Close(save, _misValue, _misValue); } _xlApp.Quit(); }
private void button16_Click(object sender, EventArgs e) { try { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; if (dgfess.Visible == true) { for (i = 0; i <= dgfess.RowCount - 1; i++) { for (j = 0; j <= dgfess.ColumnCount - 1; j++) { DataGridViewCell cell = dgfess[j, i]; xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } xlWorkBook.SaveAs("StudentFees.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); MessageBox.Show("Excel file created , you can find the file 'StudentFees.xls' in My Document", " Meera Academy"); } else if (dgstudent.Visible == true) { for (i = 0; i <= dgstudent.RowCount - 1; i++) { for (j = 0; j <= dgstudent.ColumnCount - 1; j++) { DataGridViewCell cell = dgstudent[j, i]; xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } xlWorkBook.SaveAs("studentDetail.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); MessageBox.Show("Excel file created , you can find the file 'StudentDetail.xls' in My Document", " Meera Academy"); } // releaseObject(xlWorkSheet); // releaseObject(xlWorkBook); //releaseObject(xlApp); } catch (Exception a) { MessageBox.Show(a.Message.ToString(), "Error !!"); } }
/// <summary> /// 把Excel文件转换成PDF格式文件 /// </summary> /// <param name="sourcePath">源文件路径</param> /// <param name="targetPath">目标文件路径</param> /// <returns>true=转换成功</returns> public bool XLSConvertToPDF(string sourcePath, string targetPath) { bool result = false; Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF; object missing = Type.Missing; Excel.ApplicationClass application = null; Excel.Workbook workBook = null; try { application = new Excel.ApplicationClass(); object target = targetPath; object type = targetType; workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); workBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); result = true; } catch { result = false; } finally { if (workBook != null) { workBook.Close(true, missing, missing); workBook = null; } if (application != null) { application.Quit(); application = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } if (workBook != null) { workBook.Close(); } if (application != null) { application.Quit(); } return(result); }
public void CreateFiles(bool state) { String[] sheets = GetExcelSheetNames(this.BookName); String str; Directory.CreateDirectory(this.IndexDirectoryPath + "/" + BookName); String divfilePath = this.IndexDirectoryPath + "/" + this.BookName + "/" + this.BookName + ".index"; CreateDivFileSheets(divfilePath); Excel.Application app = new Excel.ApplicationClass(); app.Visible = true; app.DisplayAlerts = false; Excel.Workbook xlWorkBook; if (state) { xlWorkBook = app.Workbooks.Open("D:\\ptw\\prefaceNPS.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); } else { xlWorkBook = app.Workbooks.Open("D:\\ptw\\prefaceNP.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); } for (int i = 0; i < sheets.Length; ++i) { str = sheets[i].Replace("#", "."); if (str.Remove(str.Length - 1, 1) == "Historique" || str.Remove(str.Length - 1, 1) == "Comptes annuels") { createindexFiles(str.Remove(str.Length - 1, 1), xlWorkBook, state); } } xlWorkBook.Close(); app.Quit(); }
/// <summary> /// 写Excel /// </summary> public void WriteExcel() { string currentDir = System.Environment.CurrentDirectory; string fileName = $"{currentDir}\\TestCaseTemplate.xlsx"; Excel.Application excelApp = new Excel.ApplicationClass(); Excel.Workbook workbook; if (System.IO.File.Exists(fileName)) { workbook = excelApp.Workbooks.Open(fileName, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); } else { workbook = excelApp.Workbooks.Add(true); } this.WriteInWorkSheet(workbook); excelApp.Visible = false; excelApp.DisplayAlerts = false; string saveDir = fileName.Replace("TestCaseTemplate.xlsx", $"TestCase_{DateTime.Now:yyyyMMddHHmmss}.xlsx"); OutputDisplay.ShowMessage($"文件保存路勁:{saveDir}\n", Color.Azure); workbook.SaveAs(saveDir); workbook.Close(false, Missing.Value, Missing.Value); excelApp.Quit(); workbook = null; excelApp = null; }
public void Should_read_value_repeatedly() { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); //xlApp.Visible = true; //xlApp.UserControl = true; string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls"); xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //xlWorkSheet.Change += new Excel.DocEvents_ChangeEventHandler(xlWorkSheet_Change); for (int i = 0; i < 100; i++) { Console.WriteLine(xlWorkSheet.get_Range("B23", "B23").Value2.ToString()); //xlWorkSheet. Thread.Sleep(1000); } xlWorkBook.Close(false, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
private bool ExcelToPDF(string sourcePath, string targetPath) { bool result; object missing = Type.Missing; Excel.ApplicationClass application = null; Excel.Workbook workBook = null; try { application = new Excel.ApplicationClass(); object target = targetPath; object type = Excel.XlFixedFormatType.xlTypePDF; workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); workBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); result = true; } catch { result = false; } finally { workBook.Close(true, missing, missing); application.Quit(); } return(result); }
// Read from the Excel workbook. private void btnRead_Click(object sender, EventArgs e) { // Get the Excel application object. Excel.Application excel_app = new Excel.ApplicationClass(); // Make Excel visible (optional). excel_app.Visible = true; // Open the workbook read-only. Excel.Workbook workbook = excel_app.Workbooks.Open(txtFile.Text, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Get the first worksheet. Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets[1]; // Get the titles and values. SetTitleAndListValues(sheet, 1, 1, lblTitle1, lstItems1); SetTitleAndListValues(sheet, 1, 2, lblTitle2, lstItems2); // Close the workbook without saving changes. workbook.Close(false, Type.Missing, Type.Missing); // Close the Excel server. excel_app.Quit(); }
/// <summary> /// 释放COM组件所占用的资源 /// </summary> public void ReleaseRescorce() { try { if (work != null || app != null) { work.Close(null, null, null); app.Workbooks.Close(); app.Quit(); if (dataSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(dataSheet); } if (work != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(work); } if (app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); } app = null; work = null; dataSheet = null; GC.Collect(); } } catch { } }
public static void CreateExcel(DataSet ds, string excelPath) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; try { xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++) { xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString(); } } xlWorkBook.SaveAs(excelPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlApp); releaseObject(xlWorkBook); releaseObject(xlWorkSheet); } catch (Exception ex) { throw ex; } }
/// <summary> /// 获取获得当前你选择的Excel Sheet的所有名字 /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static string[] GetExcelSheetNames(string filePath) { Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Workbooks wbs = excelApp.Workbooks; Workbook wb = wbs.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); int count = wb.Worksheets.Count; string[] names = new string[count]; for (int i = 1; i <= count; i++) { names[i - 1] = ((Worksheet)wb.Worksheets[i]).Name; } wb.Close(null, null, null); excelApp.Quit(); wbs.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs); excelApp = null; wbs = null; wb = null; return names; }
private void SaveFile_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; string str; int rCnt = 0; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open(this.fileName, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; //xlApp.Visible = true; for (rCnt = 2; rCnt <= Program.tweets.Count + 1; rCnt++) { Tweet t = (Tweet)Program.tweets[rCnt - 2]; (range.Cells[rCnt, 1] as Excel.Range).Value2 = t.ID; (range.Cells[rCnt, 2] as Excel.Range).Value2 = t.text; (range.Cells[rCnt, 3] as Excel.Range).Value2 = t.relevanceLabel; (range.Cells[rCnt, 4] as Excel.Range).Value2 = t.sentimentLabel; } xlWorkBook.Save(); xlWorkBook.Close(true, null, null); xlApp.Quit(); }
public void Should_detect_row_and_value_changed() { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls"); xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //xlWorkSheet.Activate(); xlWorkSheet.Change += new Excel.DocEvents_ChangeEventHandler(xlWorkSheet_Change); lock (this) { Monitor.Wait(this); } xlWorkBook.Close(false, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; Int16 i, j; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (i = 0; i <= grdTransakcii.RowCount - 2; i++) { for (j = 0; j <= grdTransakcii.ColumnCount - 1; j++) { xlWorkSheet.Cells[i + 1, j + 1] = grdTransakcii[j, i].Value.ToString(); } } xlWorkBook.SaveAs(@"E:\Fakturii.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); MessageBox.Show("Направен е export", "Export", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); }
private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; string str; int rCnt = 0; int cCnt = 0; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++) { str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2; MessageBox.Show(str); } } xlWorkBook.Close(true, null, null); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
public static void CloseExcelApplicationClass(Excel.ApplicationClass _ExcelApplication) { try { if (_ExcelApplication != null) { _ExcelApplication.Workbooks.Close(); _ExcelApplication.Quit(); } } finally { } try { //Close ComObject: if (_ExcelApplication.ActiveWorkbook != null) { Marshal.ReleaseComObject(_ExcelApplication.ActiveWorkbook); } } finally { } try { if (_ExcelApplication != null) { Marshal.ReleaseComObject(_ExcelApplication); } } finally { } }
public static void ExportToExcel(this DataTable dt, String fileName) { var xlApp = new Excel.ApplicationClass(); var xlWorkBook = xlApp.Workbooks.Open( fileName, 0, false, // for read/write 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0 ); var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[1]; var NumColumns = dt.Columns.Count; var rowCount = dt.Rows.Count; try { foreach (DataRow dr in dt.Rows) { for (int i = 1; i < NumColumns + 1; i++) { xlWorkSheet.Cells[rowCount, i] = dr[i - 1].ToString(); } rowCount += 1; } xlWorkBook.Save(); } catch (Exception) { throw; } finally { xlApp.Quit(); } }
private void button2_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (i = 0; i <= dataGridView1.RowCount - 1; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { DataGridViewCell cell = dataGridView1[j, i]; xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } xlWorkBook.SaveAs("csharp.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); MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls"); }
private void button2_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; Int16 i, j; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (i = 0; i <= dataGridView1.RowCount - 2; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString(); } } xlWorkBook.SaveAs(@"c:\csharp.net-informations.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); }
private void button3_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; Int16 i, j; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (i = 0; i <= dataGridView1.RowCount - 2; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString(); } } xlWorkBook.SaveAs(@"C:\Users\Soroush\Desktop\HospitalDatabaseOutputDrug.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); }
// //// Supprimer typologie dans "D:\\ptw\\Histo.ptw" // private void renommer() { Excel.Application xlApp; Excel.Workbook xlWorkBook; object misValue = System.Reflection.Missing.Value; //missing values, for example, when you invoke methods that have default parameter values. //remplace les paramètres par default des fonctions utilisés xlApp = new Excel.ApplicationClass(); xlApp.Visible = true; xlWorkBook = xlApp.Workbooks.Open(fichierprepare, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, true, false); //Afficher pas les Alerts !!non utiliser avant assurer!!! xlApp.DisplayAlerts = false; Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Historique"); //Excel.Worksheet sheetTypologie = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Typologie IFRS"); //sheetTypologie.Delete(); xlWorkSheet.SaveAs(prefaceNP, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); //kill WorkSheet releaseObject(xlWorkBook); //Kill WorkBook releaseObject(xlApp); //Kill Application Application }
/// <summary> /// this functio create a excel file with the values from a table /// </summary> public static string Export(string path, System.Data.DataTable dt) { string output = "OK"; try { //create a new instance of excel file Excel.ApplicationClass excel = new Excel.ApplicationClass(); excel.Application.Workbooks.Add(true); //first let's extract the columns and create a little header Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet; worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "@"; //worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "#"; worksheet.Activate(); int horizontal = 1; int vertical = 1; foreach (DataColumn dc in dt.Columns) { excel.Cells[vertical, horizontal] = dc.ColumnName; horizontal++; } //go to next row vertical++; foreach (DataRow row in dt.Rows) { horizontal = 1; foreach (DataColumn column in dt.Columns) { excel.Cells[vertical, horizontal] = row[column]; horizontal++; } vertical++; } //save the excel file worksheet.Name = "PROROGHE Non Lavorate"; //DirectoryInfo dr = new DirectoryInfo(path); //dr.Create(); FileInfo fi = new FileInfo(path); if (fi.Exists == true) fi.Delete(); worksheet.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing); excel.Workbooks.Close(); excel.Quit(); } catch (Exception ex) { output = ex.Message + "\n" + ex.StackTrace; } return output; }
static void Main(string[] args) { object path; MSExcel.Application excelApp; // Excel 应用程序变量 MSExcel.Workbook excelDoc; // Excel 文档变量 path = @"F:\xianfeng\MyExcel.xlsx"; excelApp = new MSExcel.ApplicationClass(); if (File.Exists((string)path)) { File.Delete((string)path); } //由于使用的是COM库,因此有许多变量需要用Nothing 代替 Object Nothing = Missing.Value; excelDoc = excelApp.Workbooks.Add(Nothing); //WdSaveFormat 为Excel 文档的保存格式 Object format = MSExcel.XlFileFormat.xlWorkbookDefault; //将excelDoc 文档对象的内容保存为xlsx文档 excelDoc.SaveAs(path, Nothing, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //关闭excelDoc 文档对象 excelDoc.Close(Nothing, Nothing, Nothing); //关闭excelApp 组件对象 excelApp.Quit(); Console.WriteLine(path + " 创建完毕"); }
private void supprimercol_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlApp.Visible = true; xlApp.DisplayAlerts = false; xlWorkBook = xlApp.Workbooks.Open(prefaceNP, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, true, false); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Historique-s"); Excel.Range range = xlWorkSheet.UsedRange; object[,] values = (object[, ])range.Value2; int time1 = System.Environment.TickCount; ////////////////////////////////////////400000////////////////////// int rCnt = 0; int cCnt = 0; int row400000 = 0; cCnt = range.Columns.Count; for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { string valuecellabs = Convert.ToString(values[rCnt, cCnt]); if (Regex.Equals(valuecellabs, "400000")) { row400000 = rCnt; break; } } for (int col = 1; col <= xlWorkSheet.UsedRange.Columns.Count; col++) { string value = Convert.ToString(values[row400000, col]); if (Regex.Equals(value, "-1")) { Excel.Range rangeDelx = xlWorkSheet.Cells[row400000, col] as Excel.Range; rangeDelx.EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft); range = xlWorkSheet.UsedRange; values = (object[, ])range.Value2; col--; } } xlWorkSheet.SaveAs(prefaceNP, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); xlApp.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); int time2 = System.Environment.TickCount; int times = time2 - time1; string tim = Convert.ToString(Convert.ToDecimal(times) / 1000); //MessageBox.Show("jobs done " + tim + " seconds used"); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
/// <summary> /// 获取Sheet1表的名称。 /// </summary> public string getSheetNameOfSheet1() { if (!File.Exists(_path)) { MessageBox.Show("此Excel不存在!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); return(""); } App = new Microsoft.Office.Interop.Excel.ApplicationClass(); App.Visible = false; try { WBook = App.Workbooks.Open(this._path, 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); } catch (Exception ex) { MessageBox.Show(ex.ToString()); if (WBook != null) { WBook.Close(true, _path, Type.Missing); } //退出excel App.Quit(); //释放资源 if (WBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(WBook); } System.Runtime.InteropServices.Marshal.ReleaseComObject(App); //调用GC的垃圾回收方法 GC.Collect(); GC.WaitForPendingFinalizers(); return(""); } string name = ((Worksheet)WBook.Sheets[1]).Name; WBook.Close(true, _path, Type.Missing); //退出excel App.Quit(); //释放资源 System.Runtime.InteropServices.Marshal.ReleaseComObject(WBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(App); //调用GC的垃圾回收方法 GC.Collect(); GC.WaitForPendingFinalizers(); return(name); }
void button1_Click(object sender, EventArgs e) { SaveFileDialog openDlg = new SaveFileDialog(); openDlg.Filter = "Execl files (*.xls)|*.xls"; string path = openDlg.FileName; if (openDlg.ShowDialog() == DialogResult.OK) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Int16 i, j; xlApp = new Excel.ApplicationClass(); xlApp.DisplayAlerts = false; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (i = 0; i <= dataGridView1.RowCount - 2; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString(); } } try { xlWorkBook.SaveAs(path.ToString(), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); } catch (Exception ex) { MessageBox.Show("Сталася помилка під час збереження " + ex.ToString()); } xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
public void WireInfomationCIM(string path, int index, string name, string sdt, string Address) { try { Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Range chartRange; xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open(path, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //xlWorkSheet.Cells[1, 1] = "http://csharp.net-informations.com"; //xlWorkSheet.Rows.Insert(0, 0); //xlWorkSheet.Rows.Insert(0, 0); //xlWorkSheet.Rows.Insert(0, 0); //thong tin KH //Ten KH xlWorkSheet.get_Range("A" + index + 1, "H" + index + 1).Merge(false); chartRange = xlWorkSheet.get_Range("A" + index + 1, "H" + index + 1); chartRange.FormulaR1C1 = name; //So DT xlWorkSheet.get_Range("A" + index + 2, "H" + index + 2).Merge(false); chartRange = xlWorkSheet.get_Range("A" + index + 2, "H" + index + 2); chartRange.FormulaR1C1 = sdt; //Dia Chi xlWorkSheet.get_Range("A" + index + 3, "H" + index + 3).Merge(false); chartRange = xlWorkSheet.get_Range("A" + index + 3, "H" + index + 3); chartRange.FormulaR1C1 = Address; chartRange.Font.Bold = true; chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); chartRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; chartRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //xlWorkSheet.get_Range("A" + index, "H" + index+2).Formula = value; xlWorkBook.Save(); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } catch (Exception exception) { CTLError.WriteError("CTLImportExcel WireInfomationCIM", exception.Message); return; } }
public static void SavePierToDB(string fileName, List <CRailwayProject> bridgeList) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { app.Visible = false; //Workbook wBook = app.Workbooks.Add(true); Workbook wBook = app.Workbooks.Open(fileName); Worksheet wSheet = wBook.Worksheets["Pier"] as Worksheet; wSheet.Cells[1, 1] = "BridgeName"; wSheet.Cells[1, 2] = "PierName"; wSheet.Cells[1, 3] = "Longitude"; wSheet.Cells[1, 4] = "Latitude"; wSheet.Cells[1, 5] = "Altitude"; wSheet.Cells[1, 6] = "YawOffset"; wSheet.Cells[1, 7] = "DKCode"; wSheet.Cells[1, 8] = "Meter"; int count = 1; foreach (CRailwayProject p in bridgeList) { foreach (CRailwayDWProj dwp in p.mDWProjList) { wSheet.Cells[1 + count, 1] = p.ProjectName; wSheet.Cells[1 + count, 2] = dwp.DWName; wSheet.Cells[1 + count, 3] = dwp.mLongitude_Mid; wSheet.Cells[1 + count, 4] = dwp.mLatitude_Mid; wSheet.Cells[1 + count, 5] = dwp.mAltitude_Mid; wSheet.Cells[1 + count, 6] = dwp.mHeading_Mid; wSheet.Cells[1 + count, 7] = dwp.DKCode_Start; wSheet.Cells[1 + count, 8] = dwp.Mileage_Start; count++; } } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 //wBook.SaveAs(@"D:\result.xlsx"); wBook.Save(); //_Excel.Workbook book = app.Workbooks.Open(savePath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); //_Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet; //保存excel文件 //app.Save(@"D:\test1.xlsx"); //app.SaveWorkspace(@"D:\test1.xlsx"); app.Quit(); app = null; return; } catch (Exception err) { Console.WriteLine("导出Excel出错!错误原因:" + err.Message); return; } }
private static void saveCacuResultAsExcel(string path, double[] bavg, double[] bvar, double[] oavg, double[] ovar, double[] oBavg, double[] oBvar) { MSExcel.Application excelApp; //Excel 应用程序变量 MSExcel.Workbook excelDoc; //Excel文档变量 excelApp = new MSExcel.ApplicationClass(); //初始化 if (File.Exists(path)) { File.Delete(path); } //由于使用的是COM库,因此许多变量需要用Nothing代替 Object misspara = Missing.Value; excelDoc = excelApp.Workbooks.Add(misspara); //使用第一个工作表作为插入数据的工作表 MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[1]; //声明一个MSExcel.Range类型的变量r MSExcel.Range r1, r2, r3, r4, r5, r6, r7; r1 = ws.get_Range("A2", "A2"); //获取A2处的表格,并赋值 r2 = ws.get_Range("B2", "B2"); //获取A2处的表格,并赋值 r3 = ws.get_Range("C2", "C2"); //获取A2处的表格,并赋值 r4 = ws.get_Range("D2", "D2"); //获取A2处的表格,并赋值 r5 = ws.get_Range("E2", "E2"); //获取A2处的表格,并赋值 r6 = ws.get_Range("F2", "F2"); //获取A2处的表格,并赋值 r7 = ws.get_Range("G2", "G2"); //获取A2处的表格,并赋值 r1.Value2 = "行号"; r2.Value2 = "背景均值"; r3.Value2 = "背景方差"; r4.Value2 = "地物均值"; r5.Value2 = "地物方差"; r6.Value2 = "混合均值"; r7.Value2 = "混合方差 "; Random r = new Random(); for (int i = 3; i < bavg.Length + 3; i++) //行 { ws.Cells[i, 1] = String.Format("{0,12:F3}", bavg[i - 3]); // ws.Cells[i,1] = Convert.ToString(i); } //WdSaveFormat为Excel文档的保存格式 object format = MSExcel.XlFileFormat.xlWorkbookDefault; //将excelDoc文档对象的内容保存为XLSX excelDoc.SaveAs(path, format, misspara, misspara, misspara, misspara, MSExcel.XlSaveAsAccessMode.xlExclusive, misspara, misspara, misspara, misspara, misspara); //关闭excelDoc文档对象 excelDoc.Close(misspara, misspara, misspara); //关闭excelApp组件对象 excelApp.Quit(); Console.WriteLine(path + "创建完毕"); }
private void ReadRecipeFile(string strFileName) { object missing = System.Reflection.Missing.Value; Excel.Application excel = new Excel.ApplicationClass(); //lauch excel application if (excel == null) { this.label1.Text = "Can't access excel"; } else { excel.Visible = false; excel.UserControl = true; // 以只读的形式打开EXCEL文件 Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); //取得第一个工作薄 Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1); //取得总记录行数 (包括标题列) int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数 int columnsint = ws.UsedRange.Cells.Columns.Count; //得到列数 //取得数据范围区域 (不包括标题列) Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint); Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint); Excel.Range rng3 = ws.Cells.get_Range("C2", "C" + rowsint); object[,] arry1 = (object[, ])rng1.Value2; //get range's value object[,] arry2 = (object[, ])rng2.Value2; object[,] arry3 = (object[, ])rng3.Value2; //get range's value //将新值赋给一个数组 string[,] arry = new string[rowsint - 1, columnsint]; if (mRecipeList == null) { mRecipeList = new List <Recipe>(); } mRecipeList.Add(new Recipe()); int currentIndex = mRecipeList.Count - 1; mRecipeList[currentIndex].num = (double)ws.Cells.get_Range("B1", "B1").Value2; //for (int i = 1; i <= rowsint - 1; i++) for (int i = 1; i <= rowsint - 1; i++) { mRecipeList[currentIndex].mFoodList.Add(new Food()); mRecipeList[currentIndex].mFoodList[i - 1].name = arry1[i, 1].ToString(); mRecipeList[currentIndex].mFoodList[i - 1].weight = (double)arry2[i, 1]; //mReferenceList[i - 1].element2 = (double)arry3[i, 1]; } mRecipeList[currentIndex].calcElement(mReferenceList); } excel.Quit(); excel = null; Process[] procs = Process.GetProcessesByName("excel"); foreach (Process pro in procs) { pro.Kill(); //没有更好的方法,只有杀掉进程 } GC.Collect(); }
static void Main(string[] args) { Microsoft.Office.Interop.Excel.Workbook wb; Microsoft.Office.Interop.Excel.ApplicationClass xl; xl = new Microsoft.Office.Interop.Excel.ApplicationClass(); xl.Visible = true; //Microsoft.Office.Core.MsoAutomationSecurity secAuto = (MsoAutomationSecurity)MsoAutomationSecurity.msoAutomationSecurityLow; //xl.Application.AutomationSecurity = secAuto; wb = xl.Workbooks.Open( @"e:\\ccidev\testcall2.xlsm" , 0 , true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xl.Visible = true; xl.Interactive = true; object[] mytest = new object[] { 5, 0 }; object mytest2 = new object(); mytest2 = mytest; object[] oParms = new object[] { "Sheet1.Test" , "" , "" , mytest , "" , "" , null , "" , 0 , "1" , new object[] { "test", 0 } , "" }; try { RunMacro(xl, oParms); } catch (Exception ex) { Console.WriteLine(ex.Message); }; xl.Quit(); }
public cXLWorker(string sFileName) { try { xl = new Microsoft.Office.Interop.Excel.ApplicationClass(); xl.Visible = true; wb = xl.Workbooks.Open( // "e:\\ccidev\\testCall1.xlsm", sFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); RunMacro(this.xl, new Object[] { sFileName, true }); //true, // "5", // "", // "", // "true", // "Microsoft.Office.Interop.Excel.XlPlatform.xlWindows", // "\t", // "false", // "false", // "0", // "true", // "1", // "0"}); // xl.Run("Sheet1.Test",Type.Missing) //xl.Run("Sheet1.Test","Stest",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, // 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,Type.Missing); } catch (Exception ex) { xl.Quit(); throw ex; } finally { } }
public List<ResultRowCell> SearchByFile(string[] listOfWords, SupplierType supplierType, string priceFileName) { Application xlApp = new Excel.ApplicationClass(); xlApp.ReferenceStyle = XlReferenceStyle.xlA1; Workbook xlWorkBook = xlApp.Workbooks.Open(priceFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(supplierType.SheetNumber); int lastUsedRow = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row; //начало поиска var searchResult = new List<ResultRowCell>(); for (int currentRow = supplierType.ListCell[0].Row; currentRow <= lastUsedRow; currentRow++) { var resultRowCell = new ResultRowCell(); resultRowCell.Clear(); for (int iCell = 0; iCell < supplierType.ListCell.Count; iCell++) { //для всех ячеек получаем их значения int currentColumn = supplierType.ListCell[iCell].Col; //проверка ячейки на существование. var value = ((Range) xlWorkSheet.Cells[currentRow, currentColumn]).Value; string result = ""; if (value != null) result = value.ToString(); else result = ""; resultRowCell.VSupplierName = supplierType.Name; resultRowCell.Add(new ResultCell(currentRow, currentColumn, result, supplierType.ListCell[iCell].Name)); } //ищем по всем словам, по всем ячейкам int qty = 0; if ((qty = ParseRow(resultRowCell, listOfWords)) > 0) { //MessageBox.Show(@"Current row: " + currentRow.ToString()); resultRowCell.qty = qty; searchResult.Add(resultRowCell); } } //конец поиска xlWorkBook.Close(false, null, null); xlApp.Quit(); ReleaseObject(xlWorkSheet); ReleaseObject(xlWorkBook); ReleaseObject(xlApp); return searchResult; }
private void brnPrint_Click(object sender, EventArgs e) { object path; path = Application.StartupPath + @"\Pram\OperationOfDay.dd"; MSExcel.Application excelApp; //Excel应用程序变量 MSExcel.Workbook excelDoc; //Excel文档变量 excelApp = new MSExcel.ApplicationClass(); //初始化 //由于使用的是COM库,因此有许多变量需要用Nothing代替 Object Nothing = Missing.Value; excelDoc = excelApp.Workbooks._Open(Application.StartupPath + @"\Pram\OperationOfDay.dd", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //使用第一个工作表作为插入数据的工作表 MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[1]; //声明一个MSExcel.Range 类型的变量r MSExcel.Range r; r = ws.get_Range(("C" + 2), ("C" + 2)); r.Value2 = this.tbxOperationDate.Text; r = ws.get_Range(("C" + 4), ("C" + 4)); r.Value2 = this.lblTotalAmount.Text; r = ws.get_Range(("C" + 6), ("C" + 6)); r.Value2 = this.lblNewPawnAmount.Text; r = ws.get_Range(("F" + 6), ("F" + 6)); r.Value2 = this.lblRenewPawnAmount.Text; r = ws.get_Range(("I" + 6), ("I" + 6)); r.Value2 = this.lblRedeemPawnAmount.Text; r = ws.get_Range(("C" + 7), ("C" + 7)); r.Value2 = this.lblClosePawnAmount.Text; r = ws.get_Range(("F" + 7), ("F" + 7)); r.Value2 = this.lblClearPawnAmount.Text; r = ws.get_Range(("I" + 7), ("I" + 7)); r.Value2 = this.lblMoneyRL.Text; object format = MSExcel.XlFileFormat.xlWorkbookNormal; //打印文档 excelDoc.PrintOut(Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); //将excelDoc文档对象的内容保存为dd文档 //excelDoc.Save(); if (File.Exists(Application.StartupPath + @"\Pram\dump.dd")) { try { File.Delete(Application.StartupPath + @"\Pram\dump.dd"); } catch { MessageBox.Show("记录文件正在被使用,请退出Excel\n" + e.ToString()); } } excelDoc.SaveAs(Application.StartupPath + @"\Pram\dump.dd", format, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //关闭excelDoc文档对象 excelDoc.Close(Nothing, Nothing, Nothing); //关闭excelApp组件对象 excelApp.Quit(); //MessageBox.Show("生成成功"); }
private void btnExportToExcel_Click(object sender, EventArgs e) { SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"; dialog.OverwritePrompt = true; dialog.RestoreDirectory = true; if (dialog.ShowDialog() != DialogResult.OK) { return; } Excel.Application app = new Excel.ApplicationClass(); try { Object missing = System.Reflection.Missing.Value; app.Visible = false; Excel.Workbook wBook = app.Workbooks.Add(missing); Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet; Excel.Range DataCell = wSheet.get_Range("A1", "A1"); DataCell.Value2 = "参数名称"; DataCell.Next.Value2 = "参数单位"; DataCell.Next.Next.Value2 = "参数数值"; for (int i = 0; i < curWaParas.Count; ++i) { WeightParameter wp = curWaParas[i]; string cellid = "A" + (i + 2).ToString(); DataCell = wSheet.get_Range(cellid, cellid); DataCell.Value2 = wp.ParaName; DataCell.Next.Value2 = wp.ParaUnit; DataCell.Next.Next.Value2 = wp.ParaValue; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.SaveAs(dialog.FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); wBook.Close(false, missing, missing); app.Quit(); app = null; XLog.Write("成功导出参数值到文件\"" + dialog.FileName + "\"!"); } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public List <string[]> parseExcel(string path) { List <string[]> parsedData = new List <string[]>(); try { // string[] row =new string[5]; string singlerow = string.Empty; //Excel.FillFormat xlfile = new Excel.FillFormat(); Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; string str; int rCnt = 0; int cCnt = 0; xlApp = new Excel.ApplicationClass(); // xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { //string[] row = new string[9]; string[] row = new string[range.Columns.Count]; for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++) { try { str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString(); row[cCnt - 1] = str; } catch { } } parsedData.Add(row); } xlWorkBook.Close(true, null, null); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); return(parsedData); } catch { return(parsedData); } }
public bool SaveDataTableToExcel(string tableName) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { app.Visible = false; Workbook wBook = app.Workbooks.Open(@"D:\GISData\xlsx\ProjectInfo.xlsx"); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; if (ds != null) { System.Data.DataTable excelTable = ds.Tables[tableName]; 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.Save(); //保存excel文件 //app.Save(filePath); //app.SaveWorkspace(filePath); app.Quit(); } app = null; return(true); } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return(false); } finally { } }
public void draw_charts() { Excel.Application xlApp; Excel.Workbook xlWorkbook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkbook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = ""; xlWorkSheet.Cells[1, 2] = "Student1"; xlWorkSheet.Cells[1, 3] = "Student2"; xlWorkSheet.Cells[1, 4] = "Student3"; xlWorkSheet.Cells[2, 1] = "Term1"; xlWorkSheet.Cells[2, 2] = "80"; xlWorkSheet.Cells[2, 3] = "65"; xlWorkSheet.Cells[2, 4] = "45"; xlWorkSheet.Cells[3, 1] = "Term2"; xlWorkSheet.Cells[3, 2] = "78"; xlWorkSheet.Cells[3, 3] = "72"; xlWorkSheet.Cells[3, 4] = "60"; xlWorkSheet.Cells[4, 1] = "Term3"; xlWorkSheet.Cells[4, 2] = "82"; xlWorkSheet.Cells[4, 3] = "80"; xlWorkSheet.Cells[4, 4] = "65"; xlWorkSheet.Cells[5, 1] = "Term4"; xlWorkSheet.Cells[5, 2] = "75"; xlWorkSheet.Cells[5, 3] = "82"; xlWorkSheet.Cells[5, 4] = "68"; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "d5"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; xlWorkbook.SaveAs("testgraph.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); MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls"); }
public static bool writeExcel(System.Data.DataTable excelTable, string filePath) { filePath = Command.Process.root + filePath; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { app.Visible = false; Workbook wBook = app.Workbooks.Add(); 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; //保存工作簿 wSheet.SaveAs(filePath); app.Quit(); //清理内存 System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); while (System.Runtime.InteropServices.Marshal.ReleaseComObject(app) > 0) ; wSheet = null; wBook = null; app = null; GC.Collect(); return true; } catch (Exception err) { Console.WriteLine(err.Message); return false; } }
protected void Button1_Click(object sender, EventArgs e) { String sFileImage = "C:\\ImgAppImages\\SAM_5022.jpg"; String sFilePath = "C:\\ImgAppImages\\sam.xls"; if (File.Exists(sFilePath)) { File.Delete(sFilePath); } Microsoft.Office.Interop.Excel.ApplicationClass objApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet(); Microsoft.Office.Interop.Excel.Workbook objWorkBook = null; //object missing = System.Reflection.Missing.Value; try { objWorkBook = objApp.Workbooks.Add(Type.Missing); objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkBook.ActiveSheet; //Add picture to single sheet1 objSheet = (Worksheet)objWorkBook.Sheets[1]; objSheet.Name = "Graph with Report"; ////////////// //Or multiple sheets for (int iSheet = 0; iSheet < objWorkBook.Sheets.Count - 1; iSheet++) { //objSheet = objWorkBook.Sheets[iSheet] as Worksheet; ///(objSheet as Microsoft.Office.Interop.Excel._Worksheet).Activate(); } ///////////////// objSheet.Shapes.AddPicture(sFileImage, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 700, 350); objWorkBook.SaveAs(sFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { //Error Alert } finally { objApp.Quit(); objWorkBook = null; objApp = null; } }
// create excel file private void CreateExcelFile(string sName) { Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[2, 2] = "Olap Mining Report"; xlWorkBook.SaveAs(sName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }
static void Main(string[] args) { var xlApp = new MSExcel.ApplicationClass(); var xlWorkbooks = xlApp.Workbooks; xlApp.Visible = false; xlApp.DisplayAlerts = false; for (int i = 0; i < 500; i++) { var workbook = Path.Combine(Environment.CurrentDirectory, "us_foreign_assistance.xls"); //var workbook = "https://explore.data.gov/download/5gah-bvex/XLS"; var xlWorkBook = xlWorkbooks._Open(workbook, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); var xlWorkSheet = (MSExcel._Worksheet)xlWorkBook.Sheets["Notes"]; xlWorkSheet.Delete(); xlApp.Visible = false; workbook = Path.Combine(Environment.CurrentDirectory, string.Format("Book-{0}.xls", i)); xlWorkBook.SaveAs(workbook, MSExcel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, false, false, MSExcel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); // Commenting this out will cause memory usage to spike. xlWorkBook.Close(false, Missing.Value, false); log.InfoFormat("Wrote \"{0}\".", workbook); // Commenting this out does not seem to affect memory usage. Marshal.ReleaseComObject(xlWorkBook); } xlApp.DisplayAlerts = true; xlApp.Quit(); Marshal.ReleaseComObject(xlWorkbooks); Marshal.ReleaseComObject(xlApp); /* Console.Write("Press any key to continue . . . "); Console.ReadKey(false); Console.WriteLine(); */ }
/// <summary> /// 创建Excel工作薄 /// </summary> public void CreateExcel(string path) { if (File.Exists(path)) { return; } else { Excel.Application excelApp; //Excel应用程序 Excel.Workbook excelDoc; //Excel文档 excelApp = new Excel.ApplicationClass(); Object nothing = Missing.Value; excelDoc = excelApp.Workbooks.Add(nothing); Object format = Excel.XlFileFormat.xlWorkbookDefault; excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing, Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing); excelDoc.Close(nothing, nothing, nothing); excelApp.Quit(); } }
private void btnCreate_Click(object sender, EventArgs e) { Excel.Application xlApp ; Excel.Workbook xlWorkBook ; Excel.Worksheet xlWorkSheet ; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "id"; xlWorkSheet.Cells[1, 2] = "name"; xlWorkBook.SaveAs("csharp.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); MessageBox.Show("Excel file created , you can find the file csharp.xls"); }
private void btnImportFromXml_Click(object sender, EventArgs e) { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Xml files (*.xml)|*.xml|Excel files (*.xls)|*.xls|All files (*.*)|*.*"; dialog.RestoreDirectory = true; if (dialog.ShowDialog() != DialogResult.OK) { return; } if (dialog.FileName.EndsWith(".xml")) { XmlDocument xmldoc = new XmlDocument(); try { xmldoc.Load(dialog.FileName); } catch { MessageBox.Show("打开文件错误!"); return; } XmlNode rootnode = xmldoc.SelectSingleNode("参数列表"); if (rootnode == null) { MessageBox.Show("错误的参数文件!"); return; } foreach (XmlNode node in rootnode.ChildNodes) { string ParaName = node.ChildNodes[0].InnerText; ParaName = ParaName.Trim(); ParaData wp = curWaParas.Find(p => p.paraName == ParaName); if (wp == null) { continue; } if (wp.paraUnit != node.ChildNodes[1].InnerText.Trim()) { continue; } double value = 0; double.TryParse(node.ChildNodes[3].InnerText, out value); wp.paraValue = value; } for (int i = 0; i < dataGridViewParaInput.Rows.Count; ++i) { dataGridViewParaInput.Rows[i].Cells[1].Value = curWaParas[i].paraValue.ToString(); } } if (dialog.FileName.EndsWith(".xls")) { Excel.Application app = new Excel.ApplicationClass(); try { Object missing = System.Reflection.Missing.Value; app.Visible = false; Excel.Workbook wBook = app.Workbooks.Open(dialog.FileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet; if (wSheet.Rows.Count > 1) { for (int i = 2; i <= wSheet.Rows.Count; ++i) { string cellid = "A" + i.ToString(); Excel.Range DataCell = wSheet.get_Range(cellid, cellid); string ParaName = (string)DataCell.Text; ParaName = ParaName.Trim(); if (ParaName == "") { break; } ParaData wp = curWaParas.Find(p => p.paraName == ParaName); if (wp == null) { continue; } if (wp.paraUnit != (string)DataCell.Next.Text) { continue; } wp.paraValue = (double)DataCell.Next.Next.Value2; } } for (int i = 0; i < dataGridViewParaInput.Rows.Count; ++i) { dataGridViewParaInput.Rows[i].Cells[1].Value = curWaParas[i].paraValue.ToString(); } app.Quit(); app = null; } catch (Exception err) { MessageBox.Show("导入Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } XLog.Write("从文件\"" + dialog.FileName + "\"导入参数值成功!"); }
private void btnImportFromExcel_Click(object sender, EventArgs e) { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"; if (dialog.ShowDialog() != DialogResult.OK) { return; } Excel.Application app = new Excel.ApplicationClass(); try { Object missing = System.Reflection.Missing.Value; app.Visible = false; Excel.Workbook wBook = app.Workbooks.Open(dialog.FileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet; if (wSheet.Rows.Count > 1) { for (int i = 2; i <= wSheet.Rows.Count; ++i) { string cellid = "A" + i.ToString(); Excel.Range DataCell = wSheet.get_Range(cellid, cellid); string ParaName = (string)DataCell.Text; ParaName = ParaName.Trim(); if (ParaName == "") { break; } ParaData wp = curWaParas.Find(p => p.paraName == ParaName); if (wp == null) { continue; } if (wp.paraUnit != (string)DataCell.Next.Text) { continue; } wp.paraValue = (double)DataCell.Next.Next.Value2; } } for (int i = 0; i < dataGridViewParaInput.Rows.Count; ++i) { dataGridViewParaInput.Rows[i].Cells[1].Value = curWaParas[i].paraValue.ToString(); } app.Quit(); app = null; XLog.Write("从文件\"" + dialog.FileName + "\"导入参数值成功!"); } catch (Exception err) { MessageBox.Show("导入Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
private void btnExportToXml_Click(object sender, EventArgs e) { SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Xml files (*.xml)|*.xml|Excel files (*.xls)|*.xls"; dialog.OverwritePrompt = true; dialog.RestoreDirectory = true; if (dialog.ShowDialog() != DialogResult.OK) { return; } if (dialog.FileName.EndsWith(".xml")) { XmlDocument xmldoc = new XmlDocument(); XmlTextWriter writeXml = null; try { writeXml = new XmlTextWriter(dialog.FileName, Encoding.GetEncoding("gb2312")); } catch { MessageBox.Show("创建或写入文件失败!"); return; } writeXml.Formatting = Formatting.Indented; writeXml.Indentation = 5; writeXml.WriteStartDocument(); writeXml.WriteStartElement("参数列表"); { foreach (ParaData wp in curWaParas) { writeXml.WriteStartElement("参数"); { writeXml.WriteStartElement("参数名称"); writeXml.WriteString(wp.paraName); writeXml.WriteEndElement(); } { writeXml.WriteStartElement("参数单位"); writeXml.WriteString(wp.paraUnit); writeXml.WriteEndElement(); } { writeXml.WriteStartElement("参数类型"); writeXml.WriteValue(wp.paraType); writeXml.WriteEndElement(); } { writeXml.WriteStartElement("参数数值"); writeXml.WriteValue(wp.paraValue); writeXml.WriteEndElement(); } { writeXml.WriteStartElement("参数备注"); writeXml.WriteString(wp.strRemark); writeXml.WriteEndElement(); } writeXml.WriteEndElement(); } } writeXml.WriteEndElement(); writeXml.Close(); } if (dialog.FileName.EndsWith(".xls")) { Excel.Application app = new Excel.ApplicationClass(); try { Object missing = System.Reflection.Missing.Value; app.Visible = false; Excel.Workbook wBook = app.Workbooks.Add(missing); Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet; Excel.Range DataCell = wSheet.get_Range("A1", "A1"); DataCell.Value2 = "参数名称"; DataCell.Next.Value2 = "参数单位"; DataCell.Next.Next.Value2 = "参数数值"; for (int i = 0; i < curWaParas.Count; ++i) { ParaData wp = curWaParas[i]; string cellid = "A" + (i + 2).ToString(); DataCell = wSheet.get_Range(cellid, cellid); DataCell.Value2 = wp.paraName; DataCell.Next.Value2 = wp.paraUnit; DataCell.Next.Next.Value2 = wp.paraValue; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.SaveAs(dialog.FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); wBook.Close(false, missing, missing); app.Quit(); app = null; } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } XLog.Write("成功导出参数值到文件\"" + dialog.FileName + "\"!"); }
private void btnExportToExcel_Click(object sender, EventArgs e) { SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"; dialog.OverwritePrompt = true; dialog.RestoreDirectory = true; if (dialog.ShowDialog() != DialogResult.OK) { return; } Excel.Application app = new Excel.ApplicationClass(); try { Object missing = System.Reflection.Missing.Value; app.Visible = false; Excel.Workbook wBook = app.Workbooks.Add(missing); Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet; Excel.Range DataCell = wSheet.get_Range("A1", "A1"); DataCell.Value2 = "参数名称"; DataCell.Next.Value2 = "参数单位"; DataCell.Next.Next.Value2 = "参数数值"; for (int i = 0; i < curWaParas.Count; ++i) { ParaData wp = curWaParas[i]; string cellid = "A" + (i + 2).ToString(); DataCell = wSheet.get_Range(cellid, cellid); DataCell.Value2 = wp.paraName; DataCell.Next.Value2 = wp.paraUnit; DataCell.Next.Next.Value2 = wp.paraValue; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.SaveAs(dialog.FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); wBook.Close(false, missing, missing); app.Quit(); app = null; XLog.Write("成功导出参数值到文件\"" + dialog.FileName + "\"!"); } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
private void exportExcel2() { Excel.Application xlApp = new Excel.ApplicationClass(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks); if (xlApp == null) { WebTools.Alert("Excel无法启动"); return; } // 创建Excel工作薄 Dictionary<string, List<FpStudentObject>> schoolStudents = QueryStudentGroupbySchool(null, qDateStart.Value, qDateEnd.Value); String[] sheetTitles = { "缺受理号", "未收费", "已收费" }; String[] sheetColumns = { "受理号", "姓名", "身份证号码", "准驾车型", "导入时间" }; String dir = MapPath(string.Format("~/temp/{0}/",Session.SessionID)); Directory.CreateDirectory(dir); String excelName=string.Format("指纹记录[{0}][{1}].xls",this.qDateStart.Value,this.qDateEnd.Value); String excelPath = dir +excelName ; if (File.Exists(excelPath)) { File.Delete(excelPath); } xlApp.SheetsInNewWorkbook = schoolStudents.Keys.Count ; xlApp.Workbooks.Add(Missing.Value); Excel.Workbook xlBook = xlApp.ActiveWorkbook; System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets); int sheetIndex = 0; // try // { //xlBook.Worksheets.Add(Missing.Value, Missing.Value, schoolStudents.Keys.Count - 1, Missing.Value); //} // catch (Exception ex) { // WebTools.Alert(ex.Message); // return; // } foreach (string schoolCode in schoolStudents.Keys) { WebTools.Alert(schoolCode); sheetIndex++; List<FpStudentObject> students = schoolStudents[schoolCode]; string schoolName =schoolCode; ArrayList deps= SimpleOrmOperator.QueryConditionList<DepartMent>(string.Format("where c_depcode='{0}'", schoolCode)); if (deps.Count > 0) { schoolName = (deps[0] as DepartMent).DepNickName; } int rowIndex = 1; foreach (FpStudentObject student in students) { int color = 0; string result = ""; if (string.IsNullOrEmpty(student.LSH)) { color = 3; result = "缺流水号"; } else if (student.FEE_STATUE != "Y") { color = 6; result = "收费审核未通过"; } else { result = "考勤进行中"; } Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[sheetIndex]; xlSheet.Name = string.Format("{0}({1})", schoolName, schoolStudents[schoolCode].Count); int colIndex = 1; Excel.Range rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 20; rang.Interior.ColorIndex = color; rang.Formula = student.LSH; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 15; rang.Formula = student.NAME; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 25; rang.Formula = student.IDCARD; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; rang.Formula = student.CAR_TYPE; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.ColumnWidth = 15; rang.Value2 = student.CREATE_TIME.ToLongDateString(); rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.ColumnWidth = 30; rang.Value2 = result; rowIndex++; } } try{ xlBook.Save(); xlBook.Saved = true; xlBook.SaveCopyAs(excelPath); WebTools.Alert("保存成功"); } finally { xlBook.Close(true, Missing.Value, Missing.Value); //System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook); } xlApp.Workbooks.Close(); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(excelName, Encoding.UTF8)); Response.WriteFile(excelPath); Response.End(); Response.Close(); }
private void lnkexporttoexcel_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; Excel.Range cells = xlWorkSheet.Cells; for (i = 1; i <= gdvmeapresult.ColumnCount; i++) { string column = gdvmeapresult.Columns[i - 1].Name; xlWorkSheet.Cells[2, i + 1] = column; cells = (Excel.Range)xlWorkSheet.Cells[2, i + 1]; cells.Font.Bold = true; cells.Borders.Weight = Excel.XlBorderWeight.xlMedium; cells.ColumnWidth = 15; } for (i = 2; i <= gdvmeapresult.RowCount + 1; i++) { for (j = 2; j <= gdvmeapresult.ColumnCount + 1; j++) { DataGridViewCell cell = gdvmeapresult[j - 2, i - 2]; xlWorkSheet.Cells[i + 1, j] = Convert.ToString(cell.Value); ////format cells if (j > 2) { Color bgc = Color.Pink; Color fgc = Color.Black; if (cell.Value.ToString() == "-")//data not available { bgc = Color.Gray; } else { decimal mapevalue = decimal.Parse(cell.Value.ToString()); if (mapevalue > 25) bgc = Color.Red; //Color.Black;//(204, 51, 51); if (mapevalue < -25) bgc = Color.Blue;//(114, 154, 210); if (mapevalue <= 25 && mapevalue >= -25) bgc = Color.White; } ((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Interior.Color = System.Drawing.ColorTranslator.ToOle(bgc); ((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Font.Color = System.Drawing.ColorTranslator.ToOle(fgc); } ((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Borders.Weight = Excel.XlBorderWeight.xlThin; //end cell format } } //last row range Excel.Range range = xlWorkSheet.get_Range(xlWorkSheet.Cells[i + 1, 2], xlWorkSheet.Cells[i + 1, gdvmeapresult.ColumnCount + 1]); range.Merge(true); range.RowHeight = 40; range.WrapText = true; xlWorkSheet.Cells[i + 1, 2] = "Note: Red represents underforecasts (>25%), Blue represents overforecasts (<-25%)," + "Gray represents insufficient data to complete the forecast, and no field color represents an accurate forecast, within 25%."; //header Excel.Range header = xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]); header = header.EntireRow; for (int no = 0; no < 5; no++) { header.Insert(Excel.XlInsertShiftDirection.xlShiftDown, misValue); } header = xlWorkSheet.get_Range(xlWorkSheet.Cells[2, 2], xlWorkSheet.Cells[2, gdvmeapresult.ColumnCount + 1]); header.Value = "Forecast MAPE Result"; header.Merge(true); header.Font.Underline = true; header.Font.Bold = true; header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; header.RowHeight = 40; header.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.Cells[3, 2] = "Forecast Id : "; xlWorkSheet.Cells[3, 3] = forecastInfo.ForecastNo; xlWorkSheet.Cells[3, 4] = "Methodology : "; xlWorkSheet.Cells[3, 5] = forecastInfo.Methodology; xlWorkSheet.Cells[3, 6] = "Wastage % : "; xlWorkSheet.Cells[3, 7] = forecastInfo.Westage.ToString(); xlWorkSheet.Cells[4, 2] = "Start Date : "; xlWorkSheet.Cells[4, 3] = forecastInfo.StartDate.ToShortDateString(); xlWorkSheet.Cells[4, 4] = "Data Usage : "; xlWorkSheet.Cells[4, 5] = forecastInfo.DataUsage; xlWorkSheet.Cells[4, 6] = "Add By % : "; xlWorkSheet.Cells[4, 7] = forecastInfo.Scaleup.ToString(); xlWorkSheet.Cells[5, 2] = "Period : "; xlWorkSheet.Cells[5, 3] = forecastInfo.Period; xlWorkSheet.Cells[5, 4] = "Regression Type : "; xlWorkSheet.Cells[5, 5] = forecastInfo.Method; xlWorkSheet.Cells[5, 6] = "Extension Period : "; xlWorkSheet.Cells[5, 7] = forecastInfo.Extension.ToString(); //header end saveFileDialog1.FileName = forecastInfo.ForecastNo + ".xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { xlWorkBook.SaveAs(saveFileDialog1.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); LqtUtil.releaseObject(xlWorkSheet); LqtUtil.releaseObject(xlWorkBook); LqtUtil.releaseObject(xlApp); MessageBox.Show("Exported Successfully!"); } }
private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e) { statusClose = 1; Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US"); Excel.Application xlApp = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; Excel.Sheets xlSheets = null; object misValue = System.Reflection.Missing.Value; try { xlApp = new Excel.ApplicationClass(); //xlWorkBook = xlApp.Workbooks.Add(misValue); if there is no existing excel file xlWorkBook = xlApp.Workbooks.Open(textBox1.Text, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); if there is no existing excel file xlSheets = xlWorkBook.Worksheets; xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.get_Item("Sheet1"); Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.UsedRange; //xlWorkSheet.Cells[2, 2] = "http://csharp.net-informations.com"; if there is no existing excel file //int colCount = range.Columns.Count; //int rowCount = range.Rows.Count; //for (int index = 1; index < 20; index++) //{ // xlWorkSheet.Cells[rowCount + index, 1] = rowCount + index; // xlWorkSheet.Cells[rowCount + index, 2] = "New Item" + index; //} int baris = 1; //cari cara menghitung jumlah baris yg telah ditulis di excel if (cekBaris(xlWorkSheet) > 1) baris = cekBaris(xlWorkSheet); progressBar1.SafeControlInvoke(ProgressBar => progressBar1.Maximum = listView1.SelectedItems.Count + 1); progressBar1.SafeControlInvoke(ProgressBar => progressBar1.Minimum = 0); listView1.SafeControlInvoke(listView => { foreach (ListViewItem items in listView1.SelectedItems) { baris++; progressBar1.SafeControlInvoke(ProgressBar => progressBar1.Value++); xlWorkSheet.Cells[baris, 1] = " "; xlWorkSheet.Cells[baris, 2] = items.SubItems[0].Text; xlWorkSheet.Cells[baris, 3] = items.SubItems[1].Text; xlWorkSheet.Cells[baris, 4] = items.SubItems[2].Text; xlWorkSheet.Cells[baris, 5] = items.SubItems[3].Text; xlWorkSheet.Cells[baris, 6] = items.SubItems[4].Text; xlWorkSheet.Cells[baris, 7] = items.SubItems[5].Text; xlWorkSheet.Cells[baris, 8] = items.SubItems[6].Text; xlWorkSheet.Cells[baris, 9] = items.SubItems[7].Text; xlWorkSheet.Cells[baris, 10] = items.SubItems[8].Text; xlWorkSheet.Cells[baris, 11] = items.SubItems[9].Text; xlWorkSheet.Cells[baris, 12] = items.SubItems[10].Text; xlWorkSheet.Cells[baris, 13] = items.SubItems[11].Text; xlWorkSheet.Cells[baris, 14] = items.SubItems[12].Text; xlWorkSheet.Cells[baris, 15] = items.SubItems[13].Text; } }); xlApp.AlertBeforeOverwriting = false; xlApp.DisplayAlerts = false; xlWorkBook.SaveAs(textBox1.Text, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } catch (Exception ex) { MessageBox.Show("Terjadi kesalahan penulisan excel\nPesan kesalahan : " + ex.Message, "PERHATIAN"); } finally { releaseObject(xlSheets); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
private void button3_Click(object sender, EventArgs e) { disableButton(0); string filePath = ""; if (this.folderBrowserDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { filePath = folderBrowserDialog1.SelectedPath + "\\Laporan_" + string.Format("{0:dd_MM_yyyy}", DateTime.Now) + ".xls"; filePath = filePath.Replace(@"\\", @"\"); textBox1.Text = filePath; Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; Excel.Range chartRange; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //add data xlWorkSheet.Cells[1, 1] = "No Urut"; xlWorkSheet.Cells[1, 2] = "Tgl Bayar"; xlWorkSheet.Cells[1, 3] = "No Bukti"; xlWorkSheet.Cells[1, 4] = "KPA"; xlWorkSheet.Cells[1, 5] = "Ket bayar"; xlWorkSheet.Cells[1, 6] = "No rekening"; xlWorkSheet.Cells[1, 7] = "penerimaan"; xlWorkSheet.Cells[1, 8] = "pengeluaran"; xlWorkSheet.Cells[1, 9] = "sisa"; xlWorkSheet.Cells[1, 10] = "ppn"; xlWorkSheet.Cells[1, 11] = "pph"; xlWorkSheet.Cells[1, 12] = "f_df"; xlWorkSheet.Cells[1, 13] = "no ppn"; xlWorkSheet.Cells[1, 14] = "NTPNPPn"; xlWorkSheet.Cells[1, 15] = "NTPNPPh"; #region Beberapa contoh manipulasi excel //xlWorkSheet.Cells[4, 2] = ""; //xlWorkSheet.Cells[4, 3] = "Student1"; //xlWorkSheet.Cells[4, 4] = "Student2"; //xlWorkSheet.Cells[4, 5] = "Student3"; //xlWorkSheet.Cells[5, 2] = "Term1"; //xlWorkSheet.Cells[5, 3] = "80"; //xlWorkSheet.Cells[5, 4] = "65"; //xlWorkSheet.Cells[5, 5] = "45"; //xlWorkSheet.Cells[6, 2] = "Term2"; //xlWorkSheet.Cells[6, 3] = "78"; //xlWorkSheet.Cells[6, 4] = "72"; //xlWorkSheet.Cells[6, 5] = "60"; //xlWorkSheet.Cells[7, 2] = "Term3"; //xlWorkSheet.Cells[7, 3] = "82"; //xlWorkSheet.Cells[7, 4] = "80"; //xlWorkSheet.Cells[7, 5] = "65"; //xlWorkSheet.Cells[8, 2] = "Term4"; //xlWorkSheet.Cells[8, 3] = "75"; //xlWorkSheet.Cells[8, 4] = "82"; //xlWorkSheet.Cells[8, 5] = "68"; //xlWorkSheet.Cells[9, 2] = "Total"; //xlWorkSheet.Cells[9, 3] = "315"; //xlWorkSheet.Cells[9, 4] = "299"; //xlWorkSheet.Cells[9, 5] = "238"; //xlWorkSheet.get_Range("b2", "e3").Merge(false); //chartRange = xlWorkSheet.get_Range("b2", "e3"); //chartRange.FormulaR1C1 = "MARK LIST"; //chartRange.HorizontalAlignment = 3; //chartRange.VerticalAlignment = 3; //chartRange = xlWorkSheet.get_Range("a1", "r1"); //chartRange.Font.Bold = true; //chartRange = xlWorkSheet.get_Range("b9", "e9"); //chartRange.Font.Bold = true; //chartRange = xlWorkSheet.get_Range("b2", "e9"); //chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); #endregion chartRange = xlWorkSheet.get_Range("a1", "o1"); chartRange.Font.Bold = true; chartRange = xlWorkSheet.get_Range("a1", "o1"); chartRange.Font.Size = 12; xlApp.AlertBeforeOverwriting = false; xlApp.DisplayAlerts = false; xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlApp); releaseObject(xlWorkBook); releaseObject(xlWorkSheet); MessageBox.Show("File Tercetak", "KONFIRMASI"); } disableButton(1); }
private void exportExcel() { this.btnExport.Enabled = false; string btnText = this.btnExport.Text; this.btnExport.Text = "报表生成中...."; Excel.Application xlApp = new Excel.ApplicationClass(); if (xlApp == null) { MessageBoxHelper.Show("Microsoft Excel 无法启动"); return; } // 创建Excel工作薄 Dictionary<string, DepartMent> cacheDeps = DepartMent.queryDict(oraclDataAccess); Dictionary<string, List<FpStudentObject>> schoolStudents = FpStudentObject.QueryGroupbySchool(oraclDataAccess,null, dtpStart.Value.ToShortDateString(), dtpEnd.Value.ToShortDateString()); String[] sheetTitles = { "缺受理号", "未收费", "已收费" }; String[] sheetColumns = { "受理号", "姓名", "身份证号码", "准驾车型", "导入时间" }; //String dir = MapPath("~/temp/"); //Directory.CreateDirectory(dir); //String excelPath = this.openFileDialog1.FileName; System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks); if (schoolStudents.Keys.Count < 1) { MessageBoxHelper.Show("没有查询到记录"); return; } xlApp.SheetsInNewWorkbook = schoolStudents.Keys.Count; Excel.Workbook xlBook = xlApp.Workbooks.Add(Missing.Value); int sheetIndex = 0; //xlBook.Sheets.Add(Missing.Value, Missing.Value, schoolStudents.Keys.Count - 1, Missing.Value); foreach (string schoolCode in schoolStudents.Keys) { sheetIndex++; List<FpStudentObject> students = schoolStudents[schoolCode]; string schoolName = schoolCode; try { schoolName = cacheDeps[schoolCode].DepNickName; } catch (Exception ex) { } tssInfo.Text = string.Format("({2}/{3}).{0}:{1}...", schoolName, students.Count, sheetIndex, schoolStudents.Keys.Count); int rowIndex = 1; foreach (FpStudentObject student in students) { int color = 0; string result = ""; if (string.IsNullOrEmpty(student.LSH)) { color = 3; result = "缺流水号"; } else if (student.FEE_STATUE != "Y") { color = 6; result = "收费审核未通过"; } else { result = "考勤进行中"; } Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[sheetIndex]; xlSheet.Name = string.Format("{0}({1})", schoolName, schoolStudents[schoolCode].Count); int colIndex = 1; Excel.Range rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 20; rang.Interior.ColorIndex = color; rang.Formula = student.LSH; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 15; rang.Formula = student.NAME; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 25; rang.Formula = student.IDCARD; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; rang.Formula = student.CAR_TYPE; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.ColumnWidth = 15; rang.Value2 = student.CREATE_TIME.ToLongDateString(); rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.ColumnWidth = 30; rang.Value2 = result; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.ColumnWidth = 30; rang.Value2 = student.SCHOOL_NAME; rowIndex++; } } try { xlBook.Save(); xlBook.Saved = true; xlBook.SaveCopyAs(target); } finally { xlBook.Close(true, Missing.Value, Missing.Value); //System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook); } xlApp.Workbooks.Close(); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); this.btnExport.Text = btnText; this.btnExport.Enabled = true; this.target = string.Empty; this.tssInfo.Text = string.Empty; }
public static void ExportForDriverToExcel(DateTime date) { FolderBrowserDialog sfd = new FolderBrowserDialog(); sfd.Description = "Şöförler için excel"; if (sfd.ShowDialog() == DialogResult.OK) { Excel.Application ExcelApp; Excel.Workbook objBook; object missing = System.Reflection.Missing.Value; object fileName = "normal.dot"; object newTemplate = false; object docType = 0; ExcelApp = new Excel.ApplicationClass(); System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); ExcelApp.Visible = false; objBook = ExcelApp.Workbooks.Add(missing); #region sheet1 objBook.Sheets.Add(); Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Sheets[1]; objSheet.Name = "Anadolu"; #region Header for Anadolu objSheet.Cells[1, 1] = "NO"; objSheet.get_Range("A1", "A1").Font.Bold = true; objSheet.get_Range("A1", "A1").Font.Color = -16776961; objSheet.get_Range("A1", "A1").Font.Size = 10; objSheet.get_Range("A1", "A1").ColumnWidth = 5.29; objSheet.get_Range("A1", "A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet.get_Range("A1", "A1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.Cells[1, 2] = "AD SOYAD"; objSheet.get_Range("B1", "B1").Font.Bold = true; objSheet.get_Range("B1", "B1").Font.Color = -16776961; objSheet.get_Range("B1", "B1").Font.Size = 10; objSheet.get_Range("B1", "B1").ColumnWidth = 14.86; objSheet.get_Range("B1", "B1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet.get_Range("B1", "B1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.Cells[1, 3] = "TELEFON NO"; objSheet.get_Range("C1", "C1").Font.Bold = true; objSheet.get_Range("C1", "C1").Font.Color = -16776961; objSheet.get_Range("C1", "C1").Font.Size = 10; objSheet.get_Range("C1", "C1").ColumnWidth = 18.43; objSheet.get_Range("C1", "C1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet.get_Range("C1", "C1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.Cells[1, 4] = "ADRES"; objSheet.get_Range("D1", "D1").Font.Bold = true; objSheet.get_Range("D1", "D1").Font.Color = -16776961; objSheet.get_Range("D1", "D1").Font.Size = 10; objSheet.get_Range("D1", "D1").ColumnWidth = 18; objSheet.get_Range("D1", "D1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet.get_Range("D1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; Dictionary<int, int> mypairs = new Dictionary<int, int>(); string[] productCols = Order.getUsedProductTypes(date); int colRow = 5; for (int j = 0; j < productCols.Length; j++) { objSheet.Cells[1, colRow] = productCols[j]; (objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 4.20; (objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true; (objSheet.Cells[1, colRow] as Excel.Range).Font.Color = -16776961; (objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 10; (objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; mypairs.Add(colRow, 0); colRow++; } objSheet.Cells[1, colRow] = "AÇIKLAMA"; (objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true; (objSheet.Cells[1, colRow] as Excel.Range).Font.Color = -16776961; (objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 10; (objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 17.14; (objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; colRow++; #endregion int row = 2; //this gets the order list List<Order> ords = Order.Orders_GetByDate(date); for (int k = 0; k < ords.Count; k++) { bool goon = true; Addresses[] addListTop = Addresses.Addresses_GetByCustomerID(ords[k].CustomerID).ToArray(); for (int i = 0; i < addListTop.Length; i++) { if (addListTop[i].Type == ords[k].AddressType) { if (addListTop[i].Area == 0) { goon = false; continue; } } } if (!goon) { continue; } objSheet.Cells[row, 1] = Customer.Customer.Customer_GetByID(ords[k].CustomerID).PreFix + " " + Customer.Customer.Customer_GetByID(ords[k].CustomerID).No.ToString().PadLeft(3, '0'); (objSheet.Cells[row, 1] as Excel.Range).WrapText = true; (objSheet.Cells[row, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, 1] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; string strName = (Customer.Customer.Customer_GetByID(ords[k].CustomerID).AutorizedName != "") ? Customer.Customer.Customer_GetByID(ords[k].CustomerID).AutorizedName : Customer.Customer.Customer_GetByID(ords[k].CustomerID).Name; //ad objSheet.Cells[row, 2] = strName; (objSheet.Cells[row, 2] as Excel.Range).WrapText = true; (objSheet.Cells[row, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, 2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; //telefon objSheet.Cells[row, 3] = ords[k].Telephone; (objSheet.Cells[row, 3] as Excel.Range).WrapText = true; (objSheet.Cells[row, 3] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, 3] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; //adres. objSheet.Cells[row, 4] = ords[k].Address; (objSheet.Cells[row, 4] as Excel.Range).WrapText = true; (objSheet.Cells[row, 4] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, 4] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; OrderDetail[] odtmp = ords[k].getDetails().ToArray(); int colIndex = 5; for (int i = 0; i < odtmp.Length; i++) { for (int j = 0; j < productCols.Length; j++) { if (Product.Product.Products_GetByID(odtmp[i].ProductID).Name == productCols[j]) { mypairs[colIndex + j] += odtmp[i].Unit; objSheet.Cells[row, colIndex + j] = odtmp[i].Unit; (objSheet.Cells[row, colIndex + j] as Excel.Range).WrapText = true; (objSheet.Cells[row, colIndex + j] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, colIndex + j] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; } } } objSheet.Cells[row, colIndex + productCols.Length] = ords[k].Description; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).WrapText = true; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; row++; } objSheet.Cells[3 + row, 4] = "TOPLAM"; Dictionary<int, int>.KeyCollection keys = mypairs.Keys; foreach (int i in keys) { objSheet.Cells[3 + row, i] = mypairs[i]; (objSheet.Cells[3 + row, i] as Excel.Range).Font.Bold = true; } //ExcelApp.ActiveWindow = Excel.XlWindowView.xlPageBreakPreview; //objSheet.VPageBreaks[1].DragOff(Excel.XlDirection.xlToRight, 1); //ExcelApp.ActiveWindow = Excel.XlWindowView.xlNormalView; #endregion #region sheet2 objBook.Sheets.Add(); Excel.Worksheet objSheet2 = (Excel.Worksheet)objBook.Sheets[2]; objSheet2.Name = "Avrupa"; #region Header for Avrupa objSheet2.Cells[1, 1] = "NO"; objSheet2.get_Range("A1", "A1").Font.Bold = true; objSheet2.get_Range("A1", "A1").Font.Color = -16776961; objSheet2.get_Range("A1", "A1").Font.Size = 10; objSheet2.get_Range("A1", "A1").ColumnWidth = 5.29; objSheet2.get_Range("A1", "A1").WrapText = true; objSheet2.get_Range("A1", "A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet2.get_Range("A1", "A1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.Cells[1, 2] = "AD SOYAD"; objSheet2.get_Range("B1", "B1").Font.Bold = true; objSheet2.get_Range("B1", "B1").Font.Color = -16776961; objSheet2.get_Range("B1", "B1").Font.Size = 10; objSheet2.get_Range("B1", "B1").ColumnWidth = 14.86; objSheet2.get_Range("B1", "B1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet2.get_Range("B1", "B1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.Cells[1, 3] = "TELEFON NO"; objSheet2.get_Range("C1", "C1").Font.Bold = true; objSheet2.get_Range("C1", "C1").Font.Color = -16776961; objSheet2.get_Range("C1", "C1").Font.Size = 10; objSheet2.get_Range("C1", "C1").ColumnWidth = 18.43; objSheet2.get_Range("C1", "C1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet2.get_Range("C1", "C1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("C1", "C1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.Cells[1, 4] = "ADRES"; objSheet2.get_Range("D1", "D1").Font.Bold = true; objSheet2.get_Range("D1", "D1").Font.Color = -16776961; objSheet2.get_Range("D1", "D1").Font.Size = 10; objSheet2.get_Range("D1", "D1").ColumnWidth = 18; objSheet2.get_Range("D1", "D1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet2.get_Range("D1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet2.get_Range("D1", "D1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; Dictionary<int, int> mypairs2 = new Dictionary<int, int>(); string[] productCols2 = Order.getUsedProductTypes(date); int colRow2 = 5; for (int j = 0; j < productCols2.Length; j++) { objSheet2.Cells[1, colRow2] = productCols2[j]; (objSheet2.Cells[1, colRow2] as Excel.Range).ColumnWidth = 4.20; (objSheet2.Cells[1, colRow2] as Excel.Range).Font.Bold = true; (objSheet2.Cells[1, colRow2] as Excel.Range).Font.Color = -16776961; (objSheet2.Cells[1, colRow2] as Excel.Range).Font.Size = 10; (objSheet2.Cells[1, colRow2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet2.Cells[1, colRow2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; mypairs2.Add(colRow2, 0); colRow2++; } objSheet2.Cells[1, colRow2] = "AÇIKLAMA"; (objSheet2.Cells[1, colRow2] as Excel.Range).Font.Bold = true; (objSheet2.Cells[1, colRow2] as Excel.Range).Font.Color = -16776961; (objSheet2.Cells[1, colRow2] as Excel.Range).Font.Size = 10; (objSheet2.Cells[1, colRow2] as Excel.Range).ColumnWidth = 17.14; (objSheet2.Cells[1, colRow2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet2.Cells[1, colRow2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet2.Cells[1, colRow2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; colRow2++; #endregion int row2 = 2; //this gets the order list List<Order> ords2 = Order.Orders_GetByDate(date); for (int k = 0; k < ords2.Count; k++) { bool goon = true; Addresses[] addListTop = Addresses.Addresses_GetByCustomerID(ords2[k].CustomerID).ToArray(); for (int i = 0; i < addListTop.Length; i++) { if (addListTop[i].Type == ords2[k].AddressType) { if (addListTop[i].Area == 1) { goon = false; continue; } } } if (!goon) { continue; } objSheet2.Cells[row2, 1] = Customer.Customer.Customer_GetByID(ords2[k].CustomerID).PreFix + " " + Customer.Customer.Customer_GetByID(ords2[k].CustomerID).No.ToString().PadLeft(3, '0'); (objSheet2.Cells[row2, 1] as Excel.Range).WrapText = true; (objSheet2.Cells[row2, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet2.Cells[row2, 1] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; string strName = (Customer.Customer.Customer_GetByID(ords2[k].CustomerID).AutorizedName != "") ? Customer.Customer.Customer_GetByID(ords2[k].CustomerID).AutorizedName : Customer.Customer.Customer_GetByID(ords2[k].CustomerID).Name; objSheet2.Cells[row2, 2] = strName; (objSheet2.Cells[row2, 2] as Excel.Range).WrapText = true; (objSheet2.Cells[row2, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet2.Cells[row2, 2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; objSheet2.Cells[row2, 3] = ords2[k].Telephone; (objSheet2.Cells[row2, 3] as Excel.Range).WrapText = true; (objSheet2.Cells[row2, 3] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet2.Cells[row2, 3] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 3] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; objSheet2.Cells[row2, 4] = ords2[k].Address; (objSheet2.Cells[row2, 4] as Excel.Range).WrapText = true; (objSheet2.Cells[row2, 4] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet2.Cells[row2, 4] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, 4] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; OrderDetail[] odtmp = ords2[k].getDetails().ToArray(); int colIndex = 5; for (int i = 0; i < odtmp.Length; i++) { for (int j = 0; j < productCols2.Length; j++) { if (Product.Product.Products_GetByID(odtmp[i].ProductID).Name == productCols2[j]) { mypairs2[colIndex + j] += odtmp[i].Unit; objSheet2.Cells[row2, colIndex + j] = odtmp[i].Unit; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).WrapText = true; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; } } } objSheet2.Cells[row2, colIndex + productCols2.Length] = ords2[k].Description; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).WrapText = true; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet2.Cells[row2, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; row2++; } objSheet2.Cells[3 + row2, 4] = "TOPLAM"; Dictionary<int, int>.KeyCollection keys2 = mypairs2.Keys; foreach (int i in keys2) { objSheet2.Cells[3 + row2, i] = mypairs2[i]; (objSheet2.Cells[3 + row2, i] as Excel.Range).Font.Bold = true; } Dictionary<int, int> totalPair = new Dictionary<int, int>(); Dictionary<int, int>.KeyCollection keeys = mypairs.Keys; foreach (int i in keeys) { if (!totalPair.ContainsKey(i)) { totalPair.Add(i, mypairs[i]); } else { totalPair[i] += mypairs[i]; } } Dictionary<int, int>.KeyCollection keeys2 = mypairs2.Keys; foreach (int i in keeys2) { if (!totalPair.ContainsKey(i)) { totalPair.Add(i, mypairs2[i]); } else { totalPair[i] += mypairs2[i]; } } Dictionary<int, int>.KeyCollection totalKeys = totalPair.Keys; objSheet2.Cells[5 + row2, 4] = "GENEL TOPLAM"; objSheet.Cells[5 + row, 4] = "GENEL TOPLAM"; foreach (int item in totalKeys) { objSheet.Cells[5 + row, item] = totalPair[item]; (objSheet.Cells[5 + row, item] as Excel.Range).Font.Bold = true; objSheet2.Cells[5 + row2, item] = totalPair[item]; (objSheet2.Cells[5 + row2, item] as Excel.Range).Font.Bold = true; } #endregion string fileNameXLS = date.Day.ToString("00") + "." + date.Month.ToString("00") + "." + date.Year + "-Şöför.xls"; objBook.SaveAs(sfd.SelectedPath + "\\" + fileNameXLS, Excel.XlFileFormat.xlWorkbookNormal, null, null, true, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null); objBook.Close(false, Type.Missing, System.Type.Missing); ExcelApp.Quit(); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("tr-TR"); } }
/// <summary> /// Converts an Excel sheet to PDF /// </summary> /// <param name="inputFile">The Excel input file</param> /// <param name="outputFile">The PDF output file</param> /// <returns></returns> /// <exception cref="OCCsvFileLimitExceeded">Raised when a CSV <paramref name="inputFile"/> has to many rows</exception> internal static void Convert(string inputFile, string outputFile) { // We only need to perform this check if we are running on a server if (NativeMethods.IsWindowsServer()) CheckIfSystemProfileDesktopDirectoryExists(); CheckIfPrinterIsInstalled(); DeleteAutoRecoveryFiles(); ExcelInterop.Application excel = null; ExcelInterop.Workbook workbook = null; string tempFileName = null; try { excel = new ExcelInterop.ApplicationClass { ScreenUpdating = false, DisplayAlerts = false, DisplayDocumentInformationPanel = false, DisplayRecentFiles = false, DisplayScrollBars = false, AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable, PrintCommunication = true, // DO NOT REMOVE THIS LINE, NO NEVER EVER ... DON'T EVEN TRY IT Visible = false }; var extension = Path.GetExtension(inputFile); if (string.IsNullOrWhiteSpace(extension)) extension = string.Empty; if (extension.ToUpperInvariant() == ".CSV") { // Yes this look somewhat weird but we have to change the extension if we want to handle // CSV files with different kind of separators. Otherwhise Excel will always overrule whatever // setting we make to open a file tempFileName = Path.GetTempFileName() + Guid.NewGuid() + ".txt"; File.Copy(inputFile, tempFileName); inputFile = tempFileName; } workbook = Open(excel, inputFile, extension, false); // We cannot determine a print area when the document is marked as final so we remove this workbook.Final = false; var usedSheets = 0; foreach (var sheetObject in workbook.Sheets) { var sheet = sheetObject as ExcelInterop.Worksheet; if (sheet != null) { var protection = sheet.Protection; var activeWindow = excel.ActiveWindow; try { // ReSharper disable once RedundantCast ((Microsoft.Office.Interop.Excel._Worksheet)sheet).Activate(); if (!sheet.ProtectContents || protection.AllowFormattingColumns) if (activeWindow.View != ExcelInterop.XlWindowView.xlPageLayoutView) sheet.Columns.AutoFit(); } catch (COMException) { // Do nothing, this sometimes failes and there is nothing we can do about it } finally { Marshal.ReleaseComObject(activeWindow); Marshal.ReleaseComObject(protection); } var printArea = GetWorksheetPrintArea(sheet); switch (printArea) { case "shapes": SetWorkSheetPaperSize(sheet, string.Empty); usedSheets += 1; break; case "": break; default: SetWorkSheetPaperSize(sheet, printArea); usedSheets += 1; break; } Marshal.ReleaseComObject(sheet); continue; } var chart = sheetObject as ExcelInterop.Chart; if (chart != null) { SetChartPaperSize(chart); Marshal.ReleaseComObject(chart); } } // It is not possible in Excel to export an empty workbook if (usedSheets != 0) workbook.ExportAsFixedFormat(ExcelInterop.XlFixedFormatType.xlTypePDF, outputFile); else throw new OCFileContainsNoData("The file '" + Path.GetFileName(inputFile) + "' contains no data"); } finally { if (workbook != null) { workbook.Saved = true; workbook.Close(false); Marshal.ReleaseComObject(workbook); } if (excel != null) { excel.Quit(); Marshal.ReleaseComObject(excel); } if (!string.IsNullOrEmpty(tempFileName) && File.Exists(tempFileName)) File.Delete(tempFileName); } GC.Collect(); GC.WaitForPendingFinalizers(); }
public static string ExportToExcel(DateTime date) { FolderBrowserDialog sfd = new FolderBrowserDialog(); sfd.Description = "Excel Rapor"; //sfd.Filter = "Excel|*.xls"; if (sfd.ShowDialog() == DialogResult.OK) { Excel.Application ExcelApp; Excel.Workbook objBook; object missing = System.Reflection.Missing.Value; object fileName = "normal.dot"; object newTemplate = false; object docType = 0; ExcelApp = new Excel.ApplicationClass(); System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); ExcelApp.Visible = false; objBook = ExcelApp.Workbooks.Add(missing); objBook.Sheets.Add(); Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Sheets[1]; objSheet.Name = "Rapor"; #region Anadolu #region baslık //objSheet.get_Range("A1", "A2").Merge(); objSheet.Cells[1, 1] = "SIRA NO"; objSheet.get_Range("A1", "A1").Font.Bold = true; objSheet.get_Range("A1", "A1").Font.Size = 11; objSheet.get_Range("A1", "A1").ColumnWidth = 7.57; objSheet.get_Range("A1", "A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet.get_Range("A1", "A1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("A1", "A1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; //objSheet.get_Range("B1", "B2").Merge(); objSheet.Cells[1, 2] = "MÜŞTERİ NO"; objSheet.get_Range("B1", "B1").Font.Bold = true; objSheet.get_Range("B1", "B1").Font.Size = 11; objSheet.get_Range("B1", "B1").ColumnWidth = 11.57; objSheet.get_Range("B1", "B1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; objSheet.get_Range("B1", "B1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; objSheet.get_Range("B1", "B1").Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; Dictionary<int, int> mypairs = new Dictionary<int, int>(); string[] productCols = Order.getUsedProductTypes(date); int colRow = 3; int colStartRow = 3; for (int j = 0; j < productCols.Length; j++) { objSheet.Cells[1, colRow] = productCols[j]; (objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true; (objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 11; (objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 4.71; (objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; mypairs.Add(colRow, 0); colRow++; } objSheet.Cells[1, colRow] = "BÖLGE"; (objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 15; (objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true; (objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 11; (objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; colRow++; objSheet.Cells[1, colRow] = "ÖDEME ŞEKLİ"; (objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 11.86; (objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 11; (objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true; (objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; colRow++; objSheet.Cells[1, colRow] = "AÇIKLAMA"; //(objSheet.Cells[1, colRow] as Excel.Range).ColumnWidth = 21; //(objSheet.Cells[1, colRow] as Excel.Range).RowHeight = 21; (objSheet.Cells[1, colRow] as Excel.Range).Font.Bold = true; (objSheet.Cells[1, colRow] as Excel.Range).Font.Size = 11; (objSheet.Cells[1, colRow] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble; (objSheet.Cells[1, colRow] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble; colRow++; #endregion int row = 3; List<Order> ords = Order.Orders_GetByDate(date); for (int k = 0; k < ords.Count; k++) { objSheet.Cells[row, 1] = k + 1; (objSheet.Cells[row, 1] as Excel.Range).Font.Bold = true; (objSheet.Cells[row, 1] as Excel.Range).Font.Color = -16776961; (objSheet.Cells[row, 1] as Excel.Range).Font.Size = 11; (objSheet.Cells[row, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, 1] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; objSheet.Cells[row, 2] = Customer.Customer.Customer_GetByID(ords[k].CustomerID).PreFix + " " + Customer.Customer.Customer_GetByID(ords[k].CustomerID).No.ToString().PadLeft(3, '0'); //(objSheet.Cells[row, 2] as Excel.Range).ColumnWidth = 10; (objSheet.Cells[row, 2] as Excel.Range).Font.Bold = true; (objSheet.Cells[row, 2] as Excel.Range).Font.Color = -16776961; (objSheet.Cells[row, 2] as Excel.Range).Font.Size = 11; (objSheet.Cells[row, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, 2] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; OrderDetail[] odtmp = ords[k].getDetails().ToArray(); int colIndex = 3; for (int i = 0; i < odtmp.Length; i++) { for (int j = 0; j < productCols.Length; j++) { if (Product.Product.Products_GetByID(odtmp[i].ProductID).Name == productCols[j]) { mypairs[colIndex + j] += odtmp[i].Unit; objSheet.Cells[row, colIndex + j] = odtmp[i].Unit; (objSheet.Cells[row, colIndex + j] as Excel.Range).Font.Bold = true; (objSheet.Cells[row, colIndex + j] as Excel.Range).Font.Color = -16776961; (objSheet.Cells[row, colIndex + j] as Excel.Range).Font.Size = 11; } (objSheet.Cells[row, colIndex + j] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, colIndex + j] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + j] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; } } Addresses[] addList = Addresses.Addresses_GetByCustomerID(ords[k].CustomerID).ToArray(); for (int i = 0; i < addList.Length; i++) { if (addList[i].Type == ords[k].AddressType) { objSheet.Cells[row, colIndex + productCols.Length] = addList[i].District; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Font.Bold = true; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Font.Color = -16776961; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Font.Size = 11; } (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; } objSheet.Cells[row, colIndex + productCols.Length + 1] = PayingTypes.PayingTypes_GetByID(ords[k].PayingType).PreFix; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Font.Bold = true; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Font.Color = -16776961; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Font.Size = 11; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 1] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; //objSheet.Cells[row , colIndex + productCols.Length + 2] = ords[k].Description; //(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Font.Bold = true; //(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Font.Color = -16776961; //(objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Font.Size = 11; (objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; (objSheet.Cells[row, colIndex + productCols.Length + 2] as Excel.Range).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; row++; } Dictionary<int, int>.KeyCollection keys = mypairs.Keys; foreach (int i in keys) { objSheet.Cells[3 + ords.Count, i] = mypairs[i]; } #endregion string fileNameXLS = date.Day.ToString("00") + "." + date.Month.ToString("00") + "." + date.Year + ".xls"; objBook.SaveAs(sfd.SelectedPath + "\\" + fileNameXLS, Excel.XlFileFormat.xlWorkbookNormal, null, null, true, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null); objBook.Close(false, Type.Missing, System.Type.Missing); ExcelApp.Quit(); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("tr-TR"); return sfd.SelectedPath + "\\" + fileNameXLS; } return ""; }
private void btn_XCLOpen_Click(object sender, EventArgs e) { XCLFileDialog.ShowDialog(); if (XCLFileDialog.FileName != "") { Microsoft.Office.Interop.Excel.ApplicationClass appEXCEL = new Microsoft.Office.Interop.Excel.ApplicationClass(); // create the workbook object by opening the excel file. //Workbook workBook = appEXCEL.Workbooks.Open(XCLFileDialog.FileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); this.workBook = appEXCEL.Workbooks.Open(XCLFileDialog.FileName, 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); // Get The Active Worksheet Using Sheet Name Or Active Sheet foreach (Worksheet mySheet in workBook.Sheets) { //Worksheet mySheet = (Worksheet)workBook.Sheets.get_Item(i); comboBox2.Items.Add(mySheet.Name); } Worksheet workSheet = (Worksheet)workBook.ActiveSheet; comboBox2.SelectedItem = workSheet.Name; lbl_messages.Text = ConfigurationSettings.AppSettings["sheetText1"] + workSheet.Name + ConfigurationSettings.AppSettings["sheetText2"]; toolStripStatusLabel2.Text = ConfigurationSettings.AppSettings["changeSheetText"]; appEXCEL.Quit(); button1.Enabled = true; button2.Enabled = false; btCreateXMLFlux.Enabled = true; button3.Enabled = true; webBrowser1.DocumentText = ConfigurationSettings.AppSettings["excelReadyText"]; webBrowser2.DocumentText = ConfigurationSettings.AppSettings["excelReadyText"]; } }
private void exportExcel() { Excel.Application xlApp = new Excel.ApplicationClass(); if (xlApp == null) { WebTools.Alert("Excel无法启动"); return; } // 创建Excel工作薄 Dictionary<string, List<FpStudentObject>> schoolStudents = QueryStudentGroupbySchool(null,qDateStart.Value,qDateEnd.Value); String[] sheetTitles={"缺受理号","未收费","已收费"}; String[] sheetColumns={"受理号","姓名","身份证号码","准驾车型","导入时间"}; String dir = MapPath(string.Format("~/temp/{0}/", Session.SessionID)); if (Directory.Exists(dir)) { Directory.Delete(dir,true); } Directory.CreateDirectory(dir); String pathPattern = dir+"{0}.xls"; foreach (string schoolCode in schoolStudents.Keys) { Excel.Workbook xlBook = xlApp.Workbooks.Add(Missing.Value); int[] rowIndexs = { 0, 0, 0 }; List<FpStudentObject> students = schoolStudents[schoolCode]; string schoolName=null; xlBook.Sheets.Add(Missing.Value, Missing.Value, sheetTitles.Length-1,Missing.Value); foreach(FpStudentObject student in students){ int sheetIndex = -1; if (string.IsNullOrEmpty(student.LSH)) { sheetIndex = 0; } else if (student.FEE_STATUE != "Y") { sheetIndex = 1; } else if (student.FEE_STATUE == "Y") { sheetIndex = 2; } if (sheetIndex >= 0) { Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[sheetIndex]; xlSheet.Name = string.Format("{0}({1})", sheetTitles[sheetIndex],rowIndexs[sheetIndex]+1); int colIndex = 1; int rowIndex = rowIndexs[sheetIndex]+1; Excel.Range rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; rang.Select(); rang.ColumnWidth = 200; rang.Formula = student.LSH; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; rang.Select(); rang.ColumnWidth = 120; rang.Formula = student.NAME; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; rang.Select(); rang.ColumnWidth = 250; rang.Formula = student.IDCARD; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; rang.Formula = student.CAR_TYPE; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; rang.Select(); rang.ColumnWidth = 120; rang.Value2 = student.CREATE_TIME.ToLongDateString(); rowIndexs[sheetIndex]++; } schoolName=student.SCHOOL_NAME; } if(string.IsNullOrEmpty(schoolName))continue; try { xlBook.Save(); xlBook.Saved = true; String path = string.Format(pathPattern, schoolName); if (File.Exists(path)) { File.Delete(path); } xlBook.SaveCopyAs(path); } finally { xlBook.Close(true, Missing.Value, Missing.Value); //System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook); } } xlApp.Workbooks.Close(); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); String zipFileName=string.Format("指纹记录[{0}][{1}].zip",qDateStart.Value,qDateEnd.Value); if (File.Exists(zipFileName)) { File.Delete(zipFileName); } string zipPath = dir + zipFileName; FileHelper.ZipDir(dir,zipPath); Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(zipFileName, Encoding.UTF8)); Response.WriteFile(zipPath); Response.End(); Response.Close(); }
private void button4_Click(object sender, EventArgs e) { /* Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook wb = xla.Workbooks.Add(Excel.XlSheetType.xlWorksheet); Excel.Worksheet ws = (Excel.Worksheet)xla.ActiveSheet; xla.Visible = true;*/ Excel.Application xlApp ; Excel.Workbook xlWorkBook ; Excel.Worksheet ws ; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); ws = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlApp.Visible = true; //Sayfa ayarlari ws.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; ws.get_Range("A1", "H8").Font.Bold = true; ws.get_Range("A1", "H8").Font.Size=14; // //ResourceManager resManager = new ResourceManager("PersonalManagementSystem.Resource1",System.Reflection.Assembly.GetExecutingAssembly()); // resmi kopyalayip yapistiran kod //System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(adminTab)); //Image image = ((System.Drawing.Image)(resources.GetObject("1"))); //System.Windows.Forms.Clipboard.SetDataObject(image, true); //ws.Paste(ws.get_Range("A2", "A2"), image); ws.Shapes.AddPicture(Application.StartupPath+"\\1.JPG", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 70, 70); ws.Cells[3, 3] = "T.C. MALTEPE UNIVERSITY"; ws.Cells[4, 3] = FacultyComboBox.Text.ToUpper(); ws.Cells[7, 1] = "Personnel Information"; ws.Cells[9, 1] = "Type"; ws.Cells[10, 1] = "Title"; ws.Cells[11, 1] = "Name"; ws.Cells[12, 1] = "SurName"; ws.Cells[13, 1] = "TC No"; ws.Cells[14, 1] = "Gender"; ws.Cells[15, 1] = "Blood Type"; ws.Cells[16, 1] = "Home Phone"; ws.Cells[17, 1] = "Mobile Phone"; ws.Cells[18, 1] = "Address"; ws.Cells[19, 1] = "Institution Reg. No."; ws.Cells[20, 1] = "Faculty"; ws.Cells[21, 1] = "Departmant"; ws.Cells[22, 1] = "Courses"; ws.Cells[23, 1] = "PMS User Type"; ws.Cells[10, 3] = ": " + TitleComboBox.Text.ToUpper(); ws.Cells[9, 3] = ": " + TypeComboBox.Text.ToUpper(); ws.Cells[11, 3] =": "+ NameTextBox.Text.ToUpper(); ws.Cells[12, 3] = ": " + SurnameTextBox.Text.ToUpper(); ws.Cells[13, 3] = ": " + TcNoTextBox.Text; ws.Cells[14, 3] = ": " + ((maleRadio.Checked) ? "MALE" : "FEMALE"); ws.Cells[15, 3] = ": " + BloodComboBox.Text; ws.Cells[16, 3] = ": " + homePhone.Text; ws.Cells[17, 3] = ": " + mobilePhone.Text; ws.Cells[18, 3] = ": " + address.Text.ToUpper(); ws.Cells[19, 3] = ": " + InsRegisterNo.Text; ws.Cells[20, 3] = ": " + FacultyComboBox.Text.ToUpper(); ws.Cells[21, 3] = ": " + DeptComboBox.Text.ToUpper(); ws.Cells[22, 3] = ": " + CourseComboBox.Text.ToUpper(); ws.Cells[23, 3] = ": " + ((radioAdmin.Checked) ? "ADMIN" : "PERSONNEL"); xlWorkBook.Close(true,misValue,misValue); xlApp.Quit(); ExcelKill(); }