public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath) { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (i = 0; i <= dataGridView1.RowCount - 1; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { DataGridViewCell cell = dataGridView1[j, i]; xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Your file is saved" + SaveFilePath); }
/// <exception cref="FileNotFoundException"><c>FileNotFoundException</c>.</exception> /// <exception cref="Exception"><c>Exception</c>.</exception> public IList<IDataRecord> GetDataFromExcel(string filePath) { try { Log.Info("Начало импорта"); if (!File.Exists(filePath)) throw new FileNotFoundException(string.Format("Файл не найден. [{0}]", filePath)); Excelapp = new Application { Visible = false }; Workbook = Excelapp.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ReadDoc(); } catch( Exception e) { Log.Error("Ошибка импорта", e); throw; } finally { Workbook.Close(); Excelapp.Quit(); Excelapp = null; } return DataRecords; }
public void Compare(ExcelWork firstObj, int firstRowIndex, ExcelWork secondObj, int secondRowIndex) { try { resultBook = excelApp.Workbooks.Open(SavePath); resultSheet = resultBook.Worksheets[1]; WorkBookManager(firstObj, firstRowIndex); WorkBookManager(secondObj, secondRowIndex); startColumnIndex++; resultBook.Save(); } catch (Exception ex) { /// Возврат Error Message во View /// throw new Exception(ex.Message); } finally { resultBook?.Close(); } }
public bool Copy(string where, string from, int indexSheet = 1) { Excel.Workbook paternBook = null; try { resultBook = excelApp.Workbooks.Add(); resultBook.SaveAs(SavePath); paternBook = excelApp.Workbooks.Open(where); (paternBook.Worksheets[1] as Excel.Worksheet).Copy(Before: resultBook.Worksheets[indexSheet]); resultBook.Save(); return(true); } catch (Exception) { /// Возврат Error Message во View /// return(false); } finally { resultBook?.Close(); paternBook?.Close(); } }
public (Maybe <Schedule> item, IEnumerable <Message> messages) GetSchedule(string name) { var(timetable, timetableMessages) = GetTimetable(name); if (timetableMessages.HasStoppingErrors()) { return(Maybe <Schedule> .None(string.Format(CultureInfo.CurrentCulture, Resources.Strings.CannotReadScheduleDueToErrorsInTimetable)), timetableMessages); } var messages = new List <Message>(timetableMessages); var result = new Schedule(name, timetable.Value); var app = new Excel.Application { Visible = false }; Excel.Workbook book = null; try { book = app.Workbooks.Open(GetFullFilename(name)); messages.AddRange(GetSchedules(result, book)); } finally { book?.Close(false, GetFullFilename(name)); app.Quit(); } return(Maybe <Schedule> .NoneIfError(result, messages.HasStoppingErrors()), messages); }
public void ReadProcesses() { count = 0; ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу //Читаем данные по проектам WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Processes"]; //Получаем ссылку на лист Processes List<string> row = new List<string>(); int n = 6; for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { row = new List<string>(); for (int j = 1; j < n; j++) row.Add(WorkSheetExcel.Cells[i, j].Text.ToString()); //строка массива заполняется просто суммой i и j Mas.Add(row); //строка добавляется в массив count++; } //test = WorkSheetExcel.Cells[2, 1]; WorkBookExcel.Close(false, Type.Missing, Type.Missing); ExcelApp.Quit(); GC.Collect(); }
public getData readData() { getData.dateT.Clear(); getData.prj.Clear(); getData.descr.Clear(); getData.taskTime.Clear(); notes.Clear(); ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1 //excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1 // WorkSheetExcel.Cells[i, 1].Text.ToString() != "" for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { getData.dateT.Add(WorkSheetExcel.Cells[i, 1].Text.ToString()); getData.prj.Add(WorkSheetExcel.Cells[i, 2].Text.ToString()); getData.descr.Add(WorkSheetExcel.Cells[i, 3].Text.ToString()); getData.taskTime.Add(WorkSheetExcel.Cells[i, 4].Text.ToString()); } WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя ExcelApp.Quit(); GC.Collect(); getData.dateT.Reverse(); getData.prj.Reverse(); getData.descr.Reverse(); getData.taskTime.Reverse(); return getData; }
public (Maybe <Timetable> item, IEnumerable <Message> messages) GetTimetable(string name) { var(layout, layoutMessages) = GetLayout(name); if (layoutMessages.HasStoppingErrors()) { return(Maybe <Timetable> .None(string.Format(CultureInfo.CurrentCulture, Resources.Strings.CannotReadTimetableDueToErrorsInLayout)), layoutMessages); } var result = new Timetable(name, layout.Value); var app = Excel; var messages = new List <Message>(layoutMessages); Excel.Workbook book = null; try { book = app.Workbooks.Open(GetFullFilename(name)); messages.AddRange(GetTrains(result, book)); } catch (Exception ex) { messages.Add(Message.System(ex.Message)); } finally { book?.Close(false, GetFullFilename(name)); } return(Maybe <Timetable> .NoneIfError(result, messages.HasStoppingErrors()), messages); }
public static void ConvertExcelDocumentToPdf(string srcFile, string dstFile) { Excel.Application excelApp = null; Excel.Workbook excelWorkBook = null; try { excelApp = new Excel.Application(); excelWorkBook = excelApp.Workbooks.Open( srcFile, ReadOnly: true, IgnoreReadOnlyRecommended: true, Notify: false ); excelWorkBook.ExportAsFixedFormat( Type: Excel.XlFixedFormatType.xlTypePDF, Filename: dstFile, Quality: Excel.XlFixedFormatQuality.xlQualityStandard, IncludeDocProperties: true, OpenAfterPublish: false, IgnorePrintAreas: true ); } finally { excelWorkBook?.Close(SaveChanges: false); excelApp?.Quit(); } }
protected virtual void Dispose(bool disposing) { if (IsDisposed) { return; } ExcelWorkbook?.Close(true); ExcelApplication?.Quit(); if (ExcelWorksheet != null) { Marshal.ReleaseComObject(ExcelWorksheet); } if (ExcelWorkbook != null) { Marshal.ReleaseComObject(ExcelWorkbook); } if (ExcelApplication != null) { Marshal.ReleaseComObject(ExcelApplication); } ExcelWorksheet = null; ExcelWorkbook = null; ExcelApplication = null; IsDisposed = true; }
private void WorkBookManager(ExcelWork arg, int rowIndex) { Excel.Workbook comparedBook = null; try { comparedBook = excelApp.Workbooks.Open(arg.FileName); /// Получение нужной страницы var workSheet = (Excel.Worksheet)comparedBook.Worksheets[arg.WorkSheet]; dynamic value = ReadSheet(workSheet, arg.Column); WriteToNewSheet(value, rowIndex); } catch (Exception ex) { /// Возврат Error Message во View /// throw new Exception(ex.Message); } finally { comparedBook?.Close(); } }
private Worksheet objExcelWorkSheet; //定义Workbook工作表对象 #endregion Fields #region Methods // Change the Mapping WorkFlow @2015-7-16 By CC public void EditWorkFlow(string StrTestData) { objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["WorkFlow"]; //strSheetName is the Sheet Name of Exce,if there is no name then Defult is "1" objExcelWorkSheet.Select(Type.Missing); int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //get the Row Number int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//get the column number int rowNo = 0; int ParameterRowNo = 0; int columnNo = 0; int FlagColumnNo = 0; for (int j = 1; j <= columnsint; j++) { string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, j]).Text.ToString(); if (strColumnsName == "Flag") { FlagColumnNo = j; } if (strColumnsName == "BusinessFlow") { rowNo = j; } if (strColumnsName == "ParameterValue") { ParameterRowNo = j; } } for (int i = 2; i <= rowsint; i++) { string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, FlagColumnNo]).Text.ToString(); if (strFlagValue =="Y") { int RowNO = i; objExcelWorkSheet.Cells[RowNO, FlagColumnNo] = "N"; } string strBusinessFlow = ((Range)objExcelWorkSheet.Cells[i, rowNo]).Text.ToString(); if (strBusinessFlow == "HeaderMapping" || strBusinessFlow == "TaxMapping" || strBusinessFlow == "LineItemsMapping") { columnNo = i; string strParameterValue = ((Range)objExcelWorkSheet.Cells[i, ParameterRowNo]).Text.ToString(); if (strParameterValue == "IR" || strParameterValue == "TradeBilling" || strParameterValue == "TradeCredit" || strParameterValue == "TradeDebit" || strParameterValue == "TradeReturn") { objExcelWorkSheet.Cells[i, 1] = "Y"; } } } objExcelWorkbook.Save(); objExcelWorkbook.Close(false, StrTestData, false); objExcelApp.Quit(); NAR(objExcelApp); NAR(objExcelWorkbook); NAR(objExcelWorkSheet); }
public void Save <T>(IList <T> items) where T : class { Excel.Workbook workbook = null; Excel.Worksheet worksheet; if (typeof(T).FullName == typeof(OFD).FullName) { OfdDataSave(new FileInfo(@".\data\ofd.xlsx"), items.Cast <OFD>().ToList()); } if (typeof(T).FullName == typeof(User).FullName) { UserDataSave(new FileInfo(@".\data\users.xlsx"), items.Cast <User>().ToList()); } if (typeof(T).FullName == typeof(Store).FullName) { FileInfo addressDataFile = new FileInfo(@".\data\address.xlsx"); FileInfo companyDataFile = new FileInfo(@".\data\company.xlsx"); FileInfo storeDataFile = new FileInfo(@".\data\company.xlsx"); addressDataFile.Delete(); companyDataFile.Delete(); storeDataFile.Delete(); var addresses = new Dictionary <int, Address>(); var company = new Dictionary <int, Company>(); var stores = items.Cast <Store>().ToList(); int companyID = 1; foreach (Store store in stores) { if (!addresses.ContainsKey(Convert.ToInt32(store.Number))) { addresses.Add(Convert.ToInt32(store.Number), store.Address); } if (!company.ContainsValue(store.Owner)) { company.Add(companyID++, store.Owner); } } workbook = excelApplication.Workbooks.Add(); worksheet = workbook.Sheets.Add(); AddressDataSave(worksheet, addresses); workbook.SaveAs(addressDataFile.FullName); workbook.Close(); workbook = excelApplication.Workbooks.Add(); worksheet = workbook.Sheets.Add(); CompanyDataSave(worksheet, company); workbook.SaveAs(companyDataFile.FullName); workbook.Close(); workbook = excelApplication.Workbooks.Add(); worksheet = workbook.Sheets.Add(); StoreDataSave(worksheet, stores, company); workbook.SaveAs(storeDataFile.FullName); } workbook?.Close(false); }
public static List <Comment> LoadComments(string file) { List <Comment> res = new List <Comment>(); Excel.Workbook book = null; try { Excel.Application app = new Excel.Application(); book = app.Workbooks.Open(file); Excel.Worksheet worksheet = (Excel.Worksheet)book.Worksheets.Item[1]; var range = worksheet.UsedRange; for (int i = 4;; i++) { var id = range.Range["A" + i]?.Value2; if (id == null) { break; } int j = 1; var val = range.Range["D2"]?.Value2; var ok = range.Range["D" + i]?.Value2; while (val != null) { if (ok != null) { Comment c = new Comment(); c.StudentId = id.ToString(); c.Descr = val.ToString(); res.Add(c); } char letter = (char)('D' + j); val = range.Range[letter + "2"]?.Value2; ok = range.Range[letter + "" + i]?.Value2; j++; } } } catch (Exception e) { MessageBox.Show("Произошла ошибка импорта. " + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return(null); } finally { book?.Close(); } return(res); }
public void Dispose() { _wb?.Close(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_statistic); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_dictionary); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_wb); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_app); }
public List <T> Read <T>(List <ExcelRowClassMapper> mapper) where T : class, new() { var l = new List <T>(); Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp?.Workbooks.Open(FileName); Excel._Worksheet xlWorksheet = (Excel._Worksheet)(xlWorkbook?.Sheets[1]); Excel.Range xlRange = xlWorksheet?.UsedRange; int rowCount = xlRange?.Rows.Count ?? 0; int colCount = xlRange?.Columns.Count ?? 0; T item = null; for (int i = 1; i <= rowCount; i++) { if (i == 1 && ColumnNamesInFirstRow) { readColumnNames(xlRange, colCount); } else { for (int j = 1; j <= colCount; j++) { if (j == 1) { item = new T(); setCommonValues(item, mapper); } var cell = xlRange.Cells[i, j] as Excel.Range; var value = cell?.Value; var propertyName = getPropertyName(mapper, j); var property = typeof(T).GetProperty(propertyName); if (value != null) { setValue(item, property, value); } } } if (!(item is null)) { l.Add(item); } } xlWorkbook?.Close(); xlApp?.Quit(); return(l); }
/// <summary> /// Creates objects needed for the writing process and starts it. /// </summary> /// <param name="fileCreationType"> /// The strategy for creating the excel file. /// It is assumed that the function is only called with <see cref="FileCreationType.UpdateExistingFile" />, /// <see cref="FileCreationType.CreateEmptyFile" /> or <see cref="FileCreationType.CreateNewFile" /> /// as possible values. /// </param> /// <param name="translationsDictionary"> /// The translations that should be written into the dictionary. /// </param> /// <param name="path">The path of the excel sheet.</param> private void CreateExelFileBasedOnCreationType(FileCreationType fileCreationType, Dictionary<CultureInfo, Dictionary<string, string>> translationsDictionary, string path) { var excel = new ExcelInterop.Application(); ExcelInterop.Workbook workbook = null; try { //FileCreationType.UpdateExistingFile if (fileCreationType == FileCreationType.UpdateExistingFile) { workbook = excel.Workbooks.Open(System.IO.Path.GetFullPath(path)); } //FileCreationType.CreateEmptyFile or FileCreationType.CreateNewFile else { workbook = excel.Workbooks.Add(); } //FileCreationType.UpdateExistingFile or FileCreationType.CreateNewFile if (fileCreationType != FileCreationType.CreateEmptyFile) { //get parameters. var worksheet = (ExcelInterop.Worksheet) workbook.Worksheets[1]; var textLocalizations = TextLocalizationsUtils.FlipLocalizationsDictionary(translationsDictionary); //write to sheet. WriteTranslationsToWorksheet(worksheet, textLocalizations); } //saving. excel.DisplayAlerts = false; //FileCreationType.UpdateExistingFile. if (fileCreationType == FileCreationType.UpdateExistingFile) { workbook.Save(); } //FileCreationType.CreateEmptyFile or FileCreationType.CreateNewFile. else { workbook.SaveAs(System.IO.Path.GetFullPath(path)); } } finally { workbook?.Close(); excel.Quit(); } }
public void Close() { workbook?.Close(true, null, null); application?.Quit(); Marshal.ReleaseComObject(worksheet); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(application); worksheet = null; workbook = null; application = null; range = null; log?.Close(); log = null; }
private void button1_Click(object sender, EventArgs e) { var filepath = @"d:\dupa.xlsx"; MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(filepath); MySheet = (Excel.Worksheet)MyBook.Sheets["Arkusz3"]; // Explicit cast is not required here Excel.Range cell = MySheet.Range[MySheet.Cells[1, 1], MySheet.Cells[4, 4]]; foreach (Excel.Range item in cell) { item.Value = string.Format("row:{0:D2} col:{1:D2}", item.Row, item.Column); } MyBook.SaveAs(Filename: filepath); MyBook.Close(); }
//public Write2Exl() //{ //} public void WriteInExl(List<string> workList) { xlApp = new Excel.Application(); // open Excel App xlWorkBookTar = xlApp.Application.Workbooks.Open(wrtFilePath); // open Workbook //wrtBySht("InstrumentClassData"); foreach (string item in workList) { wrtBySht(item); } xlWorkBookTar.SaveAs(wrtFilePath, misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue); xlWorkBookTar.Close(); xlApp.Quit(); finProg(); }
public void Parse(object src, object dest) { if (src == null || string.IsNullOrEmpty(src.ToString())) { throw new ArgumentNullException("源文件不能为空"); } if (dest == null || string.IsNullOrEmpty(dest.ToString())) { throw new ArgumentNullException("目标路径不能为空"); } try { xlsx = excelApp.Workbooks.Open(src.ToString(), missing, missing, missing, missing, missing , missing, missing, missing, missing, missing, missing, missing, missing, missing); int startPage = 0; int endPage = 10; if (xlsx != null) { xlsx.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, dest, missing, missing, missing, missing, missing, missing, missing); } } catch (Exception ex) { throw ex; } finally { if (xlsx != null) { xlsx.Close(false, missing, missing); xlsx = null; } if (excelApp!= null) { excelApp.Quit(); excelApp = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }
private void LoadSheetName_RobPlan() { _xlApp = new Excel.Application {DisplayAlerts = false}; _xlWorkBook = _xlApp.Workbooks.Open(_pathRobPlan); int countGroup = 0; for (int i = 1; i <= _xlWorkBook.Sheets.Count; i++) { string name = _xlWorkBook.Worksheets.get_Item(i).Name; if (name.Length != 8 || name.IndexOf('-') != 2) continue; SheetNamesRobPlan[countGroup] = _xlWorkBook.Worksheets.get_Item(i).Name; countGroup++; } _xlWorkBook.Close(); _xlApp.Quit(); }
public string ExportToExcel(DocumentModel document,string savePath) { savePath = savePath + Path.GetRandomFileName().Replace('.', 'a') + ".xlsx"; myApp = new Excel.Application(); myApp.Visible = false; myBook = myApp.Workbooks.Add(); mySheet = (Excel.Worksheet)myBook.Sheets[1]; var lastRow = mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; WriteHeadTable(document.HeadTable, ref lastRow); WriteActs(document.Acts, ref lastRow); WriteTables(document.Tables, ref lastRow); mySheet.Columns.AutoFit(); myBook.SaveAs(savePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); myBook.Close(false); myApp.Quit(); return savePath; }
public void Esegui(string codiceCondominio, decimal importo, string siglaUtente) { object oMissing = System.Reflection.Missing.Value; _thisApplication = new Excel.Application(); _thisApplication.Visible = _verbose; _spesaturaWorkbook = _thisApplication.Workbooks.Open(_excelFullPath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); _spesaturaSheet = (Excel.Worksheet)_spesaturaWorkbook.Worksheets[1]; // Trovo la prima cella libera int emptyRow = -1; for (int i = 15; i < 250; i++) { string[] columnsIndex = Settings.Default.ColumnIndex.Split(';'); bool found = true; for (int j = 0; j < columnsIndex.Length; j++) { if (((Excel.Range)_spesaturaSheet.Cells[i, int.Parse(columnsIndex[j])]).Value2 != null) { found = false; break; } } if (found) { emptyRow = i; break; } } if (emptyRow > -1) { string[] columnsIndex = Settings.Default.UpdatableColumnIndex.Split(';'); valorizzaCelle(emptyRow, int.Parse(columnsIndex[0]), _descrizione); valorizzaCelle(emptyRow, int.Parse(columnsIndex[1]), DateTime.Today.ToShortDateString()); valorizzaCelle(emptyRow, int.Parse(columnsIndex[2]), importo); valorizzaCelle(emptyRow, int.Parse(columnsIndex[3]), siglaUtente); } _spesaturaWorkbook.Close(true, Type.Missing, Type.Missing); _thisApplication.Quit(); }
public List<string> readData() { prjList.Clear(); ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Списки"]; //Получаем ссылку на лист 1 for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { prjList.Add(WorkSheetExcel.Cells[i, 1].Text.ToString()); } WorkBookExcel.Close(false, Type.Missing, Type.Missing); ExcelApp.Quit(); GC.Collect(); return prjList; }
internal static void SaveAs(string path, string output, string delimiter) { Excel.Application app = null; Excel.Workbook wb = null; try { app = new Excel.Application(); wb = app.Workbooks.Open( path, Format: Excel.XlFileFormat.xlCSV, Delimiter: delimiter ); wb.SaveAs(output, Excel.XlFileFormat.xlExcel8); app.Visible = false; } finally { wb?.Close(); app?.Quit(); } }
public void excelWrite() { //if (File.Exists(_filePath)) //{ // File.Delete(_filePath); // FileStream fs = File.Create(_filePath); // fs.Close(); //} int i=0; ExcelApp = new Excel.Application(); ExcelApp.Visible = true; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1 foreach (string n in _tNames) { RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 1]; RangeExcel.Value = _tNames[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 2]; RangeExcel.Value = _tTels[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 3]; RangeExcel.Value = _tTels2[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 4]; RangeExcel.Value = _tTels3[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 5]; RangeExcel.Value = _tEmail[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 6]; RangeExcel.Value = _tDiv[i]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 7]; RangeExcel.Value = _tPos[i]; i++; } WorkBookExcel.Close(true, Type.Missing, Type.Missing); ExcelApp.Quit(); GC.Collect(); }
public (Maybe <TrackLayout> item, IEnumerable <Message> messages) GetLayout(string name) { var messages = new List <Message>(); var result = new TrackLayout(name); var app = Excel; Excel.Workbook book = null; try { var fileName = GetFullFilename(name); book = app.Workbooks.Open(fileName); messages.AddRange(GetStations(result, book)); if (messages.CanContinue()) { messages.AddRange(GetStretches(result, book)); } } finally { book?.Close(false, GetFullFilename(name)); } return(Maybe <TrackLayout> .NoneIfError(result, messages.HasStoppingErrors()), messages); }
public bool Read(string filename) { bool bDone = false; Excel.Application a = new Excel.Application(); Excel.Workbook wb = null; Excel.Worksheet currentSheet = null; Process appProcess = GetExcelProcess(a); try { wb = a.Workbooks.Open(filename); int k = wb.Worksheets.Count < 20 ? wb.Worksheets.Count : 20; //k = 1; for (int i = 0; i < k; i++) { currentSheet = wb.Worksheets[i + 1]; Sheet sheet = new Sheet { Name = currentSheet.Name }; var lastCell = currentSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell); //последнюю ячейку // размеры базы int lastColumn = (int)lastCell.Column; int lastRow = (int)lastCell.Row; lastCell = null; for (int row = 1; row <= lastRow + 1; row++) { List <string> cols = new List <string>(); for (int col = 1; col <= lastColumn; col++) { cols.Add(currentSheet.Cells[row, col].Text.ToString()); } sheet.Cells.Add(cols); } Sheets[i] = sheet; } bDone = true; } catch (Exception ex) { ClientBase.DialogService.ShowMessage(ex.Message); } finally { currentSheet = null; wb?.Close(false, false, false); wb = null; a?.Quit(); a = null; appProcess.Kill(); GC.Collect(); } return(bDone); }
private void btnExportToXml_Click(object sender, EventArgs e) { SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Xml files (*.xml)|*.xml|Excel files (*.xls)|*.xls"; dialog.OverwritePrompt = true; dialog.RestoreDirectory = true; if (dialog.ShowDialog() != DialogResult.OK) { return; } //导出至xml文件 if (dialog.FileName.EndsWith(".xml")) { XmlDocument xmldoc = new XmlDocument(); XmlTextWriter writeXml = null; try { writeXml = new XmlTextWriter(dialog.FileName, Encoding.GetEncoding("gb2312")); } catch { MessageBox.Show("创建或写入文件失败!"); return; } writeXml.Formatting = Formatting.Indented; writeXml.Indentation = 5; writeXml.WriteStartDocument(); writeXml.WriteStartElement("参数列表"); { foreach (WeightParameter wp in curWaParas) { writeXml.WriteStartElement("参数"); { writeXml.WriteStartElement("参数名称"); writeXml.WriteString(wp.ParaName); writeXml.WriteEndElement(); } { writeXml.WriteStartElement("参数单位"); writeXml.WriteString(wp.ParaUnit); writeXml.WriteEndElement(); } { writeXml.WriteStartElement("参数类型"); writeXml.WriteValue(wp.ParaType); writeXml.WriteEndElement(); } { writeXml.WriteStartElement("参数数值"); writeXml.WriteValue(wp.ParaValue); writeXml.WriteEndElement(); } { writeXml.WriteStartElement("参数备注"); writeXml.WriteString(wp.ParaRemark); writeXml.WriteEndElement(); } writeXml.WriteEndElement(); } } writeXml.WriteEndElement(); writeXml.Close(); } //导出至xml文件 if (dialog.FileName.EndsWith(".xls")) { Excel.Application app = new Excel.ApplicationClass(); try { Object missing = System.Reflection.Missing.Value; app.Visible = false; Excel.Workbook wBook = app.Workbooks.Add(missing); Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet; Excel.Range DataCell = wSheet.get_Range("A1", "A1"); DataCell.Value2 = "参数名称"; DataCell.Next.Value2 = "参数单位"; DataCell.Next.Next.Value2 = "参数数值"; for (int i = 0; i < curWaParas.Count; ++i) { WeightParameter wp = curWaParas[i]; string cellid = "A" + (i + 2).ToString(); DataCell = wSheet.get_Range(cellid, cellid); DataCell.Value2 = wp.ParaName; DataCell.Next.Value2 = wp.ParaUnit; DataCell.Next.Next.Value2 = wp.ParaValue; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.SaveAs(dialog.FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); wBook.Close(false, missing, missing); app.Quit(); app = null; } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } XLog.Write("成功导出参数值到文件\"" + dialog.FileName + "\"!"); }
//загрузка заявок в таблицу private async Task LoadTickets(string filePath) { await Task.Run(() => { try { // создаем объект Excel, открываем указанный пользователем файл, при этом сам Excel не видим excel_app = new Excel.Application(); excel_app.Visible = false; excel_book = excel_app.Workbooks.Open(filePath); // задаем первую страницу excel_sheet = excel_book.Sheets[1]; // подсчитываем кол-во строк в документе, без учета первой строки int rowCount = excel_sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row - 1; this.Invoke((Action)delegate { //очищаем таблицу dgvTable.Rows.Clear(); tsStatusLabel.Text = "Открытие документа"; tsProgressBar.Value = 0; tsProgressBar.Maximum = rowCount; // меняем в таблице тип данных в первом столбце на дата\время Column1.ValueType = typeof(DateTime); // добавляем строки по кол-во строк в документе Excel dgvTable.Rows.Add(rowCount); // устанавливаем для боковой строки ширину dgvTable.RowHeadersWidth = 50; //проход по строкам документа for (int i = 0; i < rowCount; i++) { // проход по столбцам строки for (int j = 0; j < 4; j++) { //добавляем значения из документа Excel в таблицу dgvTable.Rows[i].Cells[j].Value = excel_sheet.Cells[i + 2, j + 1].Value; } // заменяем знак "#" на пробел во втором столбце dgvTable.Rows[i].Cells[2].Value = dgvTable.Rows[i].Cells[2].Value.ToString().Replace('#', ' '); // нумеруем кол-во строк в таблице dgvTable.Rows[i].HeaderCell.Value = (i + 1).ToString(); tsProgressBar.Value++; tsStatusLabel.Text = "Заполнение заявок"; } }); } finally { // закрываем книгу без сохранения excel_book.Close(false, false); // закрываем приложение excel_app.Quit(); // убиваем процесс System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_app); // удаляем ссылки на Excel excel_sheet = null; excel_book = null; excel_app = null; this.Invoke((Action)delegate { tsProgressBar.Value = tsProgressBar.Maximum; tsStatusLabel.Text = "Укажите шаблон"; }); // запуск сборщика мусора GC.Collect(); } }); }
private string convert(string filePath) { m_path = filePath; var missing = Type.Missing; objApp = new Excel.Application(); objWorkbooks = objApp.Workbooks; Cross_TopTag topTag; Cross_Data[] data; Cross_Paragraphs[][] paragraphs; Cross_Qas[][][] qas; Cross_Answers[][][][] answers; object[,] sheet1ValueArray; object[,] sheet2ValueArray; int countParagraphs = 0; int countQas = 0; int currentRow = 0; bool excelOpen = false; try { if (m_currentConvertingMode == convertingMode.CJSONToCExcel) { #region JSON -> Excel 변환 // ** name1 영역 파싱 topTag = JsonConvert.DeserializeObject <Cross_TopTag>(File.ReadAllText(m_path)); // name2 영역 파싱 data = new Cross_Data[topTag.data.Count]; for (int i = 0; i < data.Length; i++) { data[i] = JsonConvert.DeserializeObject <Cross_Data>(topTag.data[i].ToString()); } // ** name3 영역 파싱 paragraphs = new Cross_Paragraphs[data.Length][]; for (int i = 0; i < data.Length; i++) { paragraphs[i] = new Cross_Paragraphs[data[i].paragraphs.Count]; for (int j = 0; j < data[i].paragraphs.Count; j++) { paragraphs[i][j] = JsonConvert.DeserializeObject <Cross_Paragraphs>(data[i].paragraphs[j].ToString()); countParagraphs++; } } // ** name4 영역 파싱 qas = new Cross_Qas[data.Length][][]; for (int i = 0; i < data.Length; i++) { qas[i] = new Cross_Qas[paragraphs[i].Length][]; for (int j = 0; j < paragraphs[i].Length; j++) { qas[i][j] = new Cross_Qas[paragraphs[i][j].qas.Count]; for (int k = 0; k < paragraphs[i][j].qas.Count; k++) { qas[i][j][k] = JsonConvert.DeserializeObject <Cross_Qas>(paragraphs[i][j].qas[k].ToString()); countQas++; } } } // ** name5 영역 파싱 answers = new Cross_Answers[data.Length][][][]; for (int i = 0; i < data.Length; i++) { answers[i] = new Cross_Answers[paragraphs[i].Length][][]; for (int j = 0; j < paragraphs[i].Length; j++) { answers[i][j] = new Cross_Answers[qas[i][j].Length][]; for (int k = 0; k < qas[i][j].Length; k++) { answers[i][j][k] = new Cross_Answers[qas[i][j][k].answers.Count]; for (int m = 0; m < qas[i][j][k].answers.Count; m++) { answers[i][j][k][m] = JsonConvert.DeserializeObject <Cross_Answers>(qas[i][j][k].answers[m].ToString()); } } } } // ** sheet1ValueArray & sheet2ValueArray 영역 크기 지정 sheet1RowCount = countQas; sheet2RowCount = countParagraphs; sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount]; sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount]; // ** sheet1ValueArray & sheet2ValueArray에 데이터 입력 // * paragraph 순번 & name1 영역 for (int row = 0; row < sheet2RowCount; row++) { sheet2ValueArray[row, 0] = row + 1; sheet2ValueArray[row, 1] = topTag.version; sheet2ValueArray[row, 2] = topTag.creator; sheet2ValueArray[row, 3] = topTag.progress; sheet2ValueArray[row, 4] = topTag.formatt; sheet2ValueArray[row, 5] = topTag.time; sheet2ValueArray[row, 6] = topTag.check; sheet2ValueArray[row, 7] = topTag.firstfile; sheet2ValueArray[row, 8] = topTag.secondfile; } // * name2 & name3 영역 currentRow = 0; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { sheet2ValueArray[currentRow, 9] = data[d].title; sheet2ValueArray[currentRow, 10] = paragraphs[d][p].context; sheet2ValueArray[currentRow, 11] = paragraphs[d][p].context_en; sheet2ValueArray[currentRow, 12] = paragraphs[d][p].context_tagged; currentRow++; } } // * name4 영역 currentRow = 0; int currentParaNum = 1; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { for (int q = 0; q < qas[d][p].Length; q++) { sheet1ValueArray[currentRow, 0] = currentRow + 1; sheet1ValueArray[currentRow, 1] = qas[d][p][q].id; sheet1ValueArray[currentRow, 2] = qas[d][p][q].confuseQt1; sheet1ValueArray[currentRow, 3] = qas[d][p][q].confuseQf1; sheet1ValueArray[currentRow, 4] = qas[d][p][q].confuseSat1; sheet1ValueArray[currentRow, 5] = qas[d][p][q].confuseLat1; sheet1ValueArray[currentRow, 6] = qas[d][p][q].question; sheet1ValueArray[currentRow, 7] = qas[d][p][q].question_en; sheet1ValueArray[currentRow, 8] = qas[d][p][q].question_tagged1; sheet1ValueArray[currentRow, 9] = qas[d][p][q].questionType1; sheet1ValueArray[currentRow, 10] = qas[d][p][q].questionFocus1; sheet1ValueArray[currentRow, 11] = qas[d][p][q].questionSAT1; sheet1ValueArray[currentRow, 12] = qas[d][p][q].questionLAT1; sheet1ValueArray[currentRow, 13] = qas[d][p][q].confuseQt2; sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQf2; sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseSat2; sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseLat2; sheet1ValueArray[currentRow, 17] = qas[d][p][q].question_tagged2; // sheet1ValueArray[currentRow, 18] = qas[d][p][q].questionType2; // sheet1ValueArray[currentRow, 19] = qas[d][p][q].questionFocus2; // sheet1ValueArray[currentRow, 20] = qas[d][p][q].questionSAT2; // sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionLAT2; sheet1ValueArray[currentRow, 22] = qas[d][p][q].confuseQt3; sheet1ValueArray[currentRow, 23] = qas[d][p][q].confuseQf3; sheet1ValueArray[currentRow, 24] = qas[d][p][q].confuseSat3; sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseLat3; sheet1ValueArray[currentRow, 26] = qas[d][p][q].question_tagged3; sheet1ValueArray[currentRow, 27] = qas[d][p][q].questionType3; sheet1ValueArray[currentRow, 28] = qas[d][p][q].questionFocus3; sheet1ValueArray[currentRow, 29] = qas[d][p][q].questionSAT3; sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionLAT3; sheet1ValueArray[currentRow, 37] = currentParaNum; currentRow++; } currentParaNum++; } } // * name5 영역 currentRow = 0; for (int d = 0; d < data.Length; d++) { for (int p = 0; p < paragraphs[d].Length; p++) { for (int q = 0; q < qas[d][p].Length; q++) { if (qas[d][p][q].answers.Count > 3) { return("정답의 개수가 3개 초과인 문제가 있습니다.\r\n파일: " + filePath); } int answerStartColNum = 31; for (int a = 0; a < answers[d][p][q].Length; a++) { sheet1ValueArray[currentRow, answerStartColNum] = answers[d][p][q][a].text; sheet1ValueArray[currentRow, answerStartColNum + 1] = answers[d][p][q][a].text_en; sheet1ValueArray[currentRow, answerStartColNum + 2] = answers[d][p][q][a].text_tagged; sheet1ValueArray[currentRow, answerStartColNum + 3] = answers[d][p][q][a].text_syn; sheet1ValueArray[currentRow, answerStartColNum + 4] = answers[d][p][q][a].answer_start; sheet1ValueArray[currentRow, answerStartColNum + 5] = answers[d][p][q][a].answer_end; answerStartColNum += 6; } currentRow++; } } } // ** 엑셀로 출력 excelOpen = true; objWorkbook = objWorkbooks.Add(missing); objWorksheets = objWorkbook.Worksheets; // * sheet2 부분 적용 objWorksheet = (Excel.Worksheet)objWorksheets.get_Item(1); objWorksheet.Name = "Paragraphs"; range = objWorksheet.get_Range("A1", "M1"); range.HorizontalAlignment = HCENTER; range.Interior.Color = Color.FromArgb(142, 169, 219); range.Value2 = sheet2ColHeader; Marshal.ReleaseComObject(range); Excel.Range c1 = objWorksheet.Cells[2, 1]; Excel.Range c2 = objWorksheet.Cells[sheet2RowCount + 1, sheet2ColCount]; range = objWorksheet.get_Range(c1, c2); range.Value = sheet2ValueArray; Marshal.FinalReleaseComObject(c1); Marshal.FinalReleaseComObject(c2); Marshal.FinalReleaseComObject(range); Marshal.ReleaseComObject(objWorksheet); // * sheet1 부분 적용 objWorksheet = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing); objWorksheet.Name = "Qas"; range = objWorksheet.get_Range("A1", "AL1"); range.HorizontalAlignment = HCENTER; range.Interior.Color = Color.FromArgb(142, 169, 219); range.Value2 = sheet1ColHeader; Marshal.ReleaseComObject(range); c1 = objWorksheet.Cells[2, 1]; c2 = objWorksheet.Cells[sheet1RowCount + 1, sheet1ColCount]; range = objWorksheet.get_Range(c1, c2); range.Value = sheet1ValueArray; Marshal.FinalReleaseComObject(c1); Marshal.FinalReleaseComObject(c2); Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); m_savePath = Path.ChangeExtension(m_path, "xlsx"); FileInfo fi = new FileInfo(m_savePath); if (fi.Exists) { fi.Delete(); } objWorkbook.SaveAs(m_savePath, Excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; excelOpen = false; #endregion } else { #region Excel -> JSON 변환 // ** Excel 파일 불러와서 object 이중배열에 데이터 입력 excelOpen = true; objWorkbook = objWorkbooks.Open(m_path); objWorksheets = objWorkbook.Worksheets; objWorksheet = (Excel.Worksheet)objWorksheets[1]; range = objWorksheet.UsedRange; sheet1ValueArray = (object[, ])range.get_Value(missing); Marshal.ReleaseComObject(range); Marshal.ReleaseComObject(objWorksheet); objWorksheet = (Excel.Worksheet)objWorksheets[2]; range = objWorksheet.UsedRange; sheet2ValueArray = (object[, ])range.get_Value(missing); Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; excelOpen = false; // ** sheet1, sheet2 object 이중배열의 데이터를 JSON 태그 클래스의 객체에 입력 // * topTag 객체 데이터 입력 topTag = new Cross_TopTag(); topTag.version = sheet2ValueArray[2, 2] == null ? null : sheet2ValueArray[2, 2].ToString(); topTag.creator = sheet2ValueArray[2, 3] == null ? null : sheet2ValueArray[2, 3].ToString(); topTag.progress = Convert.ToInt32(sheet2ValueArray[2, 4]); topTag.formatt = sheet2ValueArray[2, 5] == null ? null : sheet2ValueArray[2, 5].ToString(); topTag.time = Convert.ToDouble(sheet2ValueArray[2, 6]); topTag.data = new List <object>(); // * topTag 객체 내의 Data 객체 리스트 입력 IList <object> titleList = new List <object>(); for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++) { object tempTitle = sheet2ValueArray[r, 7]; if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음) { titleList.Add(tempTitle); } else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == "" { titleList.Add(tempTitle); } else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과) { continue; } Cross_Data tempData = new Cross_Data(); tempData.title = tempTitle == null ? "" : tempTitle.ToString(); tempData.paragraphs = new List <object>(); topTag.data.Add(tempData); } // * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력 int dataCount = 0; object currentTitle = sheet2ValueArray[2, 7]; List <Cross_Data> tempDataList = topTag.data.Cast <Cross_Data>().ToList(); for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++) { Cross_Paragraphs tempParagraphs = new Cross_Paragraphs(); tempParagraphs.context = sheet2ValueArray[r, 8] == null ? null : sheet2ValueArray[r, 8].ToString(); //tempParagraphs.context_original = sheet2ValueArray[r, 9] == null ? null : sheet2ValueArray[r, 9].ToString(); tempParagraphs.context_en = sheet2ValueArray[r, 9] == null ? null : sheet2ValueArray[r, 9].ToString(); tempParagraphs.context_tagged = sheet2ValueArray[r, 10] == null ? null : sheet2ValueArray[r, 10].ToString(); //if (sheet2ValueArray[r, 11] == null) //{ // tempParagraphs.context_tagged = null; //} //else //{ // //tempParagraphs.context_tagged = new List<string>(); // string[] tempTagged = sheet2ValueArray[r, 11].ToString().Split(':'); // foreach (var item in tempTagged) // { // tempParagraphs.context_tagged.Add(item); // } //} tempParagraphs.qas = new List <object>(); if (sheet2ValueArray[r, 7] == null || sheet2ValueArray[r, 7].ToString() == "") { if (r != 2) { dataCount++; } tempDataList[dataCount].paragraphs.Add(tempParagraphs); currentTitle = sheet2ValueArray[r, 7] == null ? null : sheet2ValueArray[r, 7].ToString(); } else if (sheet2ValueArray[r, 7] == currentTitle) { tempDataList[dataCount].paragraphs.Add(tempParagraphs); } else { dataCount++; tempDataList[dataCount].paragraphs.Add(tempParagraphs); currentTitle = sheet2ValueArray[r, 7].ToString(); } } topTag.data = tempDataList.Cast <object>().ToList(); // * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력 dataCount = 0; int paragraphCount = 0; int currentParagraph = 1; tempDataList = topTag.data.Cast <Cross_Data>().ToList(); List <Cross_Qas> tempQasList = new List <Cross_Qas>(); for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++) { Cross_Qas tempQas = new Cross_Qas(); tempQas.id = sheet1ValueArray[r, 2] == null ? null : sheet1ValueArray[r, 2].ToString(); tempQas.confuseQt1 = Convert.ToBoolean(sheet1ValueArray[r, 3] == null ? null : sheet1ValueArray[r, 3]); tempQas.confuseQf1 = Convert.ToBoolean(sheet1ValueArray[r, 4] == null ? null : sheet1ValueArray[r, 4]); tempQas.confuseSat1 = Convert.ToBoolean(sheet1ValueArray[r, 5] == null ? null : sheet1ValueArray[r, 5]); tempQas.confuseLat1 = Convert.ToBoolean(sheet1ValueArray[r, 6] == null ? null : sheet1ValueArray[r, 6]); tempQas.question = sheet1ValueArray[r, 7] == null ? null : sheet1ValueArray[r, 7].ToString(); tempQas.question_en = sheet1ValueArray[r, 8] == null ? null : sheet1ValueArray[r, 8].ToString(); tempQas.question_tagged1 = sheet1ValueArray[r, 9] == null ? null : sheet1ValueArray[r, 9].ToString(); tempQas.questionType1 = sheet1ValueArray[r, 10] == null ? null : sheet1ValueArray[r, 10].ToString(); tempQas.questionFocus1 = sheet1ValueArray[r, 11] == null ? null : sheet1ValueArray[r, 11].ToString(); tempQas.questionSAT1 = sheet1ValueArray[r, 12] == null ? null : sheet1ValueArray[r, 12].ToString(); tempQas.questionLAT1 = sheet1ValueArray[r, 13] == null ? null : sheet1ValueArray[r, 13].ToString(); tempQas.confuseQt2 = Convert.ToBoolean(sheet1ValueArray[r, 14] == null ? null : sheet1ValueArray[r, 14]); tempQas.confuseQf2 = Convert.ToBoolean(sheet1ValueArray[r, 15] == null ? null : sheet1ValueArray[r, 15]); tempQas.confuseSat2 = Convert.ToBoolean(sheet1ValueArray[r, 16] == null ? null : sheet1ValueArray[r, 16]); tempQas.confuseLat2 = Convert.ToBoolean(sheet1ValueArray[r, 17] == null ? null : sheet1ValueArray[r, 17]); tempQas.questionType2 = sheet1ValueArray[r, 18] == null ? null : sheet1ValueArray[r, 18].ToString(); tempQas.questionFocus2 = sheet1ValueArray[r, 19] == null ? null : sheet1ValueArray[r, 19].ToString(); tempQas.questionSAT2 = sheet1ValueArray[r, 20] == null ? null : sheet1ValueArray[r, 20].ToString(); tempQas.questionLAT2 = sheet1ValueArray[r, 21] == null ? null : sheet1ValueArray[r, 21].ToString(); tempQas.confuseQt3 = Convert.ToBoolean(sheet1ValueArray[r, 22] == null ? null : sheet1ValueArray[r, 22]); tempQas.confuseQf3 = Convert.ToBoolean(sheet1ValueArray[r, 23] == null ? null : sheet1ValueArray[r, 23]); tempQas.confuseSat3 = Convert.ToBoolean(sheet1ValueArray[r, 24] == null ? null : sheet1ValueArray[r, 24]); tempQas.confuseLat3 = Convert.ToBoolean(sheet1ValueArray[r, 25] == null ? null : sheet1ValueArray[r, 25]); tempQas.questionType3 = sheet1ValueArray[r, 26] == null ? null : sheet1ValueArray[r, 26].ToString(); tempQas.questionFocus3 = sheet1ValueArray[r, 27] == null ? null : sheet1ValueArray[r, 27].ToString(); tempQas.questionSAT3 = sheet1ValueArray[r, 28] == null ? null : sheet1ValueArray[r, 28].ToString(); tempQas.questionLAT3 = sheet1ValueArray[r, 29] == null ? null : sheet1ValueArray[r, 29].ToString(); List <Cross_Answers> tempAnswersList = new List <Cross_Answers>(); // * topTag->Data->Paragraphs->Qas 객체 리스트 내의 Answers 객체 리스트 입력 for (int i = 0; i < 3; i++) { int ansStartColNum = 22 + (i * 6);//18 if (sheet1ValueArray[r, ansStartColNum] == null) { break; // 정답의 text 공백이면 없음 처리 } Cross_Answers tempAnswers = new Cross_Answers(); tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? null : sheet1ValueArray[r, ansStartColNum].ToString(); //tempAnswers.text_original = sheet1ValueArray[r, ansStartColNum + 1] == null ? null : sheet1ValueArray[r, ansStartColNum + 1].ToString(); tempAnswers.text_en = sheet1ValueArray[r, ansStartColNum + 1] == null ? null : sheet1ValueArray[r, ansStartColNum + 1].ToString(); tempAnswers.text_tagged = sheet1ValueArray[r, ansStartColNum + 2] == null ? null : sheet1ValueArray[r, ansStartColNum + 2].ToString(); tempAnswers.text_syn = sheet1ValueArray[r, ansStartColNum + 3] == null ? null : sheet1ValueArray[r, ansStartColNum + 3].ToString(); //if (sheet1ValueArray[r, ansStartColNum + 3] == null) //{ // tempAnswers.text_tagged = null; //} //else //{ // tempAnswers.text_tagged = new List<string>(); // string[] tempTagged = sheet1ValueArray[r, ansStartColNum + 3].ToString().Split(':'); // foreach (var item in tempTagged) // { // tempAnswers.text_tagged.Add(item); // } //} //if (sheet1ValueArray[r, ansStartColNum + 4] == null) //{ // tempAnswers.text_syn = null; //} //else //{ // tempAnswers.text_syn = new List<string>(); // string[] tempSyn = sheet1ValueArray[r, ansStartColNum + 4].ToString().Split(':'); // foreach (var item in tempSyn) // { // tempAnswers.text_syn.Add(item); // } //} tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]); tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]); tempAnswersList.Add(tempAnswers); } tempQas.answers = tempAnswersList.Cast <object>().ToList(); tempQasList.Add(tempQas); currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 40]); //36 if (r + 1 <= sheet1ValueArray.GetLength(0)) // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때 { if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 40])) // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면 { topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Cast <Cross_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입 tempQasList = new List <Cross_Qas>(); if (paragraphCount < topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Count - 1) // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리 { paragraphCount++; } else // 도달하고 난 후에는 이렇게 처리 { dataCount++; paragraphCount = 0; } } } if (r == sheet1ValueArray.GetLength(0)) // 현재 row가 마지막일때 { topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Cast <Cross_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); } } // ** JSON 파일로 저장 m_savePath = Path.ChangeExtension(m_path, "json"); FileInfo fi = new FileInfo(m_savePath); if (fi.Exists) // 파일이 이미 존재하면 삭제 { fi.Delete(); } string saveJSONText; bool m_EtoJNullRemoveCheck = false; if (m_EtoJNullRemoveCheck) { saveJSONText = JsonConvert.SerializeObject(topTag, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore // Null값 객체 제거 } ); } else { saveJSONText = JsonConvert.SerializeObject(topTag, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Include // Null값 객체 포함 } ); } using (StreamWriter sw = new StreamWriter(m_savePath)) { sw.Write(saveJSONText); } #endregion } return(STR_CONVERTING_SUCCESS); } catch (Exception e) { if (excelOpen) { Marshal.FinalReleaseComObject(range); Marshal.FinalReleaseComObject(objWorksheet); Marshal.FinalReleaseComObject(objWorksheets); objWorkbook.Close(false, missing, missing); objWorkbooks.Close(); objApp.Quit(); Marshal.FinalReleaseComObject(objWorkbook); Marshal.FinalReleaseComObject(objWorkbooks); Marshal.FinalReleaseComObject(objApp); objApp = null; } return("예외처리 된 오류 발생.\r\n파일: " + filePath); } }
/// <summary> /// /// </summary> public void Dispose() { wk = axFramerControl1.ActiveDocument as Excel.Workbook; try { wk.Close(Missing.Value, Missing.Value, Missing.Value); excelapp.Quit(); if (wk != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wk); wk = null; } if (wk != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wk); wk = null; } GC.Collect(); } catch { if (wk != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wk); wk = null; } if (wk != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wk); wk = null; } GC.Collect(); } }
//读取数据 显示到 datagridviewEmo void runDisplayEmotion() { BlogReader breader; //if (Datas.sinaJSONList.Count > 0) // Datas.sinaJSONList.Clear(); if (Datas.sinaJSONList.Count == 0) { breader = new BlogReader("temp5.dat"); Datas.sinaJSONList = breader.ReadFromFile(); } Datas.ProgressBarState = "读取完成,正在分析..."; Datas.ProgresBarStyle = System.Windows.Forms.ProgressBarStyle.Marquee; string emo = ""; //表情键 List<string> emoKey = new List<string>(); List<EmoValue> emoValue = new List<EmoValue>(); using (FileStream fs = new FileStream("datas/emo.txt", FileMode.Open)) { StreamReader sr = new StreamReader(fs); while (!sr.EndOfStream) { string line = sr.ReadLine(); if (line.Length > 0) { emoKey.Add(line); emoValue.Add(new EmoValue()); } } sr.Close(); fs.Close(); } MatchCollection mCollection; foreach (SinaJSON item in Datas.sinaJSONList) { mCollection = Regex.Matches(item.Text, @"\[\w+\]"); for (int i = 0; i < mCollection.Count; i++) { emo = mCollection[i].Value; //emo = emo.Substring(1, emo.Length - 2); int index = emoKey.IndexOf(emo); int male = 0, female = 0; if (item.SinaUser.Gender.Equals("m")) male++; else female++; if (index != -1) emoValue[index] = new EmoValue(emoValue[index].male + male, emoValue[index].female + female); } } app = new Excel.Application(); workbooks = app.Workbooks as Excel.Workbooks; workbook = workbooks.Add(Type.Missing); app.DisplayAlerts = false; //删除 多余Sheet foreach (Worksheet ws in workbook.Worksheets) if (ws != app.ActiveSheet) { ws.Delete(); } foreach (Chart cht in workbook.Charts) cht.Delete(); //创建一个Sheet,存数据 //worksheet = (Worksheet)workbook. // Worksheets.Add(Type.Missing, workbook.ActiveSheet, // Type.Missing, Type.Missing); worksheet = workbook.Worksheets[1]; worksheet.Name = "数据"; int worksheetIndex = 0; for (int i = 0; i < emoKey.Count; i++) { if (emoValue[i].male > 0 || emoValue[i].female > 0) { worksheet.Cells[i + 1, 1] = emoKey[i]; worksheet.Cells[i + 1, 2] = emoValue[i].male; worksheet.Cells[i + 1, 3] = emoValue[i].female; worksheetIndex++; } } // TODO: 生成一个统计图对象: Chart xlChart = (Chart)workbook.Charts. Add(Type.Missing, worksheet, Type.Missing, Type.Missing); // TODO: 设定数据来源 Range cellRange = (Range)worksheet.Cells[1, 1]; // TODO: 通过向导生成Chart xlChart.ChartWizard(cellRange.CurrentRegion, XlChartType.xl3DColumn, Type.Missing, XlRowCol.xlColumns, 1, 0, true, "表情比较", "表情", "数量"); // TODO: 设置统计图Sheet的名称 xlChart.Name = "统计"; // TODO: 让12个Bar都显示不同的颜色 ChartGroup grp = (ChartGroup)xlChart.ChartGroups(1); grp.GapWidth = 20; grp.VaryByCategories = true; // TODO: 让Chart的条目的显示形状变成圆柱形,并给它们显示加上数据标签 Series s1 = (Series)grp.SeriesCollection(1); s1.Name = "男"; s1.BarShape = XlBarShape.xlCylinder; s1.HasDataLabels = true; Series s = (Series)grp.SeriesCollection(2); s.BarShape = XlBarShape.xlCylinder; s.HasDataLabels = true; s.Name = "女"; // TODO: 设置统计图的标题和图例的显示 xlChart.Legend.Position = XlLegendPosition.xlLegendPositionTop; xlChart.ChartTitle.Font.Size = 24; xlChart.ChartTitle.Shadow = false; xlChart.ChartTitle.Border.LineStyle = XlLineStyle.xlContinuous; // TODO: 设置两个轴的属性,Excel.XlAxisType.xlValue对应的是Y轴,Excel.XlAxisType.xlCategory对应的是X轴 Axis valueAxis = (Axis)xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary); valueAxis.AxisTitle.Orientation = -90; Axis categoryAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary); categoryAxis.AxisTitle.Font.Name = "宋体"; //-------------------------------------------------- //workbook.SaveAs(sPath, Type.Missing, Type.Missing, // Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing); xlChart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture); IntPtr hwnd = (IntPtr)app.Hwnd; Bitmap b = null; try { if (OpenClipboard(hwnd)) { IntPtr data = GetClipboardData(14); // CF_ENHMETAFILE 14 if (data != IntPtr.Zero) { using (Metafile mf = new Metafile(data, true)) { b = new Bitmap(mf); } } } } finally { CloseClipboard(); //clear workbook.Close(Type.Missing, Type.Missing, Type.Missing); app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); workbook = null; app = null; xlChart = null; GC.Collect(); } this.Invoke(new updateDataGridViewEmo(doUpdateDataGridViewEmo), new object[] { emoKey, emoValue, b }); }
public static void ExportExcel(DataTable dtBia1, DataTable dtBia2, DataTable dtBia3) { try { SaveFileDialog sd = new SaveFileDialog(); sd.Filter = "xls files (*.xls)|*.xls"; sd.FilterIndex = 2; sd.RestoreDirectory = true; int[] tongDiem = new int[dtBia1.Rows.Count]; if (sd.ShowDialog() == DialogResult.OK) { object misValue = System.Reflection.Missing.Value; Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct."); return; } Excel.Workbook workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); // diem bia 1 Excel.Worksheet worksheetBia1 = (Excel.Worksheet)workbook.Worksheets[1]; worksheetBia1.Name = "Điểm số bia 1"; //tao ten collumn worksheetBia1.Cells[1, 1] = "Tên"; worksheetBia1.Cells[1, 2] = "Lượt 1"; worksheetBia1.Cells[1, 3] = "Lượt 2"; worksheetBia1.Cells[1, 4] = "Lượt 3"; worksheetBia1.Cells[1, 5] = "Tổng"; for (int i = 0; i <= dtBia1.Rows.Count - 1; i++) { // tru them 2 cot tong diem va xep loai nen la -3 for (int j = 0; j <= dtBia1.Columns.Count - 3; j++) { string data = dtBia1.Rows[i].ItemArray[j].ToString(); // table excel bat dau tu 1 nen phai +1 so vs table C# worksheetBia1.Cells[i + 1 + 1, j + 1] = data; //cong tong diem, j = 0 la ten if (j > 0 && j < dtBia3.Columns.Count - 3) { try { tongDiem[i] += Int16.Parse(data); } catch (Exception exInt) { } } } } worksheetBia1.Columns.AutoFit(); // diem bia 2 Excel.Worksheet worksheetBia2 = (Excel.Worksheet)workbook.Worksheets.Add(misValue, misValue, misValue, misValue); //(Excel.Worksheet)workbook.Worksheets[2]; worksheetBia2.Name = "Điểm số bia 2"; //tao ten collumn worksheetBia2.Cells[1, 1] = "Tên"; worksheetBia2.Cells[1, 2] = "Lượt 1"; worksheetBia2.Cells[1, 3] = "Lượt 2"; worksheetBia2.Cells[1, 4] = "Lượt 3"; worksheetBia2.Cells[1, 5] = "Tổng"; for (int i = 0; i <= dtBia2.Rows.Count - 1; i++) { // tru them 2 cot tong diem va xep loai nen la -3 for (int j = 0; j <= dtBia2.Columns.Count - 3; j++) { string data = dtBia2.Rows[i].ItemArray[j].ToString(); worksheetBia2.Cells[i + 1 + 1, j + 1] = data; //cong tong diem, j = 0 la ten if (j > 0 && j < dtBia3.Columns.Count - 3) { try { tongDiem[i] += Int16.Parse(data); } catch (Exception exInt) { } } } } worksheetBia2.Columns.AutoFit(); // diem bia 3 Excel.Worksheet worksheetBia3 = (Excel.Worksheet)workbook.Worksheets.Add(misValue, misValue, misValue, misValue); worksheetBia3.Name = "Điểm số bia 3"; //tao ten collumn worksheetBia3.Cells[1, 1] = "Tên"; worksheetBia3.Cells[1, 2] = "Lượt 1"; worksheetBia3.Cells[1, 3] = "Lượt 2"; worksheetBia3.Cells[1, 4] = "Lượt 3"; worksheetBia3.Cells[1, 5] = "Tổng"; for (int i = 0; i <= dtBia3.Rows.Count - 1; i++) { // tru them 2 cot tong diem va xep loai nen la -3 for (int j = 0; j <= dtBia3.Columns.Count - 3; j++) { string data = dtBia3.Rows[i].ItemArray[j].ToString(); worksheetBia3.Cells[i + 1 + 1, j + 1] = data; //cong tong diem, j = 0 : la ten, j = dtBia3.Columns.Count - 3 : la tong diem if (j > 0 && j < dtBia3.Columns.Count - 3) { try { tongDiem[i] += Int16.Parse(data); } catch (Exception exInt) { } } } } worksheetBia3.Columns.AutoFit(); // Worksheet tong diem va ti le phan tram Excel.Worksheet worksheetTong = (Excel.Worksheet)workbook.Worksheets.Add(misValue, misValue, misValue, misValue); worksheetTong.Name = "Điểm số tổng"; //tao ten collumn worksheetTong.Cells[1, 1] = "Tên"; worksheetTong.Cells[1, 2] = "Tổng điểm 3 bia"; worksheetTong.Cells[1, 3] = "Xếp loại"; /* + Giỏi: bắn : 72 điểm trở lên. + Khá: 59 đến 71 điểm. + Đạt: 45 đến 58 điểm. + Không đạt: Dưới 45 điểm. * */ int numGioi = 0; int numKha = 0; int numDat = 0; int numKhongDat = 0; for (int i = 0; i <= dtBia3.Rows.Count - 1; i++) { string data = dtBia3.Rows[i].ItemArray[0].ToString(); worksheetTong.Cells[i + 1 + 1, 1] = data; worksheetTong.Cells[i + 1 + 1, 2] = tongDiem[i]; string xeploai = "Không đạt";// ngoai dieu kien ben duoi la khong dat thanh tich if (tongDiem[i] >= 72) { xeploai = "Giỏi"; numGioi++; } else if (tongDiem[i] >= 59 && tongDiem[i] <= 71) { xeploai = "Khá"; numKha++; } else if (tongDiem[i] >= 45 && tongDiem[i] <= 58) { xeploai = "Đạt"; numDat++; } else { numKhongDat++; } worksheetTong.Cells[i + 1 + 1, 3] = xeploai; } worksheetTong.Cells[dtBia3.Rows.Count + 3, 1] = "Tỉ lệ % xếp loại giỏi"; float tlGioi = (float)numGioi / (float)dtBia3.Rows.Count * 100; worksheetTong.Cells[dtBia3.Rows.Count + 3, 2] = tlGioi + "%"; worksheetTong.Cells[dtBia3.Rows.Count + 4, 1] = "Tỉ lệ % xếp loại khá"; float tlKha = (float)numKha / (float)dtBia3.Rows.Count * 100; worksheetTong.Cells[dtBia3.Rows.Count + 4, 2] = tlKha + "%"; worksheetTong.Cells[dtBia3.Rows.Count + 5, 1] = "Tỉ lệ % xếp loại đạt"; float tlDat = (float)numDat / (float)dtBia3.Rows.Count * 100; worksheetTong.Cells[dtBia3.Rows.Count + 5, 2] = tlDat + "%"; worksheetTong.Cells[dtBia3.Rows.Count + 6, 1] = "Tỉ lệ % xếp loại không đạt"; float tlKhongDat = (float)numKhongDat / (float)dtBia3.Rows.Count * 100; worksheetTong.Cells[dtBia3.Rows.Count + 6, 2] = tlKhongDat + "%"; worksheetTong.Columns.AutoFit(); workbook.SaveAs(sd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workbook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(worksheetBia1); releaseObject(worksheetBia2); releaseObject(worksheetBia3); releaseObject(workbook); releaseObject(xlApp); MessageBox.Show("Xuất file hoàn tất.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { } }
public string GetReport() { try { object misValue = System.Reflection.Missing.Value; _excelApp = new Excel.Application(); _excelBook = _excelApp.Workbooks.Add(misValue); _excelSheet = (Excel.Worksheet)_excelBook.Worksheets.get_Item(1); _excelSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; _excelApp.Cells[1, 1] = "ЗАО «Специализированная Автошкола»"; _excelApp.Cells[2, 1] = "Юридический адрес:"; _excelApp.Cells[3, 1] = "г. Могилев, ул. Школьная 16-405"; _excelApp.Cells[4, 1] = "УНН: 600359652"; _excelApp.Cells[5, 1] = "Р/с 3012205280013 в Ф-л ОАО Бел"; _excelApp.Cells[6, 1] = "АПБ МОУ г. Могилев,"; _excelApp.Cells[7, 1] = "пр-т Мира 91 код 942"; _excelApp.Cells[8, 1] = "Телефон:(222) 290-45-65"; _excelApp.Cells[9, 1] = "Директор: Хацкевич Андрей Евгеньевич"; _excelApp.Cells[11, 1] = "Расписание теоритических занятий"; _excelApp.Cells[12, 1] = string.Format("на {0} {1} г.", GetMonthName(Month), Year); _excelApp.Cells[14, 1] = "Дата/Время"; _excelApp.Cells.get_Range("A14", "B14").Merge(Type.Missing); _excelApp.Columns[1].ColumnWidth = 15; _excelApp.Cells[14, 1].HorizontalAlignment = Excel.Constants.xlCenter; _excelApp.Columns[1].VerticalAlignment = Excel.Constants.xlCenter; _excelApp.Columns[2].ColumnWidth = 11; _excelApp.Columns[2].HorizontalAlignment = Excel.Constants.xlCenter; _excelApp.Columns[2].VerticalAlignment = Excel.Constants.xlCenter; //вывод времени var times = Lessons.Select(l => l.Lessons.Select(t => new { StartTime = t.StartTime, EndTime = t.EndTime, })).SelectMany(t => t.Select(d => d)).Distinct().OrderBy(d => d.StartTime); int col = 3; foreach (var t in times) { _excelApp.Cells[14, col] = t.StartTime.ToString() + "-" + t.EndTime.ToString(); _excelApp.Columns[col].ColumnWidth = 19; _excelApp.Columns[col].HorizontalAlignment = Excel.Constants.xlCenter; _excelApp.Columns[col].VerticalAlignment = Excel.Constants.xlCenter; col++; } int colCount = col - 1; var cellHeader1 = (Excel.Range)_excelApp.Cells[11, 1]; var cellHeader2 = (Excel.Range)_excelApp.Cells[11, colCount]; _excelApp.Cells.get_Range(cellHeader1, cellHeader2).Merge(Type.Missing); _excelApp.Cells.get_Range(cellHeader1, cellHeader2).HorizontalAlignment = Excel.Constants.xlCenter; cellHeader1 = (Excel.Range)_excelApp.Cells[12, 1]; cellHeader2 = (Excel.Range)_excelApp.Cells[12, colCount]; _excelApp.Cells.get_Range(cellHeader1, cellHeader2).Merge(Type.Missing); _excelApp.Cells.get_Range(cellHeader1, cellHeader2).HorizontalAlignment = Excel.Constants.xlCenter; //вывод дат int row = 15; foreach (var l in Lessons.OrderBy(l => l.Dates.OrderBy(d => d.Day).FirstOrDefault().DayOfWeek)) { _excelApp.Cells[row, 1] = l.DayOfWeek; int rowStart = row; foreach (var d in l.Dates.OrderBy(d => d.Date)) { _excelApp.Cells[row, 2] = d.ToShortDateString(); row++; } int rowEnd = row - 1; _excelApp.Cells.get_Range(string.Format("A{0}", rowStart), string.Format("A{0}", rowEnd)).Merge(Type.Missing); for (int i = 3; i < col; i++) { Excel.Range cellStart = (Excel.Range)_excelApp.Cells[rowStart, i]; Excel.Range cellEnd = (Excel.Range)_excelApp.Cells[rowEnd, i]; _excelApp.Cells.get_Range(cellStart, cellEnd).Merge(Type.Missing); } } //вывод занятий for (int i = 3; i < col; i++) { for (int j = 15; j < row; j++) { string dayOfWeek = _excelApp.Cells[j, 1].Text; string str1 = ((string)_excelApp.Cells[14, i].Text).Substring(0, 8); TimeSpan startTime = TimeSpan.Parse(str1); string str2 = ((string)_excelApp.Cells[14, i].Text).Substring(9, 8); TimeSpan endTime = TimeSpan.Parse(str2); var less = Lessons.Where(l => l.DayOfWeek == dayOfWeek).FirstOrDefault(); if (less != null) { var item = less.Lessons.Where(l => l.StartTime == startTime && l.EndTime == endTime).FirstOrDefault(); if (item != null) { _excelApp.Cells[j, i] = item.Group + "\n" + item.Instructor; } } } } Excel.Range cell1 = (Excel.Range)_excelApp.Cells[14, 1]; Excel.Range cell2 = (Excel.Range)_excelApp.Cells[row - 1, col - 1]; var cells = _excelSheet.get_Range(cell1, cell2); // выделяем cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; cells.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // правая внешняя cells.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // левая внешняя cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // нижняя внешняя cells.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; // внутренние вертикальные cells.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; // внутренние горизонтальные //_excelBook.SaveAs(_saveAsPath); _excelBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, Filename: _saveAsPath); } catch (Exception) { } finally { _excelBook.Close(false); _excelApp.Quit(); } return _saveAsPath; }
public async void btnRunExtraction_Click(object sender, RoutedEventArgs e) { GlobalDataBindingValues.Instance.extractionStartTime = DateTime.Now; var date = datePicker.SelectedDate; if (date is null) { _window.Logger.ErrorText = "Please select a date for extraction."; return; } if (!CredentialStore.Instance.StoreUserCredentials()) { return; } if (CredentialStore.Instance.CancelRun) { _window.Logger.WarningText = "Run cancelled by user!"; return; } // TODO: Use binding here. var username = _window.TxtUserName.Text; var password = _window.TxtPasswordBox.Password; const int maxFailureCount = 5; for (var failureCount = 0; failureCount < maxFailureCount; failureCount++) { try { _window.Logger.NewExtraction("Customer Limit Utilisation Report Extraction Started"); var currentAdaptivEnvironment = _window.CmbBxAdaptivEnvironments.SelectedValue.ToString(); //CredentialStore.Instance.StoreUserCredentials(); var successfulLogin = await Task.Run(() => _window.OpenAdaptivAndLogin(username, password, currentAdaptivEnvironment)); if (!successfulLogin) { _window.Logger.ErrorText = "Failed to run customer limit utilisation extraction!"; return; } #region wait for browser _window.completedLoading = false; while (!_window.completedLoading) { await Task.Run(() => Thread.Sleep(100)); } await Task.Run(() => Thread.Sleep(1000)); _window.completedLoading = false; #endregion wait for browser Action methodName = JavaScriptUtils.JavaScriptErrorDialogFound; IAsyncResult result = methodName.BeginInvoke(null, null); _window.InjectJavascript( nameof(JsScripts.OpenCustomerLimitUtilisationReport), JsScripts.OpenCustomerLimitUtilisationReport); _window.InjectJavascript( nameof(JsScripts.OpenCustomerLimitUtilisationReport), JsScripts.OpenCustomerLimitUtilisationReport); _window.WebBrowser.Document?.InvokeScript(nameof(JsScripts.OpenCustomerLimitUtilisationReport)); #region wait for browser while (!_window.completedLoading) { await Task.Run(() => Thread.Sleep(100)); } await Task.Run(() => Thread.Sleep(1000)); _window.completedLoading = false; #endregion wait for browser #region wait for browser while (!_window.completedLoading) { await Task.Run(() => Thread.Sleep(100)); } await Task.Run(() => Thread.Sleep(1000)); _window.completedLoading = false; #endregion wait for browser methodName = JavaScriptErrorDialogFound; result = methodName.BeginInvoke(null, null); // TODO: Get filter details for customer limit utilisation report. var fields = new List <string>(); var conditions = new List <string>(); var criteria = new List <string>(); var conjunctions = new List <string>(); if (cmbBxFilterField1.SelectedIndex != 0) { fields.Add(cmbBxFilterField1.SelectedValue.ToString()); } if (cmbBxFilterField2.SelectedIndex != 0) { fields.Add(cmbBxFilterField2.SelectedValue.ToString()); } if (cmbBxFilterField3.SelectedIndex != 0) { fields.Add(cmbBxFilterField3.SelectedValue.ToString()); } if (cmbBxFilterCondition1.SelectedIndex != 0) { conditions.Add(cmbBxFilterCondition1.SelectedValue.ToString()); } if (cmbBxFilterCondition2.SelectedIndex != 0) { conditions.Add(cmbBxFilterCondition2.SelectedValue.ToString()); } if (cmbBxFilterCondition3.SelectedIndex != 0) { conditions.Add(cmbBxFilterCondition3.SelectedValue.ToString()); } _window.Logger.OkayText = "Filtering customer limit utilisation report..."; _window.InjectJavascript( nameof(JsScripts.FilterCustomerLimitUtilisationReportForPortfolioAnalysis), JsScripts.FilterCustomerLimitUtilisationReportForPortfolioAnalysis); //_window.InjectJavascript( // nameof(JsScripts.FilterCustomerLimitUtilisationReport), // JsScripts.FilterCustomerLimitUtilisationReport(fields, conditions, criteria, conjunctions)); _window.WebBrowser.Document?.InvokeScript(nameof(JsScripts.FilterCustomerLimitUtilisationReportForPortfolioAnalysis)); #region wait for browser //_window.completedLoading = false; //while (!_window.completedLoading) //{ // await Task.Run(() => Thread.Sleep(100)); //} //await Task.Run(() => Thread.Sleep(1000)); //_window.completedLoading = false; #endregion wait for browser methodName.EndInvoke(result); methodName = JavaScriptErrorDialogFound; result = methodName.BeginInvoke(null, null); _window.Logger.OkayText = $"Opening customer limit utilisation report for {(DateTime)date:dd-MMM-yyyy}..."; _window.InjectJavascript( nameof(JsScripts.ChooseCustomerLimitUtilisationReport), JsScripts.ChooseCustomerLimitUtilisationReport); await Task.Run(() => Thread.Sleep(1000)); _window.WebBrowser.Document?.InvokeScript( nameof(JsScripts.ChooseCustomerLimitUtilisationReport)); _window.InjectedScripts.Clear(); #region wait for browser _window.completedLoading = false; while (!_window.completedLoading) { await Task.Run(() => Thread.Sleep(100)); } await Task.Run(() => Thread.Sleep(1000)); _window.completedLoading = false; #endregion wait for browser methodName.EndInvoke(result); await Task.Run(() => Thread.Sleep(1000)); methodName = JavaScriptErrorDialogFound; result = methodName.BeginInvoke(null, null); _window.InjectJavascript( nameof(JsScripts.SelectCustomerLimitUtilisationReportDate), JsScripts.SelectCustomerLimitUtilisationReportDate); _window.WebBrowser.Document?.InvokeScript( nameof(JsScripts.SelectCustomerLimitUtilisationReportDate), new object[] { ((DateTime)date).ToString("dd/MM/yyyy") }); #region wait for browser _window.completedLoading = false; while (!_window.completedLoading) { await Task.Run(() => Thread.Sleep(100)); } await Task.Run(() => Thread.Sleep(1000)); _window.completedLoading = false; #endregion wait for browser methodName.EndInvoke(result); methodName = JavaScriptErrorDialogFound; result = methodName.BeginInvoke(null, null); _window.InjectJavascript( nameof(JsScripts.GenerateCustomerLimitUtilisationReport), JsScripts.GenerateCustomerLimitUtilisationReport); _window.WebBrowser.Document?.InvokeScript( nameof(JsScripts.GenerateCustomerLimitUtilisationReport)); methodName.EndInvoke(result); while (_window.WebBrowser.Document?.GetElementsByTagName("img").Count < 5) { await Task.Run(() => Thread.Sleep(1000)); } await Task.Run(() => Thread.Sleep(3000)); methodName = JavaScriptErrorDialogFound; result = methodName.BeginInvoke(null, null); _window.InjectJavascript( nameof(JsScripts.ExportCustomerLimitUtilisationReportToCsv), JsScripts.ExportCustomerLimitUtilisationReportToCsv); _window.WebBrowser.Document?.InvokeScript( nameof(JsScripts.ExportCustomerLimitUtilisationReportToCsv)); var overrideExistingFile = (bool)chkBxOverrideExistingFiles.IsChecked; await Task.Run(() => Thread.Sleep(1000)); methodName.EndInvoke(result); await Task.Run(() => SaveCustomerLimitUtilisationReport((DateTime)date, overrideExistingFile)); var csvFile = $"\\\\pcibtighnas1\\CBSData\\Portfolio Analysis\\Data\\Cust Util\\SBG\\CustomerLimitUtil {date:dd.MM.yyyy}.csv"; Dispatcher.Invoke((System.Action)(() => { _window.Logger.OkayText = "Converting csv extraction to xlsx..."; })); var xlsxFile = csvFile.Replace(".csv", ".xlsx"); if (overrideExistingFile && File.Exists(xlsxFile)) { File.Delete(xlsxFile); Thread.Sleep(1000); } MainWindow.ConvertWorkbookFormats(csvFile, ".xlsx"); while (!File.Exists(xlsxFile)) { await Task.Run(() => Thread.Sleep(100)); } var fileSize = FileUtils.FileSize(xlsxFile); Dispatcher.Invoke((System.Action)(() => { _window.Logger.OkayText = "Performing minor formatting on xlsx file..."; })); var xlApp = new Excel.Application(); Excel.Workbook wb = xlApp.Workbooks.Open(xlsxFile); Excel.Worksheet ws = wb.Worksheets[1]; xlApp.DisplayAlerts = false; ws.Name = "Customer Limit Utilisation"; Excel.Range topLeftCell = ws.Cells[1, 1]; Excel.Range bottomRightCell = ws.Cells[3, 1000]; Excel.Range rangeToDelete = ws.Range[topLeftCell, bottomRightCell]; rangeToDelete.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); wb.Save(); xlApp.DisplayAlerts = true; wb.Close(); xlApp.Quit(); Dispatcher.Invoke((System.Action)(() => { _window.Logger.OkayText = "Deleting csv file..."; })); File.Delete(csvFile); Dispatcher.Invoke((System.Action)(() => { if (_window.extractedFiles.Any(x => x.FilePath == xlsxFile)) { _window.extractedFiles.Remove(_window.extractedFiles.First(x => x.FilePath == xlsxFile)); } _window.extractedFiles.Add(new ExtractedFile { FilePath = xlsxFile, FileName = Path.GetFileName(xlsxFile), FileType = "Customer Limit Utilisation", FileSize = fileSize }); })); _window.Logger.ExtractionComplete("Customer Limit Utilisation"); GlobalDataBindingValues.Instance.extractionEndTime = DateTime.Now; var timeSpan = GlobalDataBindingValues.Instance.extractionEndTime - GlobalDataBindingValues.Instance.extractionStartTime; _window.Logger.OkayTextWithoutTime = $"Extraction took: {timeSpan.Minutes} minutes {timeSpan.Seconds % 60} seconds"; _window.WebBrowser.Url = new Uri("C:\\GitLab\\AdaptivBot\\ExtractionComplete.html"); break; } catch (Exception exception) { if (failureCount < maxFailureCount) { _window.Logger.ErrorText = $"Something failed for Customer Limit Utilisation extraction. {exception.Message}"; _window.Logger.ErrorText = $"Trying again. Attempt number: {failureCount + 2}"; } else { _window.Logger.ErrorText = $"Customer Limit Utilisation " + $"extraction extraction failed {maxFailureCount} times. " + $"This may be due to an Adaptiv error. Please try again later."; } } } }
/// <summary> /// 另存为 /// </summary> /// <param name="fileName"></param> public void SaveCopyAs(string fileName) { m_objBook.SaveCopyAs(fileName); m_objBook.Close(false, miss, miss); }
private void button3_Click(object sender, EventArgs e) { timer1.Enabled = true; Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(txtFile.Text); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; rowCount = xlRange.Rows.Count; colCount = xlRange.Columns.Count; int number; int c = 0; int cc = 0; bool isParsable = Int32.TryParse(txtRows.Text, out number); if (isParsable) { c = number; } object misValue = System.Reflection.Missing.Value; Excel.Workbook xlWorkBook2; Excel.Worksheet xlWorkSheet2; xlWorkBook2 = xlApp.Workbooks.Add(misValue); xlWorkSheet2 = (Excel.Worksheet)xlWorkBook2.Worksheets.get_Item(1); int nfile = 0; progressBar1.Maximum = rowCount; progressBar1.Step = 1; int r = 0; r = c; for (int x = 1; x <= colCount; x++) { xlWorkSheet2.Cells[1, x] = xlWorksheet.Cells[1, x]; } for (int i = 1; i <= rowCount; i++) { for (int j = 1; j <= colCount; j++) { if (cc <= c && i <= c) { xlWorkSheet2.Cells[i - cc, j] = xlWorksheet.Cells[i, j]; if (i - cc == 1 && checkBox1.Checked) { xlWorkSheet2.Cells[1, j] = xlWorksheet.Cells[1, j]; } } } if (i == c && c <= rowCount) { cc = c; c = c + r; nfile += 1; xlWorkBook2.SaveAs(txtSave.Text + "\\SplitedFile" + nfile + ".xlsx", Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook2.Close(true, misValue, misValue); xlWorkBook2 = xlApp.Workbooks.Add(misValue); xlWorkSheet2 = (Excel.Worksheet)xlWorkBook2.Worksheets.get_Item(1); } progressBar1.PerformStep(); } nfile += 1; xlWorkBook2.SaveAs(txtSave.Text + "\\SplitedFile" + nfile + ".xlsx", Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook2.Close(true, misValue, misValue); Marshal.ReleaseComObject(xlWorkSheet2); Marshal.ReleaseComObject(xlWorkBook2); //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); MessageBox.Show("Finished!", "Process information", MessageBoxButtons.OK, MessageBoxIcon.Information); progressBar1.Value = 0; }
public ActionResult imPortExcel(HttpPostedFileBase excelfile, string organID) { // Kiểm tra file đó có tồn tại hay không if (excelfile == null || excelfile.ContentLength == 0) { ViewBag.ListSchool = new Share().listOrgan(2).ToList(); ViewBag.Error = "Thêm File mới<br /> "; return(View("imPortExcel")); } else { // kiểm tra đuôi file có phải là file Excel hay không if (excelfile.FileName.EndsWith("xls") || excelfile.FileName.EndsWith("xlsx")) { // Khai báo đường dẫn string path = Path.Combine("D:/", excelfile.FileName); // Tạo đối tượng COM. Tạo một đối tượng COM cho mọi thứ được tham chiếu Excel.Application application = new Excel.Application(); // Tạo application cái này là mở ms Excel Excel.Workbook workbook = application.Workbooks.Open(path); // Mở WorkBook Mở file Excel mình truyền vào Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet; // Mở worksheet Mở sheet đầu tiên Excel.Range range = worksheet.UsedRange; //Lặp lại qua các hàng và cột và in ra bàn điều khiển khi nó xuất hiện trong tệp //excel is not zero based!! var role = Convert.ToInt32(Session["Role"].ToString()); if (role == 3) { var schoolID = Session["SchoolID"].ToString(); for (int i = 2; i < range.Rows.Count; i++) { Person person = new Person(); string personID; do { personID = new Share().RandomText(); } while (new CompanyAndSchool().FindPerson(personID) == false); person.PersonID = personID; person.LastName = ((Excel.Range)range.Cells[i, 3]).Text; person.FirstName = ((Excel.Range)range.Cells[i, 4]).Text; DateTime dateValue = DateTime.FromOADate(Convert.ToDouble(((Excel.Range)range.Cells[i, 5]).Value)); // Dòng code này có ý nghĩa là nó sẽ chuyển đối kiểu số thành ngày lại person.Birthday = dateValue; int gender = int.Parse(((Excel.Range)range.Cells[i, 6]).Text); person.Gender = Convert.ToBoolean(gender); person.Address = ((Excel.Range)range.Cells[i, 7]).Text; person.Phone = ((Excel.Range)range.Cells[i, 8]).Text; person.Email = ((Excel.Range)range.Cells[i, 9]).Text; person.SchoolID = schoolID; person.CompanyID = organID; person.RoleID = 5; //listproducts.Add(product); InsertPer(person); if (SendMailTK(personID)) { Intern intern = new Intern(); intern.PersonID = personID; intern.StudentCode = ((Excel.Range)range.Cells[i, 2]).Text; intern.Result = 0; InsertInt(intern); } } } else { var companyID = Session["CompanyID"].ToString(); for (int i = 2; i < range.Rows.Count; i++) { Person person = new Person(); string personID; do { personID = new Share().RandomText(); } while (new CompanyAndSchool().FindPerson(personID) == false); person.PersonID = personID; person.LastName = ((Excel.Range)range.Cells[i, 3]).Text; person.FirstName = ((Excel.Range)range.Cells[i, 4]).Text; //person.Birthday = DateTime.ParseExact(((Excel.Range)range.Cells[i, 4]).Text,"yyyy/MM/dd",null); DateTime dateValue = DateTime.FromOADate(Convert.ToDouble(((Excel.Range)range.Cells[i, 5]).Value)); person.Birthday = dateValue; int gender = int.Parse(((Excel.Range)range.Cells[i, 6]).Text); //person.Gender = bool.Parse(Convert.ToUInt32(((Excel.Range)range.Cells[i, 5]).Value)); person.Gender = Convert.ToBoolean(gender); person.Address = ((Excel.Range)range.Cells[i, 7]).Text; person.Phone = ((Excel.Range)range.Cells[i, 8]).Text; person.Email = ((Excel.Range)range.Cells[i, 9]).Text; person.CompanyID = companyID; person.RoleID = 5; //listproducts.Add(product); InsertPer(person); if (SendMailTK(personID)) { Intern intern = new Intern(); intern.PersonID = personID; intern.StudentCode = ((Excel.Range)range.Cells[i, 2]).Text; intern.Result = 0; InsertInt(intern); } } } //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //xuất các đối tượng com để dừng hoàn toàn quá trình excel chạy trong nền Marshal.ReleaseComObject(range); Marshal.ReleaseComObject(worksheet); //đóng lại và xuất thông tin workbook.Close(); Marshal.ReleaseComObject(workbook); //thoát và xuất thông tin application.Quit(); Marshal.ReleaseComObject(application); //ViewBag.ListProduct = listproducts; //dem = listproducts.Count(); //return View("List"); ViewBag.ListSchool = new Share().listOrgan(2).ToList(); ViewBag.Error = "Thêm thành công<br /> "; return(View("imPortExcel")); } else { ViewBag.ListSchool = new Share().listOrgan(2).ToList(); ViewBag.Error = "File không hợp lệ<br /> "; return(View("imPortExcel")); } } }
private void button2_Click_1(object sender, EventArgs e) { int CONT = 1; var excel = new Excel.Application(); Excel.Workbook librodetrabajo = excel.Workbooks.Open(Label1); //string conString = @"Data Source=" + Server.Text + ";Initial Catalog=" + DataBase.Text + ";User Id=" + User.Text + ";password="******""; //string POS = ""; //for (int i = 1; i <= colCount; i++) //{ // POS += "`" + xlRange.Cells[1, i].Value2.ToString() + "`,"; //} //okf = POS.Substring(0, POS.Length - 1); for (int b = 2; b <= rowCount; b++) { string value = ""; string resultExcel = ""; for (int c = 1; c <= colCount; c++) { value += xlRange.Cells[b, c].Value2.ToString() + ","; resultExcel = value.Substring(0, value.Length - 1); } string[] valores = resultExcel.Split(','); string contratoarea = valores[0]; string verticePoligono = valores[1]; latitud = Convert.ToInt32(valores[3]); longitud = Convert.ToInt32(valores[2]); m1 = latitud / 10000; //Primeros 2 números int m4 = latitud % Convert.ToInt32(digitos); string result = m4.ToString(); int ZeroConditional = int.Parse(result); string sub = ""; if (ZeroConditional == 0) { try { divMin = 0; } catch (Exception ex) { throw ex; } } else { try { sub = result.Substring(0, result.Length - 2); // Segundos dos números de en medio if (sub == "") { sub = "0"; } } catch (Exception ex) { throw ex; } } m2 = (latitud % (10000)); m3 = latitud % 100; //-------------------------------------------- //Console.WriteLine(m1+" "+sub+" "+m3); if (ZeroConditional == 0) { divMin = 0; } else { divMin = Convert.ToDouble(sub) / minutos; //Números de en medio entre minutos } divSeg = m3 / segundos; //--------------Asignar valor a LATITUD Y LONGITUD //Console.WriteLine(m1 + " " + divMin + " " + divSeg); double suma = m1 + divMin + divSeg; if (contratoarea == "CNH-R01-L03-A18/2015" && verticePoligono == "10") { } m11 = longitud / 10000; int m44 = longitud % Convert.ToInt32(digitos); string result2 = m44.ToString(); string sub1 = ""; if (result2 == "0") { sub1 = "0"; } else { sub1 = result2.Substring(0, result2.Length - 2); if (sub1 == "") { sub1 = "0"; } } m22 = (longitud % (10000)); m33 = longitud % 100; //-------------------------------------------- //Console.WriteLine(m1+" "+sub+" "+m3); divMin1 = Convert.ToDouble(sub1) / minutos; divSeg1 = m33 / segundos; //--------------Asignar valor a LATITUD Y LONGITUD //Console.WriteLine(m1 + " " + divMin + " " + divSeg); double suma2 = m11 + divMin1 + divSeg1; query = $"insert into CO_Coordenadas (IdAreaContractual,lat,lng, poligono) values ({contratoarea},'{suma}','-{suma2}',{verticePoligono})"; str.WriteLine(query); } } str.Close(); librodetrabajo.Close(); MessageBox.Show("final"); }
public static List <Question> Read(string excelFile, IEnumerable <string> tabs) { Excel.Application xlApp = null; Excel.Workbook xlWorkbook = null; int hWnd = 0; try { List <Question> listOfQUestions = new List <Question>(); xlApp = new Excel.Application(); hWnd = xlApp.Hwnd; xlWorkbook = xlApp.Workbooks.Open(excelFile); //Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[6]; Dictionary <string, Excel._Worksheet> dict = new Dictionary <string, Excel._Worksheet>(); foreach (Excel._Worksheet worksheet in xlWorkbook.Worksheets) { dict.Add(worksheet.Name, worksheet); } tabs.ToList().ForEach(s => LoadWorksheet(listOfQUestions, GetWorksheet(dict, s))); List <Question> allQuestions = new List <Question>(); allQuestions.AddRange(listOfQUestions); allQuestions.AddRange(listOfQUestions.SelectMany(s => s.Children)); foreach (var question in allQuestions) { question.Expressions = GetExpressionsForrPresentation(allQuestions, question.DisplayRule); question.Expressions.Questions.ToList().ForEach(s => allQuestions.First(q => q.Id == s).LogicalChildren.Add(question.Id)); } //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad return(listOfQUestions); } finally { //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); if (xlApp.Workbooks != null) { Marshal.ReleaseComObject(xlApp.Workbooks); } //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); SendMessage((IntPtr)hWnd, 0x10, IntPtr.Zero, IntPtr.Zero); } /* https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/ * if (sheets != null) Marshal.ReleaseComObject(sheets); * if (book != null) Marshal.ReleaseComObject(book); * if (books != null) Marshal.ReleaseComObject(books); * if (app != null) Marshal.ReleaseComObject(app); */ }
private void ExportDataSetToExcel(DataSet ds, string strPath) { int inHeaderLength = 3, inColumn = 0, inRow = 0; System.Reflection.Missing Default = System.Reflection.Missing.Value; //Create Excel File //strPath += @"\Excel" + DateTime.Now.ToString().Replace(':', '-') + ".xlsx"; _excel.Application excelApp = new _excel.Application(); _excel.Workbook excelWorkBook = excelApp.Workbooks.Add(1); foreach (System.Data.DataTable dtbl in ds.Tables) { //Create Excel WorkSheet _excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default); excelWorkSheet.Name = dtbl.TableName;//Name worksheet //Write Column Name for (int i = 0; i < dtbl.Columns.Count; i++) { excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper(); } //Write Rows for (int m = 0; m < dtbl.Rows.Count; m++) { for (int n = 0; n < dtbl.Columns.Count; n++) { inColumn = n + 1; // inRow = inHeaderLength + 2 + m; //2 excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString(); if (m % 2 == 0) { excelWorkSheet.get_Range("A" + inRow.ToString(), "I" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6"); } } _excel.Range RowL = excelWorkSheet.get_Range("A5", "I6"); RowL.EntireRow.RowHeight = 80; } //merge E4 - I4 _excel.Range M = excelWorkSheet.get_Range("E4", "I4"); M.Merge(true); //Excel Header _excel.Range cellRang = excelWorkSheet.get_Range("A1", "I3"); cellRang.Merge(false); cellRang.Interior.Color = System.Drawing.Color.White; cellRang.Font.Color = System.Drawing.Color.Gray; cellRang.HorizontalAlignment = _excel.XlHAlign.xlHAlignCenter; cellRang.VerticalAlignment = _excel.XlVAlign.xlVAlignCenter; cellRang.Font.Size = 26; excelWorkSheet.Cells[1, 1] = "GRIPPER DESIGH"; excelWorkSheet.Shapes.AddPicture(@"C:\Users\palmdotax\source\repos\GRipperDesign\Picture\Draft\Flat.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 60, 200, 80); cellRang.EntireRow.RowHeight = 20; // add Vacuum Ejector pic excelWorkSheet.Shapes.AddPicture(@"C:\Users\palmdotax\source\repos\GRipperDesign\Picture\Draft\Vacuum Ejector.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 150, 200, 80); // add Draft design _excel.Range Df = excelWorkSheet.get_Range("A8", "I10"); Df.Merge(false); Df.Interior.Color = System.Drawing.Color.White; Df.Font.Color = System.Drawing.Color.Gray; Df.HorizontalAlignment = _excel.XlHAlign.xlHAlignCenter; Df.VerticalAlignment = _excel.XlVAlign.xlVAlignCenter; Df.Font.Size = 26; Df.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); Df.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31"); excelWorkSheet.Cells[8, 1] = "DRAFT DESIGH"; //add df pic _excel.Range Dfp = excelWorkSheet.get_Range("A11", "I24"); Dfp.Merge(false); excelWorkSheet.Shapes.AddPicture(@"C:\Users\palmdotax\source\repos\GRipperDesign\Picture\Draft\Gripper.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 1, 300, 200, 80); excelWorkSheet.Shapes.AddPicture(@"C:\Users\palmdotax\source\repos\GRipperDesign\Picture\Draft\Vacuum pad Distance.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 300, 200, 80); //Style table column names cellRang = excelWorkSheet.get_Range("A4", "G4"); cellRang.Font.Bold = true; cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31"); excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = _excel.XlHAlign.xlHAlignRight; //Formate price column excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00"; //Auto fit columns excelWorkSheet.Columns.AutoFit(); } //Delete First Page excelApp.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1]; lastWorkSheet.Delete(); excelApp.DisplayAlerts = true; //Set Defualt Page (excelWorkBook.Sheets[1] as _excel._Worksheet).Activate(); excelWorkBook.SaveAs(@"C:\Users\palmdotax\source\repos\GRipperDesign\BOM.xlxs"); excelWorkBook.Close(); excelApp.Quit(); MessageBox.Show("Excel generated successfully \n As " + strPath); }
private void ExportExcel(string path) { bool success = false; object misValue = System.Reflection.Missing.Value; excelApp = new Microsoft.Office.Interop.Excel.Application(); if (excelApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } worker = new BackgroundWorker(); worker.WorkerSupportsCancellation = true; worker.WorkerReportsProgress = true; PawnGuardDBDataContext pawnguard = new PawnGuardDBDataContext(); ProgStackPanel.Visibility = System.Windows.Visibility.Visible; ProgTextCancel.Visibility = System.Windows.Visibility.Collapsed; ProgressGrid.Visibility = System.Windows.Visibility.Visible; StoneListGrid.Visibility = System.Windows.Visibility.Hidden; Import.IsEnabled = false; Export.IsEnabled = false; this.MinHeight = 500; ProgText.Text = "Exporting Data..."; ProgressStone.Value = 0; worker.DoWork += delegate(object sdr, DoWorkEventArgs dw) { Excel.Application _excelApp = (Excel.Application)dw.Argument; xlWorkBook = _excelApp.Workbooks.Open(path); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); List<Stone> stones = pawnguard.Stones.ToList<Stone>(); for (int row = 0; row < stones.Count; row++) { for (int col = 0; col < 13; col++) { if (col == 0) xlWorkSheet.Cells[row + 2, col + 1] = stones[row].rarity; if (col == 1) xlWorkSheet.Cells[row + 2, col + 1] = stones[row].name; if (col == 2) xlWorkSheet.Cells[row + 2, col + 1] = stones[row].carat; if (col == 3) xlWorkSheet.Cells[row + 2, col + 1] = stones[row].price; } worker.ReportProgress(Convert.ToInt32(((decimal)row / (decimal)stones.Count) * 100)); System.Threading.Thread.Sleep(100); if (worker.CancellationPending) { ProgText.Dispatcher.Invoke(new Action(() => { ProgText.Text = "Canceling..."; })); System.Threading.Thread.Sleep(2000); dw.Cancel = true; return; } } }; worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args) { ProgressStone.Value = args.ProgressPercentage; }; worker.RunWorkerCompleted += delegate(object sdr, RunWorkerCompletedEventArgs rwc) { if (rwc.Error != null) { MessageBox.Show(rwc.Error.Message); return; } if (!rwc.Cancelled) { ProgressStone.Value = 100; SaveFileDialog dlg = new SaveFileDialog(); dlg.FileName = "Stone Template.xlsx"; dlg.DefaultExt = ".xlsx"; dlg.Filter = "Stone Template|*.xlsx"; Nullable<bool> result = dlg.ShowDialog(); if (result == true) { string filename = dlg.FileName; xlWorkBook.SaveAs(filename); success = true; path = filename; } } else { ProgText.Dispatcher.Invoke(new Action(() => { ProgText.Text = "Cancelled"; })); } xlWorkBook.Close(true, misValue, misValue); excelApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(excelApp); ProgressGrid.Visibility = System.Windows.Visibility.Hidden; StoneListGrid.Visibility = System.Windows.Visibility.Visible; Import.IsEnabled = true; Export.IsEnabled = true; this.MinHeight = 300; if (success) { System.Diagnostics.Process.Start(path); } }; worker.RunWorkerAsync(excelApp); }
private void exportExcelv2() { if (dt_value.Rows.Count == 0) { MessageBox.Show("ไม่มีข้อมูล"); return; } var Oldcul = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat = Oldcul.DateTimeFormat; try { this.Cursor = Cursors.WaitCursor; Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 15]].Font.Bold = true; xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].EntireColumn.ColumnWidth = 15; xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 1] = "รหัสสินค้า"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].EntireColumn.ColumnWidth = 50; xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 2] = "ชื่อสินค้า"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 3] = "ม.ค."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 4] = "ก.พ."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 5] = "มี.ค."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 6] = "ม.ย."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 7] = "พ.ค."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 8] = "มิ.ย."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 9] = "ก.ค."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 10] = "ส.ค."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 11] = "ก.ย."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 12] = "ต.ค."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 13] = "พ.ย."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 14] = "ธ.ค."; xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].EntireColumn.ColumnWidth = 15; xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 15] = "รวม"; for (int i = 0; i < dt_value.Rows.Count; i++) { xlWorkSheet.Range[xlWorkSheet.Cells[i + 2, 3], xlWorkSheet.Cells[i + 2, 5]].NumberFormat = "#,##0.00"; } //xlWorkSheet.Range[xlWorkSheet.Cells[1, (countSpan_shop * (j + 1)) + 2], xlWorkSheet.Cells[dt_shop.Rows.Count + 2, (countSpan_shop * (j + 1)) + 2]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue); for (int i = 0; i < dt_value.Rows.Count; i++) { for (int j = 0; j < dt_value.Columns.Count; j++) { xlWorkSheet.Cells[i + 2, j + 1] = dt_value.Rows[i][j].ToString(); } } string fileName = String.Empty; SaveFileDialog saveFileExcel = new SaveFileDialog(); saveFileExcel.FileName = "" + DBConnString.sDb + " สรุปมูลค่าการขายแต่ละเดือน(แยกตามสินค้า) ปี " + CmbYear.Text + " "; saveFileExcel.Filter = "Excel files (*.xls,*.xlsx)|*.xls*"; saveFileExcel.FilterIndex = 2; saveFileExcel.RestoreDirectory = true; if (saveFileExcel.ShowDialog() == DialogResult.OK) { fileName = saveFileExcel.FileName; xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); this.Cursor = Cursors.Default; } else { return; } } catch (Exception e) { MessageBox.Show(e.Message); return; } finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldcul; this.Cursor = Cursors.Default; } }
private void ImportExcel(string path) { object rarity = null; object name = null; object carat = null; object price = null; object misValue = System.Reflection.Missing.Value; excelApp = new Excel.Application(); Excel.Range range; worker = new BackgroundWorker(); worker.WorkerSupportsCancellation = true; worker.WorkerReportsProgress = true; ProgStackPanel.Visibility = System.Windows.Visibility.Visible; ProgTextCancel.Visibility = System.Windows.Visibility.Collapsed; ProgressGrid.Visibility = System.Windows.Visibility.Visible; StoneListGrid.Visibility = System.Windows.Visibility.Hidden; Import.IsEnabled = false; Export.IsEnabled = false; this.MinHeight = 500; ProgText.Text = "Importing Data..."; ProgressStone.Value = 0; worker.DoWork += delegate(object sdr, DoWorkEventArgs dw) { Excel.Application _excelApp = (Excel.Application)dw.Argument; xlWorkBook = _excelApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; List<Stone> stoneItem = new List<Stone>(); for (int row = 0; row <= range.Rows.Count; row++) { for (int col = 0; col <= range.Columns.Count; col++) { if ((range.Cells[row + 2, col + 1] as Excel.Range).Value2 != null) { if (col == 0) rarity = (range.Cells[row + 2, col + 1] as Excel.Range).Value2; if (col == 1) name = (range.Cells[row + 2, col + 1] as Excel.Range).Value2; if (col == 2) carat = (range.Cells[row + 2, col + 1] as Excel.Range).Value2; if (col == 3) price = (range.Cells[row + 2, col + 1] as Excel.Range).Value2; } else { if (col == 0) rarity = string.Empty; if (col == 1) name = string.Empty; if (col == 2) carat = string.Empty; if (col == 3) price = string.Empty; } } if (worker.CancellationPending) { ProgText.Dispatcher.Invoke(new Action(() => { ProgText.Text = "Canceling..."; })); System.Threading.Thread.Sleep(3000); dw.Cancel = true; return; } try { if (!string.IsNullOrWhiteSpace(name.ToString())) { stoneItem.Add(new Stone { Id = row + 1, name = name.ToString(), rarity = rarity.ToString(), carat = carat.ToString(), price = price.ToString(), }); } } catch (Exception) { ProgText.Dispatcher.Invoke(new Action(() => { ProgText.Text = "Invalid Template!"; })); System.Threading.Thread.Sleep(1300); worker.CancelAsync(); } worker.ReportProgress(Convert.ToInt32(((decimal)row / (decimal)range.Rows.Count) * 100)); System.Threading.Thread.Sleep(5); dw.Result = stoneItem; } }; worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args) { ProgressStone.Value = args.ProgressPercentage; }; worker.RunWorkerCompleted += delegate(object sdr, RunWorkerCompletedEventArgs rwc) { xlWorkBook.Close(true, misValue, misValue); excelApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(excelApp); ProgressGrid.Visibility = System.Windows.Visibility.Hidden; StoneListGrid.Visibility = System.Windows.Visibility.Visible; Import.IsEnabled = true; Export.IsEnabled = true; this.MinHeight = 300; if (rwc.Error != null) { MessageBox.Show(rwc.Error.Message); return; } if (!rwc.Cancelled) { List<Stone> stone = (List<Stone>)rwc.Result; for (int i = 0; i < stone.Count; i++) { stone[i].parentId = AppData.parentId; stone[i].updatedAt = DateTime.Now; stone[i].createdAt = DateTime.Now; } pawnguardDb = new PawnGuardDBDataContext(); LinqExt.Truncate(pawnguardDb.Stones); pawnguardDb.Stones.InsertAllOnSubmit(stone); pawnguardDb.SubmitChanges(); FillGrid(); page_Loaded(null, new RoutedEventArgs()); } }; worker.RunWorkerAsync(excelApp); }
public getData readData() { String name; String position; String tel; String workTel; String email; String company; getData.name.Clear(); getData.position.Clear(); getData.tel.Clear(); getData.workTel.Clear(); ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1 //excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1 // WorkSheetExcel.Cells[i, 1].Text.ToString() != "" for (int i = 3; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { name = CleanString(WorkSheetExcel.Cells[i, 1].Text.ToString()); position = CleanString(WorkSheetExcel.Cells[i, 2].Text.ToString()); tel = CleanString(WorkSheetExcel.Cells[i, 3].Text.ToString()); workTel = CleanString(WorkSheetExcel.Cells[i, 4].Text.ToString()); email = CleanString(WorkSheetExcel.Cells[i, 5].Text.ToString()); company = CleanString(WorkSheetExcel.Cells[i, 6].Text.ToString()); getData.name.Add(name); getData.position.Add(position); getData.tel.Add(tel); getData.workTel.Add(workTel); getData.email.Add(email); getData.company.Add(company); } WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя ExcelApp.Quit(); GC.Collect(); //Запись в БД dbc.ClearTable("customer"); dbc.CustomerWrite(getData.name, getData.position, getData.tel, getData.workTel, getData.email, getData.company); return getData; }
private void forotborisfailov(string[] vsS, int a1, ref int z1, ref int z2, ref int z3, ref string s, string cilka) { excelapp = new Excel.Application(); excelappworkbooks = excelapp.Workbooks; excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, cilka), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelsheets = excelappworkbook.Worksheets; excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); for (int i = 0; i < 350; i++) { if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString(); else { z3++; if (z3 == 1) z1 = 0; s = vsS[z2] + vsS[z1] + a1.ToString(); if (z1 == 25) { z2++; z1 = 0; } } if (s == "IW" + a1.ToString()) break; excelcells = excelworksheet.get_Range(s, Type.Missing); string excelnumber = excelcells.Value2; if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber); z1++; } z1 = 0; z2 = 0; z3 = 0; excelworksheet = (Excel.Worksheet)excelsheets.get_Item(2); for (int i = 0; i < 350; i++) { if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString(); else { z3++; if (z3 == 1) z1 = 0; s = vsS[z2] + vsS[z1] + a1.ToString(); if (z1 == 25) { z2++; z1 = 0; } } if (s == "IW" + a1.ToString()) break; excelcells = excelworksheet.get_Range(s, Type.Missing); string excelnumber = excelcells.Value2; if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber); z1++; } z1 = 0; z2 = 0; z3 = 0; excelappworkbook.Close(); excelappworkbooks.Close(); excelapp.Workbooks.Close(); }
/// <summary> /// Сохраняем файлы в формате пдф и ексель в папки /// </summary> public void MakeExcelFile(decimal moneyAtStart, decimal moneyBalance) { string dateFile; //Повна назва файлу для зберігання try { CreateFolderForSavingFile(excelFilePath, out dateFile); oApp = new Excel.Application(); oBook = oApp.Workbooks.Add(); oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1); // Заповнюємо excel файл данними CreateFullFile(); InputInformationFields(moneyAtStart, moneyBalance); // Зберігаємо файл в форматі екселя oBook.SaveAs(dateFile + ".xlsx"); CreateFolderForSavingFile(pdfFilePath, out dateFile); // Додаткова перевірка при зберіганні pdf файла на встановлене розширення в Office try { oBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, dateFile + ".pdf"); } catch (ArgumentException) { MessageBox.Show("Помилка при збереженні PDF файла. Перевірте чи у Вас встановлене розширення в " + " Microsoft Office для збереження файлів в форматі PDF/XPS.", "Помилка при збереженні PDF", MessageBoxButtons.OK, MessageBoxIcon.Warning); } catch (Exception) { MessageBox.Show("Помилка при збереженні pdf файла."); } MessageBox.Show("Дані збережено!", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error"); } finally { oBook.Close(); oApp.Quit(); } }
void bw_DoWork(object sender, DoWorkEventArgs e) { try { app = new Microsoft.Office.Interop.Excel.Application(); theBook = app.Workbooks.Open(_fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка при открытии книги"); } try { _sheets.Clear(); foreach (Excel.Worksheet w in theBook.Worksheets) { _sheets.Add(w.Name); } // Закрываем Excel theBook.Close(false, Type.Missing, Type.Missing); app.Quit(); theBook = null; app = null; GC.Collect(); } catch (Exception ex) { MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message); } }
private void BtnImportExcel_Click(object sender, EventArgs e) { // Open the file. OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Ms Excel 2007 files (*.xlsx)|*.xlsx|Ms Excel 2003 files (*.xls)|*.xls|Text Files (*.txt)|*.txt"; dialog.InitialDirectory = "C:"; dialog.Title = "Open file"; if (dialog.ShowDialog() == DialogResult.OK) { fileName = dialog.FileName; // Get file's extension. FileInfo fileInfo = new FileInfo(fileName); fileExtension = fileInfo.Extension; } // Initialize the emails list. emails = new List <string>(); if (fileExtension == ".txt") { string line; StreamReader file = new StreamReader(fileName); while ((line = file.ReadLine()) != null) { emails.Add(line); } } else { Excel.Application excelApp = new Excel.Application(); // Read the excel file. if (excelApp != null) { Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1]; Excel.Range excelRange = excelWorksheet.UsedRange; // Get rows and columns count. int rows = excelRange.Rows.Count; int cols = excelRange.Columns.Count; for (int i = 1; i <= rows; i++) { for (int j = 1; j <= cols; j++) { Excel.Range range = (excelWorksheet.Cells[i + 1, 1] as Excel.Range); string cellValue = range.Value.ToString() ?? string.Empty; // Add to list. emails.Add(cellValue); } } excelWorkbook.Close(); excelApp.Quit(); } } // Enable the other buttons. btnFindDuplicates.Enabled = true; btnExportEmailsWithoutDuplicates.Enabled = true; btnExportPrivateBusinessEmails.Enabled = true; }
private void ExportToExcel_gp() { if (dt_gp.Rows.Count == 0) { MessageBox.Show("ไม่มีข้อมูล"); return; } var Oldcul = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat = Oldcul.DateTimeFormat; try { this.Cursor = Cursors.WaitCursor; Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int z = 0; xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 12]].Font.Bold = true; xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].EntireColumn.ColumnWidth = 10; xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 1] = "วันที่"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].EntireColumn.ColumnWidth = 13; xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 2] = "เลขที่ขาย"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].EntireColumn.ColumnWidth = 15; xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 3] = "รหัสสินค้า"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].EntireColumn.ColumnWidth = 60; xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 4] = "ชื่อสินค้า"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].EntireColumn.ColumnWidth = 10; xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 5] = "จำนวน"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].EntireColumn.ColumnWidth = 10; xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 6] = "หน่วยนับ"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 7] = "ราคาขาย"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 8] = "ราคาทุน"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 9] = "กำไรขั้นต้น"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].EntireColumn.ColumnWidth = 10; xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 10] = "GP%"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 11] = "ราคาขายรวม"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].EntireColumn.ColumnWidth = 12; xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 12] = "ราคาทุนรวม"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].EntireColumn.ColumnWidth = 20; xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 13] = "กำไร(ขาดทุน) รวม"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].EntireColumn.ColumnWidth = 15; xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 14] = "ชื่อผู้ขาย"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].EntireColumn.ColumnWidth = 20; xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 15] = "ชื่อลูกค้า"; xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].EntireColumn.ColumnWidth = 20; xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 16] = gvGP.Columns[15].HeaderText; xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].Merge(); xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].EntireColumn.ColumnWidth = 20; xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells[1, 17] = gvGP.Columns[16].HeaderText; object[,] oo = new object[dt_gp.Rows.Count, dt_gp.Columns.Count]; for (i = 0; i < dt_gp.Rows.Count; i++) { for (int j = 0; j < dt_gp.Columns.Count; j++) { string value = ""; try { decimal num = 0; DateTime dateTime = new DateTime(); value = dt_gp.Rows[i][j].ToString(); if (DateTime.TryParse(value, out dateTime) && !Decimal.TryParse(value, out num)) { oo[i, j] = (dateTime).ToOADate(); //xlWorkSheet.Cells[i + 2, j + 1] = (dateTime).ToOADate(); //xlWorkSheet.get_Range("A" + (i + 2), "A" + (j + 1)).NumberFormat = "dd-mm-yyyy"; //xlWorkSheet.get_Range("A" + (i + 2), "A" + (j + 1)).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } else { oo[i, j] = dt_gp.Rows[i][j].ToString(); //xlWorkSheet.Cells[i + 2, j + 1] = dt_gp.Rows[i][j].ToString(); } } catch (Exception ex) { } } } string excelRange = string.Format("A2:{0}{1}", findColumnLetter(dt_gp.Columns.Count), dt_gp.Rows.Count + 1); xlWorkSheet.get_Range(excelRange, Type.Missing).Value2 = oo; xlWorkSheet.get_Range("A2", "A" + dt_gp.Rows.Count + 1).NumberFormat = "dd-mm-yyyy"; xlWorkSheet.get_Range("G2", "G" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00"; xlWorkSheet.get_Range("H2", "H" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00"; xlWorkSheet.get_Range("I2", "I" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00"; string fileName = String.Empty; SaveFileDialog saveFileExcel = new SaveFileDialog(); saveFileExcel.FileName = "" + DBConnString.sDb + " GP ตั้งแต่วันที่ " + dateTimePicker1.Text + " ถึง " + dateTimePicker2.Text + " "; saveFileExcel.Filter = "Excel files (*.xls,*.xlsx)|*.xls*"; saveFileExcel.FilterIndex = 2; saveFileExcel.RestoreDirectory = true; if (saveFileExcel.ShowDialog() == DialogResult.OK) { fileName = saveFileExcel.FileName; xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); this.Cursor = Cursors.Default; MessageBox.Show("Export " + saveFileExcel.FileName + " Complete."); } else { return; } } catch (Exception e) { MessageBox.Show(e.Message); return; } finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldcul; this.Cursor = Cursors.Default; } }
private void SaveProj_Click(object sender, RoutedEventArgs e) { ExcelApp = new Excel.Application(); ExcelApp.Visible = true; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Processes"]; List<string> row = new List<string>(); int n = 6; int count = 0; row = new List<string>(); row.Add(Convert.ToString(prjSelect2.SelectedValue)); row.Add(prjHour.Text); int statSpendHour1=0; int statOstHour1=0; float statOstDay1=0; getData = rwList.readData(); for (int i = 0; i < getData.prj.Count; i++) { notes.Add(new Note() { dateT = getData.dateT[i], project = getData.prj[i], description = getData.descr[i], taskTime = getData.taskTime[i] }); } //notes = rwList.readData(); for (int i = 0; i < notes.Count; i++) { if (Convert.ToString(prjSelect2.SelectedValue) == notes[i].project) { statSpendHour1 = statSpendHour1 + Convert.ToInt16(notes[i].taskTime); } //statSpendHour1 = statSpendHour1 + Convert.ToInt16(getData.taskTime[i]); } statOstHour1 = Convert.ToInt32(prjHour.Text) - statSpendHour1; statOstDay1 = statOstHour1 / 8; row.Add(Convert.ToString(statSpendHour1)); row.Add(Convert.ToString(statOstHour1)); row.Add(Convert.ToString(statOstDay1)); Mas2.Add(row); count++; for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { row = new List<string>(); for (int j = 1; j < n; j++) row.Add(WorkSheetExcel.Cells[i, j].Text.ToString()); //строка массива заполняется просто суммой i и j Mas2.Add(row); //строка добавляется в массив count++; } for (int i = 0; i<count; i++) { RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 1]; RangeExcel.Value = Mas2[i][0]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 2]; RangeExcel.Value = Mas2[i][1]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 3]; RangeExcel.Value = Mas2[i][2]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 4]; RangeExcel.Value = Mas2[i][3]; RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 5]; RangeExcel.Value = Mas2[i][4]; } WorkBookExcel.Close(true, Type.Missing, Type.Missing); ExcelApp.Quit(); GC.Collect(); this.Close(); }
private static void SynctoExcel(string excel, string json) { List<Sheets> myMessage = JsonConvert.DeserializeObject<List<Sheets>>(File.ReadAllText(@FileLocation)); MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(@FileLocation); MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"]; // foreach (var file in myMessage) { foreach (Rows r in file.Rows) { var snumber = r.SheetLine; MySheet.Cells[snumber, 8] = r.RMuri; MySheet.Cells[snumber, 9] = r.OriginalFileCount; MySheet.Cells[snumber, 10] = r.RMFileCount; } } MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); }
private void LoadExcelFile() { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; opisLabel.Text += "\nOtwieranie pliku excel..."; Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; if (rowCount > 5 && colCount > 2) { opisLabel.Text += "\nOdczytywanie informacji nagłówkowych..."; object[,] values = (object[, ])xlRange.Value2; opisLabel.Text += "\nPrzetwarzanie danych nagłówkowych..."; DBRepository db = new DBRepository(); progressBar.Maximum = rowCount; progressBar.Value = 0; opisLabel.Text += "\nOdczytywanie " + (rowCount - 5) + " wierszy danych..."; for (int i = 6; i <= rowCount; i++) { String nazwaSklepu = values[i, 1].ToString(); String nazwaEmail = values[i, 2].ToString(); String email = values[i, 3].ToString(); String idSklepu = ""; String result = ""; if (db.SklepImport_AddSklep(nazwaSklepu, ref idSklepu, ref result)) { if (db.SklepImport_AddMail(idSklepu, nazwaEmail, email, ref result)) { czyDodano = true; } else { MessageBox.Show("Wystąpił błąd podczas dodawania adresu email '" + email + "' dla sklepu '" + nazwaSklepu + "'" + nazwaSklepu + "':\n" + result, "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Wystąpił błąd podczas dodawania sklepu '" + nazwaSklepu + "':\n" + result, "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error); } progressBar.Value++; } } else { MessageBox.Show("Wczytywany plik ma 0 wierszy lub 0 kolumn.", "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error); } opisLabel.Text += "\nZamykanie pliku excel..."; xlWorkbook.Close(false, null, null); xlApp.Quit(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; }
private static void ProcessClassFolders() { string iRow = null; try { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(@FileLocation); MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"]; MySheetV = (Excel.Worksheet)MyBook.Sheets["Variables"]; Console.WriteLine("Connected to Spread Sheet"); //Console.ReadLine(); Console.WriteLine("Processing, please wait. If its a large SS then go make a coffee."); ProcessfromRow = Convert.ToInt64(MySheetV.Cells[11, 2].Value); ProcesstoRow = Convert.ToInt64(MySheetV.Cells[12, 2].Value); string str1 = null, str2 = null, str3 = null, str4 = null, str5 = null; bool createnew = false; long fileconturi = 0, recuri = 0; //EmpList.Add(emp); List<Sheets> sheets = new List<Sheets>(); Sheets sheet = new Sheets(); sheet.SheetName = FileLocation; List<Rows> rows = new List<Rows>(); Rows r = new Rows(); for (var i = 2; i < ProcesstoRow + 1; i++) { if(i==350) { var dsd = "sfdsf"; } iRow = i.ToString(); //First child level bool bLevel3New = false; createnew = false; if (MySheet.Cells[i, 1].Value2 != null) { str1 = MySheet.Cells[i, 1].Value.ToString(); createnew = true; } if (MySheet.Cells[i, 2].Value2 != null) { str2 = MySheet.Cells[i, 2].Value.ToString(); createnew = true; } if (MySheet.Cells[i, 3].Value2 != null) { str3 = MySheet.Cells[i, 3].Value.ToString(); createnew=true; bLevel3New = true; } if(createnew) { r = new Rows(); r.level1 = str1; r.level2 = str2; r.level3 = str3; r.SheetLine = i; rows.Add(r); } // New if (MySheet.Cells[i, 4].Value2 != null) { if (bLevel3New == true) { List<Folder> lstFolder = new List<Folder>(); rows.LastOrDefault().Folders = lstFolder; } str4 = MySheet.Cells[i, 4].Value.ToString(); //int lvl4LoopCount = 0; //Check to see if the group identifyer is not blank and has a g if (MySheet.Cells[i, 12].value2 != null && MySheet.Cells[i, 12].value.ToString() == "g") { Console.WriteLine("Grouped string found in level 4"); List<string> lstLvl4 = str4.Split('/').ToList(); foreach (var kkk in lstLvl4.Where(x => x.Length > 2)) { Folder fol = new Folder(); fol.folder = kkk.Trim(); fol.SheetLine = i; rows.LastOrDefault().Folders.Add(fol); //lstFolder.Add(fol); fileconturi = recuri; if (MySheet.Cells[i, 5].Value2 != null) { str5 = MySheet.Cells[i, 5].Value.ToString(); List<Subfolder> lstsubfolder = new List<Subfolder>(); if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); sf.SheetLine = i; lstsubfolder.Add(sf); } fol.SubFolders = lstsubfolder; } else { Subfolder sf = new Subfolder(); sf.subfolder = str5.Trim(); sf.SheetLine = i; lstsubfolder.Add(sf); fol.SubFolders = lstsubfolder; } } } } else { Folder fol = new Folder(); fol.folder = str4; fol.SheetLine = i; fileconturi = recuri; if (MySheet.Cells[i, 5].Value2 != null) { List<Subfolder> lstsf = new List<Subfolder>(); str5 = MySheet.Cells[i, 5].Value.ToString(); //r.Subfolder = str5; if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); sf.SheetLine = i; lstsf.Add(sf); } } else { Subfolder sf = new Subfolder(); sf.subfolder = str5; sf.SheetLine = i; if (MySheet.Cells[i, 7].value2 != null) { sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } lstsf.Add(sf); } fol.SubFolders = lstsf; } else { if (MySheet.Cells[i, 7].value2 != null) { fol.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } } rows.LastOrDefault().Folders.Add(fol); } } else { if (MySheet.Cells[i, 5].Value2 != null) { if (rows.LastOrDefault().Folders.LastOrDefault().SubFolders==null) { List<Subfolder> lstsf = new List<Subfolder>(); rows.LastOrDefault().Folders.LastOrDefault().SubFolders = lstsf; } str5 = MySheet.Cells[i, 5].Value.ToString(); if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); sf.SheetLine = i; if (MySheet.Cells[i, 7].value2 != null) { sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } rows.LastOrDefault().Folders.LastOrDefault().SubFolders.Add(sf); } } else { Subfolder sf = new Subfolder(); sf.subfolder = str5.Trim(); sf.SheetLine = i; if (MySheet.Cells[i, 7].value2 != null) { sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } //lstsubfolder.Add(sf); //fol.SubFolders = lstsubfolder; //rows.LastOrDefault().Folders.LastOrDefault().SubFolders = lstsubfolder; rows.LastOrDefault().Folders.LastOrDefault().SubFolders.Add(sf); } } } //End New } sheet.Rows = rows; sheets.Add(sheet); // string json = JsonConvert.SerializeObject(sheets.ToArray()); //FileLocation var loc = Path.GetDirectoryName(FileLocation); System.IO.File.WriteAllText(@loc + "\\SSprocessTIP.json", json); // MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); Console.WriteLine("Successful in building Json file."); Repeatmenu(); //} } catch (Exception exc) { MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); // Console.WriteLine("Error: " + exc.Message.ToString()+" - "+iRow); return; } }
public static void Main(string[] args) { if (args == null) { throw new ArgumentNullException(nameof(args)); } var xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = null; try { if (args.Length != 2) { Console.WriteLine("usage ExcelToXml <path to xslx> <output path>"); return; } if (!System.IO.File.Exists(args[0])) { Console.WriteLine("File " + args[0] + " does not exist."); return; } if (!System.IO.Directory.Exists(args[1])) { Console.WriteLine("The directory " + args[1] + " does not exist."); return; } xlWorkbook = xlApp.Workbooks.Open(args[0]);; Console.WriteLine("Opening file " + args[0] + " for input..."); var x = xlWorkbook.Worksheets.Count; Excel.Worksheet activeSheet = xlWorkbook.ActiveSheet; var count = activeSheet.Rows.Count; // Get headers for from the spreadsheet. var headers = GetHeaders(activeSheet); for (int i = 2; i < count; i++) { var map = CreateRowMapping(activeSheet, headers, i, out var moreRows); if (!moreRows) { break; } BuildXml(map, args[1], i); } } finally { Console.WriteLine("Preparing to shutdown Excel"); xlWorkbook?.Close(); xlApp.Quit(); Console.WriteLine("Excel should be shut down"); } }
private static void processFolders() { try { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(@FileLocation); MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"]; MySheetV = (Excel.Worksheet)MyBook.Sheets["Variables"]; Console.WriteLine("Connected to Spread Sheet"); Console.ReadLine(); ProcessfromRow = Convert.ToInt64(MySheetV.Cells[11, 2].Value); ProcesstoRow = Convert.ToInt64(MySheetV.Cells[12, 2].Value); string str1 = null, str2 = null, str3 = null, str4 = null, str5 = null; bool createnew = false; long fileconturi = 0, recuri = 0; int lvl1Value = 10, lvl2Value = 10; //EmpList.Add(emp); List<Sheets> sheets = new List<Sheets>(); Sheets sheet = new Sheets(); sheet.SheetName = FileLocation; List<Rows> rows = new List<Rows>(); Rows r = new Rows(); for (var i = 2; i < ProcesstoRow+1; i++) { //First child level if (MySheet.Cells[i, y].Value2 != null) { str1 = MySheet.Cells[i, y].Value.ToString(); createnew = true; lvl1Value = lvl1Value + 10; //lvl2Value = 10; //lvl3Value = 10; y = y + 1; } else { y = y + 1; } if (MySheet.Cells[i, y].Value2 != null) { str2 = MySheet.Cells[i, y].Value.ToString(); if (createnew) { lvl2Value = lvl2Value + 10; //lvl3Value = 10; } y = y + 1; } else { y = y + 1; } if (MySheet.Cells[i, y].Value2 != null) { str3 = MySheet.Cells[i, y].Value.ToString(); if (createnew) { r = new Rows(); r.level1 = str1; r.level2 = str2; r.level3 = str3; } y = y + 1; } else { y = y + 1; r = rows.LastOrDefault(); } List<Folder> lstFolder = new List<Folder>(); if (MySheet.Cells[i, 4].Value2 != null) { str4 = MySheet.Cells[i, 4].Value.ToString(); //Check to see if the group identifyer is not blank and has a g if (MySheet.Cells[i, 12].value2 != null && MySheet.Cells[i, 12].value.ToString() == "g") { Console.WriteLine("Grouped string found in level 4"); List<string> lstLvl4 = str4.Split('/').ToList(); foreach (var kkk in lstLvl4.Where(x => x.Length > 2)) { Folder fol = new Folder(); fol.folder = kkk.Trim(); lstFolder.Add(fol); fileconturi = recuri; if (MySheet.Cells[i, 5].Value2 != null) { str5 = MySheet.Cells[i, 5].Value.ToString(); List<Subfolder> lstsubfolder = new List<Subfolder>(); if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); lstsubfolder.Add(sf); } fol.SubFolders = lstsubfolder; } else { Subfolder sf = new Subfolder(); sf.subfolder = str5.Trim(); lstsubfolder.Add(sf); fol.SubFolders = lstsubfolder; } } } } else { Folder fol = new Folder(); fol.folder = str4; fileconturi = recuri; if (MySheet.Cells[i, 5].Value2 != null) { List<Subfolder> lstsf = new List<Subfolder>(); str5 = MySheet.Cells[i, 5].Value.ToString(); //r.Subfolder = str5; if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); lstsf.Add(sf); //recsuburi = CreateNewRMSubFolder(recuri, lll.Trim(), db, i); } } else { Subfolder sf = new Subfolder(); sf.subfolder = str5; if (MySheet.Cells[i, 7].value2 != null) { sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } lstsf.Add(sf); //fol.SubFolders=lstsf; // //recsuburi = CreateNewRMSubFolder(recuri, str5, db, i); } fol.SubFolders = lstsf; } else { if (MySheet.Cells[i, 7].value2 != null) { fol.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } } lstFolder.Add(fol); } r.Folders = lstFolder; } y = 1; if (str4 != null) { MySheet.Cells[i, 8] = fileconturi.ToString(); } else { //Remove for basic testing //long Newuri = CreateNewRMfolder(str1, str2, str3, str4, str5, str3Title); // //Console.WriteLine("New folder created (Uri: " + fileconturi.ToString() + ")"); } r.SheetLine = i; //7 //r.RMuri = fileconturi.ToString(); //r.Timestamp = DateTime.Now; rows.Add(r); } sheet.Rows = rows; sheets.Add(sheet); // string json = JsonConvert.SerializeObject(sheets.ToArray()); //FileLocation var loc = Path.GetDirectoryName(FileLocation); System.IO.File.WriteAllText(@loc+"\\SSprocessTIP.json", json); // MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); //} } catch (Exception exc) { MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); // Console.WriteLine("Error: "+exc.Message.ToString()); return; } }
/// <summary> /// 方法,导出DataTable中的数据到Excel文件 /// </summary> /// <remarks> /// add com "Microsoft Excel 11.0 Object Library" /// using Excel=Microsoft.Office.Interop.Excel; /// using System.Reflection; /// </remarks> /// <param name="dt">DataTable</param> /// <param name="strOutFileName">设定导出的文件名称</param> /// <param name="strsHeadName">导出的列的的HeadName, 如果为null,则导出全部列</param> /// <returns></returns> public static bool SaveDtToExcel ( DataTable dt , string strOutFileName , string [] strsHeadName , string [] strsFldName, string strDecimalFormat="" ) { int [] nsLineNo = null; if ( dt.Rows.Count <= 0 ) { MessageBox.Show ( "没有可以导出的数据!" , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information ); return false; } #region 验证可操作性 //申明保存对话框 SaveFileDialog dlg = new SaveFileDialog (); //默然文件后缀 dlg.FileName = strOutFileName; dlg.DefaultExt = "xls "; //文件后缀列表 dlg.Filter = "EXCEL文件(*.XLS)|*.xls "; //默然路径是系统当前路径 dlg.InitialDirectory = Directory.GetCurrentDirectory (); //打开保存对话框 if ( dlg.ShowDialog () == DialogResult.Cancel ) return false; //返回文件路径 string fileNameString = dlg.FileName; //验证strFileName是否为空或值无效 if ( fileNameString.Trim () == " " ) return false; //定义表格内数据的行数和列数 int rowscount = dt.Rows.Count; int colscount = dt.Columns.Count; //行数必须大于0 if ( rowscount <= 0 ) { MessageBox.Show ( "没有数据可供保存 " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information ); return false; } //列数必须大于0 if ( colscount <= 0 ) { MessageBox.Show ( "没有数据可供保存 " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information ); return false; } //行数不可以大于65536 if ( rowscount > 65536 ) { MessageBox.Show ( "数据记录数太多(最多不能超过65536条),不能保存 " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information ); return false; } //列数不可以大于255 if ( colscount > 255 ) { MessageBox.Show ( "数据记录行数太多,不能保存 " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information ); return false; } //验证以fileNameString命名的文件是否存在,如果存在删除它 FileInfo file = new FileInfo ( fileNameString ); if ( file.Exists ) { try { file.Delete (); } catch ( Exception error ) { MessageBox.Show ( error.Message , "删除失败 " , MessageBoxButtons.OK , MessageBoxIcon.Warning ); return false; } } #endregion #region //取得要导出的列的HeadName. //如果没有指定特定的列,则默认转换 可见的 并且 有HeadName 的列。(控件列不用导出,控件列一般没有HeadName) if ( strsHeadName == null ) { MessageBox.Show ( "没有指定字段名称!" , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information ); return false; } //取得所有要执行的行,(可能用户并不是要导出所有行,而是通过checkbox选择要导出的行) //如果没有指定特定的行,则默认转出全部的行 if ( nsLineNo == null ) { nsLineNo = new int [dt.Rows.Count]; for ( int row = 0 ; row <= dt.Rows.Count - 1 ; row++ ) { nsLineNo [row] = row; } } #endregion Excel.Application objExcel = null; Excel.Workbook objWorkbook = null; Excel.Worksheet objsheet = null; try { //申明对象 objExcel = new Microsoft.Office.Interop.Excel.Application (); objWorkbook = objExcel.Workbooks.Add ( Missing.Value ); objsheet = (Excel.Worksheet) objWorkbook.ActiveSheet; //设置EXCEL不可见 objExcel.Visible = false; //向Excel中写入表格的表头 Excel.Range rag = objsheet.get_Range ( objsheet.Cells [1 , 1] , objsheet.Cells [1 , strsHeadName.Length] ); rag.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; int displayColumnsCount = 1; foreach ( string strHeadName in strsHeadName ) { objExcel.Cells [1 , displayColumnsCount] = strHeadName; displayColumnsCount++; } //设置进度条 //tempProgressBar.Refresh(); //tempProgressBar.Visible = true; //tempProgressBar.Minimum=1; //tempProgressBar.Maximum=dgv.RowCount; //tempProgressBar.Step=1; int nRow1 = 2 , nRow2 = nRow1 + nsLineNo.Length; int nColSel = 1; DataColumn col ; foreach ( string strFldName in strsFldName ) { rag = objsheet.get_Range ( objsheet.Cells [nRow1 , nColSel] , objsheet.Cells [nRow2 , nColSel] ); col = dt.Columns[strFldName]; if (col == null) { string strErr = String.Format("数据库表列名称'{0}'不存在!",strFldName); FF.Ctrl.MsgBox.ShowWarn(strErr); return false ; } switch ( col.DataType.ToString () ) { case "System.String": rag.NumberFormatLocal = "@"; break; case "System.DateTime": rag.NumberFormatLocal = "yyyy-MM-dd HH:mm" ; break; case "System.Decimal": rag.NumberFormatLocal = String.IsNullOrEmpty( strDecimalFormat ) ? "0.00" : strDecimalFormat ; //默认是"0.00"; rag.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; break; //可以根据自己的需要扩展。 default: //rag.NumberFormatLocal = "G/通用格式"; break; } nColSel ++; } //向Excel中逐行逐列写入表格中的数据 int nCol , row; int nExcelCol , nExcelRow = 2; //Execl的行列都从1开始起算。第一行是列名,数据从第二行开始写入。 for ( int i = 0 ; i <= nsLineNo.Length - 1 ; i++ ) { //tempProgressBar.PerformStep(); row = nsLineNo [i]; nExcelCol = 1; //Execl的行列都从1开始起算。第一行是列名,数据从第二行开始写入。 foreach ( string strFldName in strsFldName ) { try { //nCol = dgv.Columns [strHeadName].Index; //objExcel.Cells [nExcelRow , nExcelCol++] = dgv.Rows [row].Cells [nCol].Value.ToString ().Trim (); objExcel.Cells [nExcelRow , nExcelCol++] = dt.Rows [row] [strFldName].ToString ().Trim (); } catch ( Exception ) { return false ; } } nExcelRow++; } //隐藏进度条 //tempProgressBar.Visible = false; //保存文件 objWorkbook.SaveAs ( fileNameString , Missing.Value , Missing.Value , Missing.Value , Missing.Value , Missing.Value , Excel.XlSaveAsAccessMode.xlShared , Missing.Value , Missing.Value , Missing.Value , Missing.Value , Missing.Value ); } catch ( Exception error ) { MessageBox.Show ( error.Message , "警告 " , MessageBoxButtons.OK , MessageBoxIcon.Warning ); return false; } finally { //关闭Excel应用 if ( objWorkbook != null ) objWorkbook.Close ( Missing.Value , Missing.Value , Missing.Value ); if ( objExcel.Workbooks != null ) objExcel.Workbooks.Close (); if ( objExcel != null ) objExcel.Quit (); objsheet = null; objWorkbook = null; objExcel = null; } MessageBox.Show ( fileNameString + "\n\n导出完毕! " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information ); return true; }
public void GenerateBubbleChart(double chartWidth, double chartHeight, double bubbleScaleFactor, string dataFilePath, string outputFilePath, int worksheetToOpen) { SetStatus("Startar Excel", 0); Excel.Application excelApp = null; Excel.Workbook excelWorkbook = null; Excel.Worksheet excelWorksheet = null; Excel.Workbook sourceDataWorkbook = null; try { excelApp = new Excel.Application(); excelWorkbook = excelApp.Workbooks.Add(1); excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1]; sourceDataWorkbook = excelApp.Workbooks.Open(dataFilePath); SetStatus("Skapar nytt diagram", 1); var bubbleChart = CreateNewBubbleChart(excelWorksheet, chartWidth, chartHeight, bubbleScaleFactor); SetStatus("Ritar ut datapunkter", 2); AddDataPoints(bubbleChart, sourceDataWorkbook.Sheets[worksheetToOpen]); SetStatus("Lägger till diagramförklaring", 3); AddLegendAndClearDummySeriesNames(bubbleChart); SetStatus("Beräknar placering av textrutor", 4); SpreadOutDataLabels(bubbleChart); SetStatus("Sparar", 5); excelWorkbook.SaveAs(Filename: outputFilePath); Process.Start(outputFilePath); SetStatus("Success!", 6); } finally { if (excelWorkbook != null) { excelWorkbook.Saved = true; // Avoid prompting user to save excelWorkbook.Close(); } if (sourceDataWorkbook != null) { sourceDataWorkbook.Saved = true; // Avoid prompting user to save sourceDataWorkbook.Close(); } excelApp?.Quit(); if (excelWorksheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet); } if (excelWorkbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); } if (sourceDataWorkbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sourceDataWorkbook); } if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } } }