/// <summary> /// �����ݼ�������Ϊexcel /// </summary> /// <param name="name">����excel������</param> /// <param name="ds">�����������ݼ�</param> public static void AddExcel(string name, DataTable dt) { string fileName = name + ".xls"; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); int rowIndex = 1; int colIndex = 0; excel.Application.Workbooks.Add(true); foreach (DataColumn col in dt.Columns) { colIndex++; excel.Cells[1, colIndex] = col.ColumnName; } foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 0; for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++) { excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString(); } } excel.Visible = false; excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); excel.Quit(); excel = null; GC.Collect();//�������� }
public static void Export_To_Excel(DataGridView dtGridView, string filename, string sheetName) { int i, j; object missing = Type.Missing; Microsoft.Office.Interop.Excel.ApplicationClass excellApp; excellApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excellApp.Application.Workbooks.Add(true); try { // Add columns name to excel file for (i = 0; i < dtGridView.Columns.Count; i++) { excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name; } for (i = 0; i < dtGridView.Rows.Count; i++) { for (j = 0; j < dtGridView.Columns.Count; j++) { excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value; } } //excellApp.Save(("Loinhuan.xls"); Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)excellApp.ActiveSheet; worksheet.Activate(); worksheet.Name = sheetName; worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing); //excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls"); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } }
public static void ExportGridViewToExcel(string FileName, GridView gv) { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { ExcelApp.Application.Workbooks.Add(Type.Missing); //匯出全部欄位資料 //for (int i = 1; i < gv.Columns.Count + 1; i++) //{ // ExcelApp.Cells[1, i] = gv.Columns[i - 1].HeaderText; //} //for (int i = 0; i < gv.Rows.Count - 1; i++) //{ // for (int j = 0; j < gv.Columns.Count; j++) // { // ExcelApp.Cells[i + 2, j + 1] = gv.Rows[i].Cells[j].Text.ToString(); // } //} //隱藏第一個欄位資料 for (int i = 1; i < gv.Columns.Count ; i++) { ExcelApp.Cells[1, i] = gv.Columns[i].HeaderText; } for (int i = 0; i < gv.Rows.Count - 1; i++) { for (int j = 1; j < gv.Columns.Count; j++) { ExcelApp.Cells[i + 2, j] = gv.Rows[i].Cells[j].Text.ToString().Replace(" ", ""); } } ExcelApp.ActiveWorkbook.SaveCopyAs(FileName); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); } catch (Exception ex) { throw ex; } finally { ExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); ExcelApp = null; } GC.Collect(); }
private void button4_Click(object sender, EventArgs e) { if (bankImportYear.SelectedItem == null || bankImportMonth.SelectedItem == null) { MessageBox.Show("请选择导入的年份或月份"); } Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();//lauch excel application if (ElectriCharacter.SelectedIndex == 0) { importCityExcel(); //导入城网数据 } else { importRuralExcel(); //导入农网数据 } }
/// <summary> /// 数据库转为excel表格 /// </summary> /// <param name="dataTable">数据库数据</param> /// <param name="SaveFile">导出的excel文件</param> public static void DataSetToExcel(DataTable dataTable, string SaveFile) { Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel._Workbook workBook; Microsoft.Office.Interop.Excel._Worksheet workSheet; object misValue = System.Reflection.Missing.Value; excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); workBook = excel.Workbooks.Add(misValue); workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet; int rowIndex = 1; int colIndex = 0; //取得标题 foreach (DataColumn col in dataTable.Columns) { colIndex++; excel.Cells[1, colIndex] = col.ColumnName; } //取得表格中的数据 foreach (DataRow row in dataTable.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dataTable.Columns) { colIndex++; excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim(); //设置表格内容居中对齐 ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[colIndex, rowIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //设置表格自动适应大小 workSheet.Cells.Columns.AutoFit(); } } excel.Visible = false; workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); dataTable = null; workBook.Close(true, misValue, misValue); excel.Quit(); PublicMethod.Kill(excel);//调用kill当前excel进程 releaseObject(workSheet); releaseObject(workBook); releaseObject(excel); }
public void CreateUpdateFile(string filename, bool addData) { // Get data var cDemo = demo.GetCountryDemoRecent(); var levels = demo.GetRecentDemography(locationType.LevelNumber, cDemo.DateDemographyData.Year); DataTable data = new DataTable(); if (addData) data = CreateUpdateDataTable(levels); else data = CreateInsertDataTable(locationType); // Create excel System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlsWorkbook; Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet; object oMissing = System.Reflection.Missing.Value; //Create new workbook xlsWorkbook = xlsApp.Workbooks.Add(true); //Get the first worksheet xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]); // Load data into excel worksheet AddTableToWorksheet(data, xlsWorksheet); xlsApp.DisplayAlerts = false; xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing, oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, oMissing, oMissing, oMissing); xlsApp.Visible = true; xlsWorksheet = null; xlsWorkbook = null; xlsApp = null; System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; }
// Exportar Clientes public void exportarClientes() { Clientes.frmListaClientes frmListaClientes = new frmListaClientes(); frmListaClientes.Show(); frmListaClientes.Hide(); Microsoft.Office.Interop.Excel.ApplicationClass ExcelAppCli = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelAppCli.Application.Workbooks.Add(Type.Missing); SaveFileDialog drCli = new SaveFileDialog(); drCli.FileName = "Clientes " + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString(); drCli.Filter = "Excel files (*.xls)|*.xls"; drCli.Title = "CLIENTES"; if (drCli.ShowDialog() == DialogResult.OK) { for (int i = 1; i < frmListaClientes.dgvListadoClientes.Columns.Count + 1; i++) { ExcelAppCli.Cells[1, i] = frmListaClientes.dgvListadoClientes.Columns[i - 1].HeaderText; } for (int i = 0; i < frmListaClientes.dgvListadoClientes.Rows.Count; i++) { for (int j = 0; j < frmListaClientes.dgvListadoClientes.Columns.Count; j++) { ExcelAppCli.Cells[i + 2, j + 1] = frmListaClientes.dgvListadoClientes.Rows[i].Cells[j].Value.ToString(); } } ExcelAppCli.ActiveWorkbook.SaveCopyAs(drCli.FileName); ExcelAppCli.ActiveWorkbook.Saved = true; ExcelAppCli.Quit(); } int cantCli = frmListaClientes.dgvListadoClientes.Rows.Count; }
private void buttonExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // Creates a new Excel Application excelApp.Visible = true; // Makes Excel visible to the user. // The following line adds a new workbook Microsoft.Office.Interop.Excel.Workbook newWorkbook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); // The following code opens an existing workbook string workbookPath = Application.StartupPath + "\\template.xls"; // Add your own path here Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); // The following gets the Worksheets collection Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; // The following gets Sheet1 for editing string currentSheet = "Бланк"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); // The following gets cell A1 for editing //Microsoft.Office.Interop.Excel.Range excelCell = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A1", "A1"); // The following sets cell A1's value to "Hi There" //excelCell.Value2 = "Hi There"; _Ekskursovody a; int n = queueAccounts.Count; for (int i = 0; i < n; i++) { a = (_Ekskursovody)queueAccounts.Dequeue(); queueAccounts.Enqueue(a); if (a.id == m_journal.account_id.ToString()) { Microsoft.Office.Interop.Excel.Range excelCellAccount = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("C8", "C8"); excelCellAccount.Value2 = a.name; break; } } Microsoft.Office.Interop.Excel.Range excelCellID = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("D11", "D11"); excelCellID.Value2 = m_journal.id.ToString(); Microsoft.Office.Interop.Excel.Range excelCellStudent = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E14", "F14"); excelCellStudent.Value2 = m_journal.student; _PaymentNames pn; n = queuePaymentNames.Count; for (int i = 0; i < n; i++) { pn = (_PaymentNames)queuePaymentNames.Dequeue(); queuePaymentNames.Enqueue(pn); if (pn.id == m_journal.payment_name_id) { Microsoft.Office.Interop.Excel.Range excelCellPaymentName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B19", "C19"); excelCellPaymentName.Value2 = pn.name; break; } } _Semester s; n = queueSemesters.Count; for (int i = 0; i < n; i++) { s = (_Semester)queueSemesters.Dequeue(); queueSemesters.Enqueue(s); if (s.id == m_journal.semester) { Microsoft.Office.Interop.Excel.Range excelCellSemester = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B20", "C20"); excelCellSemester.Value2 = s.name; break; }; } _Pdv p; n = queuePdv.Count; for (int i = 0; i < n; i++) { p = (_Pdv)queuePdv.Dequeue(); queuePdv.Enqueue(p); if (p.id == m_journal.pdv) { Microsoft.Office.Interop.Excel.Range excelCellPdv = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E22", "E22"); excelCellPdv.Value2 = p.name; break; }; } _EduYears ey; n = queueEduYears.Count; for (int i = 0; i < n; i++) { ey = (_EduYears)queueEduYears.Dequeue(); queueEduYears.Enqueue(ey); if (ey.id.ToString() == m_journal.edu_year_id.ToString()) { Microsoft.Office.Interop.Excel.Range excelCellSemester = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B21", "C21"); excelCellSemester.Value2 = ey.name; break; }; } Microsoft.Office.Interop.Excel.Range excelCellSum = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F21", "F21"); excelCellSum.Value2 = m_journal.sum; String date = ""; if(DateTime.Parse(m_journal.date).Day<10) date += "0"; date += DateTime.Parse(m_journal.date).Day; date += "/"; if(DateTime.Parse(m_journal.date).Month<10) date += "0"; date += DateTime.Parse(m_journal.date).Month; date += "/"; date += DateTime.Parse(m_journal.date).Year; Microsoft.Office.Interop.Excel.Range excelCellDate = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F11", "F11"); excelCellDate.Value2 = date; }
static void ExcelRefresh(string Filename, string targetServer, string loadBalance) { object NullValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { excelApp.DisplayAlerts = false; excelApp.AskToUpdateLinks = false; excelApp.Visible = true; Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open( Filename, NullValue, false, NullValue, NullValue, NullValue, true, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue); if (loadBalance.Equals("Load Balance")) { for (int i = 1; i <= Workbook.Connections.Count; i++) { var con = Workbook.Connections.Item(i); var oleDBCon = con.OLEDBConnection; var conStr = (string)oleDBCon.Connection; var newConStr = Regex.Replace(conStr, @"Data Source=.*?;", String.Format(@"Data Source={0};", targetServer)); con.OLEDBConnection.Connection = newConStr; } } Workbook.RefreshAll(); /* if (loadBalance.Equals("Load Balance")) { for (int i = 1; i <= Workbook.Connections.Count; i++) { var con = Workbook.Connections.Item(i); var oleDBCon = con.OLEDBConnection; var conStr = (string)oleDBCon.Connection; var newConStr = Regex.Replace(conStr, @"Data Source=.*?;", String.Format(@"Data Source=tratab.tyson.com;")); con.OLEDBConnection.Connection = newConStr; } } */ Workbook.Save(); Workbook.Close(false, Filename, null); excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(Workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Workbook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp); //Workbook = null; } catch (Exception ex) { System.Diagnostics.EventLog.WriteEntry("RefreshExcelReport", ex.Message, System.Diagnostics.EventLogEntryType.Warning); System.Diagnostics.EventLog.WriteEntry("RefreshExcelReport", ex.StackTrace, System.Diagnostics.EventLogEntryType.Warning); throw ex; } finally { excelApp = null; } //excelApp.Quit(); }
public void CreateImportFile(string filename, bool importDemography, int rows, AdminLevel filterLevel) { // Get data filterBy = filterLevel; int dropdownCol = GetParams(); DataTable data = CreateNewImportDataTable(importDemography); DemoRepository demo = new DemoRepository(); CountryDemography recentCountryDemo = demo.GetCountryDemoRecent(); // Create excel System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlsWorkbook; Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet; Microsoft.Office.Interop.Excel.Worksheet xlsValidation; object oMissing = System.Reflection.Missing.Value; validationRanges = new Dictionary<string, string>(); //Create new workbook xlsWorkbook = xlsApp.Workbooks.Add(true); //Get the first worksheet xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]); // add hidden validation worksheet xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorkbook.Worksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing); xlsValidation.Name = validationSheetName; xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden; // Add columns int iCol = 0; foreach (DataColumn c in data.Columns) { iCol++; xlsWorksheet.Cells[1, iCol] = c.ColumnName; } string totalPopColumn = "F"; // Add rows for (int r = 1; r <= rows + 1; r++) { for (int i = 1; i < data.Columns.Count + 1; i++) { if (r == 1) { // Add the header the first time through xlsWorksheet.Cells[1, i] = data.Columns[i - 1].ColumnName; } else { if (i == 1 && filterByType != null) xlsWorksheet.Cells[r, i] = filterLevel.Name; if (dropdownCol == i && dropdownValues.Count > 0) { AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(i), r, dropdownBy.DisplayName, TranslationLookup.GetValue("PleaseSelect"), dropdownValues, oldCI); } if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("UrbanOrRural")) AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(i), r, TranslationLookup.GetValue("UrbanOrRural"), TranslationLookup.GetValue("PleaseSelect"), new List<string> { TranslationLookup.GetValue("AdminUnitUrban"), TranslationLookup.GetValue("AdminUnitRural"), TranslationLookup.GetValue("AdminUnitPeriRural")}, oldCI); if (importDemography) { if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("YearCensus")) xlsWorksheet.Cells[r, i] = recentCountryDemo.YearCensus; if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("GrowthRate")) xlsWorksheet.Cells[r, i] = recentCountryDemo.GrowthRate; if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("TotalPopulation")) totalPopColumn = Util.GetExcelColumnName(i); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("Pop0Month") && recentCountryDemo.Percent6mos.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.Percent6mos, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopPsac") && recentCountryDemo.PercentPsac.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentPsac, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("PopSac") && recentCountryDemo.PercentSac.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentSac, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("Pop5yo") && recentCountryDemo.Percent5yo.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.Percent5yo, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopAdult") && recentCountryDemo.PercentAdult.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentAdult, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopFemale") && recentCountryDemo.PercentFemale.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentFemale, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopMale") && recentCountryDemo.PercentMale.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentMale, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PercentRural") && recentCountryDemo.PercentRural.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentRural, r, totalPopColumn); } } } } var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); var range = xlsWorksheet.get_Range("A1", last); range.Columns.AutoFit(); xlsApp.DisplayAlerts = false; xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing, oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, oMissing, oMissing, oMissing); xlsApp.Visible = true; xlsWorksheet = null; xlsValidation = null; xlsWorkbook = null; xlsApp = null; System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; }
/// <summary> /// Xuất ra file dữ liệu trên gridView và định dạng dựa vào ext /// </summary> public static bool exportFile(GridView gridView, String ext, bool openFile, ref string outFileName, bool useHeader) { bool flag = false; bool succ = false; string filePath = null; bool flagShowView = gridView.OptionsView.ShowViewCaption; try { SaveFileDialog f = new SaveFileDialog(); f.Title = "Chọn tên tập tin muốn lưu"; if (ext.Equals("xls")) f.Filter = "Excel 97 - 2003 files (*.xls)|*.xls"; else if (ext.Equals("xlsx")) f.Filter = "Excel 2007 files (*.xlsx)|*.xlsx"; else if (ext.Equals("pdf")) f.Filter = "PDF (*.pdf)|*.pdf"; else if (ext.Equals("htm")) f.Filter = "Web Pages (*.htm)|*.htm"; else if (ext.Equals("rtf")) f.Filter = "Rich Text files (*.rtf)|*.rtf"; f.ShowDialog(); PrintableComponentLink link = null; if (useHeader && gridView.GridControl != null) { if (FrameworkParams.headerLetter != null) { gridView.OptionsView.ShowViewCaption = false; link = FrameworkParams.headerLetter.Draw(gridView.GridControl, gridView.ViewCaption, "Ngày xuất báo cáo: " + DateTime.Today.ToString(FrameworkParams.option.dateFormat)); } } if (f.FileName != "") { filePath = f.FileName; if (FrameworkParams.wait == null) { FrameworkParams.wait = new WaitingMsg(); flag = true; } if (ext.Equals("xls")) { if (gridView is PLGridView) { if (link != null) link.PrintingSystem.ExportToXls(f.FileName, new XlsExportOptions(((PLGridView)gridView)._TextExportMode)); else gridView.ExportToXls(f.FileName, new XlsExportOptions(((PLGridView)gridView)._TextExportMode)); } else { if (link != null) link.PrintingSystem.ExportToXls(f.FileName, new XlsExportOptions(TextExportMode.Text)); else gridView.ExportToXls(f.FileName, new XlsExportOptions(TextExportMode.Text)); } succ = true; } else if (ext.Equals("xlsx")) { if (gridView is PLGridView) { if (link != null) link.PrintingSystem.ExportToXlsx(f.FileName, new XlsxExportOptions(((PLGridView)gridView)._TextExportMode)); else gridView.ExportToXlsx(f.FileName, new XlsxExportOptions(((PLGridView)gridView)._TextExportMode)); } else { if (link != null) link.PrintingSystem.ExportToXlsx(f.FileName, new XlsxExportOptions(TextExportMode.Text)); else gridView.ExportToXlsx(f.FileName, new XlsxExportOptions(TextExportMode.Text)); } succ = true; } else if (ext.Equals("pdf")) { if (link != null) link.PrintingSystem.ExportToPdf(f.FileName); else gridView.ExportToPdf(f.FileName); succ = true; } else if (ext.Equals("rtf")) { if (link != null) link.PrintingSystem.ExportToRtf(f.FileName); else gridView.ExportToRtf(f.FileName); succ = true; } else if (ext.Equals("htm")) { if (link != null) link.PrintingSystem.ExportToHtml(f.FileName); else gridView.ExportToHtml(f.FileName); succ = true; } } } catch (Exception ex) { PLException.AddException(ex); return false; } finally { if (FrameworkParams.wait != null && flag == true) FrameworkParams.wait.Finish(); if (succ == true) { outFileName = filePath; if (ext.Equals("xls") || ext.Equals("xlsx")) { try { System.Globalization.CultureInfo oldCi = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(filePath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.ActiveSheet; excelSheet.Columns.AutoFit(); excelWorkbook.Save(); System.Threading.Thread.CurrentThread.CurrentCulture = oldCi; if (openFile && PLMessageBox.ShowConfirmMessage("Bạn có muốn mở tập tin này không?") == DialogResult.Yes) { excelApp.Visible = true; } else { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelSheet = null; excelWorkbook = null; excelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); } } catch { if (openFile && PLMessageBox.ShowConfirmMessage("Bạn có muốn mở tập tin này không?") == DialogResult.Yes) { if (!HelpFile.OpenFile(filePath)) HelpMsgBox.ShowNotificationMessage("Mở tập tin không thành công"); } } } else if (openFile && PLMessageBox.ShowConfirmMessage("Bạn có muốn mở tập tin này không?") == DialogResult.Yes) { if (!HelpFile.OpenFile(filePath)) HelpMsgBox.ShowNotificationMessage("Mở tập tin không thành công"); } } gridView.OptionsView.ShowViewCaption = flagShowView; } return true; }
public static void ExportToExcel_FromVS(C1FlexGrid vs, ArrayList arColsVisible) { object oMissing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbooks oBooks = oExcel.Workbooks; Microsoft.Office.Interop.Excel._Workbook oBook = null; System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); oBook = oBooks.Add(oMissing); Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets[1]; oExcel.Visible = true; try { // Dinh dang cot int iCol = 1; for (int i = 1; i < vs.Cols.Count; i++) { if (arColsVisible[i] + "" == "True") { Microsoft.Office.Interop.Excel.Range rg = oSheet.get_Range(GetExcelColumnName(iCol) + ":" + GetExcelColumnName(iCol), Missing.Value); switch (vs.Cols[i].DataType.Name) { case "Double": case "Decimal": rg.EntireColumn.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)"; break; case "Int16": case "Int32": case "Int64": case "Single": rg.EntireColumn.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)"; break; case "Boolean": break; case "DateTime": break; case "String": rg.EntireColumn.NumberFormat = "@"; break; default: break; } iCol++; } } oExcel.ScreenUpdating = false; for (int i = 0; i < vs.Rows.Count; i++) { iCol = 1; for (int j = 0; j < vs.Cols.Count; j++) { if (arColsVisible[j] + "" == "True") { oSheet.Cells[i + 2, iCol] = vs.Rows[i][j]; iCol++; } } } oSheet.Columns.AutoFit(); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message, "Error!"); } finally { oExcel.ScreenUpdating = true; System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); GC.Collect(); } }
public static bool DataGridViewExportToExcel(DataTable mDataTable, String strFileName, ref String strMsg) { strMsg = ""; // 创建Excel对象 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { strMsg = "Excel无法启动"; return(false); } // 创建Excel工作薄 Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1]; Microsoft.Office.Interop.Excel.Range range = null; /* * // 设置标题 * range = xlSheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,ts.GridColumnStyles.Count]); * range.MergeCells = true; * xlApp.ActiveCell.FormulaR1C1 = p_ReportName; * xlApp.ActiveCell.Font.Size = 20; * xlApp.ActiveCell.Font.Bold = true; * xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter; */ // 列索引,行索引,总列数,总行数 int colIndex = 0; int RowIndex = 0; int colCount = mDataTable.Columns.Count; int RowCount = mDataTable.Rows.Count + 1; // 创建缓存数据 object[,] objData = new object[RowCount + 1, colCount]; // 获取列标题 foreach (DataColumn dc in mDataTable.Columns) { objData[RowIndex, colIndex++] = dc.ColumnName; } // 获取数据 for (RowIndex = 1; RowIndex < RowCount; RowIndex++) { for (colIndex = 0; colIndex < colCount; colIndex++) { objData[RowIndex, colIndex] = mDataTable.Rows[RowIndex - 1][colIndex].ToString(); } } // 写入Excel //((Excel.Range)xlSheet.Columns["A:A ",System.Reflection.Missing.Value]).ColumnWidth = 16; range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount, colCount]); range.Value2 = objData; range = (Microsoft.Office.Interop.Excel.Range)xlSheet.Columns["A:B", System.Type.Missing]; range.ColumnWidth = 20; // 保存 try { xlBook.Saved = true; xlBook.SaveCopyAs(strFileName); MessageBox.Show("数据转存为Excel成功"); } catch (Exception ee) { strMsg = ee.Message; return(false); } finally { range = null; xlSheet = null; xlBook = null; xlApp.Quit(); xlApp = null; GC.Collect(); } return(true); }
/// <summary> /// 输出Excel /// </summary> /// <param name="Report"></param> public void ReportToExcel(DataTable Report) { try { int totalColumn = Report.Columns.Count; Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); excel.Application.Workbooks.Add(true); #region 填充数据 int row = 3; Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1]; Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn]; for (int i = 0; i < Report.Columns.Count; i++) { excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString(); } row = row + 1; for (int i = 0; i < Report.Rows.Count; i++) { for (int j = 0; j < Report.Columns.Count; j++) { object objValue = Report.Rows[i][Report.Columns[j].ColumnName]; if (Convert.IsDBNull(objValue)) { continue; } excel.Cells[row + i, j + 1] = objValue.ToString(); } } #endregion #region 画网格线 object obj = excel.get_Range(startCell, endCell).Select(); excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; #endregion excel.ActiveWindow.DisplayGridlines = false; excel.Visible = true; } catch (Exception err) { MessageBox.Show("输出到Excel发生错误!", "", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { GC.Collect(); } }
protected void DataTableToExcel2(System.Data.DataTable qingjiadantbl, string fileName, bool flag) { if (qingjiadantbl == null) { return; } int rowNum = qingjiadantbl.Rows.Count; int columnNum = qingjiadantbl.Columns.Count; int rowIndex = 1; int columnIndex = 0; var excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = false; excelApp.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbook newBook = excelApp.Workbooks.Add(); //newBook.SaveAs(fileName); //将DataTable的列名导入Excel表第一行 foreach (DataColumn col in qingjiadantbl.Columns) { columnIndex++; excelApp.Cells[rowIndex, columnIndex] = col.Caption; } //将DataTable中的数据导入Excel中 Microsoft.Office.Interop.Excel.Range r = excelApp.get_Range(excelApp.Cells[1, 3], excelApp.Cells[rowNum + 1, columnIndex]); r.NumberFormat = "@"; r.NumberFormatLocal = "@"; for (int i = 0; i < rowNum; i++) { rowIndex++;//数据从第二行开始 columnIndex = 0; for (int j = 0; j < columnNum; j++) { columnIndex++; //if (columnIndex == 3||columnIndex == 18) //{ // Microsoft.Office.Interop.Excel.Range r = excelApp.get_Range(excelApp.Cells[rowIndex, columnIndex], excelApp.Cells[rowIndex, columnIndex]); // r.NumberFormat = "@"; // r.NumberFormatLocal = "@"; //} excelApp.Cells[rowIndex, columnIndex] = qingjiadantbl.Rows[i][j].ToString(); } } excelApp.Cells.Columns.AutoFit(); newBook.SaveCopyAs(fileName); newBook.Close(); excelApp.Workbooks.Close(); excelApp.Quit(); //Kill打开的Excel进程 //Process[] excelApps; //excelApps = Process.GetProcessesByName("EXCEL"); //foreach (Process p in excelApps)`1q //{ // p.Kill(); //}//End if (flag) { AddAtt(fileName); //加入附件中 } }
public string getVersion() { Microsoft.Office.Interop.Excel.Application excelApplication; excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass(); return(excelApplication.Version); }
private void dataToExelMainMenuItem_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks; object obj = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(obj); Microsoft.Office.Interop.Excel.Sheets ss = wb.Worksheets; Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)ss.get_Item(1); //object[] headers = { "First", "Second", "Third1111111111" }; //Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A1", "C1"); //range.Value2 = headers; //range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatReport1, obj, obj, obj, obj, obj, obj); //range.Font.Bold = true; //range = ws.get_Range("A2", obj); //range = range.get_Resize(3, 3); //range.Borders.Value = true; ////range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple, obj, obj, obj, obj, obj, obj); //object[,] data = { { "1", "2", "3" }, { "4", "5", "6" }, { "7", "8", "9" } }; //range.Value2 = data; //excel.Visible = true; DataGridView grid = tabControl1.SelectedTab.Controls[0] as DataGridView; Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A1", obj); range = range.get_Resize(grid.Rows.Count, 6); object[,] data = new object[grid.Rows.Count, 6]; data[0, 0] = "Дата"; data[0, 1] = "Время"; data[0, 2] = "Широта"; data[0, 3] = "Долгота"; data[0, 4] = "От предыдущей, м"; data[0, 5] = "Всего, м"; for (int i = 0; i < grid.Rows.Count - 1; i++) { for (int j = 0; j < 6; j++) { data[i + 1, j] = grid[j, i].Value; } } range.Value2 = data; range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple, obj, obj, obj, obj, obj, obj); excel.Visible = true; } #pragma warning disable 168 catch (NullReferenceException except) { MessageBox.Show("Нет данных для отображения", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } catch (Exception except) { MessageBox.Show("При передаче данных в Excel произошла ошибка", "Ошибка-", MessageBoxButtons.OK, MessageBoxIcon.Error); } #pragma warning restore 168 }
/// <summary> /// 输出Excel /// </summary> /// <param name="Report"></param> public void ReportToExcel(DataTable Report) { try { int totalColumn = Report.Columns.Count; Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); excel.Application.Workbooks.Add(true); #region 填充数据 excel.Cells[1, 1] = HIS.SYSTEM.BussinessLogicLayer.Classes.BaseData.WorkName + "门诊收费项目收入报表"; Microsoft.Office.Interop.Excel.Range titleStartcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, 1]; Microsoft.Office.Interop.Excel.Range titleEndcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, totalColumn]; excel.get_Range(titleStartcell, titleEndcell).Merge(0); excel.get_Range(titleStartcell, titleEndcell).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; excel.get_Range(titleStartcell, titleEndcell).Font.Name = "宋体"; excel.get_Range(titleStartcell, titleEndcell).Font.Size = 15; excel.get_Range(titleStartcell, titleEndcell).Font.Bold = true; excel.Cells[2, 1] = "统计时间:"; excel.Cells[2, 2] = dtpFrom.Value.ToString("yyyy-MM-dd HH:mm:ss") + " -- " + dtpTo.Value.ToString("yyyy-MM-dd HH:mm:ss"); excel.get_Range((Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 2], (Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 6]).Merge(0); excel.Cells[2, totalColumn - 2] = "制表人:"; excel.Cells[2, totalColumn - 1] = _user.Name; int row = 3; Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1]; Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn]; for (int i = 0; i < Report.Columns.Count; i++) { excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString(); } row = row + 1; for (int i = 0; i < Report.Rows.Count; i++) { for (int j = 0; j < Report.Columns.Count; j++) { object objValue = Report.Rows[i][Report.Columns[j].ColumnName]; if (Convert.IsDBNull(objValue)) { continue; } excel.Cells[row + i, j + 1] = objValue.ToString(); } } #endregion #region 画网格线 object obj = excel.get_Range(startCell, endCell).Select(); excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; row = row + Report.Rows.Count + 1; excel.Cells[row, 1] = "审核人"; excel.Cells[row, totalColumn - 2] = "打印日期:"; excel.Cells[row, totalColumn] = HIS.SYSTEM.PubicBaseClasses.XcDate.ServerDateTime.ToString("yyyy-MM-dd"); #endregion excel.ActiveWindow.DisplayGridlines = false; excel.Visible = true; } catch { MessageBox.Show("输出到Excel发生错误!", "", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { GC.Collect(); } }
/// <summary> /// Click event to handle the export to excel /// </summary> /// <param name="sender">Sender object</param> /// <param name="e">event data</param> private void Export_Click(object sender, EventArgs e) { try { var table = (DataTable)dataGridViewMembers.DataSource; Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); excel.Application.Workbooks.Add(true); int columnIndex = 0; foreach (DataColumn col in table.Columns) { columnIndex++; excel.Cells[1, columnIndex] = col.ColumnName; } int rowIndex = 0; foreach (DataRow row in table.Rows) { rowIndex++; columnIndex = 0; foreach (DataColumn col in table.Columns) { columnIndex++; if (columnIndex == 4 || columnIndex == 5 || columnIndex == 6) { if (columnIndex == 4) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(Occupation), int.Parse(row[col.ColumnName].ToString())); } if (columnIndex == 5) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(MaritalStatus), int.Parse(row[col.ColumnName].ToString())); } if (columnIndex == 6) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(HealthStatus), int.Parse(row[col.ColumnName].ToString())); } } else { excel.Cells[rowIndex + 1, columnIndex] = row[col.ColumnName].ToString(); } } } excel.Visible = true; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet; worksheet.Activate(); } catch (Exception ex) { this.ShowErrorMessage(ex); } }
public void GenerateExcel() { Microsoft.Office.Interop.Excel.ApplicationClass xl; xl = new Microsoft.Office.Interop.Excel.ApplicationClass(); xl.Visible = false; Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)xl.Workbooks; Microsoft.Office.Interop.Excel.Workbook book = books.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet); Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; sheet.Name = "Касса"; Microsoft.Office.Interop.Excel.Range rd; Microsoft.Office.Interop.Excel.Range rd1; Microsoft.Office.Interop.Excel.Range rd2; Microsoft.Office.Interop.Excel.Range rd3; Microsoft.Office.Interop.Excel.Range rd4; Microsoft.Office.Interop.Excel.Range rd5; Microsoft.Office.Interop.Excel.Range rd6; Microsoft.Office.Interop.Excel.Range rd7; Microsoft.Office.Interop.Excel.Range rd8; Microsoft.Office.Interop.Excel.Range rd9; Microsoft.Office.Interop.Excel.Range rd10; Microsoft.Office.Interop.Excel.Range rd11; Microsoft.Office.Interop.Excel.Range rd12; Microsoft.Office.Interop.Excel.Range rd13; Microsoft.Office.Interop.Excel.Range rd14; Microsoft.Office.Interop.Excel.Range rd15; Microsoft.Office.Interop.Excel.Range rd16; Microsoft.Office.Interop.Excel.Range rd17; Microsoft.Office.Interop.Excel.Range rd18; Microsoft.Office.Interop.Excel.Range rd19; Microsoft.Office.Interop.Excel.Range rd20; Microsoft.Office.Interop.Excel.Range rd21; Microsoft.Office.Interop.Excel.Range rd22; Microsoft.Office.Interop.Excel.Range rd23; Microsoft.Office.Interop.Excel.Range rd24; Microsoft.Office.Interop.Excel.Range rd25; Microsoft.Office.Interop.Excel.Range rd26; Microsoft.Office.Interop.Excel.Range rd27; Microsoft.Office.Interop.Excel.Range rd28; Microsoft.Office.Interop.Excel.Range rd29; Microsoft.Office.Interop.Excel.Range rd30; Microsoft.Office.Interop.Excel.Range rd31; Microsoft.Office.Interop.Excel.Range rd32; Microsoft.Office.Interop.Excel.Range rd33; Microsoft.Office.Interop.Excel.Range rd34; Microsoft.Office.Interop.Excel.Range rd35; Microsoft.Office.Interop.Excel.Range rd36; Microsoft.Office.Interop.Excel.Range rd37; Microsoft.Office.Interop.Excel.Range rd38; Microsoft.Office.Interop.Excel.Range rd39; Microsoft.Office.Interop.Excel.Range rd40; Microsoft.Office.Interop.Excel.Range rd41; Microsoft.Office.Interop.Excel.Range rd42; Microsoft.Office.Interop.Excel.Range rd43; Microsoft.Office.Interop.Excel.Range rd44; Microsoft.Office.Interop.Excel.Range rd45; rd = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, "C"]; rd.Value2 = "Касса"; rd.Font.Bold = true; rd1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, "B"]; rd1.Value2 = "за период с"; rd1.ColumnWidth = 12; rd2 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, "C"]; rd2.Value2 = this.Date1.ToString("dd-MMM-yyyy"); rd3 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, "D"]; rd3.Value2 = " по "; rd3.ColumnWidth = 3; rd4 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, "E"]; rd4.Value2 = this.Date2.ToString("dd-MMM-yyyy"); rd5 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[4, "A"], sheet.Cells[4, "D"]); rd5.MergeCells = true; rd5.Font.Bold = true; rd5.Value2 = "Абонементы"; rd6 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[4, "E"]; rd6.Value2 = this.Abonements.ToString(); int ind = 6; rd30 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "A"]; rd30.Value2 = "Абонемент"; rd30.Font.Bold = true; rd30.ColumnWidth = 35; rd31 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "B"]; rd31.Value2 = "Дата"; rd31.Font.Bold = true; rd31.ColumnWidth = 10; rd32 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "C"]; rd32.Value2 = "Стоимость"; rd32.Font.Bold = true; rd32.ColumnWidth = 10; for (int i = 0; i < advBandedGridView1.RowCount; i++) { if (Convert.ToInt32(advBandedGridView1.GetRowCellValue(i, "Type")) == 0) { rd7 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[6 + i, "A"]; rd7.Value2 = advBandedGridView1.GetRowCellValue(i, "Good"); rd7.ColumnWidth = 35; rd8 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[6 + i, "B"]; rd8.Value2 = Convert.ToDateTime(advBandedGridView1.GetRowCellValue(i, "Date")).ToString("dd-MMM-yyyy"); rd8.ColumnWidth = 10; rd9 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[6 + i, "C"]; rd9.Value2 = advBandedGridView1.GetRowCellValue(i, "Cost"); rd9.ColumnWidth = 10; ind += 1; } } rd10 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[ind + 2, "A"], sheet.Cells[ind + 2, "D"]); rd10.MergeCells = true; rd10.Font.Bold = true; rd10.Value2 = "Товары"; rd11 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + 2, "E"]; rd11.Value2 = this.Goods.ToString(); ind += 4; rd33 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "A"]; rd33.Value2 = "Товар"; rd33.Font.Bold = true; rd33.ColumnWidth = 35; rd34 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "B"]; rd34.Value2 = "Дата продажи"; rd34.Font.Bold = true; rd34.ColumnWidth = 12; rd35 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "C"]; rd35.Font.Bold = true; rd35.Value2 = "Цена"; rd35.ColumnWidth = 10; rd36 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "D"]; rd36.Font.Bold = true; rd36.Value2 = "Количество"; rd36.ColumnWidth = 12; rd37 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "E"]; rd37.Value2 = "Итого"; rd37.Font.Bold = true; rd37.ColumnWidth = 12; int newInt = ind; int servInd = 0; for (int i = 0; i < advBandedGridView1.RowCount; i++) { if (Convert.ToInt32(advBandedGridView1.GetRowCellValue(i, "Type")) == 1) { rd12 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "A"]; rd12.Value2 = advBandedGridView1.GetRowCellValue(i, "Good"); rd12.ColumnWidth = 35; rd13 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "B"]; rd13.Value2 = Convert.ToDateTime(advBandedGridView1.GetRowCellValue(i, "Date")).ToString("dd-MMM-yyyy"); rd13.ColumnWidth = 10; rd14 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "C"]; rd14.Value2 = advBandedGridView1.GetRowCellValue(i, "Cost"); rd14.ColumnWidth = 10; rd14 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "D"]; rd14.Value2 = advBandedGridView1.GetRowCellValue(i, "Quantity"); rd14.ColumnWidth = 10; rd28 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "E"]; rd28.Value2 = Convert.ToDouble(advBandedGridView1.GetRowCellValue(i, "Quantity")) * Convert.ToDouble(advBandedGridView1.GetRowCellValue(i, "Cost")); rd28.ColumnWidth = 10; newInt += 1; } else { servInd += 1; } } ind = newInt; rd15 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[ind + 2, "A"], sheet.Cells[ind + 2, "D"]); rd15.MergeCells = true; rd15.Font.Bold = true; rd15.Value2 = "Услуги"; rd16 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + 2, "E"]; rd16.Value2 = this.Services.ToString(); ind = ind + 4; rd38 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "A"]; rd38.Value2 = "Услуга"; rd38.Font.Bold = true; rd38.ColumnWidth = 35; rd39 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "B"]; rd39.Value2 = "Дата продажи"; rd39.Font.Bold = true; rd39.ColumnWidth = 12; rd40 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "C"]; rd40.Value2 = "Цена"; rd40.Font.Bold = true; rd40.ColumnWidth = 12; rd41 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "D"]; rd41.Value2 = "Количество"; rd41.Font.Bold = true; rd41.ColumnWidth = 12; rd42 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "E"]; rd42.Value2 = "Итого"; rd42.ColumnWidth = 12; rd42.Font.Bold = true; int newInt2 = ind; int servInd2 = 0; for (int i = 0; i < advBandedGridView1.RowCount; i++) { if (Convert.ToInt32(advBandedGridView1.GetRowCellValue(i, "Type")) == 2) { rd17 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "A"]; rd17.Value2 = advBandedGridView1.GetRowCellValue(i, "Good"); rd17.ColumnWidth = 35; rd18 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "B"]; rd18.Value2 = Convert.ToDateTime(advBandedGridView1.GetRowCellValue(i, "Date")).ToString("dd-MMM-yyyy"); rd18.ColumnWidth = 12; rd19 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "C"]; rd19.Value2 = advBandedGridView1.GetRowCellValue(i, "Cost"); rd19.ColumnWidth = 12; rd20 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "D"]; rd20.Value2 = advBandedGridView1.GetRowCellValue(i, "Quantity"); rd20.ColumnWidth = 12; rd29 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "E"]; rd29.Value2 = Convert.ToDouble(advBandedGridView1.GetRowCellValue(i, "Quantity")) * Convert.ToDouble(advBandedGridView1.GetRowCellValue(i, "Cost")); rd29.ColumnWidth = 12; newInt2 += 1; } else { servInd2 += 1; } } ind = newInt2 + 2; rd21 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[ind, "A"], sheet.Cells[ind, "D"]); rd21.MergeCells = true; rd21.Font.Bold = true; rd21.Value2 = "Расходы"; rd22 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind, "E"]; rd22.Value2 = this.Charges.ToString(); int val = ind; ind = ind + 2; int newInt3 = ind; int servInd3 = 0; rd43 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "A"]; rd43.Value2 = "Название"; rd43.Font.Bold = true; rd43.ColumnWidth = 35; rd44 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "B"]; rd44.Value2 = "Дата"; rd44.Font.Bold = true; rd44.ColumnWidth = 10; rd45 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "C"]; rd45.Value2 = "Сумма"; rd45.Font.Bold = true; rd45.ColumnWidth = 10; for (int i = 0; i < advBandedGridView1.RowCount; i++) { if (Convert.ToInt32(advBandedGridView1.GetRowCellValue(i, "Type")) == 3) { rd23 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd3, "A"]; rd23.Value2 = advBandedGridView1.GetRowCellValue(i, "Good"); rd23.ColumnWidth = 35; rd24 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd3, "B"]; rd24.Value2 = Convert.ToDateTime(advBandedGridView1.GetRowCellValue(i, "Date")).ToString("dd-MMM-yyyy"); rd24.ColumnWidth = 10; rd25 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd3, "C"]; rd25.Value2 = advBandedGridView1.GetRowCellValue(i, "Total"); rd25.ColumnWidth = 10; newInt3 += 1; } else { servInd3 += 1; } } ind = newInt3 + 2; int valFinish = ind; Microsoft.Office.Interop.Excel.Range rd47 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[val, "A"], sheet.Cells[valFinish, "F"]); rd47 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[ind, "A"], sheet.Cells[ind, "E"]); rd47.MergeCells = true; rd47.Font.Bold = true; rd47.Value2 = "Сальдо"; rd47 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + 1, "G"]; rd47.Value2 = (this.Abonements + this.Goods + this.Services + this.Charges).ToString(); xl.Visible = true; }
public void CreateUpdateFile(string filename, List<IHaveDynamicIndicatorValues> forms) { ReloadDropdownValues(); LoadRelatedLists(); System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlsWorkbook; Microsoft.Office.Interop.Excel.Workbooks xlsWorkbooks; Microsoft.Office.Interop.Excel.Sheets xlsWorksheets; Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet; Microsoft.Office.Interop.Excel.Worksheet xlsValidation; object oMissing = System.Reflection.Missing.Value; validationRanges = new Dictionary<string, string>(); //Create new workbook xlsWorkbooks = xlsApp.Workbooks; xlsWorkbook = xlsWorkbooks.Add(true); xlsWorksheets = xlsWorkbook.Worksheets; //Get the first worksheet xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]); // add hidden validation worksheet xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing); xlsValidation.Name = validationSheetName; xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden; xlsWorksheet.Cells[1, 1] = "* " + TranslationLookup.GetValue("ID"); xlsWorksheet.Cells[1, 2] = "* " + TranslationLookup.GetValue("Location"); int locationCount = 2; int xlsColCount = 2; xlsColCount = AddTypeSpecific(xlsWorksheet, xlsColCount); int colCountAfterStatic = xlsColCount; foreach (var item in Indicators) { if (item.Value.DataTypeId == (int)IndicatorDataType.SentinelSite || item.Value.IsCalculated || item.Value.IsMetaData) continue; string isReq = ""; if (item.Value.IsRequired) isReq = "* "; xlsColCount++; xlsWorksheet.Cells[1, xlsColCount] = isReq + TranslationLookup.GetValue(item.Key, item.Key); } //xlsWorksheet.Cells[1, xlsColCount + 1] = TranslationLookup.GetValue("Notes"); // row 2+ admin levels DemoRepository repo = new DemoRepository(); int xlsRowCount = 2; foreach (var form in forms) { var adminLevel = repo.GetAdminLevelById(form.AdminLevelId.Value); xlsWorksheet.Cells[xlsRowCount, 1] = form.Id; xlsWorksheet.Cells[xlsRowCount, 2] = GetAdminLevelName(adminLevel, form); AddTypeSpecificLists(xlsWorksheet, xlsValidation, adminLevel.Id, xlsRowCount, oldCI, locationCount); AddTypeSpecificListValues(xlsWorksheet, xlsValidation, adminLevel.Id, xlsRowCount, oldCI, locationCount, form); int colCount = colCountAfterStatic; foreach (var key in Indicators.Keys) { if (Indicators[key].DataTypeId == (int)IndicatorDataType.SentinelSite || Indicators[key].IsCalculated || Indicators[key].IsMetaData) continue; string value = ""; var iv = form.IndicatorValues.FirstOrDefault(x => x.IndicatorId == Indicators[key].Id); if (iv != null) value = iv.DynamicValue; colCount++; colCount = AddValueToCell(xlsWorksheet, xlsValidation, colCount, xlsRowCount, value, Indicators[key], oldCI, true); } xlsRowCount++; } // Auto fit var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); var range = xlsWorksheet.get_Range("A1", last); range.Columns.AutoFit(); // Save and display xlsApp.DisplayAlerts = false; xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing, oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, oMissing, oMissing, oMissing); xlsApp.Visible = true; Marshal.ReleaseComObject(xlsWorksheets); Marshal.ReleaseComObject(xlsWorksheet); Marshal.ReleaseComObject(xlsValidation); Marshal.ReleaseComObject(xlsWorkbooks); Marshal.ReleaseComObject(xlsWorkbook); Marshal.ReleaseComObject(xlsApp); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; }
private void OpenExcel(string strFileName) { object objMissing = System.Reflection.Missing.Value; this.appClsExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); appClsExcel.UserControl = true; appClsExcel.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbook wBookExcel = appClsExcel.Workbooks.Open(strFileName, objMissing, true, objMissing, objMissing, objMissing , objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing); Microsoft.Office.Interop.Excel.Worksheet wSheetExcel = (Microsoft.Office.Interop.Excel.Worksheet)wBookExcel.ActiveSheet; Microsoft.Office.Interop.Excel.Range rCell = wSheetExcel.UsedRange; object[,] objList = (object[, ])rCell.Value2; int excelcounts = objList.GetLength(0); wbs = appClsExcel.Workbooks; wb = wbs[1]; ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["信息维护"]; int rowCount = ws.UsedRange.Rows.Count; int colCount = ws.UsedRange.Columns.Count; if (rowCount <= 0) { MessageBox.Show("文件中没有数据记录", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (colCount != 8) { MessageBox.Show("字段个数不对", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } DataTable MouldCycleTable = new IQC_MouldCycle().Tables["IQC_MouldCycle"]; for (int iRow = 1; iRow < rowCount; iRow++) { if (objList[iRow + 1, 1] != null && objList[iRow + 1, 2] != null) { DataRow row; row = MouldCycleTable.NewRow(); row["materialcode"] = objList[iRow + 1, 1].ToString(); row["vendorcode"] = objList[iRow + 1, 2].ToString(); row["Mouldcode"] = objList[iRow + 1, 3] == null ? "" : objList[iRow + 1, 3].ToString(); row["Mouldtype"] = objList[iRow + 1, 4] == null ? "" : objList[iRow + 1, 4].ToString(); row["ColorMode"] = objList[iRow + 1, 5] == null ? "" : objList[iRow + 1, 5].ToString(); row["MouldQty"] = objList[iRow + 1, 6] == null ? "" : objList[iRow + 1, 6].ToString(); row["CavityQty"] = objList[iRow + 1, 7] == null ? "" : objList[iRow + 1, 7].ToString(); row["MouldLife"] = objList[iRow + 1, 8] == null ? "" : objList[iRow + 1, 8].ToString(); //row["RestQty"] = objList[iRow + 1, 1].ToString(); //row["thisTimeQty"] = objList[iRow + 1, 1].ToString(); row["updateMan"] = Login.username; MouldCycleTable.Rows.Add(row); } else { break; } } gridControl.DataSource = MouldCycleTable; appClsExcel.Quit(); appClsExcel = null; Process[] procs = Process.GetProcessesByName("Excel"); foreach (Process pro in procs) { pro.Kill(); } GC.Collect(); }
public static bool SetDataToExcel(string pathToExcelFile, string sheetName, DataTable data, string[] intro, string title) { try { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook wb; if (!System.IO.File.Exists(pathToExcelFile)) { //15/03/2011 Template ? System.IO.File.Copy(SystemHelper.GetConfigValue("appStartupPath") + "\\template\\newWorkbook.xlsx", pathToExcelFile); } wb = app.Workbooks.Open(pathToExcelFile, 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); //Find sheetname int index = 1; for (index = 1; index <= wb.Sheets.Count; index++) { if (((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Name.ToLower().Trim() == sheetName.ToLower().Trim()) break; } if (index > wb.Sheets.Count) { wb.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); } else { ((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Activate(); } Microsoft.Office.Interop.Excel.Worksheet activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet; activeSheet.Name = sheetName; //activeSheet.Cells.FormatConditions = if (!System.IO.File.Exists(pathToExcelFile)) { //wb.SaveAs(pathToExcelFile, 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); wb.SaveAs(pathToExcelFile, 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); } else { wb.Save(); } // exit excel, still now work so far wb.Close(true, Type.Missing, Type.Missing); app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); System.Runtime.InteropServices.Marshal.ReleaseComObject(app.Workbooks); while (System.Runtime.InteropServices.Marshal.ReleaseComObject(app) != 0) { }; //System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Close(); //System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Kill(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathToExcelFile, true)) { IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) // the sheet with that name couldn't be found { return false; } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>(); //sheetData.RemoveAllChildren<Row>(); //SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart; int lastRowIndex = sheetData.ChildElements.Count; int k = 0; //step in intro for (int i = lastRowIndex; i < data.Rows.Count + 4 + lastRowIndex + intro.Length; i++) // +1 for the header label, + 2 for spacing row, + 1 for title { DataRow dataRow = null; if (i > lastRowIndex + 3 + intro.Length) { dataRow = data.Rows[i - 4 - lastRowIndex - intro.Length]; } Row aRow = new Row(); aRow.RowIndex = (UInt32)i + 1; bool isRowEnd = false;//Fix the excel expand for (int j = 0; j < data.Columns.Count; j++) { if (isRowEnd) { break;//Fix the excel expand } Cell cell = new Cell(); cell.DataType = CellValues.InlineString; cell.CellReference = GetAlpha(j + 1) + (i + 1); InlineString inlineString = new InlineString(); Text t = new Text(); if (i <= lastRowIndex + intro.Length - 1)//Intro { if (j == 0) { t.Text = intro[k]; k++; cell.StyleIndex = (UInt32Value)3U; isRowEnd = true; } } else if (i == lastRowIndex + intro.Length + 1)//Title { if (j == 0) { t.Text = title; cell.StyleIndex = (UInt32Value)4U; isRowEnd = true; } } else if (i == lastRowIndex + 3 + intro.Length)//Header { t.Text = data.Columns[j].ToString(); cell.StyleIndex = (UInt32Value)1U; } else { if (i != intro.Length + lastRowIndex && i != intro.Length + lastRowIndex + 2)//Null spacing row t.Text = dataRow[j].ToString();//Data row } inlineString.AppendChild(t); cell.AppendChild(inlineString); aRow.AppendChild(cell); } sheetData.AppendChild(aRow); } System.Xml.XmlWriter test = System.Xml.XmlWriter.Create("Test.xml"); worksheetPart.Worksheet.WriteTo(test); test.Close(); worksheetPart.Worksheet.Save(); document.WorkbookPart.Workbook.Save(); document.Close(); } } catch (Exception e) { SystemHelper.LogEntry(string.Format("Error occurs on method {0} - Message {1}", "GetDataFromExcel", e.Message)); return false; } return true; }
private void OpenFileExcel(string file) { System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); var viewApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); viewApp.Workbooks.Open(file, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); System.Threading.Thread.CurrentThread.CurrentCulture = _oldCi; viewApp.Visible = true; }
public void CreateImportFile(IndicatorManagerOptions indicatorManagerOptions, string filename) { ImporterBase importer = new ImporterBase(); Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlsWorkbook; Microsoft.Office.Interop.Excel.Workbooks xlsWorkbooks; Microsoft.Office.Interop.Excel.Sheets xlsWorksheets; Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet; Microsoft.Office.Interop.Excel.Worksheet xlsValidation; object oMissing = System.Reflection.Missing.Value; validationRanges = new Dictionary<string, string>(); //Create new workbook xlsWorkbooks = xlsApp.Workbooks; xlsWorkbook = xlsWorkbooks.Add(true); xlsWorksheets = xlsWorkbook.Worksheets; //Get the first worksheet xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]); // add hidden validation worksheet xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing); xlsValidation.Name = validationSheetName; xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden; // row 1 column headers xlsWorksheet.Cells[1, 1] = "Indicator ID"; xlsWorksheet.Cells[1, 2] = "Type ID"; xlsWorksheet.Cells[1, 3] = "Type Name"; xlsWorksheet.Cells[1, 4] = "Form Name"; // Drop down xlsWorksheet.Cells[1, 5] = "Indicator Name English"; xlsWorksheet.Cells[1, 6] = "Indicator Name French"; xlsWorksheet.Cells[1, 7] = "Indicator Name Portuguese"; xlsWorksheet.Cells[1, 8] = "Indicator Name Bahasa"; xlsWorksheet.Cells[1, 9] = "Indicator Type"; // Drop down xlsWorksheet.Cells[1, 10] = "Is Required"; xlsWorksheet.Cells[1, 11] = "Aggregation Rule"; // Drop down xlsWorksheet.Cells[1, 12] = "Merge Rule"; // Drop down xlsWorksheet.Cells[1, 13] = "Split Rule"; // Drop down xlsWorksheet.Cells[1, 14] = "Sort Order"; // row 2+ indicators int xlsRowCount = 2; foreach (var ind in indicatorManagerOptions.SelectedIndicators) { xlsWorksheet.Cells[xlsRowCount, 1] = ind.ID; xlsWorksheet.Cells[xlsRowCount, 2] = (int)indicatorManagerOptions.EntityType; xlsWorksheet.Cells[xlsRowCount, 3] = indicatorManagerOptions.EntityType.ToString(); importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(4), xlsRowCount, "", "", indicatorManagerOptions.FormTypes, System.Threading.Thread.CurrentThread.CurrentCulture); xlsWorksheet.Cells[xlsRowCount, 4] = ind.FormName; var english = new TranslationLookupInstance(new CultureInfo("en-US")); xlsWorksheet.Cells[xlsRowCount, 5] = english.GetValue(ind.Key, ind.Key); var french = new TranslationLookupInstance(new CultureInfo("fr-FR")); xlsWorksheet.Cells[xlsRowCount, 6] = french.GetValue(ind.Key, ind.Key); var port = new TranslationLookupInstance(new CultureInfo("pt-PT")); xlsWorksheet.Cells[xlsRowCount, 7] = port.GetValue(ind.Key, ind.Key); var bahasa = new TranslationLookupInstance(new CultureInfo("id-ID")); xlsWorksheet.Cells[xlsRowCount, 8] = bahasa.GetValue(ind.Key, ind.Key); importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(9), xlsRowCount, "", "", Enum.GetNames(typeof(IndicatorDataType)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture); xlsWorksheet.Cells[xlsRowCount, 9] = ((IndicatorDataType)ind.DataTypeId).ToString(); xlsWorksheet.Cells[xlsRowCount, 10] = ind.IsRequired.ToString(); importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(11), xlsRowCount, "", "", Enum.GetNames(typeof(IndicatorAggType)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture); xlsWorksheet.Cells[xlsRowCount, 11] = ((IndicatorAggType)ind.AggregationRuleId).ToString(); importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(12), xlsRowCount, "", "", Enum.GetNames(typeof(MergingRule)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture); xlsWorksheet.Cells[xlsRowCount, 12] = ((MergingRule)ind.MergeRule).ToString(); importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(13), xlsRowCount, "", "", Enum.GetNames(typeof(RedistrictingRule)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture); xlsWorksheet.Cells[xlsRowCount, 13] = ((RedistrictingRule)ind.SplitRule).ToString(); xlsWorksheet.Cells[xlsRowCount, 14] = ind.SortOrder; xlsRowCount++; } // Auto fit var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); var range = xlsWorksheet.get_Range("A1", last); range.Columns.AutoFit(); // Save and display xlsApp.DisplayAlerts = false; xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing, oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, oMissing, oMissing, oMissing); xlsApp.Visible = true; Marshal.ReleaseComObject(xlsWorksheets); Marshal.ReleaseComObject(xlsWorksheet); Marshal.ReleaseComObject(xlsValidation); Marshal.ReleaseComObject(xlsWorkbooks); Marshal.ReleaseComObject(xlsWorkbook); Marshal.ReleaseComObject(xlsApp); }
public override bool Excel2HTML(string sourcePath, string targetPath, string targetRelativeDirectory) { Microsoft.Office.Interop.Excel.XlFileFormat targetType = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml; object missing = Type.Missing; Microsoft.Office.Interop.Excel.ApplicationClass excelApp = null; Microsoft.Office.Interop.Excel.Workbook workbook = null; try { if (!sourcePath.EndsWith(".xls") && !sourcePath.EndsWith(".xlsx")) { return(false); } if (File.Exists(targetPath)) { File.Delete(targetPath); } excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (excelApp == null) { Log.LogInfo("无法创建excel对象,可能您的电脑未安装excel"); return(false); } Log.DebugInfo("excel srcPath: " + sourcePath); Log.DebugInfo("excel targetPath: " + targetPath); object target = targetPath; object type = targetType; Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks; workbook = workbooks.Open(sourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); workbook.SaveAs(target, type, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } catch (Exception e) { Log.LogInfo("excel转换html:", e); return(false); } finally { if (workbook != null) { workbook.Close(false, missing, missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; GC.Collect(); } if (excelApp != null) { excelApp.Quit(); excelApp = null; } GC.Collect(); GC.WaitForPendingFinalizers(); //修改html文件中的src元素的路径 changeHref(targetPath, targetRelativeDirectory); } return(true); //throw new NotImplementedException(); }
/// <summary> /// 创建一个空的Excel文件(2003版的Excel) /// </summary> /// <param name="path"></param> /// <returns></returns> public static string CreateBlankExcel(string path) { Microsoft.Office.Interop.Excel.Application excelApp; Microsoft.Office.Interop.Excel.Workbook excelDoc; DateTime excelBeginCreatingTime = DateTime.Now; excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); DateTime excelAfterCreatingTime = DateTime.Now; if (File.Exists(path)) { File.Delete(path); } Object nothing = Missing.Value; excelDoc = excelApp.Workbooks.Add(nothing); Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal; excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing); excelDoc.Close(nothing, nothing, nothing); excelApp.Quit(); // 关闭进程 foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) { DateTime proStartCreatingTime = proc.StartTime; if ((proStartCreatingTime >= excelBeginCreatingTime) && (proStartCreatingTime <= excelAfterCreatingTime)) { proc.Kill(); } } return path; }
private void btnExport_Click(object sender, EventArgs e) { try { System.Data.DataTable ds = new System.Data.DataTable(); Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); if (chkSummery.Checked == false) { foreach (DataGridViewColumn col in dgvStockRegister.Columns) { ds.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvStockRegister.Rows) { DataRow dRow = ds.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } ds.Rows.Add(dRow); } if (cmbMainCategory.Text == "खते") { ds.Columns[3].ColumnName = "कोणाकडून आले व कंपनी"; ds.Columns[5].ColumnName = "दिवसातील आवक"; ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक"); ds.Columns.Remove("बियाणाचे नाव"); ds.Columns.Remove("बियाणाचा प्रकार"); ds.TableName = "खते रजिस्टर"; } if (cmbMainCategory.Text == "किटकनाशके") { ds.Columns[3].ColumnName = "किटकनाशक कोणाकडून खरेदी केले त्याचे नाव व पावती क्र. व दिनांक"; ds.Columns[5].ColumnName = "खरेदी केलेला साठा"; ds.Columns.Remove("बियाणाचे नाव"); ds.Columns.Remove("बियाणाचा प्रकार"); ds.TableName = "किटकनाशके रजिस्टर"; } if (cmbMainCategory.Text == "बियाणे") { ds.Columns[3].ColumnName = "कोणाकडून आले"; ds.Columns[5].ColumnName = "किती आले"; ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक"); ds.TableName = "बियाणे रजिस्टर"; } } else { foreach (DataGridViewColumn col in dgvSummerysales.Columns) { ds.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvSummerysales.Rows) { DataRow dRow = ds.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } ds.Rows.Add(dRow); } ds.TableName = "विक्री रजिस्टर"; } xlWorksheet.Name = ds.TableName; for (int j = 1; j < ds.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < ds.Rows.Count; k++) { for (int l = 0; l < ds.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = ds.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ac) { MessageBox.Show(ac.Message); } }
private void buttonOst_Click(object sender, EventArgs e) { //массив0 выдано refreshOst(); int[,] arr = new int[listViewBilety2.Items.Count, listViewBilety2.Columns.Count]; for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++) { arr[x, y] = int.Parse(listViewBilety2.Items[x].SubItems[y].Text.Trim()); } } //массив1 сдано refreshOst1(); int[,] arr1 = new int[listViewBilety2.Items.Count, listViewBilety2.Columns.Count]; for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++) { arr1[x, y] = int.Parse(listViewBilety2.Items[x].SubItems[y].Text.Trim()); } } //массив2 названия refreshOst2(); string[,] b = new string[listViewBilety2.Items.Count, listViewBilety2.Columns.Count]; for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++) { b[x, y] = listViewBilety2.Items[x].SubItems[y].Text; } } //refreshBilety2(); listViewBilety2.Items.Clear(); listViewBilety2.Columns.Clear(); List<int> ARR = new List<int>(); List<int> ARR1 = new List<int>(); List<int> ARR2 = new List<int>(); List<int> ARR3 = new List<int>(); List<int> unique = new List<int>(); List<object> unique2 = new List<object>(); //диапазоны из нач/кон значений выдано for (int x = 0; x <= ((arr.Length / 2) - 1); x++) { for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++) { ARR.Add(i); } } //диапазоны из нач/кон значений сдано for (int x = 0; x <= ((arr1.Length / 2) - 1); x++) { for (int i = arr1[x, 0], maxi = arr1[x, 1]; i <= maxi; i++) { ARR1.Add(i); } } //удаление того, что есть в журнале, но нет в билетах for (int x = 0; x < ARR.Count; x++) { for (int y = 0; y < ARR1.Count; y++) { if (ARR[x].Equals(ARR1[y])) ARR3.Add(ARR[x]); } } //объединение списков ARR2.AddRange(ARR.ToArray()); ARR2.AddRange(ARR3.ToArray()); ARR2.Sort(); //удаление двойных for (int m = 0; m < ARR2.Count; m++) { for (int j = m + 1; j < ARR2.Count; j++) { if (ARR2[j].Equals(ARR2[m])) { ARR2.RemoveAt(j--); ARR2.RemoveAt(m); } } } //приведение к начальным for (int x = 0; x <= ((arr.Length / 2) - 1); x++) { for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++) { if (ARR2.Contains(i)) { unique.Add(x + 1); unique.Add(i); } } } //собственно диапазоны остатка for (int x = 0; x < unique.Count; x = x + 2) { int m; if (x == 0) m = x + 1; else m = x - 2; if (!unique[x].Equals(unique[m])) { unique2.Add(unique[x]); unique2.Add(unique[x + 1]); } int f; if (x < unique.Count - 2) f = x + 2; else f = x - 1; if (!unique[x].Equals(unique[f])) { unique2.Add(unique[x + 1]); for (int y = 0; y < b.Length / 3; y++) { if (unique[x + 1].Equals(int.Parse(b[y, 0]))) { unique2.Add(b[y, 1]); unique2.Add(b[y, 2]); } } } } //массив для екселя object[,] c = new object[unique2.Count, unique2.Count / 3]; for (int i = 0, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 0] = unique2[i]; } for (int i = 1, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 1] = unique2[i]; } for (int i = 2, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 2] = unique2[i]; } for (int i = 3, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 3] = unique2[i]; } for (int i = 4, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 4] = unique2[i]; } object[] a = unique2.ToArray(); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); string workbookPath = Application.StartupPath + "\\templates/bilety.xls"; Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; string currentSheet = "Бланк"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A4", "E" + unique2.Count); excelCellName.Value2 = c; }
protected void m_exportButton_Click(object sender, EventArgs e) { //取得所有学生的数据 string commandText = "select * from Stu"; System.Data.DataTable dt = m_sqlHelper.Query(commandText); Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel._Workbook xBk; Microsoft.Office.Interop.Excel._Worksheet xSt; xBk = excel.Workbooks.Add(true); xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet; int i = 1; foreach (DataColumn col in dt.Columns) { xSt.Cells[1, i] = col.ColumnName.ToString(); i++; } int dataRow = 2; for (int j = 0; j < dt.Rows.Count; j++) { for (int k = 0; k < dt.Columns.Count; k++) { xSt.Cells[dataRow, k + 1] = dt.Rows[j][k].ToString(); } dataRow++; } xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "Stu2014.xls"); dt = null; xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); string path = Server.MapPath("Stu2014.xls"); System.IO.FileInfo file = new System.IO.FileInfo(path); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息,为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); // 添加头信息,指定文件大小,让浏览器能够显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流,必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(file.FullName); // 停止页面的执行 Response.End(); }
/// <summary> /// Click event to handle the export to excel /// </summary> /// <param name="sender">Sender object</param> /// <param name="e">event data</param> private void Export_Click(object sender, EventArgs e) { try { var table = (DataTable)dataGridViewMembers.DataSource; Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); excel.Application.Workbooks.Add(true); int columnIndex = 0; foreach (DataColumn col in table.Columns) { columnIndex++; excel.Cells[1, columnIndex] = col.ColumnName; } int rowIndex = 0; foreach (DataRow row in table.Rows) { rowIndex++; columnIndex = 0; foreach (DataColumn col in table.Columns) { columnIndex++; if (columnIndex == 4 || columnIndex == 5 || columnIndex == 6) { if (columnIndex == 4) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(Occupation), row[col.ColumnName]); } if (columnIndex == 5) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(MaritalStatus), row[col.ColumnName]); } if (columnIndex == 6) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(HealthStatus), row[col.ColumnName]); } } else { excel.Cells[rowIndex + 1, columnIndex] = row[col.ColumnName].ToString(); } } } excel.Visible = true; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet; worksheet.Activate(); } catch (Exception ex) { this.ShowErrorMessage(ex); } }
private void CalcData(int P_int_Row, string P_str_Column) { CloseProcess("EXCEL"); //关闭所有Excel进程 string P_str_workBook1 = Application.StartupPath + "\\Excel1.xls"; //记录第一个要计算的工作簿路径 string P_str_workBook2 = Application.StartupPath + "\\Excel2.xls"; //记录第二个要计算的工作簿路径 string P_str_workBook3 = Application.StartupPath + "\\Excel3.xls"; //记录第三个要计算的工作簿路径 string P_str_workBook4 = Application.StartupPath + "\\Sum.xls"; //记录存储计算结果的工作簿路径 object missing = System.Reflection.Missing.Value; //定义object缺省值 Microsoft.Office.Interop.Excel.ApplicationClass excel1 = new Microsoft.Office.Interop.Excel.ApplicationClass(); //实例化Excel对象 excel1.Visible = false; //设置Excel文件隐藏 //打开第一个计算的工作簿 Microsoft.Office.Interop.Excel.Workbook workbook1 = excel1.Workbooks.Open(P_str_workBook1, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //记录要计算的第一个工作表 Microsoft.Office.Interop.Excel._Worksheet worksheet1 = (Microsoft.Office.Interop.Excel._Worksheet)workbook1.Worksheets.get_Item(tscbox_Sheet.Text); Microsoft.Office.Interop.Excel.ApplicationClass excel2 = new Microsoft.Office.Interop.Excel.ApplicationClass(); //实例化Excel对象 excel2.Visible = false; //设置Excel文件隐藏 //打开第二个计算的工作簿 Microsoft.Office.Interop.Excel.Workbook workbook2 = excel2.Workbooks.Open(P_str_workBook2, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //记录要计算的第二个工作表 Microsoft.Office.Interop.Excel._Worksheet worksheet2 = (Microsoft.Office.Interop.Excel._Worksheet)workbook2.Worksheets.get_Item(tscbox_Sheet.Text); Microsoft.Office.Interop.Excel.ApplicationClass excel3 = new Microsoft.Office.Interop.Excel.ApplicationClass(); //实例化Excel对象 excel3.Visible = false; //设置Excel文件隐藏 //打开第三个计算的工作簿 Microsoft.Office.Interop.Excel.Workbook workbook3 = excel3.Workbooks.Open(P_str_workBook3, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //记录要计算的第三个工作表 Microsoft.Office.Interop.Excel._Worksheet worksheet3 = (Microsoft.Office.Interop.Excel._Worksheet)workbook3.Worksheets.get_Item(tscbox_Sheet.Text); Microsoft.Office.Interop.Excel.ApplicationClass excel4 = new Microsoft.Office.Interop.Excel.ApplicationClass(); //实例化Excel对象 excel4.Visible = false; //设置Excel文件隐藏 //打开存储计算结果的工作簿 Microsoft.Office.Interop.Excel.Workbook workbook4 = excel4.Workbooks.Open(P_str_workBook4, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //记录要存储结算结果的工作表 Microsoft.Office.Interop.Excel._Worksheet worksheet4 = (Microsoft.Office.Interop.Excel._Worksheet)workbook4.Worksheets.get_Item(tscbox_Sheet.Text); excel4.DisplayAlerts = false; //设置保存Excel时不显示对话框 worksheet4.Activate(); //激活工作表 Decimal P_dml_NumOne = 0; //获取第一个工作表的值 Decimal P_dml_NumTwo = 0; //获取第二个工作表的值 Decimal P_dml_NumThree = 0; //获取第三个工作表的值 //判断指定单元格内容是否为空 if (((Microsoft.Office.Interop.Excel.Range)worksheet1.Cells[P_int_Row, P_str_Column]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)worksheet1.Cells[P_int_Row, P_str_Column]).Text == null) { P_dml_NumOne = 0;//将第一个值设置为0 } else { P_dml_NumOne = Convert.ToDecimal(((Microsoft.Office.Interop.Excel.Range)worksheet1.Cells[P_int_Row, P_str_Column]).Text);//记录第一个值 } //判断指定单元格内容是否为空 if (((Microsoft.Office.Interop.Excel.Range)worksheet2.Cells[P_int_Row, P_str_Column]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)worksheet2.Cells[P_int_Row, P_str_Column]).Text == null) { P_dml_NumTwo = 0;//将第二个值设置为0 } else { P_dml_NumTwo = Convert.ToDecimal(((Microsoft.Office.Interop.Excel.Range)worksheet2.Cells[P_int_Row, P_str_Column]).Text);//记录第二个值 } //判断指定单元格内容是否为空 if (((Microsoft.Office.Interop.Excel.Range)worksheet3.Cells[P_int_Row, P_str_Column]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)worksheet3.Cells[P_int_Row, P_str_Column]).Text == null) { P_dml_NumThree = 0;//将第三个值设置为0 } else { P_dml_NumThree = Convert.ToDecimal(((Microsoft.Office.Interop.Excel.Range)worksheet3.Cells[P_int_Row, P_str_Column]).Text); //记录第三个值 } Decimal P_dml_Sum = P_dml_NumOne + P_dml_NumTwo + P_dml_NumThree; //计算总和 try { worksheet4.Cells[P_int_Row, P_str_Column] = P_dml_Sum.ToString(); //向工作簿的指定单元格中写入计算后的数据 workbook4.Save(); //保存Excel文件 } catch { MessageBox.Show("写入第" + P_int_Row.ToString() + "行,第" + P_str_Column + "列时出错!"); } finally { workbook4.Save();//保存Excel文件 //退出打开的4个Excel文件 excel1.Quit(); excel2.Quit(); excel3.Quit(); excel4.Quit(); } WBrowser_Excel.Navigate(P_str_workBook4);//在窗体中显示多表计算后的Excel文件 }
public void Export(DataTable dt, string sheetName, string title) { //Tạo các đối tượng Excel //Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); object oBooks = oExcel.Workbooks; Microsoft.Office.Interop.Excel.Sheets oSheets; Microsoft.Office.Interop.Excel.Workbook oBook; Microsoft.Office.Interop.Excel.Worksheet oSheet; //Tạo mới một Excel WorkBook //System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //oExcel.Workbooks.Add(); //System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; oExcel.Visible = true; oExcel.DisplayAlerts = false; oExcel.Application.SheetsInNewWorkbook = 1; oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing)); oSheets = oBook.Worksheets; oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1); oSheet.Name = sheetName; //// Tạo phần đầu nếu muốn Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "H1"); head.MergeCells = true; head.Value2 = title; head.Font.Bold = true; head.Font.Name = "Tahoma"; head.Font.Size = "18"; head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo tiêu đề cột Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3"); cl1.Value2 = "Tên Môn Học"; cl1.ColumnWidth = 18; Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3"); cl2.Value2 = "Số Tín Chỉ"; cl2.ColumnWidth = 10; Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3"); cl3.Value2 = "Họ Tên"; cl3.ColumnWidth = 20; Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3"); cl4.Value2 = "Điểm Lần I"; cl4.ColumnWidth = 10; Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3"); cl5.Value2 = "Điểm Lần II"; cl5.ColumnWidth = 10; Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3"); cl6.Value2 = "Lần Thi"; cl6.ColumnWidth = 8; Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3"); cl7.Value2 = "Ngày Thi"; cl7.ColumnWidth = 13; Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3"); cl8.Value2 = "Ghi Chú"; cl8.ColumnWidth = 10; Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "H3"); rowHead.Font.Bold = true; // Kẻ viền rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Thiết lập màu nền rowHead.Interior.ColorIndex = 15; rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable, // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần. object[,] arr = new object[dt.Rows.Count, dt.Columns.Count]; //Chuyển dữ liệu từ DataTable vào mảng đối tượng for (int r = 0; r < dt.Rows.Count; r++) { DataRow dr = dt.Rows[r]; for (int c = 0; c < dt.Columns.Count; c++) { arr[r, c] = dr[c]; } } //Thiết lập vùng điền dữ liệu int rowStart = 4; int columnStart = 1; int rowEnd = rowStart + dt.Rows.Count - 1; int columnEnd = dt.Columns.Count; // Ô bắt đầu điền dữ liệu Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart]; // Ô kết thúc điền dữ liệu Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd]; // Lấy về vùng điền dữ liệu Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2); //Điền dữ liệu vào vùng đã thiết lập range.Value2 = arr; // Kẻ viền range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Căn giữa cột STT Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart]; Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3); oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; }
private void buttonZP_Click(object sender, EventArgs e) { refreshReports(); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); //копировать шаблон //System.IO.File.Copy(Application.StartupPath + "\\template.xls", Application.StartupPath + "\\template2.xls", true); string workbookPath = Application.StartupPath + "\\templates/template.xls"; Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); // параметры опена FileName, UpdateLinks,ReadOnly, Format, Password,WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, // Editable, Notify, Converter, AddToMRU Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; // бланк для редактирования string currentSheet = "Бланк"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); //Имя Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E9", "E9"); excelCellName.Value2 = comboBoxEkskursovod.Text; //Даты отчета Microsoft.Office.Interop.Excel.Range excelCellDate1 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("C8", "C8"); excelCellDate1.Value2 = dateTimePicker1.Text; Microsoft.Office.Interop.Excel.Range excelCellDate2 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F8", "F8"); excelCellDate2.Value2 = dateTimePicker2.Text; //Категория Microsoft.Office.Interop.Excel.Range excelCellCat = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("D11", "D11"); excelCellCat.Value2 = getCategory(); //зп Microsoft.Office.Interop.Excel.Range excelCellZP = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A14", "G46"); Microsoft.Office.Interop.Excel.Range excelCellZP1 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("J14", "P46"); Microsoft.Office.Interop.Excel.Range excelCellZP2 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("S14", "Y46"); Microsoft.Office.Interop.Excel.Range excelCellZP3 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("AB14", "AH46"); object[,] obj = new object[listViewReports.Items.Count, listViewReports.Columns.Count]; for (int x = 0, maxX = listViewReports.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewReports.Columns.Count; y < maxY; y++) { obj[x, y] = listViewReports.Items[x].SubItems[y].Text; } if (listViewReports.Items.Count > 99) { object[,] obj0 = new object[33, 7]; object[,] obj1 = new object[33, 7]; object[,] obj2 = new object[33, 7]; object[,] obj3 = new object[33, 7]; Array.Copy(obj, 0, obj0, 0, 231); Array.Copy(obj, 231, obj1, 0, 231); Array.Copy(obj, 462, obj2, 0, 231); Array.Copy(obj, 693, obj3, 0, ((listViewReports.Items.Count * 7) - 693)); excelCellZP.Value2 = obj0; excelCellZP1.Value2 = obj1; excelCellZP2.Value2 = obj2; excelCellZP3.Value2 = obj3; } else if (listViewReports.Items.Count > 66) { object[,] obj0 = new object[33, 7]; object[,] obj1 = new object[33, 7]; object[,] obj2 = new object[33, 7]; Array.Copy(obj, 0, obj0, 0, 231); Array.Copy(obj, 231, obj1, 0, 231); Array.Copy(obj, 462, obj2, 0, ((listViewReports.Items.Count * 7) - 462)); excelCellZP.Value2 = obj0; excelCellZP1.Value2 = obj1; excelCellZP2.Value2 = obj2; } else if (listViewReports.Items.Count > 33) { object[,] obj0 = new object[33, 7]; object[,] obj1 = new object[33, 7]; Array.Copy(obj, 0, obj0, 0, 231); Array.Copy(obj, 231, obj1, 0, ((listViewReports.Items.Count * 7) - 231)); excelCellZP.Value2 = obj0; excelCellZP1.Value2 = obj1; } else { object[,] obj0 = new object[33, 7]; Array.Copy(obj, 0, obj0, 0, (listViewReports.Items.Count * 7)); excelCellZP.Value2 = obj0; } } }
public List<DataTable> Import(string path) { List<DataTable> foundDataTables = new List<DataTable>(); Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass(); 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.Sheets sheets = workBook.Worksheets; foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) { DataTable dt = new DataTable(sheet.Name); if (sheet.Name.Contains("Operation")) { int index = 0; object rowIndex = 2; dt.Columns.Add("Step"); dt.Columns.Add("Comment"); dt.Columns.Add("Operation"); DataRow row; while (((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 1]).Value2 != null) { rowIndex = 2 + index; row = dt.NewRow(); row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 1]).Value2); row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 2]).Value2); row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 3]).Value2); index++; dt.Rows.Add(row); } } else if (sheet.Name.Contains("Linked")) { int index = 0; object rowIndex = 2; dt.Columns.Add("FileLink"); dt.Columns.Add("RelativePath"); DataRow row; while (((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 1]).Value2 != null) { rowIndex = 2 + index; row = dt.NewRow(); row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 1]).Value2); row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 2]).Value2); index++; dt.Rows.Add(row); } } foundDataTables.Add(dt); } app.Workbooks.Close(); return foundDataTables; }
private void buttonExcel_Click(object sender, EventArgs e) { if (listViewReports.Items.Count == 0) { MessageBox.Show("Нет элементов для экспорта в excel", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); excelApp.Application.Workbooks.Add(Type.Missing); excelApp.Columns.ColumnWidth = 15; int i = 1; int i2 = 4; int i3 = 1; foreach (ColumnHeader col in listViewReports.Columns) { excelApp.Cells[i2-1, i3] = col.Text; i3++; } foreach (ListViewItem lvi in listViewReports.Items) { i = 1; foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems) { excelApp.Cells[i2, i] = lvs.Text; i++; } i2++; } }
//将listview中的数据导出到excel中 private void DoExport(ListView listView, string strFileName) { int rowNum = listView.Items.Count;//行数 int columnNum = listView.Items[0].SubItems.Count;//列数 int rowIndex = 1; int columnIndex = 0; if (rowNum == 0 || string.IsNullOrEmpty(strFileName)) { return; } if (rowNum > 0) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); object missing = System.Reflection.Missing.Value;//作为缺省值参数传给word或excel对象的某个函数 Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];//取得sheet1 Microsoft.Office.Interop.Excel.Range range = null; if (xlApp == null) { MessageBox.Show("无法创建excel对象,可能您的系统没有安装excel"); return; } xlApp.DefaultFilePath = ""; xlApp.DisplayAlerts = true; //将ListView的列名导入Excel表第一行 foreach (ColumnHeader dc in listView.Columns) { columnIndex++; xlSheet.Cells[rowIndex, columnIndex] = dc.Text; } //将ListView中的数据导入Excel中 for (int i = 0; i < rowNum; i++) { rowIndex++; columnIndex = 0; for (int j = 0; j < columnNum; j++) { columnIndex++; //注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。 xlSheet.Cells[rowIndex, columnIndex] = Convert.ToString(listView.Items[i].SubItems[j].Text) + "\t"; range = xlSheet.get_Range(xlSheet.Cells[rowIndex, columnIndex], xlSheet.Cells[rowIndex, columnIndex]);//表示获取要设置的单元格或单元格范围 range.Columns.AutoFit(); // 设置列宽为自动适应 } } xlSheet.SaveAs(strFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing); MessageBox.Show("日志导出成功"); xlApp.Quit(); } }
/// <summary> /// datagridview导出Excel, /// </summary> /// <param name="fname">保存的文件名</param> /// <param name="strCaption">标题行</param> /// <param name="myDGV">datagridview</param> /// <returns></returns> public static string ExportExcel(string fname, string strCaption, DataGridView myDGV) { string result = ""; // 列索引,行索引,总列数,总行数 int ColIndex = 0; int RowIndex = 0; int ColCount = myDGV.ColumnCount; int RowCount = myDGV.RowCount + 1; if (myDGV.RowCount == 0) { result = "没有数据可供导出"; } // 创建Excel对象 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { result = "创建Excel文件失败"; } try { // 创建Excel工作薄 Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1]; // 设置标题 Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //标题所占的单元格数与DataGridView中的列数相同 range.MergeCells = true; xlApp.ActiveCell.FormulaR1C1 = strCaption; xlApp.ActiveCell.Font.Size = 20; xlApp.ActiveCell.Font.Bold = true; xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; // 创建缓存数据 object[,] objData = new object[RowCount + 1, ColCount]; //获取列标题 foreach (DataGridViewColumn col in myDGV.Columns) { objData[RowIndex, ColIndex++] = col.HeaderText; } // 获取数据 for (RowIndex = 1; RowIndex < RowCount; RowIndex++) { for (ColIndex = 0; ColIndex < ColCount; ColIndex++) { if (myDGV[ColIndex, RowIndex - 1].ValueType == typeof(string) || myDGV[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))//这里就是验证DataGridView单元格中的类型,如果是string或是DataTime类型,则在放入缓存时在该内容前加入" "; { objData[RowIndex, ColIndex] = "'" + myDGV[ColIndex, RowIndex - 1].Value; } else { objData[RowIndex, ColIndex] = myDGV[ColIndex, RowIndex - 1].Value; } } System.Windows.Forms.Application.DoEvents(); } // 写入Excel range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount, ColCount]); range.Value2 = objData; //保存 xlBook.Saved = true; xlBook.SaveCopyAs(fname); //返回值 result = "成功导出,保存在c:\\"; } catch (Exception) { result = "出现错误"; } finally { xlApp.Quit(); GC.Collect(); //强制回收 } return result; }
public static Workbook Open(string excelFilename) { Workbook wb = new Workbook(); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(excelFilename, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); // Loop throught Worksheets Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = null; for(int w = 1; w <= excelWorkbook.Worksheets.Count; ++w) { excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Worksheets[w]; Worksheet ws = new Worksheet(); ws.Name = excelWorksheet.Name; Microsoft.Office.Interop.Excel.Range usedRange = excelWorksheet.UsedRange; // convert the range to a System.Array System.Array array = (System.Array)usedRange.Cells.Value2; System.Diagnostics.Debug.WriteLine("Worksheet of name " + ws.Name); if (array != null) { System.Diagnostics.Debug.WriteLine("Rows range from " + array.GetLowerBound(0) + " to " + array.GetUpperBound(0)); System.Diagnostics.Debug.WriteLine("Columns range from " + array.GetLowerBound(1) + " to " + array.GetUpperBound(1)); for (int i = array.GetLowerBound(0); i <= array.GetUpperBound(0); ++i) { for (int j = array.GetLowerBound(1); j <= array.GetUpperBound(1); ++j) { if (array.GetValue(i, j) == null) { //System.Diagnostics.Debug.WriteLine("cell (" + i + "," + j + ") = null"); } else { //System.Diagnostics.Debug.WriteLine("cell (" + i + "," + j + ") = " + array.GetValue(i, j).ToString()); } // Note: Excel uses 1 based indices if (array.GetValue(i, j) != null) { string sText = array.GetValue(i, j).ToString(); ws.SetText(i - 1, j - 1, sText); } } } wb.AddWorkSheet(ws); } } // Clean up excelWorkbook.Close(false, null, null); excelApp.Workbooks.Close(); excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); excelWorksheet = null; excelWorkbook = null; if (excelApp != null) { System.Diagnostics.Process[] pProcess; pProcess = System.Diagnostics.Process.GetProcessesByName("Excel"); pProcess[0].Kill(); } excelApp = null; GC.Collect(); return wb; }
//private void subtot() //{ // try // { // decimal TotalCredit = 0; // for (int i = 0; i < dgvAccounting.Rows.Count - 1; ++i) // { // if (dgvAccounting.Rows[i].Cells[5].Value != null) // { // TotalCredit += Convert.ToDecimal(dgvAccounting.Rows[i].Cells[5].Value); // } // } // dgvAccounting[5, dgvAccounting.Rows.Count - 1].Value = TotalCredit; // // txtTotaldrAmount.Text = TotalCredit.ToString(); // decimal DueAmt = 0; // } // catch(Exception ex) // { // throw ex; // } //} private void btneExcelReport_Click(object sender, EventArgs e) { try { if (dgvAccounting.Rows.Count > 0) { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); DataTable dtAccount = new DataTable(); foreach (DataGridViewColumn col in dgvAccounting.Columns) { dtAccount.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvAccounting.Rows) { DataRow dRow = dtAccount.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dtAccount.Rows.Add(dRow); } if (Utility.Langn == "English") { dtAccount.TableName = "Expenses Report"; } else { dtAccount.TableName = "खर्च रिपोर्ट(Expenses Report)"; } xlWorksheet.Name = dtAccount.TableName; for (int j = 1; j < dtAccount.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = dtAccount.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < dtAccount.Rows.Count; k++) { for (int l = 0; l < dtAccount.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = dtAccount.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public virtual void CreateImportFile(string filename, List<AdminLevel> adminLevels, AdminLevelType adminLevelType, ImportOptions opts) { options = opts; ReloadDropdownValues(); LoadRelatedLists(); System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlsWorkbook; Microsoft.Office.Interop.Excel.Workbooks xlsWorkbooks; Microsoft.Office.Interop.Excel.Sheets xlsWorksheets; Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet; Microsoft.Office.Interop.Excel.Worksheet xlsValidation; object oMissing = System.Reflection.Missing.Value; validationRanges = new Dictionary<string, string>(); //Create new workbook xlsWorkbooks = xlsApp.Workbooks; xlsWorkbook = xlsWorkbooks.Add(true); xlsWorksheets = xlsWorkbook.Worksheets; //Get the first worksheet xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]); // add hidden validation worksheet xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing); xlsValidation.Name = validationSheetName; xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden; // row 1 column headers DemoRepository repo = new DemoRepository(); List<string> names = adminLevelType == null? new List<string>() : repo.GetAdminLevelTypeNames(adminLevelType.Id); if (options.SurveyNames.Count > 0) // Multiple admin units for surveys xlsWorksheet.Cells[1, 1] = "* " + TranslationLookup.GetValue("SurveyName"); else xlsWorksheet.Cells[1, 1] = "* " + TranslationLookup.GetValue("ID"); for (int i = 0; i < names.Count; i++) xlsWorksheet.Cells[1, 2 + i] = "* " + names[i]; int locationCount = names.Count + 1; int xlsColCount = names.Count + 1; // add one for id; xlsColCount = AddTypeSpecific(xlsWorksheet, xlsColCount); int colCountAfterStatic = xlsColCount; foreach (var item in Indicators) { if (item.Value.DataTypeId == (int)IndicatorDataType.SentinelSite || item.Value.IsCalculated || item.Value.IsMetaData) continue; string isReq = ""; if (item.Value.IsRequired) isReq = "* "; // if the filtered list still has more than 7 possible multiselect values, do some weird shit. if (options.IndicatorValuesSublist.ContainsKey(item.Value.DisplayName) && options.IndicatorValuesSublist[item.Value.DisplayName].Count > 6) { int optionNumber = 1; foreach (string opt in options.IndicatorValuesSublist[item.Value.DisplayName]) { xlsColCount++; xlsWorksheet.Cells[1, xlsColCount] = isReq + TranslationLookup.GetValue(item.Key, item.Key) + Translations.ImportSelectionOption + optionNumber; optionNumber++; } } else { xlsColCount++; xlsWorksheet.Cells[1, xlsColCount] = isReq + TranslationLookup.GetValue(item.Key, item.Key); } } //xlsWorksheet.Cells[1, xlsColCount + 1] = TranslationLookup.GetValue("Notes"); // row 2+ admin levels int xlsRowCount = 2; if (options.SurveyNames.Count > 0) // Multiple admin units for surveys { foreach (var survey in options.SurveyNames.OrderBy(a => a.DisplayName)) { xlsWorksheet.Cells[xlsRowCount, 1] = survey.DisplayName; AddTypeSpecificLists(xlsWorksheet, xlsValidation, 0, xlsRowCount, oldCI, locationCount); xlsRowCount = AddIndicatorColumns(oldCI, xlsWorksheet, xlsValidation, colCountAfterStatic, xlsRowCount); } } else { foreach (AdminLevel l in adminLevels.OrderBy(a => a.SortOrder)) { xlsWorksheet.Cells[xlsRowCount, 1] = l.Id; List<AdminLevel> parents = repo.GetAdminLevelParentNames(l.Id); int aCol = 2; foreach (AdminLevel adminlevel in parents) { xlsWorksheet.Cells[xlsRowCount, aCol] = adminlevel.Name; aCol++; } AddTypeSpecificLists(xlsWorksheet, xlsValidation, l.Id, xlsRowCount, oldCI, locationCount); xlsRowCount = AddIndicatorColumns(oldCI, xlsWorksheet, xlsValidation, colCountAfterStatic, xlsRowCount); } } // Auto fit var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); var range = xlsWorksheet.get_Range("A1", last); range.Columns.AutoFit(); // Save and display xlsApp.DisplayAlerts = false; xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing, oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, oMissing, oMissing, oMissing); xlsApp.Visible = true; Marshal.ReleaseComObject(xlsWorksheets); Marshal.ReleaseComObject(xlsWorksheet); Marshal.ReleaseComObject(xlsValidation); Marshal.ReleaseComObject(xlsWorkbooks); Marshal.ReleaseComObject(xlsWorkbook); Marshal.ReleaseComObject(xlsApp); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; }
private void buttonOst_Click(object sender, EventArgs e) { //массив0 квитанции refreshOst(); int[,] arr = new int [listViewKvit.Items.Count, listViewKvit.Columns.Count]; for (int x = 0, maxX = listViewKvit.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewKvit.Columns.Count; y < maxY; y++) { arr[x, y] = int.Parse(listViewKvit.Items[x].SubItems[y].Text.Trim()); } } //массив1 журнал refreshOst1(); int[] arr1 = new int[listViewKvit.Items.Count]; for (int x = 0, maxX = listViewKvit.Items.Count; x < maxX; x++) { arr1[x] = int.Parse(listViewKvit.Items[x].Text.Trim()); } string[,] b = new string [10, 10]; refreshKvit(); List <int> ARR = new List <int>(); List <int> ARR1 = new List <int>(); List <int> ARR2 = new List <int>(); List <int> ARR3 = new List <int>(); List <object> unique = new List <object>(); List <object> unique2 = new List <object>(); //диапазоны из нач/кон значений квитанции for (int x = 0; x <= ((arr.Length / 2) - 1); x++) { for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++) { ARR.Add(i); } } //журнал foreach (int g in arr1) { ARR1.Add(g); } //удаление того, что есть в журнале, но нет в билетах for (int x = 0; x < ARR.Count; x++) { for (int y = 0; y < ARR1.Count; y++) { if (ARR[x].Equals(ARR1[y])) { ARR3.Add(ARR[x]); } } } //объединение списков ARR2.AddRange(ARR.ToArray()); ARR2.AddRange(ARR3.ToArray()); ARR2.Sort(); //удаление двойных for (int m = 0; m < ARR2.Count; m++) { for (int j = m + 1; j < ARR2.Count; j++) { if (ARR2[j].Equals(ARR2[m])) { ARR2.RemoveAt(j--); ARR2.RemoveAt(m); } } } //приведение к начальным for (int x = 0; x <= ((arr.Length / 2) - 1); x++) { for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++) { if (ARR2.Contains(i)) { unique.Add(x + 1); unique.Add(i); } } } //собственно диапазоны остатка for (int x = 0; x < unique.Count; x = x + 2) { int m; if (x == 0) { m = x + 1; } else { m = x - 2; } if (!unique[x].Equals(unique[m])) { unique2.Add(unique[x]); unique2.Add(unique[x + 1]); } int f; if (x < unique.Count - 2) { f = x + 2; } else { f = x - 1; } if (!unique[x].Equals(unique[f])) { unique2.Add(unique[x + 1]); /*for (int y = 0; y < b.Length/3; y++) * { * if (unique[x + 1].Equals(int.Parse(b[y, 0]))) * { * unique2.Add(b[y, 1]); * unique2.Add(b[y, 2]); * } * }*/ } } //массив для екселя object[,] c = new object[unique2.Count, 3]; for (int i = 0, k = 0; i < unique2.Count; i = i + 3, k++) { c[k, 0] = unique2[i]; } for (int i = 1, k = 0; i < unique2.Count; i = i + 3, k++) { c[k, 1] = unique2[i]; } for (int i = 2, k = 0; i < unique2.Count; i = i + 3, k++) { c[k, 2] = unique2[i]; } /*for (int i = 3, k = 0; i < unique2.Count; i = i + 5, k++) * { * c[k, 3] = unique2[i]; * } * * for (int i = 4, k = 0; i < unique2.Count; i = i + 5, k++) * { * c[k, 4] = unique2[i]; * }*/ object[] a = unique2.ToArray(); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); string workbookPath = Application.StartupPath + "\\templates/bilety.xls"; Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; string currentSheet = "Бланк"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A4", "C" + unique2.Count); excelCellName.Value2 = c; }
/// <summary> /// 创建一个空的Excel文件(2003版的Excel) /// </summary> /// <param name="path"></param> /// <returns></returns> public static string CreateBlankExcel(string path) { Microsoft.Office.Interop.Excel.Application excelApp; Microsoft.Office.Interop.Excel.Workbook excelDoc; excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (File.Exists(path)) { File.Delete(path); } Object nothing = Missing.Value; excelDoc = excelApp.Workbooks.Add(nothing); Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal; excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing); excelDoc.Close(nothing, nothing, nothing); excelApp.Quit(); return path; }
private void btnDocFile_Click(object sender, EventArgs e) { if (txtDuongDan.Text.Length > 0) { Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; Microsoft.Office.Interop.Excel.Range range; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open(txtDuongDan.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; if (range.Rows.Count > 0) { btnKiemTraDuLieu.Enabled = true; btnXoaDuLieu.Enabled = true; } // string str; int rCnt = 0; //int stt = 0; for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++) { try { if ((xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2 != null) || (xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2.ToString() == "")) { DataRow dr = dt.NewRow(); if (xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2 != null) { dr[0] = xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2.ToString(); } if (xlWorkSheet.get_Range("B" + rCnt, "B" + rCnt).Value2 != null) { dr[1] = xlWorkSheet.get_Range("B" + rCnt, "B" + rCnt).Value2.ToString(); } else { dr[1] = ""; } if (xlWorkSheet.get_Range("C" + rCnt, "C" + rCnt).Value2 != null) { dr[2] = xlWorkSheet.get_Range("C" + rCnt, "C" + rCnt).Value2.ToString(); } else { dr[2] = ""; } if (xlWorkSheet.get_Range("D" + rCnt, "D" + rCnt).Value2 != null) { // MessageBox.Show("" + xlWorkSheet.get_Range("D" + rCnt, "D" + rCnt).Value2); dr[3] = DateTime.Parse(xlWorkSheet.get_Range("D" + rCnt, "D" + rCnt).Value2.ToString()); // dr[3] = DateTime.FromOADate(d); // MessageBox.Show("" + dr[3]); } else { dr[3] = DateTime.Now; } if (xlWorkSheet.get_Range("E" + rCnt, "E" + rCnt).Value2 != null) { dr[4] = xlWorkSheet.get_Range("E" + rCnt, "E" + rCnt).Value2.ToString(); } else { dr[4] = ""; } if (xlWorkSheet.get_Range("F" + rCnt, "F" + rCnt).Value2 != null) { if (xlWorkSheet.get_Range("F" + rCnt, "F" + rCnt).Value2.ToString().Trim() == "Nam") { dr[5] = true; } else { dr[5] = false; } } else { dr[5] = true; } if (xlWorkSheet.get_Range("G" + rCnt, "G" + rCnt).Value2 != null) { dr[6] = xlWorkSheet.get_Range("G" + rCnt, "G" + rCnt).Value2.ToString(); } else { dr[6] = ""; } if (xlWorkSheet.get_Range("H" + rCnt, "H" + rCnt).Value2 != null) { dr[7] = xlWorkSheet.get_Range("H" + rCnt, "H" + rCnt).Value2.ToString(); } else { dr[7] = ""; } if (xlWorkSheet.get_Range("I" + rCnt, "I" + rCnt).Value2 != null) { dr[8] = xlWorkSheet.get_Range("I" + rCnt, "I" + rCnt).Value2.ToString(); } else { dr[8] = ""; } if (xlWorkSheet.get_Range("J" + rCnt, "J" + rCnt).Value2 != null) { dr[9] = DateTime.Parse(xlWorkSheet.get_Range("J" + rCnt, "J" + rCnt).Value2.ToString()); // dr[9] = DateTime.FromOADate(d); } else { dr[9] = DateTime.Now; } if (xlWorkSheet.get_Range("K" + rCnt, "K" + rCnt).Value2 != null) { dr[10] = xlWorkSheet.get_Range("K" + rCnt, "K" + rCnt).Value2.ToString(); } else { dr[10] = ""; } dr[11] = true; dr[12] = "0"; dt.Rows.Add(dr); } } catch { rCnt = range.Rows.Count; // thoat vong for khi ko co du lieu } } xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); gridItem.DataSource = dt; btnKiemTraDuLieu.Enabled = true; } }
private void buttonExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); excelApp.Application.Workbooks.Add(Type.Missing); excelApp.Columns.ColumnWidth = 15; excelApp.Cells[1, 1 ]= "№"; excelApp.Cells[1, 2 ]= "Дата"; excelApp.Cells[1, 3 ]= "Квитанция"; excelApp.Cells[1, 4 ]= "ФИО"; excelApp.Cells[1, 5 ]= "Город"; excelApp.Cells[1, 6 ]= "Кол-во дней"; excelApp.Cells[1, 7] = "Кол-во человек"; excelApp.Cells[1, 8] = "Житие"; excelApp.Cells[1, 9] = "Парковка"; excelApp.Cells[1, 10] = "Бронь"; excelApp.Cells[1, 11] = "Безнал"; excelApp.Cells[1, 12] = "Заказчик"; excelApp.Cells[1, 13] = "Сумма"; int i = 1; int i2 = 4; foreach (ListViewItem lvi in listViewRasselenie.Items) { i = 1; foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems) { excelApp.Cells[i2, i] = lvs.Text; i++; } i2++; } }
private void buttonExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); string workbookPath = Application.StartupPath + "\\templates/zhurnal.xls"; Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; string currentSheet = "Бланк"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); int i = 1; int i2 = 4; foreach (ListViewItem lvi in listViewZhurnal.Items) { i = 1; foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems) { excelWorksheet.Cells[i2, i] = lvs.Text; i++; } i2++; } }
public static void SaveAsExcel(string name, DataTable dt) { OleDbConnection cnnxls = new OleDbConnection(string.Format(excelstring, name)); string insertsql = ""; string insertcolumnstring = ""; string insertvaluestring = ""; string fileName = name + ".xls"; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); excel.Application.Workbooks.Add(true); int colIndex = 0; foreach (DataColumn col in dt.Columns) { colIndex++; excel.Cells[1, colIndex] = col.ColumnName; insertcolumnstring += string.Format("{0},", col.ColumnName); } excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); //������¼ //conn.execute(sql); insertcolumnstring = insertcolumnstring.Trim(','); foreach (DataRow row in dt.Rows) { foreach (DataColumn dc in dt.Columns) { row[dc].ToString(); insertvaluestring += string.Format("'{0}',", row[dc].ToString().Replace("'", "''")); } string sql = string.Format("insert into [Sheet1$]({0}) values({1})", insertcolumnstring, insertvaluestring); OleDbDataAdapter myDa = new OleDbDataAdapter(sql, cnnxls); myDa.InsertCommand.ExecuteNonQuery(); } excel.Visible = false; //excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); excel.Quit(); excel = null; GC.Collect();//�������� }
/// <summary> /// 导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExport_Click(object sender, EventArgs e) { string saveFileName = "工作难度系数"; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xlsx"; saveDialog.Filter = "Excel文件|*.xlsx"; saveDialog.FileName = saveFileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) { return; //被点了取消 } dynamic xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } ExcelHelper excel = new ExcelHelper(saveFileName);//创建excel #region 标题信息 excel.SetCells(1, 1, "编号"); excel.SetCells(1, 2, "来源"); excel.SetCells(1, 3, "名称"); excel.SetCells(1, 4, "描述"); excel.SetCells(1, 5, "开始时间"); excel.SetCells(1, 6, "结束时间"); excel.SetCells(1, 7, "预计工作量"); excel.SetCells(1, 8, "实际工作量"); excel.SetCells(1, 9, "难度系数"); excel.SetCellsBackColor(1, 1, 1, 9, ColorIndex.灰色25); //设置颜色 excel.SetCellsAlign(1, 1, 1, 9, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter); //设置对齐 #endregion #region 项目计划 List <string> columns; columns = new List <string>() { "RowNo", "source", "name", "desc", "startdate", "enddate", "workload", "actualworkload", "degree" }; Export(dt, excel, columns); #endregion #region 设置单元格格式 //先设置单元格居右 excel.SetCellsAlign(2, 1, dt.Rows.Count + 1, 9, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight); int first = 1; //foreach (DataRow item in dt.Rows) //{ // rowno++; // if (item["type"].ToString() == "-1") // { // last = rowno - 2; // if (last > 2) // excel.SetCellsBorder(first, 1, last, 8);//设置边框 // first = rowno; // excel.SetCellsStyle(rowno, 1, rowno, 8, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, true, ColorIndex.灰色25);//设置 // excel.SetCellsStyle(rowno + 1, 1, rowno + 1, 8, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, false, ColorIndex.灰色25);//设置 // } //} excel.SetCellsBorder(first, 1, dt.Rows.Count, 9); //设置边框 excel.SetCellsBorder(dt.Rows.Count, 8, dt.Rows.Count + 1, 9); //设置边框 excel.InsertRows(1, 2); excel.MergeCells(1, 1, 2, 9, project.Name + "—工作难度系数"); //项目名称 excel.SetCellsStyle(1, 1, 1, 1, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, true, ColorIndex.无色); //项目名称粗体 #endregion excel.SaveAsFile();//文件保存 MessageBox.Show("操作成功!"); }