private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Sheet 1 content"; xlWorkBook.SaveAs("d:\\csharp-Excel.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 d:\\csharp-Excel.xls"); }
static void Main(string[] args) { System.IO.StreamReader txtfile = new System.IO.StreamReader("D:\\Learning\\TestDLL\\Dllpath.txt"); int counter = txtfile.ReadLine().Count(); Console.WriteLine(counter); Console.ReadLine(); Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application(); if (xlapp== null) { Console.WriteLine("Excel is not installed"); return; } Excel.Workbook xlworkbook; Excel.Worksheet xlworksheet; object misvalue = System.Reflection.Missing.Value; xlworkbook = xlapp.Workbooks.Add(misvalue); String filepath = @"D:\Learning\TestDLL\pdfshell.dll"; FileVersionInfo myFileVersionInfo = FileVersionInfo.GetVersionInfo(filepath); xlworksheet=xlworkbook.Worksheets.get_Item(1); xlworksheet.Name = "DLLProperties"; xlworksheet.Cells[1, 1] ="NameofFile" ; xlworksheet.Cells[1, 2] = "ProductVersion"; xlworksheet.Cells.Font.Size = 25; xlworkbook.SaveAs("D:\\Learning\\demoexcel3.xls", Excel.XlFileFormat.xlWorkbookNormal, misvalue, misvalue, misvalue, misvalue, Excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue, misvalue, misvalue); xlworkbook.Close(true,misvalue,misvalue); xlapp.Quit(); }
public void SpremanjeFaktura() { string OtvorenaFaktura = Application.StartupPath + "\\CMR\\CMR.xlsx"; //unos podataka ExcelObj = new Microsoft.Office.Interop.Excel.Application(); theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); sheets = theWorkbook.Worksheets; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); excelApp.Workbooks.Open(OtvorenaFaktura); var BrojOtpremnice = (worksheet.Cells[1, 10] as Microsoft.Office.Interop.Excel.Range).Value; int NoviBrojOtpremnice = Convert.ToInt16(BrojOtpremnice) + 1; excelApp.Cells[1, 10] = NoviBrojOtpremnice; excelApp.ActiveWorkbook.Save(); theWorkbook.Close(0); excelApp.Quit(); foreach (Process proc in Process.GetProcessesByName("EXCEL")) { proc.Kill(); } Process.Start(OtvorenaFaktura); }
static void DrawCombinedLineChart(IEnumerable<Worksheet> source, Worksheet dest, string SeriesName, double maximum, double minimum) { Excel.ChartObjects xlCharts = (Excel.ChartObjects)dest.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 800, 400); Excel.Chart chartPage = myChart.Chart; myChart.Select(); chartPage.ChartType = Excel.XlChartType.xlXYScatterLines; Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis.MaximumScale = maximum; //xAxis.LogBase = 10; Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); yAxis.MaximumScale = 1; foreach (var sheet in source) { Excel.Series series1 = seriesCollection.NewSeries(); series1.Name = sheet.Name; series1.XValues = sheet.UsedRange.get_Range("A:A"); series1.Values = sheet.UsedRange.get_Range("B:B"); series1.Smooth = true; series1.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone; } }
private void drawGraph() { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 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); //Get all the sheets in the workbook while (thread1.IsAlive) { //son satır bulunuyor excel dosyasındaki Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range range = xlWorkSheet.get_Range("A1", last); int lastUsedRow = last.Row; int lastUsedColumn = last.Column; string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz // alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor string[] HeatingData = ReceiveData.Split(':'); string[] HeatingData2 = HeatingData[1].Split('D'); var result = HeatingData2[0]; double heating = Convert.ToDouble(result); theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz! string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss"); Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor! this.Invoke((MethodInvoker)delegate { chart1.Series["Series1"].Points.AddY(result); // excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100); xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman; }); } } catch { // MessageBox.Show("Dosya bulunamadı"); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Zaman"; xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius"; xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.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("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır"); } }
/// <summary> /// 表导出Excel /// </summary> /// <param name="modeFilePath">模板路径</param> /// <param name="saveFilePath">保存文件路径</param> /// <param name="tableName">导出数据名称</param> public void ListToExcel(string modeFilePath, string saveFilePath, string tableName) { //需要添加 Microsoft.Office.Interop.Excel引用 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); if (app == null) { Console.Write("服务器上缺少Excel组件,需要安装Office软件。"); } app.Visible = false; app.UserControl = true; Excel.Workbooks workbooks = app.Workbooks; //加载模板 Excel._Workbook workbook = workbooks.Add(modeFilePath); Excel.Sheets sheets = workbook.Sheets; try { switch (tableName) { case "SingleInspection": //SettlementObservationWriteData(workbook, sheets); SingleInspectionWriteData(workbook, sheets,saveFilePath); break; case "LoftingScore": LoftingScoreWriteData(workbook, sheets); break; //......... } //保存生成的Excel文件。 //Missing 在System.Reflection命名空间下。 workbook.SaveAs(saveFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //按顺序释放资源。 eb.NAR(sheets); eb.NAR(workbook); eb.NAR(workbooks); app.Quit(); eb.NAR(app); eb.KillProcess("EXCEL");//杀死excel进程 switch(tableName) { case "SingleInspection": ef.Save_Excel(saveFilePath, "施工测量放样报验单"); break; } ef.Get_Excel(14, @"D:\123.xls"); } catch (Exception ex) { MessageBox.Show("导出异常" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { eb.KillProcess("EXCEL");//杀死excel进程 } }
public void export(DataTable dt) { xlApp = new Excel.Application(); xlApp.Visible = true; try { xlApp.Workbooks.Add(Type.Missing); xlApp.Interactive = false; xlApp.EnableEvents = false; xlSheet = (Excel.Worksheet)xlApp.Sheets[1]; xlSheet.Name = "Итог"; int collInd = 0; int rowInd = 0; string data = ""; for (int i = 0; i < dt.Columns.Count; i++) { data = dt.Columns[i].ColumnName.ToString(); xlSheet.Cells[1, i + 1] = data; xlSheetRange = xlSheet.get_Range("A1:Z1", Type.Missing); xlSheetRange.WrapText = true; xlSheetRange.Font.Bold = true; } for (rowInd = 0; rowInd < dt.Rows.Count; rowInd++) { for (collInd = 0; collInd < dt.Columns.Count; collInd++) { data = dt.Rows[rowInd].ItemArray[collInd].ToString(); xlSheet.Cells[rowInd + 2, collInd + 1] = data; } } xlSheetRange = xlSheet.UsedRange; xlSheetRange.Columns.AutoFit(); xlSheetRange.Rows.AutoFit(); } catch (Exception ex) { Console.WriteLine(ex); } finally { xlApp.Visible = true; xlApp.Interactive = true; xlApp.ScreenUpdating = true; xlApp.UserControl = true; } }
public void Read(string filename) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(filename); // Get worksheet names foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Worksheets) Debug.WriteLine(sh.Name); // Get values from sheets SH1 and SH3 (in my file) //object val1 = wb.Sheets["SH1"].Cells[1, "A"].Value2; //object val3 = wb.Sheets["SH3"].Cells[1, "A"].Value2; //Debug.WriteLine("{0} / {1}", val1, val3); wb.Close(); excel.Quit(); }
public static void Excel_FromDataTable(DataTable dt) { // Create an Excel object and add workbook... Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template??? // Add column headings... int iCol = 0; foreach (DataColumn c in dt.Columns) { iCol++; excel.Cells[1, iCol] = c.ColumnName; } // for each row of data... int iRow = 0; foreach (DataRow r in dt.Rows) { iRow++; // add each row's cell data... iCol = 0; foreach (DataColumn c in dt.Columns) { iCol++; excel.Cells[iRow + 1, iCol] = r[c.ColumnName]; } } // Global missing reference for objects we are not defining... object missing = System.Reflection.Missing.Value; // If wanting to Save the workbook... workbook.SaveAs("G:/IssueLogger/IssueLog.xls", Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);//Change_Me // If wanting to make Excel visible and activate the worksheet... excel.Visible = true; Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet; ((Excel._Worksheet)worksheet).Activate(); // If wanting excel to shutdown... ((Excel._Application)excel).Quit(); }
//Matrix R private void button10_Click(object sender, EventArgs e) { string projectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory())); string path = projectPath + @"/HistEqDataSet/Data-Latih"; //string path = projectPath + @"/ImageDataSet/Data-Latih"; string path2 = projectPath + @"/pcaProcess/MatrixR.xlsx"; Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook wb = ExcelApp.Workbooks.Open(path2); Excel.Worksheet sh = (Excel.Worksheet)wb.Sheets["Sheet1"]; DirectoryInfo di = new DirectoryInfo(path); FileInfo[] Images = di.GetFiles("*.bmp"); FileInfo[] sortedImages = Images.OrderBy(f => f.CreationTime).ToArray(); int i = 1; int img = 0; for (int j = 1; j <=2178; j++) { Image image = Image.FromFile(path + "/" + sortedImages[img].Name); Bitmap b = new Bitmap(image); for (int y = 0; y < 48; y++) { for (int x = 0; x < 48; x++) { Color c = b.GetPixel(x, y); int rgb = c.R; sh.Cells[i, j].Value = rgb; i++; } } img = img + 1; i = 1; } wb.Save(); wb.Close(); MessageBox.Show("Matrix R Selesai", "Done!", MessageBoxButtons.OK); }
/// <summary> /// Deze methode gaat de gegevens op een bepaalde plaats in een excel document ophalen /// </summary> /// <param name="fileName">De file waaruit de gegevens gehaald moeten worden</param> private static void ReadExcelFileSAX(string fileNaam) { Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Visible = false; Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(fileNaam, 0, true); Excel.Sheets excelSheets = excelWorkbook.Worksheets; string currentSheet = "Sheet1"; Excel.Worksheet xlws = (Excel.Worksheet)excelSheets.get_Item(currentSheet); gewichtLinksVooraan = ((Excel.Range)xlws.Cells[12, 4]).Value2.ToString(); gewichtRechtsVooraan = ((Excel.Range)xlws.Cells[13, 4]).Value.ToString(); gewichtAchterLinks = ((Excel.Range)xlws.Cells[15, 4]).Value.ToString(); gewichtAchterRechts = ((Excel.Range)xlws.Cells[16, 4]).Value.ToString(); gewichtAchterMidden = ((Excel.Range)xlws.Cells[17, 4]).Value.ToString(); }
protected void ExportExcel(DataTable dt) { if (dt == null || dt.Rows.Count == 0) return; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { return; } System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("th-TH"); Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; Microsoft.Office.Interop.Excel.Range range; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString(); } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; } xlApp.Visible = true; }
/// <summary> /// "Export to Excel" button, uses textbox to left to determine fileName /// </summary> private void btnCreateExcelFile_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); //Content Type ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 30; Microsoft.Office.Interop.Excel.Workbook wb = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet wsContentType = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet; // storing header part in Excel for (int i = 1; i < grdReport.Columns.Count + 1; i++) { wsContentType.Cells[1, i] = grdReport.Columns[i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < grdReport.Rows.Count - 1; i++) { for (int j = 0; j < grdReport.Columns.Count; j++) { wsContentType.Cells[i + 2, j + 1] = grdReport.Rows[i].Cells[j].Value.ToString(); } } if (saveContentType.ShowDialog() == System.Windows.Forms.DialogResult.OK) { ExcelApp.ActiveWorkbook.SaveCopyAs(saveContentType.FileName); ExcelApp.ActiveWorkbook.Saved = true; } ExcelApp.Quit(); } catch (Exception exp) { LogError(exp); MessageBox.Show("Failed to create Excel file"); } }
public static String openFile() { string filePath = string.Empty; string fileExt = string.Empty; OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file //cbbSheetName.Items.Clear(); //txtSheet.Clear(); if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) { //txtfilename.Text = file.FileName; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open(file.FileName); foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets) { //cbbSheetName.Items.Add(wSheet.Name); } } return(file.FileName); }
public ExcelWriter(string filename, ReportType type) { Report = type; oApplication = new Microsoft.Office.Interop.Excel.Application(); oWorkbook = null; oWorksheet = null; string outputPath = Filename = System.Configuration.ConfigurationManager.AppSettings["OutputLocation"]; if (type == ReportType.MonthyTally) { Filename = Path.Combine(outputPath, "MonthlyTally"); } if (!Directory.Exists(Filename)) Directory.CreateDirectory(Filename); Filename = Path.Combine(Filename, filename); if (File.Exists(Filename)) File.Delete(Filename); logger = Logger.CreateLogger(); }
private void Button4_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook; Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet; //Книга. ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); //Таблица. ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelApp.Columns.ColumnWidth = 20; for (int i = 0; i < dataGridView1.Rows.Count; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { ExcelApp.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value; } } //Вызываем нашу созданную эксельку. ExcelApp.Visible = true; ExcelApp.UserControl = true; }
private void BtnConvertirScotiabank_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = true; Microsoft.Office.Interop.Excel.Workbook libros; Microsoft.Office.Interop.Excel.Worksheet hoja1, hoja2; libros = app.Workbooks.Add(); libros.Sheets.Add(); TablaScotiabank(); hoja1 = (Microsoft.Office.Interop.Excel.Worksheet)libros.Worksheets.get_Item(1); hoja1.Name = "Exported from gridview"; for (int i = 0; i < GritConvert.Rows.Count - 1; i++) { for (int j = 0; j < GritConvert.Columns.Count; j++) { hoja1.Cells[i + 1, j + 1] = GritConvert.Rows[i].Cells[j].Value.ToString(); } } DatoSapiensScotiabank(); hoja2 = (Microsoft.Office.Interop.Excel.Worksheet)libros.Worksheets.get_Item(2); hoja2.Name = "Exported from gridview2"; for (int i = 0; i < GritConvert.Rows.Count - 1; i++) { for (int j = 0; j < GritConvert.Columns.Count; j++) { hoja2.Cells[i + 1, j + 1] = GritConvert.Rows[i].Cells[j].Value.ToString(); } } libros.SaveAs(@"C:\Users\arni_\OneDrive\Documentos\trabajo para hacer star global\prueba\ComparaScotiabank.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); app.Quit(); }
public void ExportExcel(DevExpress.XtraGrid.Views.Grid.GridView gridView3) { string filename = ""; using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Workbook|*.xlsx" }) { if (sfd.ShowDialog() == DialogResult.OK) { filename = sfd.FileName; } } try { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Application.Workbooks.Add(Type.Missing); for (int i = 1; i < gridView3.Columns.Count + 1; i++) { ExcelApp.Cells[1, i] = gridView3.Columns[i].Caption; } for (int i = 0; i < gridView3.RowCount - 1; i++) { for (int j = 0; j < gridView3.Columns.Count; j++) { ExcelApp.Cells[i + 2, j + 1] = gridView3.Columns[j].ToString(); } } ExcelApp.ActiveWorkbook.SaveCopyAs(filename); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); MessageBox.Show("Ghi file thành công tại thư mục chạy ứng dụng"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static void ExportToExcel(List <TableData> list) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (int i = 0; i < list.Count; i++) { int row = int.Parse(list[i].Line); List <string> values = list[i].Values; int index = 0; foreach (string x in values) { index++; xlWorkSheet.Cells[row + 1, index] = values[index - 1]; } } //xlWorkSheet.Cells[1, 1] = list[0].Values[0]; //xlWorkSheet.Cells[1, 2] = list[0].Values[1]; //xlWorkSheet.Cells[1, 3] = list[0].Values[2]; xlWorkBook.SaveAs("c:\\temp\\csharp-Excel.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 csharp-Excel.xls"); }
public void Inputexcel(Microsoft.Office.Interop.Excel.Application excelApp1) { try { string ZFCEPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "System\\报价单.xls"); //需要换 成日期的导出表 System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook analyWK = excelApp.Workbooks.Open(ZFCEPath, Type.Missing, true, Type.Missing, "htc", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelApp.Visible = true; excelApp.ScreenUpdating = true; Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)analyWK.Worksheets[1]; Microsoft.Office.Interop.Excel.Range rng; // rng = WS.get_Range(WS.Cells[2, 1], WS.Cells[WS.UsedRange.Rows.Count, 45]); rng = WS.Range[WS.Cells[1, 1], WS.Cells[WS.UsedRange.Rows.Count, 16]]; int rowCount = WS.UsedRange.Rows.Count - 1; object[,] o = new object[1, 1]; o = (object[, ])rng.Value2; Microsoft.Office.Interop.Excel.AllowEditRanges ranges = WS.Protection.AllowEditRanges; ranges.Add("Information", WS.Range["B2:E6"], Type.Missing); WS.Protect("123", true); clsCommHelp.CloseExcel(excelApp, analyWK); } catch (Exception ex) { throw ex; } }
/// <summary> /// /// </summary> /// <param name="dt"></param> protected void ExportExcel(DataTable dt) { if (dt == null || dt.Rows.Count == 0) { return; } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { return; } System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; Microsoft.Office.Interop.Excel.Range range; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString(); } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; } xlApp.Visible = true; }
private void button2_Click(object sender, EventArgs e) { if (dataGridView1.Rows.Count > 0) { try { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.Sheets[1]; worksheet = workbook.ActiveSheet; worksheet.Name = "Exported from gridview"; for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText; } for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } } app.Columns.AutoFit(); app.Visible = true; MessageBox.Show("Exported"); } catch { } } }
private void button2_Click(object sender, EventArgs e) { Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook ExcelWorkBook; Excel.Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelWorkSheet.Cells[1, 3] = "Измерения"; int i; for (int j = 2; j < Names2.Count; j++) { ExcelWorkSheet.Cells[2, j] = Names2.ElementAt(j); } for (i = 3; i < AllIzmer2.Count + 3; i++) { for (int j = 2; j < Names2.Count; j++) { ExcelWorkSheet.Cells[i, j] = AllIzmer2.ElementAt(i - 3).Subordinate[j]; } } int k = i++; for (int j = 2; j < Names1.Count; j++) { ExcelWorkSheet.Cells[k, j] = Names1.ElementAt(j); } k++; for (i = k; i < AllIzmer1.Count + k; i++) { for (int j = 2; j < Names1.Count; j++) { ExcelWorkSheet.Cells[i, j] = AllIzmer1.ElementAt(i - k).Subordinate[j]; } } ExcelApp.Visible = true; }
public System.Data.DataTable ConvertToDataTable(string path) { System.Data.DataTable dt = null; try { object rowIndex = 14; dt = new System.Data.DataTable(); DataRow row; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet; int temp = 1; while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, temp]).Value2 != null) { dt.Columns.Add(Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, temp]).Value2)); temp++; } rowIndex = Convert.ToInt32(rowIndex) + 1; int columnCount = temp; temp = 1; while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, temp]).Value2 != null) { row = dt.NewRow(); for (int i = 1; i < columnCount; i++) { row[i - 1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, i]).Value2); } dt.Rows.Add(row); rowIndex = Convert.ToInt32(rowIndex) + 1; temp = 1; } app.Workbooks.Close(); } catch (Exception ex) { //lblError.Text = ex.Message; } return(dt); }
private void btExport_Click(object sender, EventArgs e) { if (gridwiew.Rows.Count > 0) { Microsoft.Office.Interop.Excel.Application xcelApp = new Microsoft.Office.Interop.Excel.Application(); xcelApp.Application.Workbooks.Add(Type.Missing); for (int i = 1; i < gridwiew.Columns.Count + 1; i++) { xcelApp.Cells[1, i] = gridwiew.Columns[i - 1].HeaderText; } for (int i = 0; i < gridwiew.Rows.Count; i++) { for (int j = 0; j < gridwiew.Columns.Count; j++) { xcelApp.Cells[i + 2, j + 1] = gridwiew.Rows[i].Cells[j].Value.ToString(); } } xcelApp.Columns.AutoFit(); xcelApp.Visible = true; } }
/// <summary> /// Создает Excel таблицу, указывает шапку и др настройки. /// </summary> private void CreateExcelTableSettings() { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "№"; xlWorkSheet.Cells[1, 2] = "НАИМЕНОВАНИЕ"; xlWorkSheet.Cells[1, 3] = "ДЕЙСТВИЯ"; xlWorkSheet.Cells[1, 4] = "РЕЗУЛЬТАТ"; xlWorkSheet.Cells[2, 1] = "1"; xlWorkSheet.Cells[2, 2] = "2"; xlWorkSheet.Cells[2, 3] = "3"; xlWorkSheet.Cells[2, 4] = "4"; xlWorkSheet.Cells[1, 1].Font.Bold = true; xlWorkSheet.Cells[1, 2].Font.Bold = true; xlWorkSheet.Cells[1, 3].Font.Bold = true; xlWorkSheet.Cells[1, 4].Font.Bold = true; xlWorkSheet.Cells[2, 1].Font.Bold = true; xlWorkSheet.Cells[2, 2].Font.Bold = true; xlWorkSheet.Cells[2, 3].Font.Bold = true; xlWorkSheet.Cells[2, 4].Font.Bold = true; xlWorkSheet.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[2, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[2, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[2, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[2, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; (xlWorkSheet.Columns["A", Type.Missing]).ColumnWidth = 5; (xlWorkSheet.Columns["B", Type.Missing]).ColumnWidth = 25; (xlWorkSheet.Columns["C", Type.Missing]).ColumnWidth = 60; (xlWorkSheet.Columns["D", Type.Missing]).ColumnWidth = 60; row = 3; }
private void button6_Click(object sender, EventArgs e) { Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application(); if (xlapp == null) { MessageBox.Show("Microsft Excel Not Found", "Error", MessageBoxButtons.OKCancel); } Excel.Workbook workbok; Excel.Worksheet workshet; object misValue = System.Reflection.Missing.Value; workbok = xlapp.Workbooks.Add(misValue); workshet = (Excel.Worksheet)workbok.Worksheets.get_Item(1); // workshet = (Excel.Worksheets)workbok.Worksheets.get_Item(1); workshet.Cells[1, 1] = "Sno"; workshet.Cells[1, 2] = "Faculty Code"; workshet.Cells[1, 3] = "Student ID"; workshet.Cells[1, 4] = "Internal"; workshet.Cells[1, 5] = "Report Writing"; workshet.Cells[1, 6] = "Technical Content"; workshet.Cells[1, 7] = "Presentation"; workshet.Cells[1, 8] = "Total"; // WebClient we = new WebClient(); Accf[] rst = JsonConvert.DeserializeObject <Accf[]>(res_strings_fact); for (int i = 0; i < rst.Length; i++) { workshet.Cells[i + 2, 1] = rst[i].ID; workshet.Cells[i + 2, 2] = rst[i].Faculty_Code; workshet.Cells[i + 2, 3] = rst[i].Student_id; workshet.Cells[i + 2, 4] = rst[i]._internal; workshet.Cells[i + 2, 5] = rst[i].Report_Writting; workshet.Cells[i + 2, 6] = rst[i].Technical_Content; workshet.Cells[i + 2, 7] = rst[i].Presentaion; workshet.Cells[i + 2, 8] = rst[i].Total; } workbok.SaveAs("d:\\According Faculty.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workbok.Close(true, misValue, misValue); xlapp.Quit(); }
private void btnExport_Click(object sender, EventArgs e) //자료 내보내기 { //저장할 디렉토리를 물어보고, 거기에 저장 SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "Excel Files(*.xls)|*.xls"; dlg.Title = "엑셀 파일로 내보내기"; if (dlg.ShowDialog() == DialogResult.OK) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlworkbook = xlApp.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlworkbook.Worksheets.get_Item(1); //타이틀 for (int k = 0; k < dgvMember.Columns.Count; k++) { xlWorkSheet.Cells[1, k + 1] = dgvMember.Columns[k].Name; //((Excel.Range)xlSheet.Cells[2, i + 1]).Interior.Color = Excel.XlRgbColor.rgbGhostWhite; } for (int i = 0; i < dgvMember.Rows.Count; i++) { for (int k = 0; k < dgvMember.Columns.Count; k++) { xlWorkSheet.Cells[i + 2, k + 1] = dgvMember[k, i].Value.ToString(); } } xlworkbook.SaveAs(dlg.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal); xlworkbook.Close(true); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlworkbook); releaseObject(xlApp); MessageBox.Show("Excel Export가 완료되었습니다."); } }
public void cargaFormatoTableta(string Path) { System.IO.FileInfo fi = new System.IO.FileInfo(Path); if (fi.Exists) { fi.Delete(); } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "CCT"; xlWorkSheet.Cells[1, 2] = "N° de serie"; xlWorkBook.SaveAs(Path); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }
//string filePath; public string gettext(string filePath) { // Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; string str = ""; int rCnt = 0; int cCnt = 0; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); int totalSheets = (int)xlWorkBook.Sheets.Count; for (int scnt = 1; scnt <= totalSheets; scnt++) { xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(scnt); 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 + " "; } } releaseObject(xlWorkSheet); } xlWorkBook.Close(true, null, null); xlApp.Quit(); releaseObject(xlWorkBook); releaseObject(xlApp); //MessageBox.Show(str); return(str); }
void createfile() { Excel.Application xxlApp = new Microsoft.Office.Interop.Excel.Application(); if (xxlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xxlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); position = 0; WriteArray(users + 1, 8, xlWorkSheet); xxlApp.Visible = true; xlWorkSheet.Visible = Excel.XlSheetVisibility.xlSheetVisible; }
public void Convert(String metricsFolder) { metrics_files = Directory.GetFiles(metricsFolder); foreach (String file in metrics_files) { string[] nameArray = file.Split('\\'); //get an array of all elements split at \\ name = nameArray[nameArray.Length - 1]; //return the last element of the array which shoudl always be the file name string path = metricsFolder + "\\backups\\"; //check to make sure backup directory exists, otherwise create it if (!Directory.Exists(path)) Directory.CreateDirectory(path); var app = new Microsoft.Office.Interop.Excel.Application(); var wb = app.Workbooks.Open(file); wb.SaveAs(Filename: file + "x", FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook); wb.Close(); app.Quit(); path += name;//add the file name to the backup path so the move can be completed File.Move(file, path); } }
private void button1_Click(object sender, EventArgs e) { //dataGridView2.Visible = false; dataGridView2.DataSource = null; dataGridView2.ColumnCount = 9; Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook workbook = app.Workbooks.Open(@"d:\csharp.net-informations2.xls"); Excel.Worksheet worksheet = workbook.ActiveSheet; int rcount = worksheet.UsedRange.Rows.Count; int i = 0; for (; i + 1 < rcount; i++) { dataGridView2.Columns[0].HeaderText = worksheet.Cells[1, 1].Value; dataGridView2.Columns[1].HeaderText = worksheet.Cells[1, 2].Value; dataGridView2.Columns[2].HeaderText = worksheet.Cells[1, 3].Value; dataGridView2.Columns[3].HeaderText = worksheet.Cells[1, 4].Value; dataGridView2.Columns[4].HeaderText = worksheet.Cells[1, 5].Value; dataGridView2.Columns[5].HeaderText = worksheet.Cells[1, 6].Value; dataGridView2.Columns[6].HeaderText = worksheet.Cells[1, 7].Value; dataGridView2.Columns[7].HeaderText = worksheet.Cells[1, 8].Value; dataGridView2.Columns[8].HeaderText = worksheet.Cells[1, 9].Value; // dataGridView2.Rows[0].Cells["Column1"].Value = worksheet.Cells[1, 1].Value; //ataGridView1.Rows[i].Cells["Column2"].Value = worksheet.Cells[i + 2, 2].Value; dataGridView2.Rows.Add(worksheet.Cells[i + 2, 1].Value, worksheet.Cells[i + 2, 2].Value, worksheet.Cells[i + 2, 3].Value, worksheet.Cells[i + 2, 4].Value, worksheet.Cells[i + 2, 5].Value, worksheet.Cells[i + 2, 6].Value, worksheet.Cells[i + 2, 7].Value, worksheet.Cells[i + 2, 8].Value, worksheet.Cells[i + 2, 9].Value); } workbook.Close(); app.Quit(); }
public TO_EXEL(string lo, DataGridView datalov, string spb, DataGridView dataspbv, string spblo , DataGridView dataspblov, string svod, DataGridView svodv) { DataGridView datalo = datalov; DataGridView dataspb = dataspbv; DataGridView dataspblo = dataspblov; DataGridView datasvod = svodv; Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook ExcelWorkBook; Excel.Worksheet ExcelWorklo; Excel.Worksheet ExcelWorkspb; Excel.Worksheet ExcelWorkspblo; Excel.Worksheet ExcelWorksvod; ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); ExcelWorkBook.Sheets.Add(); ExcelWorkBook.Sheets.Add(); ExcelWorklo = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); //ap = dataGridView1.DataSource // ExcelWorklo = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelWorklo.Name = lo; export(ExcelWorklo, datalo); ExcelWorkspb = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(2); ExcelWorkspb.Name = spb; export(ExcelWorkspb, dataspb); ExcelWorkspblo = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(3); ExcelWorkspblo.Name = spblo; export(ExcelWorkspblo, dataspblo); // ExcelWorksvod = (Excel.Worksheet)ExcelWorkBook.Worksheets. ExcelWorksvod = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(4); ExcelWorksvod.Name = svod; export(ExcelWorksvod, datasvod); ExcelApp.Visible = true; }
public static void ExportToExcel(string strFileName, string strSheetName) { // Run the garbage collector GC.Collect(); // Delete the file if it already exists if (System.IO.File.Exists(strFileName)) { System.IO.File.SetAttributes(strFileName, FileAttributes.Normal); System.IO.File.Delete(strFileName); } // Open an instance of excel. Create a new workbook. // A workbook by default has three sheets, so if you just want a single one, delete sheet 2 and 3 Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Excel._Workbook xlWB = (Excel._Workbook)xlApp.Workbooks.Add(Missing.Value); Excel._Worksheet xlSheet = (Excel._Worksheet)xlWB.Sheets[1]; ((Excel._Worksheet)xlWB.Sheets[2]).Delete(); ((Excel._Worksheet)xlWB.Sheets[2]).Delete(); xlSheet.Name = strSheetName; // Write a value into A1 xlSheet.Cells[1, 1] = "Some value"; // Tell Excel to save your spreadsheet xlWB.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); xlApp.Quit(); // Release the COM object, set the Excel variables to Null, and tell the Garbage Collector to do its thing System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWB); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlSheet = null; xlWB = null; xlApp = null; GC.Collect(); }
public void cargaInstitucionMasivo(string Path) { System.IO.FileInfo fi = new System.IO.FileInfo(Path); if (fi.Exists) { fi.Delete(); } else { fi.Create(); } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 2; xlWorkSheet.Cells[1, 1] = "CCT"; xlWorkSheet.Cells[1, 2] = "Nombre"; xlWorkSheet.Cells[1, 3] = "Grado [S para Superior, M para Medio Superior]"; xlWorkSheet.Cells[1, 4] = "Estado"; xlWorkSheet.Cells[1, 5] = "Municipio"; xlWorkSheet.Cells[1, 6] = "Colonia"; xlWorkSheet.Cells[1, 7] = "Calle"; xlWorkSheet.Cells[1, 8] = "Numero Exterior"; xlWorkSheet.Cells[1, 9] = "C.P."; xlWorkSheet.Cells[1, 10] = "Telefono"; xlWorkSheet.Cells[1, 11] = "Correo"; xlWorkSheet.Cells[1, 12] = "Oficinas (Si o No)"; xlWorkSheet.Cells[1, 13] = "Dependencia"; xlWorkBook.SaveAs(Path); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }
public void cargaUsuariosMasivo(string Path) { System.IO.FileInfo fi = new System.IO.FileInfo(Path); if (fi.Exists) { fi.Delete(); } else { fi.Create(); } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 2; xlWorkSheet.Cells[1, 1] = "CURP"; xlWorkSheet.Cells[1, 2] = "RFC ó CCT donde pertenece"; xlWorkSheet.Cells[1, 3] = "C.P."; xlWorkSheet.Cells[1, 4] = "Estado Recidencia"; xlWorkSheet.Cells[1, 5] = "Municipio"; xlWorkSheet.Cells[1, 6] = "Colonia"; xlWorkSheet.Cells[1, 7] = "Calle"; xlWorkSheet.Cells[1, 8] = "Numero Interior"; xlWorkSheet.Cells[1, 9] = "Numero Exterior"; xlWorkSheet.Cells[1, 10] = "Telefono"; xlWorkSheet.Cells[1, 11] = "Telefono Celular"; xlWorkSheet.Cells[1, 12] = "Correo"; xlWorkBook.SaveAs(Path); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }
private void gomb_excel_Click(object sender, RoutedEventArgs e) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Az excel nincs rendesen telepítve!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Számítógép neve:"; xlWorkSheet.Cells[1, 2] = a1; xlWorkSheet.Cells[2, 1] = "Felhasználó neve:"; xlWorkSheet.Cells[2, 2] = a2; xlWorkSheet.Cells[3, 1] = "Verzió:"; xlWorkSheet.Cells[3, 2] = a3; xlWorkSheet.Cells[4, 1] = "Operációs rendszer platformja:"; xlWorkSheet.Cells[4, 2] = a5; xlWorkSheet.Cells[5, 1] = "Processzor szálak száma:"; xlWorkSheet.Cells[5, 2] = a6; xlWorkSheet.Cells[6, 1] = "Program mappája:"; xlWorkSheet.Cells[6, 2] = a7; xlWorkSheet.Cells[7, 1] = "Rendszer mappája:"; xlWorkSheet.Cells[7, 2] = a8; xlWorkSheet.Cells[8, 1] = "Domain név:"; xlWorkSheet.Cells[8, 2] = a9; xlWorkBook.SaveAs("D:\\Suli\\Komplex_programozas\\HardWare_SoftWare_Monitor\\HardWare_SoftWare_Monitor\\data.xlsx", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); MessageBox.Show("Az adatok sikeresen elmentve!"); }
private void Convert_Dataset_to_Excel() { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Visible = true; Workbook xlWorkbook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); DataTableCollection collection = ds.Tables; for (int i = collection.Count; i > 0; i--) { Sheets xlSheets = null; Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Worksheets; xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataTable table = collection[i - 1]; xlWorksheet.Name = table.TableName; for (int j = 1; j < table.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < table.Rows.Count; k++) { for (int l = 0; l < table.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = table.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); } ((Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; }
/// <summary> /// Save the Current working workbook /// CLose the Current working workbook /// Quit the Excel Interop process /// /// After calling this function, you should NOT use this Builder ANYMORE /// </summary> public void CloseWorkbook() { mExcelApp.ScreenUpdating = true; mExcelApp.Visible = false; mExcelApp.DisplayAlerts = false; mBook.Save(); mBook.Close(Type.Missing, Type.Missing, Type.Missing); mSheet = null; mBook = null; if (mExcelApp != null) { mExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcelApp); mExcelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }
private void SaveXLS_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ObjWorkBook; Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet; //Книга. ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value); //Таблица. ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1]; //Значения [y - строка,x - столбец] for (int i = 0; i <= ArrData.rowNumber; i++) { ObjWorkSheet.Cells[i + 1, 1] = ArrData.arrNum[i]; ObjWorkSheet.Cells[i + 1, 2] = ArrData.arrValue[i]; } string fileName = String.Empty; SaveFileDialog saveFileXLS = new SaveFileDialog(); saveFileXLS.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveFileXLS.FilterIndex = 2; saveFileXLS.RestoreDirectory = true; if (saveFileXLS.ShowDialog() == DialogResult.OK) { fileName = saveFileXLS.FileName; } else { return; } ObjWorkBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ObjWorkBook.Close(false, Type.Missing, Type.Missing); ObjExcel.Quit(); saveFileXLS.Dispose(); }
private void toolStripButton23_Click(object sender, EventArgs e) { //Экспорт в Excel Microsoft.Office.Interop.Excel.Application exApp = new Microsoft.Office.Interop.Excel.Application(); exApp.Visible = true; exApp.Workbooks.Add(); Worksheet workSheet = (Worksheet)exApp.ActiveSheet; workSheet.Cells[1, 1] = "Ведомость о питании по классам"; workSheet.Cells[3, 1] = "Месяц"; workSheet.Cells[3, 1].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; workSheet.Cells[3, 2] = "Год"; workSheet.Cells[3, 2].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; workSheet.Cells[3, 3] = "Название класса"; workSheet.Cells[3, 3].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; workSheet.Cells[3, 4] = "Название типа питания"; workSheet.Cells[3, 4].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; workSheet.Cells[3, 5] = "Количество"; workSheet.Cells[3, 5].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; workSheet.Cells[3, 6] = "Цена питания"; workSheet.Cells[3, 6].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; int rowExcel = 4; for (int i = 0; i < dataGridView17.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView17.Columns.Count; j++) { workSheet.Cells[i + rowExcel, j + 1] = dataGridView17.Rows[i].Cells[j].Value.ToString(); workSheet.Cells[i + rowExcel, j + 1].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } } workSheet.SaveAs("Ведомость о питании по классам.xlsx"); MessageBox.Show("Экспорт данных завершен...", "Школьное питание", MessageBoxButtons.OK, MessageBoxIcon.Information); exApp.Quit(); }
public void actualizaraexcel(DataGridView dataGridView1) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Open(txtArchivo.Text); int IndiceColumna = 0; foreach (DataGridViewColumn col in dataGridView1.Columns) //Columnas { IndiceColumna++; excel.Cells[1, IndiceColumna] = col.Name; } int indiceFila = 0; foreach (DataGridViewRow row in dataGridView1.Rows) //Filas { indiceFila++; IndiceColumna = 0; foreach (DataGridViewColumn col in dataGridView1.Columns) { IndiceColumna++; excel.Cells[indiceFila + 1, IndiceColumna] = row.Cells[col.Name].Value; } } //excel.GetSaveAsFilename(txtArchivo.Text); //excel.Visible = true; //excel.Workbooks.Add(); excel.Visible = true; /*excel.Range["A1"].Value = "ID"; * excel.Range["B1"].Value = "USUARIO"; * excel.Range["C1"].Value = "DIRECCION"; * excel.Range["D1"].Value = "TELEFONO"; * excel.Range["E1"].Value = "EDAD"; * excel.Range["A2"].Select();*/ MessageBox.Show("Hoja de Datos Actualizada"); }
private void массив_excel(object[,] массив, int строк, int столбцов) { Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); string curDir = System.IO.Directory.GetCurrentDirectory(); object шаблон = curDir + @"\платежи_улица.xlt"; if (!System.IO.File.Exists(шаблон.ToString())) { MessageBox.Show("Нет файла " + шаблон.ToString()); return; } Microsoft.Office.Interop.Excel.Workbook o = oExcel.Workbooks.Add(Template: шаблон); Excel.Worksheet eList = (Excel.Worksheet)o.Worksheets[1]; // Excel.ListObject tab1 = eList.ListObjects[1]; oExcel.Application.Visible = true; eList.Cells[1, 2] = this.Text + " " + DateTime.Today.ToShortDateString(); eList.Cells[3, 1].Select(); eList.Cells[3, 1].Copy(); Excel.Range rg = eList.get_Range("A3", Type.Missing); rg = rg.get_Resize(строк - 1, столбцов); rg.PasteSpecial(Excel.XlPasteType.xlPasteFormats); rg.NumberFormat = "@"; rg.HorizontalAlignment = Excel.Constants.xlRight; rg.VerticalAlignment = Excel.Constants.xlTop; // eList.Cells[3, 1].Paste(); // o.ActiveSheet.Paste(); rg.Value2 = массив; eList.Cells[3, 1].Select(); oExcel.Application.Visible = true; }
void saveData() { ChooseFolder(); string sifre = sifreTxtBox.Text; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet worksheet = null; worksheet = workbook.Sheets["Sayfa1"]; worksheet = workbook.ActiveSheet; worksheet.Name = "Records"; for (int i = 0; i < sayac; i++) { worksheet.Cells[1, i + 1] = kolonlarDizi[i]; } worksheet.Cells[1, sayac + 1] = "SicilNo (Ondalıksız)"; worksheet.Cells[1, sayac + 2] = "Tarih (Metin)"; //string encPass = encryptionName(sifreTxtBox.Text); String dosyaAdı; dosyaAdı = projeAdi.Text + "-" + projeSahibi.Text; var saveFileDialoge = new SaveFileDialog(); saveFileDialoge.FileName = dosyaAdı; saveFileDialoge.DefaultExt = ".xlsx"; string lastPath = path.Text; workbook.SaveAs(lastPath, 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); path.Text = path.Text + dosyaAdı; app.Quit(); Application.Exit(); }
public static Application RegisterAndGetApp() { Application excel = null; // get excel, throw exception if it is not var officeType = Type.GetTypeFromProgID("Excel.Application"); if (officeType == null) throw new Exception("Excel is not installed."); try { excel = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); } catch (COMException e) { // 0x800401E3 - the excel process simply was not running, we continue if we // encounter this exception if ( !e.ToString().Contains("0x800401E3") ) { throw new Exception("Error setting up communication with Excel. Try closing any open Excel instances."); } } if (excel == null) excel = new Microsoft.Office.Interop.Excel.Application(); if (excel == null) { throw new Exception("Excel could not be opened."); } dynSettings.Controller.DynamoModel.CleaningUp += DynamoModelOnCleaningUp; excel.Visible = ShowOnStartup; return excel; }
private void ExportDataSetToExcel(DataTable dt,DataTable dt2) { // Load Excel application Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); // Create empty workbook excel.Workbooks.Add(); // Create Worksheet from active sheet Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet; Microsoft.Office.Interop.Excel.Worksheet workSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excel.Sheets.Add(); // I created Application and Worksheet objects before try/catch, // so that i can close them in finnaly block. // It's IMPORTANT to release these COM objects!! try { // ------------------------------------------------ // Creation of header cells // ------------------------------------------------ workSheet.Cells[1, "A"]="origen"; workSheet.Cells[1, "B"]="column1"; workSheet.Cells[1, "C"]="referencia1"; workSheet.Cells[1, "D"]="referencia2"; workSheet.Cells[1, "E"]="referencia3"; workSheet.Cells[1, "F"]="presupuesto"; workSheet.Cells[1, "G"]="codcapi"; workSheet.Cells[1, "H"]="capitulo"; workSheet.Cells[1, "I"]="codunit"; workSheet.Cells[1, "J"]="unitario"; workSheet.Cells[1, "K"]="undunita"; workSheet.Cells[1, "L"]="cantxppto"; workSheet.Cells[1, "M"]="codinsu"; workSheet.Cells[1, "N"]="insutipo"; workSheet.Cells[1, "O"]="insumo"; workSheet.Cells[1, "P"]="unidinsu"; workSheet.Cells[1, "Q"]="ctrlinven"; workSheet.Cells[1, "R"]="validación"; workSheet.Cells[1, "S"]="precioppto"; workSheet.Cells[1, "T"]="consumounitario"; workSheet.Cells[1, "U"]="consumototal"; workSheet.Cells[1, "V"]="adic"; workSheet.Cells[1, "W"]="precioaut"; workSheet.Cells[1, "X"]="PrecioCompra"; workSheet.Cells[1, "Y"]="PrecioEntrado"; workSheet.Cells[1, "Z"]="Ped"; workSheet.Cells[1, "AA"]="aprob"; workSheet.Cells[1, "AB"]="comp"; workSheet.Cells[1, "AC"]="ent"; workSheet.Cells[1, "AD"]="sali"; workSheet.Cells[1, "AE"]="traslado"; workSheet.Cells[1, "AF"]="xcomp"; workSheet.Cells[1, "AG"]="xent"; workSheet.Cells[1, "AH"]="saldoxunit"; workSheet.Cells[1, "AI"]="saldoxppto"; workSheet.Cells[1, "AJ"]="vlrEnt"; workSheet.Cells[1, "AK"]="vlrEnttraslado"; workSheet.Cells[1, "AL"]="VlrCompradoxent"; workSheet.Cells[1, "AM"]="vlrxcomp"; workSheet.Cells[1, "AN"]="VlrTraslado"; workSheet.Cells[1, "AO"]="vlrproy"; workSheet.Cells[1, "AP"]="Vlrini"; workSheet.Cells[1, "AQ"]="vlrejec"; workSheet2.Cells[1, "A"] = "referencia1"; // ------------------------------------------------ // Populate sheet with some real data from "cars" list // ------------------------------------------------ int row = 2; // start row (in row 1 are header cells) for (int i = 0; i <= dt.Rows.Count - 1; i++) { workSheet.Cells[row, "A"] = dt.Rows[i].Field<string>("origen"); workSheet.Cells[row, "B"] = dt.Rows[i].Field<DateTime>("column1"); workSheet.Cells[row, "C"] = dt.Rows[i].Field<string>("referencia1"); workSheet.Cells[row, "D"] = dt.Rows[i].Field<string>("referencia2"); workSheet.Cells[row, "E"] = dt.Rows[i].Field<string>("referencia3"); /* workSheet.Cells[row, "F"] = dt.Rows[i].Field<string>("presupuesto"); workSheet.Cells[row, "G"] = dt.Rows[i].Field<string>("codcapi"); workSheet.Cells[row, "H"] = dt.Rows[i].Field<string>("capitulo"); workSheet.Cells[row, "I"] = dt.Rows[i].Field<string>("codunit"); workSheet.Cells[row, "J"] = dt.Rows[i].Field<string>("unitario"); workSheet.Cells[row, "K"] = dt.Rows[i].Field<string>("undunita"); workSheet.Cells[row, "L"] = dt.Rows[i].Field<decimal>("cantxppto"); workSheet.Cells[row, "M"] = dt.Rows[i].Field<string>("codinsu"); workSheet.Cells[row, "N"] = dt.Rows[i].Field<string>("insutipo"); // workSheet.Cells[row, "O"] = dt.Rows[i].Field<string>("Proyecto"); workSheet.Cells[row, "P"] = dt.Rows[i].Field<string>("insumo"); workSheet.Cells[row, "Q"] = dt.Rows[i].Field<string>("unidinsu"); workSheet.Cells[row, "R"] = dt.Rows[i].Field<string>("ctrlinven"); workSheet.Cells[row, "S"] = dt.Rows[i].Field<string>("validación"); workSheet.Cells[row, "T"] = dt.Rows[i].Field<decimal>("precioppto"); workSheet.Cells[row, "U"] = dt.Rows[i].Field<decimal>("consumounitario"); workSheet.Cells[row, "V"] = dt.Rows[i].Field<decimal>("consumototal"); workSheet.Cells[row, "W"] = dt.Rows[i].Field<decimal>("precioaut"); workSheet.Cells[row, "X"] = dt.Rows[i].Field<decimal>("PrecioCompra"); workSheet.Cells[row, "Y"] = dt.Rows[i].Field<decimal>("PrecioEntrado"); workSheet.Cells[row, "Z"] = dt.Rows[i].Field<decimal>("Ped"); workSheet.Cells[row, "AA"] = dt.Rows[i].Field<decimal>("aprob"); workSheet.Cells[row, "AB"] = dt.Rows[i].Field<decimal>("comp"); workSheet.Cells[row, "AC"] = dt.Rows[i].Field<decimal>("traslado"); workSheet.Cells[row, "AD"] = dt.Rows[i].Field<decimal>("sali"); workSheet.Cells[row, "AE"] = dt.Rows[i].Field<decimal>("xcomp"); workSheet.Cells[row, "AF"] = dt.Rows[i].Field<decimal>("xent"); workSheet.Cells[row, "AG"] = dt.Rows[i].Field<decimal>("saldoxunit"); workSheet.Cells[row, "AH"] = dt.Rows[i].Field<decimal>("saldoxppto"); workSheet.Cells[row, "AI"] = dt.Rows[i].Field<decimal>("vlrEnt"); workSheet.Cells[row, "AJ"] = dt.Rows[i].Field<decimal>("vlrEnttraslado"); workSheet.Cells[row, "AK"] = dt.Rows[i].Field<decimal>("VlrCompradoxent"); workSheet.Cells[row, "AL"] = dt.Rows[i].Field<decimal>("vlrxcomp"); workSheet.Cells[row, "AM"] = dt.Rows[i].Field<decimal>("VlrTraslado"); workSheet.Cells[row, "AN"] = dt.Rows[i].Field<decimal>("vlrproy"); workSheet.Cells[row, "AO"] = dt.Rows[i].Field<decimal>("Vlrini"); //workSheet.Cells[row, "AP"] = dt.Rows[i].Field<decimal>("Fecha"); workSheet.Cells[row, "AQ"] = Convert.ToString(dt.Rows[i].Field<decimal>("vlrejec")); workSheet.Cells[row].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);*/ //57 //124 row++; } int row2=2; for (int i = 0; i <= dt2.Rows.Count - 1; i++) { workSheet2.Cells[row2, "A"] = dt2.Rows[i].Field<string>("referencia1"); workSheet2.Cells[row2, "D"] = dt2.Rows[i].Field<string>("referencia2"); workSheet2.Cells[row2, "E"] = dt2.Rows[i].Field<string>("referencia3"); workSheet2.Cells[row2, "F"] = dt2.Rows[i].Field<string>("presupuesto"); workSheet2.Cells[row2, "G"] = dt2.Rows[i].Field<string>("codcapi"); /* workSheet2.Cells[row2, "H"] = dt2.Rows[i].Field<string>("capitulo"); workSheet2.Cells[row2, "I"] = dt2.Rows[i].Field<string>("codunit"); workSheet2.Cells[row2, "K"] = dt2.Rows[i].Field<string>("undunita"); workSheet2.Cells[row2, "L"] = dt2.Rows[i].Field<decimal>("cantxppto"); workSheet2.Cells[row2, "M"] = dt2.Rows[i].Field<string>("codinsu"); workSheet2.Cells[row2, "N"] = dt2.Rows[i].Field<string>("insutipo"); // workSheet.Cells[row, "O"] = dt.Rows[i].Field<string>("Proyecto"); workSheet2.Cells[row2, "P"] = dt2.Rows[i].Field<string>("insumo"); workSheet2.Cells[row2, "Q"] = dt2.Rows[i].Field<string>("unidinsu"); workSheet2.Cells[row2, "R"] = dt2.Rows[i].Field<string>("ctrlinven"); workSheet2.Cells[row2, "S"] = dt2.Rows[i].Field<string>("validación"); workSheet2.Cells[row2, "T"] = dt2.Rows[i].Field<decimal>("precioppto"); workSheet2.Cells[row2, "U"] = dt2.Rows[i].Field<decimal>("consumounitario"); workSheet2.Cells[row2, "V"] = dt2.Rows[i].Field<decimal>("consumototal"); workSheet2.Cells[row2, "W"] = dt2.Rows[i].Field<decimal>("precioaut"); workSheet2.Cells[row2, "X"] = dt2.Rows[i].Field<decimal>("PrecioCompra"); workSheet2.Cells[row2, "Y"] = dt2.Rows[i].Field<decimal>("PrecioEntrado"); workSheet2.Cells[row2, "Z"] = dt2.Rows[i].Field<decimal>("Ped"); workSheet2.Cells[row2, "AA"] = dt2.Rows[i].Field<decimal>("aprob"); workSheet2.Cells[row2, "AB"] = dt2.Rows[i].Field<decimal>("comp"); workSheet2.Cells[row2, "AC"] = dt2.Rows[i].Field<decimal>("traslado"); workSheet2.Cells[row2, "AD"] = dt2.Rows[i].Field<decimal>("sali"); workSheet2.Cells[row2, "AE"] = dt2.Rows[i].Field<decimal>("xcomp"); workSheet2.Cells[row2, "AF"] = dt2.Rows[i].Field<decimal>("xent"); workSheet2.Cells[row2, "AG"] = dt2.Rows[i].Field<decimal>("saldoxunit"); workSheet2.Cells[row2, "AH"] = dt2.Rows[i].Field<decimal>("saldoxppto"); workSheet2.Cells[row2, "AI"] = dt2.Rows[i].Field<decimal>("vlrEnt"); workSheet2.Cells[row2, "AJ"] = dt2.Rows[i].Field<decimal>("vlrEnttraslado"); workSheet2.Cells[row2, "AK"] = dt2.Rows[i].Field<decimal>("VlrCompradoxent"); workSheet2.Cells[row2, "AL"] = dt2.Rows[i].Field<decimal>("vlrxcomp"); workSheet2.Cells[row2, "AM"] = dt2.Rows[i].Field<decimal>("VlrTraslado"); workSheet2.Cells[row2, "AN"] = dt2.Rows[i].Field<decimal>("vlrproy"); workSheet2.Cells[row2, "AO"] = dt2.Rows[i].Field<decimal>("Vlrini"); workSheet2.Cells[row2, "AP"] = dt2.Rows[i].Field<int>("IdFecha"); workSheet2.Cells[row2, "AQ"] = Convert.ToString(dt2.Rows[i].Field<decimal>("vlrejec"));*/ //57 //124 row2++; } // Apply some predefined styles for data to look nicely :) workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); workSheet2.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); // Define filename string fileName = string.Format(@"{0}\ExcelData.xls", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)); // Save this data as a file workSheet.SaveAs(fileName); workSheet2.SaveAs(fileName); // Display SUCCESS message } catch (Exception ex) { throw ex; } finally { // Quit Excel application excel.Quit(); // Release COM objects (very important!) if (excel != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); if (workSheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); // Empty variables excel = null; workSheet = null; // Force garbage collector cleaning GC.Collect(); } }
private void btnToExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excel = null; try { //DataGridView沒有資料就不執行 if (this.dgvShowData.Rows.Count <= 1) { MessageBox.Show("沒有可滙出的資料!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } //new 出一個Excel excel = new Microsoft.Office.Interop.Excel.Application(); //看的到Excel在工作 excel.Visible = true; //新增加一工作簿 excel.Application.Workbooks.Add(true); //寫入欄位名稱 for (int i = 0; i < dgvShowData.Columns.Count; i++) { excel.Cells[1, i + 1] = dgvShowData.Columns[i].HeaderText; } //把DataGridView資料寫到Excel for (int i = 0; i < dgvShowData.Rows.Count - 1; i++) { for (int j = 0; j < dgvShowData.Columns.Count; j++) { if (dgvShowData[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dgvShowData[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dgvShowData[j, i].Value.ToString(); } } } //設定滙出後,欄位寛度自動配合資料調整 excel.Cells.EntireRow.AutoFit(); //自動調整列高 excel.Cells.EntireColumn.AutoFit(); excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; excel.get_Range("A1").Font.Color = Color.White; excel.get_Range("A1").Interior.Color = Color.DodgerBlue; excel.get_Range("B1").Font.Color = Color.White; excel.get_Range("B1").Interior.Color = Color.DodgerBlue; excel.get_Range("C1").Font.Color = Color.White; excel.get_Range("C1").Interior.Color = Color.DodgerBlue; excel.get_Range("D1").Font.Color = Color.White; excel.get_Range("D1").Interior.Color = Color.DodgerBlue; excel.get_Range("E1").Font.Color = Color.White; excel.get_Range("E1").Interior.Color = Color.DodgerBlue; excel.get_Range("F1").Font.Color = Color.White; excel.get_Range("F1").Interior.Color = Color.DodgerBlue; excel.get_Range("G1").Font.Color = Color.White; excel.get_Range("G1").Interior.Color = Color.DodgerBlue; excel.get_Range("H1").Font.Color = Color.White; excel.get_Range("H1").Interior.Color = Color.DodgerBlue; excel.get_Range("I1").Font.Color = Color.White; excel.get_Range("I1").Interior.Color = Color.DodgerBlue; excel.get_Range("J1").Font.Color = Color.White; excel.get_Range("J1").Interior.Color = Color.DodgerBlue; excel.get_Range("K1").Font.Color = Color.White; excel.get_Range("K1").Interior.Color = Color.DodgerBlue; excel.get_Range("L1").Font.Color = Color.White; excel.get_Range("L1").Interior.Color = Color.DodgerBlue; excel.get_Range("M1").Font.Color = Color.White; excel.get_Range("M1").Interior.Color = Color.DodgerBlue; excel.get_Range("N1").Font.Color = Color.White; excel.get_Range("N1").Interior.Color = Color.DodgerBlue; excel.get_Range("O1").Font.Color = Color.White; excel.get_Range("O1").Interior.Color = Color.DodgerBlue; //設置禁止彈出覆蓋或儲存的彈跳視窗 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; MessageBox.Show("已成功輸出至Excel!", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { //釋放資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); } }
public static void Save(DataGridView dgv, string Savepath) { Microsoft.Office.Interop.Excel.Application APP = new Microsoft.Office.Interop.Excel.Application(); APP.Application.Workbooks.Add(Type.Missing); APP.Columns.ColumnWidth = 20; for (int i = 1; i < dgv.Columns.Count + 1; i++) { APP.Cells[1, i] = dgv.Columns[i - 1].HeaderText; } for (int i = 0; i < dgv.Rows.Count; i++) { for (int j = 0; j < dgv.Columns.Count; j++) { APP.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value; } } FileInfo file = new FileInfo(Savepath); if (file.Exists) { file.Delete(); } OpenV = true; APP.ActiveWorkbook.SaveAs(Savepath.ToString()); APP.ActiveWorkbook.Saved = true; APP.Quit(); }
public void SpremanjeFaktura() { textBoxPDV.Text = textBoxPDV.Text.Replace("%", ""); textBoxRabat.Text = textBoxRabat.Text.Replace("%", ""); string putanjaDoNoveFakture; string put = System.IO.File.ReadAllText(Application.StartupPath + "\\Fakture\\PutFakture.txt"); if (textBoxRabat.Text.Length != 0) putanjaDoNoveFakture = Application.StartupPath + "\\Fakture\\NOVA_RABAT_SLO.xlsx"; else putanjaDoNoveFakture = Application.StartupPath + "\\Fakture\\NOVA_SLO.xlsx"; Excel.Application excelApp = new Excel.Application(); if (radioPlaceno.Checked == true || radioNePlaceno.Checked == true) { Microsoft.Office.Interop.Excel.Application ExcelObj = null; ExcelObj = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(putanjaDoNoveFakture, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); int rowIndex, colIndex; excelApp.Workbooks.Open(putanjaDoNoveFakture); rowIndex = 17; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = textBoxDVO.Text; rowIndex = 18; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = textBoxValuta.Text; rowIndex = 21; colIndex = 7; excelApp.Cells[rowIndex, colIndex] = textBoxBrojFakture.Text; rowIndex = 22; colIndex = 7; excelApp.Cells[rowIndex, colIndex] = textBoxRegBroj.Text; rowIndex = 30; colIndex = 5; excelApp.Cells[rowIndex, colIndex] = textBoxKolicina.Text; rowIndex = 30; colIndex = 6; excelApp.Cells[rowIndex, colIndex] = textBoxCijena.Text; rowIndex = 27; colIndex = 1; if (textBoxRabat.Text.Length != 0) rowIndex = 29; excelApp.Cells[rowIndex, colIndex] = textBoxRelacija.Text; rowIndex = 30; colIndex = 3; excelApp.Cells[rowIndex, colIndex] = textBoxJM.Text; rowIndex = 17; colIndex = 1; excelApp.Cells[rowIndex, colIndex] = textBoxImeFirme.Text; rowIndex = 18; colIndex = 1; excelApp.Cells[rowIndex, colIndex] = textBoxUlica.Text; rowIndex = 19; colIndex = 1; excelApp.Cells[rowIndex, colIndex] = textBoxMjesto.Text; rowIndex = 31; colIndex = 2; if (textBoxRabat.Text.Length != 0) { rowIndex = 31; colIndex = 3; } excelApp.Cells[rowIndex, colIndex] = textBoxPozicija.Text; //ako je s rabatom if (textBoxRabat.Text.Length != 0) { rowIndex = 30; colIndex = 9; double rabat = Convert.ToDouble(textBoxRabat.Text); excelApp.Cells[rowIndex, colIndex] = rabat / 100; rowIndex = 30; colIndex = 10; double PDVa = Convert.ToDouble(textBoxPDV.Text); excelApp.Cells[rowIndex, colIndex] = PDVa / 100; } else { rowIndex = 30; colIndex = 9; double PDV = Convert.ToDouble(textBoxPDV.Text); excelApp.Cells[rowIndex, colIndex] = PDV / 100; } rowIndex = 34; colIndex = 6; string euro = textBoxEur.Text; if (euro.Contains('.')) { textBoxEur.Text = euro.Replace('.', ','); } double UkupnaCijenaBezPDV = Convert.ToDouble(textBoxKolicina.Text) * Convert.ToDouble(textBoxCijena.Text); double UkupnaCijenaPDV = UkupnaCijenaBezPDV * (Convert.ToDouble(textBoxPDV.Text) / 100); double KonačnaCijena = UkupnaCijenaBezPDV + UkupnaCijenaPDV; double eur = KonačnaCijena / Convert.ToDouble(textBoxEur.Text); if (textBoxRabat.Text.Length != 0) excelApp.Cells[39, 6] = eur; else excelApp.Cells[rowIndex, colIndex] = eur; var datum = Convert.ToDateTime(textBoxDVO.Text); string datumPrikaz = datum.ToShortDateString(); int mjesec = datum.Month; int godina = datum.Year; string dira = put + textBoxImeFirme.Text + "\\" + textBoxRelacija.Text + "\\" + mjesec + "-" + godina + "\\PLAČENO\\"; string dirNea = put + textBoxImeFirme.Text + "\\" + textBoxRelacija.Text + "\\" + mjesec + "-" + godina + "\\NEPLAČENO\\"; string dir = dira.Replace("\\\\", "\\"); string dirNe = dirNea.Replace("\\\\", "\\"); spremanje = dir + textBoxBrFakture.Text + " " + textBoxDVO.Text + ".xlsx"; spremanjeNe = dirNe + textBoxBrFakture.Text + " " + textBoxDVO.Text + ".xlsx"; System.IO.Directory.CreateDirectory(dirNe); System.IO.Directory.CreateDirectory(dir); if (radioPlaceno.Checked) { excelApp.ActiveWorkbook.SaveCopyAs(spremanje); string ime = Path.GetFileNameWithoutExtension(spremanje); Marshal.ReleaseComObject(ExcelObj); Marshal.ReleaseComObject(theWorkbook); Marshal.ReleaseComObject(sheets); Marshal.ReleaseComObject(worksheet); try { foreach (Process proc in Process.GetProcessesByName("EXCEL")) { proc.Kill(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } System.Diagnostics.Process.Start(spremanje); } if (radioNePlaceno.Checked) { excelApp.ActiveWorkbook.SaveCopyAs(spremanjeNe); string imeNe = Path.GetFileNameWithoutExtension(spremanjeNe); Marshal.ReleaseComObject(ExcelObj); Marshal.ReleaseComObject(theWorkbook); Marshal.ReleaseComObject(sheets); Marshal.ReleaseComObject(worksheet); try { foreach (Process proc in Process.GetProcessesByName("EXCEL")) { proc.Kill(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } System.Diagnostics.Process.Start(spremanjeNe); } File.AppendAllText(Application.StartupPath + "\\Fakture\\data", System.Environment.NewLine + BrFakture); } else { MessageBox.Show("Niste označili da li je plačeno ili ne!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void exportarexcel(DataGridView tabla) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); int IndiceColumna = 0; foreach ( DataGridViewColumn col in tabla.Columns)//columnas { IndiceColumna++; excel.Cells[1, IndiceColumna] = col.Name; } int IndiceFila = 0; foreach (DataGridViewRow row in tabla.Rows)//filas { IndiceFila++; IndiceColumna = 0; foreach (DataGridViewColumn col in tabla.Columns) { IndiceColumna++; excel.Cells[IndiceFila + 1, IndiceColumna] = row.Cells[col.Name].Value; } } excel.Visible = true; }
public void Template(DataTable dt) { string serverpath = this.MapPath("."); string date = System.DateTime.Now.ToString("ddMMyyyy_hhmmssm"); string newfile = @"" + serverpath + "\\ExcelReports\\BreakEven_Report_" + date + ".xls"; string filename = "BreakEven_Report_" + date + ".xls"; String ExcelPath = System.Configuration.ConfigurationManager.AppSettings["ExcelPath"].ToString(); string newpath = ExcelPath + "BreakEven_Report_" + date + ".xls"; //string newpath = "\\Reports\\ExcelReports\\BreakEven_Report_" + date + ".xls"; lnkexcel.HRef = newpath; if (File.Exists(newfile) == false) { File.Copy(@"" + serverpath + "\\ExcelTemplates\\BreakEven_Report_Template.xls", newfile); Microsoft.Office.Interop.Excel.Application xlApp = null; Excel.Workbook xlWorkbook = null; Excel.Sheets xlSheets = null; Excel.Worksheet xlNewSheet = null; try { xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) return; xlWorkbook = xlApp.Workbooks.Open(newfile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); xlSheets = xlWorkbook.Sheets as Excel.Sheets; xlNewSheet = (Excel.Worksheet)xlSheets[2]; int rowCount = 1; foreach (DataRow dr in dt.Rows) { rowCount += 1; for (int i = 1; i < dt.Columns.Count + 1; i++) { xlNewSheet.Cells[rowCount, i] = dr[i - 1].ToString(); } } xlNewSheet = (Excel.Worksheet)xlSheets[1]; hfBevnval.Value = string.IsNullOrEmpty(hfBevnval.Value) ? "0.0" : hfBevnval.Value; xlNewSheet.Cells[14, 13] = hfBevnval.Value + "%"; //Excel.Worksheet excelc = null; //excelc=(Excel.Worksheet)xlSheets[2]; //excelc.Cells.Value2("M14", "M14"); //xcel.Range oRange; // Resize the columns //oRange = xlNewSheet.get_Range(xlNewSheet.Cells[1, 1], // xlNewSheet.Cells[rowCount, dt.Columns.Count]); //oRange.EntireColumn.AutoFit(); objcf.HideDBData_XLSheets(xlSheets, "2"); xlWorkbook.Save(); xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing); xlApp.Quit(); foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) { if (proc.MainWindowTitle.ToString() == "") { //proc.Kill(); } } //Response.Redirect(newfilepath); string fName = newfile; //DownLoadFileFromServer(newpath); } finally { xlApp = null; } System.Web.UI.ScriptManager.RegisterStartupScript(this, this.GetType(), "", "anc();", true); } }
private void btnToExcel_Click(object sender, EventArgs e) { Excel.Application excel = null; try { //DataGridView沒有資料就不執行 if (dgvDataShow.Rows.Count <= 1) { MessageBox.Show("沒有可滙出的資料!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //設定滙出後的存檔路徑(儲存在桌面) string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\即將到期用戶名單" + ".xls"; //new 出一個Excel excel = new Microsoft.Office.Interop.Excel.Application(); //看的到Excel在工作 excel.Visible = false; //新增加一工作簿 excel.Application.Workbooks.Add(true); PGB pgb = new PGB(); pgb.progressBar1.Value = 0; pgb.progressBar1.Minimum = 0; pgb.progressBar1.Maximum = dgvDataShow.Rows.Count - 1; pgb.progressBar1.Step = 1; pgb.Show(); //寫入欄位名稱 for (int i = 0; i < dgvDataShow.Columns.Count; i++) { excel.Cells[1, i + 1] = dgvDataShow.Columns[i].HeaderText; } //把DataGridView資料寫到Excel for (int i = 0; i < dgvDataShow.Rows.Count - 1; i++) { pgb.progressBar1.Value++; Application.DoEvents(); for (int j = 0; j < dgvDataShow.Columns.Count; j++) { if (dgvDataShow[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dgvDataShow[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dgvDataShow[j, i].Value.ToString(); } } } //設定滙出後,欄位寛度自動配合資料調整 excel.Cells.EntireRow.AutoFit(); //自動調整列高 excel.Cells.EntireColumn.AutoFit(); //設置禁止彈出覆蓋或儲存的彈跳視窗 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; //將檔案儲存到SaveFile指定的位置 excel.ActiveWorkbook.SaveCopyAs(SaveFilePath); if (excel.Application.Version == "11.0")//Office 2007 up { excel.ActiveWorkbook.SaveAs(SaveFilePath); } else { //Office 2003 Up,FileFormat: Excel.XlFileFormat.xlExcel8=>指定Excel 2003 xls格式 excel.ActiveWorkbook.SaveAs(SaveFilePath, FileFormat: Excel.XlFileFormat.xlExcel8); } pgb.Dispose(); MessageBox.Show("已成功滙出Excel檔!" + Environment.NewLine + "檔案儲存在您電腦的桌面,檔名:即將到期用戶名單" + ".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message); } //關閉工作簿和結束Excel程式 excel.Workbooks.Close(); excel.Quit(); //釋放資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); }
private void initCom() { System.Diagnostics.Process[] pTemp = System.Diagnostics.Process.GetProcesses(); int count = 0; foreach (System.Diagnostics.Process pTempProcess in pTemp) { if ((pTempProcess.ProcessName.ToLower() == ("excel").ToLower()) || (pTempProcess.ProcessName.ToLower()) == "excel.exe" || (pTempProcess.ProcessName.ToLower()) == "winexcel.exe" || (pTempProcess.ProcessName.ToLower()) == "winexcel") { count++; } } // 保持有两个WordApp for (int i = 0; i < (2 - count); i++) { excelapp = new Excel.ApplicationClass(); } }
//生成Excel private bool ExportToExcel(DataTable dt) { bool result = false; this.UseWaitCursor = true; if (dt.Rows.Count > 65536) { MessageBox.Show("數據記錄太多(最多不能超過65536條),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return result; } Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { labMsg.Text = "Excel正在匯出中…………"; if (xlApp == null) { throw new Exception("无法创建Excel对象,可能您的机器未安装Excel"); } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(true); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "過膠機生產日報表"; //worksheet.QueryTables.Add #region worksheet的格式 worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印 worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印 worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效 worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁 worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中 worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102); worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126); worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126); xlApp.Visible = false; #endregion #region 列名 #region 循環填充列名,很慢,已經REMARK //for (i = 1; i <= dt.Columns.Count - 1; i++) //{ // if (i == 11 || i == 12 || i == 13) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Substring(8, 3); // } // else if (i == 14 || i == 15 || i == 16 || i == 17) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim().Substring(8); // } // else if (i >= 23 && i <= 31) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim(); // } // else // { // MergeColumn(xlApp, xlApp.Cells[RowIndex, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex, i] = dt.Columns[i].ColumnName; // } //} #endregion int RowIndex = 2; MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 30], "過膠機生產日報表", 16, 65535, 0); //大標題 MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 30], "常用物料使用情況", 12, 65535, 0); RowIndex = RowIndex + 1; MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "上工序次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "上工序廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "累計數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "級別", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 23], "物料編號", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "物料名稱", 12, 65535, 35); MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "採購單號", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "生產批次", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "實際用量", 12, 65535, 8.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "單位", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "寬度(mm)", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "長度(m)", 12, 65535, 9); #endregion #region 填充數據 向Excel中逐行逐列写入表格中的数据 RowIndex = RowIndex + 1; //數據開始的行號 int ExcelCurrectRow = RowIndex; //當前行 int dtRowNO; int realRowNO = 0;//dt實際行 int dtKeyRowNO = RowIndex;//NO:4 int megreCount = 0; int dtMetrialRowNO = 23;//物料編號所在列 int tempRow; int tempCol; #region 較慢的填充數據方法 已經REMARK //for (int row = 0; row < dt.Rows.Count; row++) //{ // //DataRow[] rows = dt.Select(dt.Rows[row][0].ToString()); // for (int col = 1; col < dt.Columns.Count; col++) // { // if (row > 0) //班次內工程ID相等 col22之後不合併 // { // if (dt.Rows[row - 1][0].ToString() == dt.Rows[row][0].ToString() && col < 22) // { // CellStyle(xlApp, xlApp.Cells[currectRow, col], xlApp.Cells[RowIndex, col], 10, -4142, true); // } // else // { // CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false); // xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString(); // if (col < 22) currectRow = RowIndex; // } // } // else // { // CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false); // xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString(); // } // } // RowIndex++; // //if (RowIndex == 15) goto range; // for 測試 //} #endregion DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" }); for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++) { DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' "); megreCount = currectKeyRow.Length; for (tempRow = 0; tempRow < megreCount; tempRow++) { if (tempRow == 0) { for (tempCol = 1; tempCol < dtMetrialRowNO; tempCol++) //先合併物料編號之前的單元格 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true); } realRowNO += megreCount; } for (tempCol = dtMetrialRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false); } dtKeyRowNO++; } //dtKeyRowNO++; //if (realRowNO > dt.Rows.Count) break; /* * DataTable pjsf_iPJSFID = dt.DefaultView.ToTable(true, new string[] { "pjsf_iPJSFID" }); List<Thread> threads = new List<Thread>(); for (dtRowNO = 0; dtRowNO < pjsf_iPJSFID.Rows.Count; dtRowNO++) { */ } #endregion #region 設置Sheet邊框 //range: Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]); SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; #endregion Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]); DataFormat.Font.Name = "楷体"; DataFormat.Font.Size = 10; Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]); IntFormat.NumberFormatLocal = "0.00"; #region savecode Object missing = Missing.Value; SaveFileDialog saveForm = new SaveFileDialog(); saveForm.Filter = "Execl files (*.xls)|*.xls"; saveForm.FilterIndex = 0; saveForm.FileName = "過膠機生產日報表"; if (saveForm.ShowDialog() == DialogResult.OK) { string fileName = saveForm.FileName; workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } #endregion result = true; } catch (Exception ex) { throw ex; } finally { labMsg.Text = ""; this.UseWaitCursor = false; xlApp.Quit(); IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法 int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); System.GC.Collect(); } labMsg.Text = ""; return result; }
ref LV_ITEM lParam );// pointer to struct of LV_ITEM #endregion /// <summary> /// 方法,导出DataTable中的数据到Excel文件 , 多个不同的数据结构显示在一个Excel页面中。 /// </summary> /// <remarks> /// add com "Microsoft Excel 11.0 Object Library" /// using Excel=Microsoft.Office.Interop.Excel; /// using System.Reflection; /// </remarks> /// <param name="dgv">DataTable</param> /// <param name="strOutFileName">设定导出的文件名称</param> /// <param name="strsHeadName">导出的列的的HeadName, 如果为null,则导出全部列</param> /// <returns></returns> public static bool SaveDtToExcel(ArrayList aryParam) { if (aryParam == null || aryParam.Count == 0) return false; Excel.Application objExcel = null; Excel.Workbook objWorkbook = null; Excel.Worksheet objsheet = null; string fileNameString = ""; //Excel的文件名称。 int n = 0; //标明当前是第几个数据结构。 int nLine = 1; //每个数据结构的开始的行数 int nJG = 5; //每个数据结构之间 间隔的行数 foreach (EXCELPARAM param in aryParam) { DataTable dt = param.dt; string strOutFileName = param.strOutFile; string[] strsHeadName = param.strsHeadName; string[] strsFldName = param.strsFldName; int[] nsLineNo = null; if (dt.Rows.Count <= 0) { MessageBox.Show("没有可以导出的数据!", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } //处理第一个数据结构。 选择文件名称。 if (n == 0) { #region 验证可操作性 //申明保存对话框 SaveFileDialog dlg = new SaveFileDialog(); //默然文件后缀 dlg.FileName = strOutFileName; dlg.DefaultExt = "xls "; //文件后缀列表 dlg.Filter = "EXCEL文件(*.XLS)|*.xls "; //默然路径是系统当前路径 dlg.InitialDirectory = Directory.GetCurrentDirectory(); //打开保存对话框 if (dlg.ShowDialog() == DialogResult.Cancel) return false; //返回文件路径 fileNameString = dlg.FileName; //验证strFileName是否为空或值无效 if (fileNameString.Trim() == " ") return false; //验证以fileNameString命名的文件是否存在,如果存在删除它 FileInfo file = new FileInfo(fileNameString); if (file.Exists) { try { file.Delete(); } catch (Exception error) { MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return false; } } //申明对象 objExcel = new Microsoft.Office.Interop.Excel.Application(); objWorkbook = objExcel.Workbooks.Add(Missing.Value); objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet; //设置EXCEL不可见 objExcel.Visible = false; } //定义表格内数据的行数和列数 int rowscount = dt.Rows.Count; int colscount = dt.Columns.Count; //行数必须大于0 if (rowscount <= 0) { MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } //列数必须大于0 if (colscount <= 0) { MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } //行数不可以大于65536 if (rowscount > 65536) { MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } //列数不可以大于255 if (colscount > 255) { MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } #endregion #region //取得要导出的列的HeadName. //如果没有指定特定的列,则默认转换 可见的 并且 有HeadName 的列。(控件列不用导出,控件列一般没有HeadName) if (strsHeadName == null) { MessageBox.Show("没有指定字段名称!", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } //取得所有要执行的行,(可能用户并不是要导出所有行,而是通过checkbox选择要导出的行) //如果没有指定特定的行,则默认转出全部的行 if (nsLineNo == null) { nsLineNo = new int[dt.Rows.Count]; for (int row = 0; row <= dt.Rows.Count - 1; row++) { nsLineNo[row] = row; } } #endregion try { //向Excel中写入表格的表头 Excel.Range rag = objsheet.get_Range(objsheet.Cells[nLine, 1], objsheet.Cells[nLine, strsHeadName.Length]); rag.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; int displayColumnsCount = 1; foreach (string strHeadName in strsHeadName) { objExcel.Cells[nLine, displayColumnsCount] = strHeadName; displayColumnsCount++; } //定义每列的数据格式。 int nRow1 = nLine + 1, nRow2 = nRow1 + nsLineNo.Length; int nColSel = 1; foreach (string strFldName in strsFldName) { rag = objsheet.get_Range(objsheet.Cells[nRow1, nColSel], objsheet.Cells[nRow2, nColSel]); switch (dt.Columns[strFldName].DataType.ToString()) { case "System.String": rag.NumberFormatLocal = "@"; break; case "System.DateTime": rag.NumberFormatLocal = "yyyy-MM-dd HH:mm"; break; //可以根据自己的需要扩展。 default: //rag.NumberFormatLocal = "G/通用格式"; break; } nColSel++; } //向Excel中逐行逐列写入表格中的数据 int nCol, row; int nExcelCol, nExcelRow = nLine + 1; //Execl的行列都从1开始起算。第一行是列名,数据从第二行开始写入。 for (int i = 0; i <= nsLineNo.Length - 1; i++) { row = nsLineNo[i]; nExcelCol = 1; //Execl的行列都从1开始起算。第一行是列名,数据从第二行开始写入。 foreach (string strFldName in strsFldName) { try { objExcel.Cells[nExcelRow, nExcelCol++] = dt.Rows[row][strFldName].ToString().Trim(); } catch (Exception) { } } nExcelRow++; } } catch (Exception error) { MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return false; } // n++; nLine += nsLineNo.Length + nJG; } //保存文件 objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //关闭Excel应用 if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value); if (objExcel.Workbooks != null) objExcel.Workbooks.Close(); if (objExcel != null) objExcel.Quit(); objsheet = null; objWorkbook = null; objExcel = null; MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return true; }
/// <summary> /// Initialize File /// </summary> private void CreateExcel() { app = new Excel.Application(); workbook = app.Workbooks.Open(ExcelFilePath, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheets = workbook.Sheets; //get first sheet of file excel worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worksheets.get_Item(1); }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { bool detailed = radioButtonDetailledMode.Checked; Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.get_Range("a1", "f1").Merge(false); Excel.Range chartRange = xlWorkSheet.get_Range("a1", "f1"); chartRange.FormulaR1C1 = string.Format("{0} assets information, media account '{1}'", radioButtonAllAssets.Checked ? "All" : "Selected", _context.Credentials.ClientId); chartRange.VerticalAlignment = 3; chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); chartRange.Font.Size = 20; xlWorkSheet.get_Range("a2", "f2").Merge(false); Excel.Range chartRange2 = xlWorkSheet.get_Range("a2", "f2"); chartRange2.FormulaR1C1 = string.Format("Exported with Azure Media Services Explorer v{0} on {1}. Dates are {2}.", Assembly.GetExecutingAssembly().GetName().Version.ToString(), checkBoxLocalTime.Checked ? DateTime.Now.ToString() : DateTime.UtcNow.ToString(), checkBoxLocalTime.Checked ? "local" : "UTC based" ); chartRange2.VerticalAlignment = 3; chartRange2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); chartRange2.Font.Size = 12; int row = 4; int index = 1; xlWorkSheet.Cells[row, index++] = "Asset Name"; xlWorkSheet.Cells[row, index++] = "Id"; xlWorkSheet.Cells[row, index++] = "Last Modified"; xlWorkSheet.Cells[row, index++] = "Type"; xlWorkSheet.Cells[row, index++] = "Size"; int backindex = index; _context.StreamingEndpoints.ToList().ForEach(se => xlWorkSheet.Cells[row, index++] = "Streaming URL" ); index = backindex + _context.StreamingEndpoints.Count(); xlWorkSheet.Cells[row, index++] = "Expiration time"; if (detailed) { xlWorkSheet.Cells[row, index++] = "Alternate Id"; xlWorkSheet.Cells[row, index++] = "Storage Account"; xlWorkSheet.Cells[row, index++] = "Streaming Locators Count"; xlWorkSheet.Cells[row, index++] = "Streaming Min Expiration time"; xlWorkSheet.Cells[row, index++] = "Streaming Max Expiration time"; xlWorkSheet.Cells[row, index++] = "SAS Locators Count"; xlWorkSheet.Cells[row, index++] = "SAS Min Expiration time"; xlWorkSheet.Cells[row, index++] = "SAS Max Expiration time"; xlWorkSheet.Cells[row, index++] = "Dynamic encryption"; xlWorkSheet.Cells[row, index++] = "Asset filters count"; } Excel.Range formatRange; formatRange = xlWorkSheet.get_Range("a4"); formatRange.EntireRow.Font.Bold = true; formatRange.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue); if (radioButtonAllAssets.Checked) { int skipSize = 0; int batchSize = 1000; int currentBatch = 0; int total = _context.Assets.Count(); int index2 = 1; while (true) { IQueryable _assetsCollectionQuery = _context.Assets.Skip(skipSize).Take(batchSize); foreach (IAsset asset in _assetsCollectionQuery) { row++; currentBatch++; ExportAssetExcel(asset, xlWorkSheet, row, detailed, checkBoxLocalTime.Checked); backgroundWorker1.ReportProgress(100 * index2 / total, DateTime.Now); //notify progress to main thread. We also pass time information in UserState to cover this property in the example. //if cancellation is pending, cancel work. if (backgroundWorker1.CancellationPending) { xlApp.DisplayAlerts = false; xlWorkBook.Close(); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); e.Cancel = true; return; } index2++; } if (currentBatch == batchSize) { skipSize += batchSize; currentBatch = 0; } else { break; } } } else // Selected or visible asets { IEnumerable<IAsset> myassets; if (radioButtonSelectedAssets.Checked) { myassets = _selassets; } else { myassets = _visibleassets; } int total = myassets.Count(); int index3 = 1; foreach (IAsset asset in myassets) { row++; ExportAssetExcel(asset, xlWorkSheet, row, detailed, checkBoxLocalTime.Checked); backgroundWorker1.ReportProgress(100 * index3 / total, DateTime.Now); //notify progress to main thread. We also pass time information in UserState to cover this property in the example. //if cancellation is pending, cancel work. if (backgroundWorker1.CancellationPending) { xlApp.DisplayAlerts = false; xlWorkBook.Close(); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); e.Cancel = true; return; } index3++; } } // Set the range to fill. var aRange = xlWorkSheet.get_Range("A4", "Z100"); aRange.EntireColumn.AutoFit(); try { xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, 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); } catch { MessageBox.Show("Error when saving the Excel file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } if (checkBoxOpenFileAfterExport.Checked) System.Diagnostics.Process.Start(filename); }
public void createPortfolioXlFile(string folderPath) { if (!System.IO.Directory.Exists(folderPath)) { throw new DirectoryNotFoundException(); } Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } object misValue = System.Reflection.Missing.Value; Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int count = 1; foreach (string colName in columnNames) { xlWorkSheet.Cells[1, count++] = colName; } Excel.Range row1 = xlWorkSheet.UsedRange; row1.Columns.AutoFit(); try { xlWorkBook.SaveAs(path); xlWorkBook.Close(false, misValue, misValue); xlApp.Quit(); } finally { Utility.releaseObject(xlWorkSheet); Utility.releaseObject(xlWorkBook); Utility.releaseObject(xlApp); } }
private void boutonExporter_Click(object sender, EventArgs e) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Fichier Excel (*.xlsx)|*.xlsx"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = "Exporter le fichier Excel vers..."; saveFileDialog.ShowDialog(); Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; worksheet.Name = "Exported from gridview"; for(int i=1;i<dataGridView1.Columns.Count+1;i++) { worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText; } for (int i=0; i < dataGridView1.Rows.Count ; i++) { for(int j=0;j<dataGridView1.Columns.Count;j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); } } workbook.SaveAs(saveFileDialog.FileName,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing); app.Quit(); }