private void TKBNgayLopForm_Load(object sender, EventArgs e)
{
this.Text = "Thời khoá biểu lớp " + Lop.ten + " - " + ngay.ToString("dd/MM/yyyy");
using (FileStream stream = new FileStream("TKB_Ngay_Theo_Lop.xlsx", FileMode.Open))
{
Xlsx.LoadDocument(stream, DocumentFormat.Xlsx);
wb = Xlsx.Document;
}
Worksheet ws = wb.Worksheets[0];
ws.Name = "TKB_ngay_" + ngay.ToString("dd-MM-yyyy") + "_lop_" + Lop.ten;
Cell cell = ws[0, 0];
TkbNgayLopHelper tKB_helper = new TkbNgayLopHelper(ws, Lop, tietHocs, ngay, size, cell);
tKB_helper.setBackground();
IEnumerable <Cell> cells = ws.Search("_trucBan");
foreach (Cell c in cells)
{
c.Value = trucBan;
}
tKB_helper.xep();
done = true;
}
public static IFormat getType(string type_)
{
IFormat type = null;
try
{
switch (type_.ToLower())
{
case "csv":
type = new CSV();
break;
case "xls":
type = new Xls();
break;
case "xlsx":
type = new Xlsx();
break;
// Expand list here
}
}
catch (Exception)
{
throw new Exception("Incorrect file type");
}
return(type);
}
public void XlsxSubstituteSharedStrings()
{
var xdoc = new XmlDocument();
xdoc.Load("XlsxSharedStrings2.xml");
var stream = new MemoryStream();
xdoc.Save(stream);
var xdoc2 = new XmlDocument();
stream.Position = 0;
xdoc2.Load(stream);
var replacements = new Dictionary <string, string>()
{
{ "Доверенность", "123-456/АГ" },
{ "Адрес", "Петропавловск-Камчатский" },
{ "Фамилия", "Иванов И.П." },
};
var sheetDocs = new List <XmlDocument>();
var engine = new Engine();
Xlsx.ReplaceMergeFieldsAndReturnMissingFieldNames(xdoc2, sheetDocs, replacements, engine);
var fields = Xlsx.GetSharedStrings(xdoc2);
Assert.AreEqual(" На основании доверенности № 123-456/АГ в лице Иванов И.П., проживающий по адресу: Петропавловск-Камчатский ", fields.Single().StringValue);
}
private void saveToFileToolStripMenuItem_Click(object sender, EventArgs e)
{
DataTable table = this.DataTable;
if (table != null)
{
if (resultSaveFileDialog.ShowDialog(this) == DialogResult.OK)
{
string ext = Path.GetExtension(resultSaveFileDialog.FileName).ToLower();
if (ext == ".txt" || ext == ".csv")
{
char separator = (ext == ".txt") ? '\t' : ',';
DataUtils.WriteToFile(resultSaveFileDialog.FileName, table, separator);
}
else if (ext == ".xml")
{
if (String.IsNullOrEmpty(table.TableName))
{
table.TableName = Path.GetFileNameWithoutExtension(resultSaveFileDialog.FileName);
}
table.WriteXml(resultSaveFileDialog.FileName);
}
else
{
using (var reader = table.CreateDataReader()) {
Xlsx.Save(resultSaveFileDialog.FileName, "Result", reader);
}
}
}
}
}
public void XlsxSubstituteDouble()
{
var xdoc = new XmlDocument();
xdoc.Load("XlsxSharedStrings3.xml");
var stream = new MemoryStream();
xdoc.Save(stream);
var xdoc2 = new XmlDocument();
stream.Position = 0;
xdoc2.Load(stream);
var sheetDoc = new XmlDocument();
sheetDoc.Load("sheet1.xml");
var sheetStream = new MemoryStream();
sheetDoc.Save(sheetStream);
var sheetDoc2 = new XmlDocument();
sheetStream.Position = 0;
sheetDoc2.Load(sheetStream);
double dvalue = 1500.03;
var replacements = new Dictionary <string, string>()
{
{ "Цена", dvalue.ToString(CultureInfo.InvariantCulture) },
};
var sheetDocList = new List <XmlDocument>();
sheetDocList.Add(sheetDoc2);
var cells = Xlsx.GetStringCells(sheetDoc2, 1).ToList();
Assert.AreEqual(1, cells.Count());
var engine = new Engine();
Xlsx.ReplaceMergeFieldsAndReturnMissingFieldNames(xdoc2, sheetDocList, replacements, engine);
var fields = Xlsx.GetSharedStrings(xdoc2).ToList();
Assert.AreEqual(2, fields.Count);
Assert.AreEqual("", fields[1].StringValue);
var cells2 = Xlsx.GetStringCells(sheetDoc2, 1);
Assert.AreEqual(0, cells2.Count());
// check that the original cell node updated with a new value
Assert.AreEqual(1500.03, cells.Single().DoubleValue);
}
private void executeToXlsxWorker_DoWork(object sender, DoWorkEventArgs e)
{
var args = (ExecuteToXlsxArgs)e.Argument;
using (var command = args.Command) {
using (var reader = command.ExecuteReader()) {
Xlsx.Save(args.FileName, "Result", reader);
}
}
}
public void ReturnsShortListIfNotEnoughRows()
{
var opts = new dcp.lib.DataCopier.Options();
using (var reader = new Xlsx(stream, opts))
{
var rows = reader.Next(9);
Assert.Equal(3, rows.Count);
}
}
public void ReturnsCorrectNumberOfRows()
{
var opts = new dcp.lib.DataCopier.Options();
using (var reader = new Xlsx(stream, opts))
{
var rows = reader.Next(3);
Assert.Equal(3, rows.Count);
}
}
public void ReadsLastRowValue()
{
var opts = new dcp.lib.DataCopier.Options();
using (var reader = new Xlsx(stream, opts))
{
var row = reader.Next(1);
Assert.Equal(new DateTime(2031, 12, 1), row[0][2]);
}
}
public void ReadsFirstRowValue()
{
var opts = new DataCopier.Options();
using (var reader = new Xlsx(stream, opts))
{
var rows = reader.Next(1);
Assert.Equal(1.1, rows[0][0]);
}
}
public void SkipsColumns()
{
var opts = new dcp.lib.DataCopier.Options();
opts.skipColumns = 1;
using (var reader = new Xlsx(stream, opts))
{
var rows = reader.Next(1);
Assert.Equal("one", rows[0][0]);
}
}
public void ReadsSelectedWorksheet()
{
var opts = new dcp.lib.DataCopier.Options();
opts.worksheetNumber = 1;
using (var reader = new Xlsx(stream, opts))
{
var rows = reader.Next(1);
Assert.Equal(new DateTime(9031, 12, 1), rows[0][2]);
}
}
public void LeavesRows()
{
var opts = new dcp.lib.DataCopier.Options();
opts.leaveRows = 1;
using (var reader = new Xlsx(stream, opts))
{
var rows = reader.Next(9);
Assert.Equal(2, rows.Count);
}
}
public void UsesMaxColumns()
{
var opts = new dcp.lib.DataCopier.Options();
opts.maxColumns = 1;
using (var reader = new Xlsx(stream, opts))
{
var rows = reader.Next(1);
Assert.Equal(1.1, rows[0][0]);
Assert.Single(rows[0]);
}
}
static void Main()
{
Xlsx xlsx = Xlsx.Load("test");
if (xlsx.Error == null)
{
Console.WriteLine("Success");
}
else
{
Console.WriteLine("Result: " + xlsx.Error);
}
}
public void XlsxParseSharedStrings()
{
var xdoc = new XmlDocument();
xdoc.Load("XlsxSharedStrings.xml");
var fields = Xlsx.GetSharedStrings(xdoc).ToArray();
Assert.AreEqual(4, fields.Length);
Assert.AreEqual("[[Родительный (Должность представителя Лицензиата) ]]", fields[0].Text);
Assert.AreEqual("[[ Родительный (ФИО представителя Лицензиата) ]] ", fields[1].Text);
Assert.AreEqual("[[Полное наименование компании Лицензиата]]", fields[2].Text);
Assert.AreEqual(" На основании доверенности № [[Доверенность]] в лице [[ Фамилия ]], проживающий по адресу:[[ Адрес]] ", fields[3].Text);
}
public ChartAdapter(ChartPart chartPart)
{
_chart = chartPart.ChartSpace.GetFirstChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>() ??
throw new ArgumentException("ChartPart contains no Chart object", nameof(chartPart));
_package = chartPart.EmbeddedPackagePart;
if (_package != null)
{
using var stream = _package.GetStream();
var embeddedBytes = new byte[stream.Length];
stream.Position = 0;
stream.Read(embeddedBytes, 0, (int)stream.Length);
_embeddedDocument = new Xlsx(embeddedBytes);
_sheetsMap = _embeddedDocument.Document?.WorkbookPart?.Workbook?.Sheets?.ChildElements.OfType <Sheet>()
.Where(x => !string.IsNullOrEmpty(x.Name?.Value))
.ToDictionary(x => x.Name.Value);
if (_sheetsMap == null)
{
throw new ArgumentException("Embedded document is corrupted.");
}
}
}
public static IEnumerable <IMergeError> MergeXL(this Engine engine, string templatePath, Dictionary <string, string> fieldValues, string outputPath)
{
File.Copy(templatePath, outputPath, true);
return(Xlsx.MergeInplace(engine, outputPath, fieldValues));
}
