/// <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());
}
