/// <summary> /// Save MRU item list with values /// </summary> private void ExcelImportWizard_FormClosing(object sender, FormClosingEventArgs e) { if (_mus.MRUItems == null) { _mus.MRUItems = new List <string>(); } else { _mus.MRUItems.Clear(); } foreach (var item in FileSelectEdit.Properties.Items) { _mus.MRUItems.Add(item.ToString()); } _mus.Save(); if (ObjectSpace.Session.InTransaction) { ObjectSpace.Session.RollbackTransaction(); } if (ExcelDocument != null) { ExcelDocument.Close(); } }
/// <summary> /// Make a best-effort at closing - it'll throw if it's already been disposed, no need to cause a scene. /// </summary> private void CloseExcelDocumentNoThrow() { if (ExcelDocument != null) { try { ExcelDocument.Close(); } catch { } ExcelDocument = null; } }
public void Complete() { //save all parts ExcelStyleSheet.Save(); ExcelSharedStringsTable.SharedStringTable.Save(); ExcelWorkSheetPart.Worksheet.Save(); if (ExcelDocument != null) { ExcelDocument.Close(); } }
//stuff that happens after file is selected private void FileSelectEdit_EditValueChanged(object sender, EventArgs e) { var edit = (sender as MRUEdit); if (edit == null) { return; } if (string.IsNullOrEmpty(edit.Text) || !new FileInfo(edit.Text).Exists) { SheetSelectEdit.Properties.DataSource = null; SheetSelectEdit.EditValue = null; } else { try { ExcelDocument = SpreadsheetDocument.Open(edit.Text, false); var sheets = ExcelDocument.Sheets() .Select(p => p.OXmlSheet.Name) .ToList(); SheetSelectEdit.Properties.DataSource = sheets; if (sheets.Count == 1) { SheetSelectEdit.EditValue = sheets.FirstOrDefault(); } ImportMapDescriptionEdit.Text = (new FileInfo(edit.Text)).Name; } catch { if (ExcelDocument != null) { ExcelDocument.Close(); } throw; } } var b = SheetSelectEdit.Properties.DataSource != null; SheetSelectEdit.Enabled = b; welcomeWizardPage1.AllowNext = b; }
public static void ExportLinesToExcel( Form form, ListView list) { // 询问文件名 SaveFileDialog dlg = new SaveFileDialog(); dlg.Title = "请指定要输出的 Excel 文件名"; dlg.CreatePrompt = false; dlg.OverwritePrompt = true; // dlg.FileName = this.ExportExcelFilename; // dlg.InitialDirectory = Environment.CurrentDirectory; dlg.Filter = "Excel 文件 (*.xlsx)|*.xlsx|All files (*.*)|*.*"; dlg.RestoreDirectory = true; if (dlg.ShowDialog() != DialogResult.OK) { return; } ExcelDocument doc = null; // Sheet sheet = null; int _lineIndex = 0; doc = ExcelDocument.Create(dlg.FileName); doc.NewSheet("Sheet1"); int nColIndex = 0; foreach (ColumnHeader header in list.Columns) { doc.WriteExcelCell( _lineIndex, nColIndex++, header.Text, true); } _lineIndex++; Cursor oldCursor = form.Cursor; form.Cursor = Cursors.WaitCursor; for (int i = 0; i < list.SelectedIndices.Count; i++) { int index = list.SelectedIndices[i]; ListViewItem item = list.Items[index]; List <CellData> cells = new List <CellData>(); nColIndex = 0; foreach (ListViewItem.ListViewSubItem subitem in item.SubItems) { cells.Add( new CellData( nColIndex++, subitem.Text, true, 0) ); } doc.WriteExcelLine( _lineIndex, cells, WriteExcelLineStyle.None); _lineIndex++; } doc.SaveWorksheet(); if (doc != null) { doc.Close(); doc = null; } form.Cursor = oldCursor; }
/// <summary> /// Create and get a spreadsheet as a serialized string based on the Matrix /// </summary> /// <param name="theMatrix"></param> /// <param name="rowLists"></param> /// <param name="lngIsoCode"></param> /// <param name="ci"></param> /// <returns></returns> internal string GetXlsx(Matrix theMatrix, List <List <XlsxValue> > rowLists, string lngIsoCode = null, CultureInfo ci = null) { ExcelDocument xl = new ExcelDocument(); xl.CreateSpreadsheet(); Specification spec = theMatrix.GetSpecFromLanguage(lngIsoCode); if (spec == null) { spec = theMatrix.MainSpec; } //First we create a contents page. This is built up piecemeal List <XlsxValue> line; List <List <XlsxValue> > matrix = new List <List <XlsxValue> >(); //some blank lines first for (int i = 0; i < 7; i++) { line = new List <XlsxValue>(); int headerId = i == 0 ? 1 : 0; for (int j = 0; j < 1; j++) { XlsxValue xval = new XlsxValue() { Value = "", StyleId = 0, DataType = CellValues.String }; line.Add(xval); } matrix.Add(line); } //Now create the actual entries in the contents page line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, CellWidth = 20, Value = Label.Get("xlsx.table", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, CellWidth = 20, Value = "", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.code", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Code, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = spec.Contents, StyleId = 0 }); matrix.Add(line); string lngCulture = Utility.GetUserAcceptLanguage(); DateTime dtLast; string dateString = ""; if (theMatrix.CreationDate.Length > 15) { dtLast = new DateTime(Convert.ToInt32(theMatrix.CreationDate.Substring(0, 4)), Convert.ToInt32(theMatrix.CreationDate.Substring(4, 2)), Convert.ToInt32(theMatrix.CreationDate.Substring(6, 2)), Convert.ToInt32(theMatrix.CreationDate.Substring(9, 2)), Convert.ToInt32(theMatrix.CreationDate.Substring(12, 2)), 0); dateString = dtLast.ToString(ci != null ? ci : CultureInfo.InvariantCulture); } else { dateString = theMatrix.CreationDate; } line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.last-updated", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = dateString, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.note", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = spec.Notes != null ? String.Join(" ", spec.Notes) : spec.NotesAsString != null ? spec.NotesAsString : "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.url", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Configuration_BSO.GetCustomConfig("url.application") + "/" + Utility.GetCustomConfig("APP_COOKIELINK_RELEASE") + '/' + theMatrix.Release.RlsCode.ToString(), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.product", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.code", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.PrcCode, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.PrcValue, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.contacts", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.GrpContactName, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.email", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.GrpContactEmail, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.phone", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.GrpContactPhone, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.copyright", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.code", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Copyright.CprCode, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Copyright.CprValue, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.url", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Copyright.CprUrl, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.properties", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.official-statistics", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.IsOfficialStatistic ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.exceptional", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.RlsExceptionalFlag ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.archived", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.RlsArchiveFlag ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.analytical", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.RlsAnalyticalFlag ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.dependency", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.RlsDependencyFlag ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.language", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.iso-code", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = spec.Language, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.iso-name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = new Language_BSO().Read(spec.Language).LngIsoName, StyleId = 0 }); matrix.Add(line); //We've created the 2D object to represent the contents, so now we can create a worksheet from that: xl.InsertDataWorksheet(matrix, Label.Get("About", lngIsoCode), OrientationValues.Landscape, true); //On the worksheet we've just created, add an image to the top left hand corner xl.AddImage(Configuration_BSO.GetCustomConfig("url.logo"), Label.Get("About", lngIsoCode), 1, 1); //Create a second worksheet based on the Matrix contents xl.InsertDataWorksheet(theMatrix.GetMatrixSheet(null, false, 1), theMatrix.Code, OrientationValues.Landscape, true); //cleanup xl.Close(); //Test option...get a local version of the xlsx file //xl.SaveToFile(@"C:\nok\Schemas\" + theMatrix.Code + ".xlsx"); //return the serialized version of the spreadsheet return(xl.SerializeSpreadsheetFromByteArrayBase64()); }