public void EndWritingWithoutTemplate()
{
_writer.WriteEndElement();
_writer.WriteEndElement();
_writer.Close();
}
private static void Finish(OpenXmlWriter writer, SpreadsheetDocument xl, WorksheetPart worksheetpart)
{
// end worksheet
writer.WriteEndElement();
writer.Close();
// write root element
writer = OpenXmlWriter.Create(xl.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet {
Name = "Sheet1",
SheetId = 1,
Id = xl.WorkbookPart.GetIdOfPart(worksheetpart)
});
// end Sheets
writer.WriteEndElement();
// end Workbook
writer.WriteEndElement();
writer.Close();
xl.Close();
}
/// <summary>
/// 共享字符串
/// </summary>
/// <param name="sharedStringTablePart"></param>
private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart)
{
using (OpenXmlWriter writer = OpenXmlWriter.Create(sharedStringTablePart))
{
// SharedStringTable 开始
writer.WriteStartElement(new SharedStringTable()
{
Count = totalCount, UniqueCount = (uint)stringIndexDic.Keys.Count
});
foreach (var str in stringIndexDic.Keys)
{
// SharedStringItem 开始
writer.WriteStartElement(new SharedStringItem());
// 写入 Text
writer.WriteElement(new Text(str));
// SharedStringItem 结束
writer.WriteEndElement();
}
// SharedStringTable 结束
writer.WriteEndElement();
writer.Close();
}
}
public void Dispose()
{
rowsWriter?.Dispose();
CreateAutoFilterDefinedName();
sheetWriter?.Close();
document?.Dispose();
}
public void CreateSheets(string filename, string sheetname, int sheetid)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
if (sheetid == 1)
{
workbookPart.Workbook = new Workbook();
}
Workbook workbook = myDoc.WorkbookPart.Workbook;
//WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlWriter writer = OpenXmlWriter.Create(workbookPart);
writer.WriteStartElement(workbook);
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = sheetname,
SheetId = (uint)sheetid
});
// this is for Sheets
writer.WriteEndElement();
// this is for Workbook
writer.WriteEndElement();
writer.Close();
myDoc.Close();
}
}
/// <summary>
/// 通过SAX创建工作表
/// </summary>
/// <param name="worksheetPart"></param>
private void CreateSheetBySAX(WorksheetPart worksheetPart, DrawingsPart drawingsPart)
{
using (_writer = OpenXmlWriter.Create(worksheetPart))
{
_rowIndex = 1;
//S: Worksheet
_writer.WriteStartElement(new Worksheet());
if (drawingsPart != null)
{
_writer.WriteElement(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
}
InitSheetViews();
InitColumns();
MergeCells();
FillData();
//E: Worksheet
_writer.WriteEndElement();
_writer.Close();
}
}
static void WriteRandomValuesSAX(string filename, int numRows, int numCols)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
Row r = new Row();
Cell c = new Cell();
CellValue v = new CellValue("Test");
c.AppendChild(v);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
for (int row = 0; row < numRows; row++)
{
writer.WriteStartElement(r);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(c);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
}
}
public void Write(Sheet sheet)
{
if (sheet == null)
{
throw new ArgumentNullException(nameof(sheet));
}
writer.WriteStartElement(sheetTemplate);
if (sheet.Columns?.Any() ?? false)
{
Write(sheet.Columns);
}
if (sheet.Rows?.Any() ?? false)
{
Write(sheet.Rows);
}
if (sheet.MergeCells?.Any() ?? false)
{
Write(sheet.MergeCells);
}
writer.WriteEndElement();
writer.Close();
}
public void Dispose()
{
Writer.WriteEndElement(); //end of SheetData
Writer.WriteEndElement(); //end of worksheet
Writer.Close(); // TODO: poner en una clase sheet
CreateShareStringPart();
Writer.Dispose();
}
public void EndWriteHead()
{
_writer.WriteEndElement(); //end of SheetData
_writer.WriteEndElement(); //end of worksheet
_writer.Close();
_spreadsheet.Dispose();
}
public void WriteRandomValuesSAX(string filename, int numRows, int numCols, int sheetid, int countRows)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
Row r = new Row();
Cell c = new Cell();
CellValue v = new CellValue("Test");
c.AppendChild(v);
var s = new SheetData();
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(s);
//writer.WriteStartElement(new Sheet());
for (int row = countRows; row < numRows; row++)
{
writer.WriteStartElement(r);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(c);
}
writer.WriteEndElement();
}
//writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
//writer = OpenXmlWriter.Create(myDoc.WorkbookPart);
//writer.WriteStartElement(new Workbook());
//writer.WriteStartElement(new Sheets());
//writer.WriteElement(new Sheet()
//{
// Name = $"Sheet21{sheetid}",
// SheetId = (uint)sheetid,
// Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart)
//});
//// this is for Sheets
//writer.WriteEndElement();
//// this is for Workbook
//writer.WriteEndElement();
//writer.Close();
myDoc.Close();
}
}
private void FinishBuilding()
{
_writer.WriteEndElement(); // end SheetData
if (_excelImages.Count > 0)
{
_writer.WriteElement(GetDrawing());
}
_writer.WriteEndElement(); // end Worksheet
_writer.Close();
_buildingIsFinished = true;
}
public void Close()
{
_workBookWriter.WriteEndElement(); // End Writing Sheets
_workBookWriter.WriteEndElement(); // End Writing Workbook
_workBookWriter.Close();
_sharedStringWriter.Close();
_styleSheetWriter.WriteAndClose();
_xl.Close();
}
public void WriteAndClose()
{
_writer.WriteStartElement(new Stylesheet());
{
WriteNumberingFormats();
WriteFonts();
WriteFills();
WriteBorders();
WriteCellFormats();
}
_writer.WriteEndElement();
_writer.Close();
}
private static void WriteData(WorksheetPart workSheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
{
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
// Write Values
for (int row = 0; row < 2000; row++)
{
Row r = new Row()
{
RowIndex = Convert.ToUInt32(row + 2)
};
writer.WriteStartElement(r);
for (int col = 0; col < 20; col++)
{
Cell c = new Cell()
{
StyleIndex = Convert.ToUInt32(2),
CellReference = new StringValue()
};
CellValue v = new CellValue("xxx");
c.DataType = new EnumValue <CellValues>(CellValues.String);
c.Append(v);
writer.WriteElement(c);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
}
}
public int writeHeader(List <ChannelModel> channels, OperatorModel operatorParam, FacilityModel facilityParam)
{
int retvalueTemp = 0;
if (_doc != null && _workBook != null && _workSheet != null)
{
string originalPartId = _workBook.GetIdOfPart(_workSheet);
WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>();
string replacementPartId = _workBook.GetIdOfPart(replacementPart);
_reader = OpenXmlReader.Create(_workSheet);
_writer = OpenXmlWriter.Create(replacementPart);
while (_reader.Read())
{
if (_reader.ElementType == typeof(Selection))
{
continue;
}
if (_reader.ElementType == typeof(SheetData))
{
if (_reader.IsEndElement)
{
continue;
}
_writer.WriteStartElement(new SheetData()); // beginning of sheetdata
// append section begins
var labelRow = new Row(); // label row
labelRow.RowIndex = 1;
_writer.WriteStartElement(labelRow); // begining of row
var operatorLabelCell = new Cell();
var facilityLabelCell = new Cell();
var creationDateLabelCell = new Cell();
operatorLabelCell.CellValue = new CellValue(" Operatör ");
facilityLabelCell.CellValue = new CellValue(" Tesis ");
creationDateLabelCell.CellValue = new CellValue(" Oluşturulma Tarihi ");
_writer.WriteElement(operatorLabelCell);
_writer.WriteElement(facilityLabelCell);
_writer.WriteElement(creationDateLabelCell);
_writer.WriteEndElement(); // end of label row
var infoRow = new Row(); // info row
infoRow.RowIndex = 2;
_writer.WriteStartElement(infoRow); // begining of row
var operatorInfoCell = new Cell();
var facilityInfoCell = new Cell();
var creationDateInfoCell = new Cell();
operatorInfoCell.CellValue = new CellValue(operatorParam.DisplayName);
facilityInfoCell.CellValue = new CellValue(facilityParam.ListDisplay);
creationDateInfoCell.CellValue = new CellValue(DateTime.Now.ToShortTimeString());
_writer.WriteElement(operatorInfoCell);
_writer.WriteElement(facilityInfoCell);
_writer.WriteElement(creationDateInfoCell);
_writer.WriteEndElement(); // end of info row
var channelListRow = new Row(); // channelListRow row
channelListRow.RowIndex = 3;
_writer.WriteStartElement(channelListRow); // begining of row
for (int k = 0; k < channels.Count; k++)
{
var dataInfoCell = new Cell();
dataInfoCell.CellValue = new CellValue(channels[k].name);
_writer.WriteElement(dataInfoCell);
}
_writer.WriteEndElement(); // end of channel info row
_writer.WriteEndElement(); // end of sheetdata
}
else
{
if (_reader.IsStartElement)
{
_writer.WriteStartElement(_reader);
if (_reader.ElementType == typeof(CellValue))
{
_writer.WriteString(_reader.GetText());
}
}
else if (_reader.IsEndElement)
{
_writer.WriteEndElement();
}
else
{
}
}
}
_writer.Close();
_reader.Close();
Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First();
sheet.Id.Value = replacementPartId;
_workBook.DeletePart(_workSheet);
}
else
{
retvalueTemp = -1;
}
return(retvalueTemp);
}
public static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
UInt32 inx = 1;
writer.WriteStartElement(new Columns());
foreach (DataColumn dc in dt.Columns)
{
writer.WriteElement(new Column {
Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH
});
inx++;
}
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
int numberOfColumns = dt.Columns.Count;
bool[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex, Height = 20, CustomHeight = true
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
writer.AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == typeof(decimal).FullName) || (col.DataType.FullName == typeof(double).FullName) || (col.DataType.FullName == typeof(Single).FullName);
IsDateColumn[colInx] = (col.DataType.FullName == typeof(DateTime).FullName);
}
writer.WriteEndElement(); // End of header "Row"
double cellFloatValue = 0;
CultureInfo ci = Thread.CurrentThread.CurrentCulture; //new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
cellFloatValue = 0;
bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
writer.AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces ? NumericFormatDecimaPlaces.Two : NumericFormatDecimaPlaces.Zero);
}
}
else if (IsDateColumn[colInx])
{
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
writer.AppendDateCell(cellReference, dateValue);
}
else
{
writer.AppendTextCell(cellReference, cellValue);
}
}
else
{
writer.AppendTextCell(cellReference, cellValue);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
public ActionResult Search(FormCollection model)
{
string selectedReport = model["cboReports"];
string datePeriod = model["DatePeriod"];
if (selectedReport.HasValue())
{
Guid idReport = new Guid(selectedReport);
Reports report = Reports.Get(idReport);
IEnumerable <dynamic> data = Reports.Run(report, datePeriod);
HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true")
{
Path = "/"
});
var context = HttpContext.Response;
context.Buffer = context.BufferOutput = false;
context.Cache.SetCacheability(HttpCacheability.Private);
context.Cache.SetExpires(DateTime.Now);
//context.ContentType = (new ContentType("text/csv") { CharSet = "utf-8" }).ToString(); // CSV
//context.ContentType = (new ContentType("application/vnd.ms-excel") { CharSet = "utf-8" }).ToString();
context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
CharSet = "utf-8"
}.ToString();
context.AppendHeader("Content-Disposition",
new ContentDisposition
{
DispositionType = DispositionTypeNames.Attachment,
FileName = string.Format(CultureInfo.InvariantCulture, report.FileNamePrefix + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now)
}.ToString()
);
context.AppendHeader("X-Content-Type-Options", "nosniff");
using (MemoryStream mDocument = new MemoryStream())
{
// Using SAX
using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook))
{
List <OpenXmlAttribute> attributes;
document.AddWorkbookPart();
// Stylesheet
WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesheet.Stylesheet = new Stylesheet(new Fonts(
new Font( // 0 = Default
new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}
),
new Font( // 1 = Bold
new Bold()
),
new Font( // 2 = Red
new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}
)
),
new Fills(
new Fill()
{
}
),
new Borders(new Border()
{
}),
new CellFormats(
new CellFormat()
{
FontId = 0
}, // 0
new CellFormat()
{
FontId = 1, ApplyFont = true
}, // 1
new CellFormat()
{
FontId = 2, ApplyFont = true
} // 2
)
);
stylesheet.Stylesheet.Save();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
IDictionary <string, object> firstRow = data.FirstOrDefault();
if (firstRow != null)
{
int row = 1;
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col1 = 1;
foreach (var cols in firstRow.Keys.ToList())
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col1) + row),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(cols));
writer.WriteEndElement();
col1++;
}
writer.WriteEndElement();
row++;
foreach (IDictionary <string, object> row2 in data)
{
attributes =
new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col = 1;
foreach (var key in row2.Keys)
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col) + row)
};
if (row2[key] is decimal)
{
if ((decimal)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
else if (row2[key] is double)
{
if ((double)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : ""));
writer.WriteEndElement();
col++;
}
writer.WriteEndElement();
row++;
}
}
else
{
// Empty row (no data found)
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, "1")
};
writer.WriteStartElement(new Row(), attributes);
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(1) + 1),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(""));
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Sheet 1",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
document.Save();
document.Close();
mDocument.WriteTo(context.OutputStream);
}
}
return(null);
}
return(null);
}
public int write(List <List <IWritable> > dataList)
{
int retvalueTemp = 0;
if (_doc != null && _workBook != null && _workSheet != null)
{
string originalPartId = _workBook.GetIdOfPart(_workSheet);
WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>();
string replacementPartId = _workBook.GetIdOfPart(replacementPart);
_reader = OpenXmlReader.Create(_workSheet);
_writer = OpenXmlWriter.Create(replacementPart);
while (_reader.Read())
{
if (_reader.ElementType == typeof(Selection))
{
continue;
}
if (_reader.ElementType == typeof(SheetData))
{
if (_reader.IsStartElement)
{
_writer.WriteStartElement(_reader);
continue;
}
// append section begins
//_writer.WriteStartElement(new SheetData()); // beginning of sheetdata
for (int rowIndex = 0; rowIndex < dataList.Count; rowIndex++)
{
Row row = new Row();
_writer.WriteStartElement(row); // begining of row
for (int colIndex = 0; colIndex < dataList.Count; colIndex++)
{
Cell cell = new Cell();
cell.CellValue = new CellValue(dataList[rowIndex][colIndex].ToString());
_writer.WriteElement(cell);
}
_writer.WriteEndElement(); // end of row
}
_writer.WriteEndElement(); // end of sheetdata
}
else
{
if (_reader.IsStartElement)
{
_writer.WriteStartElement(_reader);
if (_reader.ElementType == typeof(CellValue))
{
_writer.WriteString(_reader.GetText());
}
}
else if (_reader.IsEndElement)
{
_writer.WriteEndElement();
}
else
{
}
}
}
_writer.Close();
_reader.Close();
Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First();
sheet.Id.Value = replacementPartId;
_workBook.DeletePart(_workSheet);
}
else
{
retvalueTemp = -1;
}
return(retvalueTemp);
}
public void WriteRandomValuesSAX(string filename, int numRows, int numCols)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
WorksheetPart replacementPart =
workbookPart.AddNewPart <WorksheetPart>();
string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
Row r = new Row();
Cell c = new Cell();
CellFormula f = new CellFormula();
f.CalculateCell = true;
f.Text = "RAND()";
c.Append(f);
CellValue v = new CellValue();
c.Append(v);
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
if (reader.IsEndElement)
{
continue;
}
writer.WriteStartElement(new SheetData());
for (int row = 0; row < numRows; row++)
{
writer.WriteStartElement(r);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(c);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
}
else
{
if (reader.IsStartElement)
{
writer.WriteStartElement(reader);
}
else if (reader.IsEndElement)
{
writer.WriteEndElement();
}
}
}
reader.Close();
writer.Close();
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>()
.Where(s => s.Id.Value.Equals(origninalSheetId)).First();
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = "";
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement();
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
if (IsNumericColumn[colInx])
{
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
else if (IsDateColumn[colInx])
{
DateTime dtValue;
string strValue = "";
if (DateTime.TryParse(cellValue, out dtValue))
{
strValue = dtValue.ToShortDateString();
}
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
}
else
{
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
}
/// <summary>
/// 表格内容
/// </summary>
/// <param name="worksheetPart"></param>
private void GenerateWorksheetPartContent(WorksheetPart worksheetPart)
{
using (OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart))
{
// worksheet 开始
writer.WriteStartElement(new Worksheet());
// 是否有自定义列
bool hasCustomColume = _columnInfos.Any(x => x.Width != null);
if (hasCustomColume)
{
// cols 开始
writer.WriteStartElement(new Columns());
}
int columnCount = _columnInfos.Count;
// 表头
Row headRow = new Row()
{
RowIndex = 1, Spans = new ListValue <StringValue>()
{
InnerText = $"1:{columnCount}"
}
};
Cell[] headCells = new Cell[columnCount];
for (int i = 0; i < columnCount; i++)
{
ColumnInfo citem = _columnInfos[i];
uint cindex = (uint)i + 1;
if (citem.Width != null)
{
var column = new Column()
{
Min = cindex, Max = cindex, Width = citem.Width.Value, BestFit = true, CustomWidth = true
};
// col
writer.WriteElement(column);
}
Cell cell = ConstructHeadCell(citem.Show);
cell.CellReference = CellReferenceUtil.GetCellReference(0, (uint)i);
headCells[i] = cell;
}
headRow.Append(headCells);
if (hasCustomColume)
{
// cols 结束
writer.WriteEndElement();
}
// sheetData 开始
writer.WriteStartElement(new SheetData());
// 写入表头行
writer.WriteElement(headRow);
// 写入数据
uint rowIndex = 2;
foreach (T data in _sourceDatas)
{
Row row = new Row()
{
RowIndex = rowIndex, Spans = new ListValue <StringValue>()
{
InnerText = $"1:{columnCount}"
}
};
// row 开始
writer.WriteStartElement(row);
for (int k = 0; k < columnCount; k++)
{
ColumnInfo citem = _columnInfos[k];
PropertyInfo pi = _propertyDic[citem.PropertyName];
object v = pi.GetValue(data, null);
Cell cell = ConstructCell(v, citem.FormatString, pi.PropertyType);
cell.CellReference = CellReferenceUtil.GetCellReference(rowIndex - 1, (uint)k);
// 写入 c
writer.WriteElement(cell);
}
// row 结束
writer.WriteEndElement();
rowIndex++;
}
// sheetData 结束
writer.WriteEndElement();
// worksheet 结束
writer.WriteEndElement();
writer.Close();
}
}
public void WriteEndSheet()
{
_workSheetWriter.WriteEndElement(); // End Writing SheetData
_workSheetWriter.WriteEndElement(); // End Writing Worksheet
_workSheetWriter.Close();
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
// To demonstrate how to set column-widths in Excel, here's how to set the width of all columns to our default of "25":
UInt32 inx = 1;
writer.WriteStartElement(new Columns());
foreach (DataColumn dc in dt.Columns)
{
writer.WriteElement(new Column {
Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH
});
inx++;
}
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
// We'll set the row-height to 20px, and (using the "AppendHeaderTextCell" function) apply some formatting to the cells.
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex, Height = 20, CustomHeight = true
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
// Uncomment the following lines, for an example of how to create some Named Ranges in your Excel file
#if FALSE
// For each column of data in this worksheet, let's create a Named Range, showing where there are values in this column
// eg "NamedRange_UserID" = "Drivers!$A2:$A6"
// "NamedRange_Surname" = "Drivers!$B2:$B6"
string columnHeader = col.ColumnName.Replace(" ", "_");
string NamedRange = string.Format("{0}!${1}2:${2}{3}", worksheetName, excelColumnNames[colInx], excelColumnNames[colInx], dt.Rows.Count + 1);
DefinedName definedName = new DefinedName()
{
Name = "NamedRange_" + columnHeader,
Text = NamedRange
};
definedNamesCol.Append(definedName);
#endif
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellFloatValue = 0;
CultureInfo ci = new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
// Create cell with data
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
// For numeric cells without any decimal places.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellFloatValue = 0;
bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer);
}
}
else if (IsDateColumn[colInx])
{
// For date values, we save the value to Excel as a number, but need to set the cell's style to format
// it as either a date or a date-time.
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
AppendDateCell(cellReference, dateValue, writer);
}
else
{
// This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
AppendTextCell(cellReference, cellValue, writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(cellReference, cellValue, writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
public static Stream ToXLSX(TranslationModule project)
{
MemoryStream xlsStream = new MemoryStream();
var worksheet = new MemoryWorksheet();
Export.ToIWorksheet(project, worksheet);
var ss = new Stylesheet();
ss.CellStyleFormats = new CellStyleFormats();
ss.CellStyleFormats.Count = 1;
ss.CellStyles = new CellStyles();
ss.CellStyles.Count = 1;
ss.DifferentialFormats = new DifferentialFormats();
ss.DifferentialFormats.Count = 0;
ss.TableStyles = new TableStyles();
ss.TableStyles.Count = 0;
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(xlsStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myDoc.AddWorkbookPart();
//workbookPart.Workbook = new Workbook();
WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = ss;
stylesPart.Stylesheet.Save();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
//SheetData sheetData = new SheetData();
//worksheetPart.Worksheet = new Worksheet(sheetData);
/*
* Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
* Sheet sheet = new Sheet()
* {
* Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart),
* SheetId = 1,
* Name = "mySheet"
* };
* sheets.Append(sheet);
*/
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
for (int row = 0; row < worksheet.Rows; row++)
{
var oxa = new List <OpenXmlAttribute>();
// this is the row index
oxa.Add(new OpenXmlAttribute("r", null, row.ToString()));
writer.WriteStartElement(new Row(), oxa);
for (int col = 0; col < worksheet.Columns; col++)
{
oxa = new List <OpenXmlAttribute>();
// this is the data type ("t"), with CellValues.String ("str")
oxa.Add(new OpenXmlAttribute("t", null, "str"));
string val = worksheet[row, col] != null ? worksheet[row, col].ToString() : "";
//var cell = new Cell(new CellValue(val));
writer.WriteStartElement(new Cell(), oxa);
//Cell c = new Cell();
CellValue v = new CellValue(val);
writer.WriteElement(v);
//c.AppendChild(v);
//writer.WriteElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(myDoc.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
// you can use object initialisers like this only when the properties
// are actual properties. SDK classes sometimes have property-like properties
// but are actually classes. For example, the Cell class has the CellValue
// "property" but is actually a child class internally.
// If the properties correspond to actual XML attributes, then you're fine.
writer.WriteElement(new Sheet()
{
Name = "Sheet1",
SheetId = 1,
Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart)
});
// this is for Sheets
writer.WriteEndElement();
// this is for Workbook
writer.WriteEndElement();
writer.Close();
myDoc.Close();
}
return(xlsStream);
}
public virtual void Export(string fileName, LayoutList list)
{
this.list = list;
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = xl.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
//add styles
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
// Add a SharedStringTablePart to the WorkbookPart.
var stringPart = workbookpart.AddNewPart <SharedStringTablePart>();
var stringTable = new StringKeyList();
// Add Sheets to the Workbook.
var sheets = xl.WorkbookPart.Workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = xl.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DataSheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
mcells = new List <MergeCell>();
writer = OpenXmlWriter.Create(worksheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetProperties());
writer.WriteElement(new OutlineProperties()
{
SummaryBelow = false, SummaryRight = false
});
writer.WriteEndElement();
mc = 0;
writer.WriteStartElement(new Columns());
WriteMapColumns(list.ListInfo.Columns, 0, 0);
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
int ind = 1;
var row = new Row()
{
RowIndex = (uint)ind, Height = 25
};
row.AppendChild(GetCell(list.Description, 0, ind, (uint)13, stringTable));
WriteRows(writer, new List <Row>(new Row[] { row }));
mcells.Add(new MergeCell()
{
Reference = new CellRange(0, 1, mc - 1, 1).ToString()
});
WriteMapItem(list.ListInfo.Columns, -1, null, 0, 0, ref ind, stringTable);
if (list.Selection.Count > 1)
{
var items = list.Selection.GetItems <object>();
for (var i = 0; i < items.Count; i++)
{
var item = items[i];
WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable);
}
}
else if (list.NodeInfo != null)
{
var items = list.NodeInfo.Nodes.GetTopLevel().ToList();
for (var i = 0; i < items.Count; i++)
{
var item = items[i] as Node;
WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable);
}
}
else if (list.ListInfo.GroupVisible)
{
foreach (LayoutGroup g in list.Groups)
{
this.group = g;
if (list.ListInfo.GroupHeader)
{
ind++;
var header = new Row()
{
RowIndex = (uint)ind, CustomHeight = true, Height = 20
};
header.AppendChild(GetCell(g.TextValue, 0, ind, 8, stringTable));
mcells.Add(new MergeCell()
{
Reference = new CellRange(0, ind, mc - 1, ind).ToString()
});
WriteRow(writer, header);
}
for (int i = g.IndexStart; i <= g.IndexEnd; i++)
{
WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable);
}
if (list.ListInfo.CollectingRow)
{
WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable);
}
//ind++;
}
}
else
{
for (int i = 0; i < list.ListSource.Count; i++)
{
WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable);
}
if (list.ListInfo.CollectingRow)
{
WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable);
}
}
writer.WriteEndElement();
if (mcells.Count > 0)
{
writer.WriteStartElement(new MergeCells());
foreach (var cell in mcells)
{
writer.WriteElement(cell);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.Close();
}
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
// Create cell with data
if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
else if (IsDateColumn[colInx])
{
// This is a date value.
DateTime dtValue;
string strValue = "";
if (DateTime.TryParse(cellValue, out dtValue))
{
strValue = dtValue.ToShortDateString();
}
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
private static void WriteDataTableToExcelWorksheet(ReportCondition dtCondition, DataTable dtDetail,
WorksheetPart worksheetPart, WorkbookStylesPart stylesPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.Unicode); //ASCII
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = string.Empty;
uint rowIndexHeader = 1;
// Create the Conditon Report
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "BOC Detailed Utilization", 1, stylesPart.Stylesheet, System.Drawing.Color.White, false, 20, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Date:", 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat(
"B"
, string.Format("{0} - {1}", dtCondition.FromDate.ToString("dd.MM.yyyy"), dtCondition.ToDate.AddDays(-1).ToString("dd.MM.yyyy"))
, 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Location:", 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.LocationPath.ToString(), 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Group:", 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.GroupName.ToString(), 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Resource:", 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.ResourceName.ToString(), 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Method:", 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.Method.ToString(), 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Include weekends:", 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", SetWeekendTitle(dtCondition), 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Upper threshold (%):", 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.UpperThreshold.ToString(), 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Lower threshold (%):", 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.LowerThreshold.ToString(), 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
// End of Conditon Report
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
int numberOfColumns = dtDetail.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
// Create the Header row in our Excel Worksheet
writer.WriteStartElement(new Row {
RowIndex = 11
});
AppendTextCellFormat("A", "Resource", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer);
AppendTextCellFormat("B", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer);
AppendTextCellFormat("C", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer);
AppendTextCellFormat("D", "Flag", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer);
AppendTextCellFormat("E", "Group", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer);
AppendTextCellFormat("F", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer);
AppendTextCellFormat("G", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dtDetail.Columns[colInx];
//AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement();
// End of header row
// Now, step through each row of data in our DataTable...
uint rowIndex = 11;
double cellNumericValue = 0;
foreach (DataRow dr in dtDetail.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
// Create cell with data
if (IsNumericColumn[colInx])
{
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
if (cellNumericValue >= 0)
{
cellValue = ((float)cellNumericValue).ToString("0.00");
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
else
{
cellValue = string.Empty;
//AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer);
}
}
}
else if (IsDateColumn[colInx])
{
DateTime dtValue = DateTime.Parse(cellValue);
string strValue = dtValue.ToShortDateString();
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
}
else if (colInx == 0)
{
AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer);
}
else
{
//AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
public void ExportSAX(DataTable dt, string excelFilePath)
{
if (dt == null || excelFilePath == string.Empty)
{
throw new ArgumentNullException();
}
var fi = new FileInfo(excelFilePath);
#region 文件已存在
if (File.Exists(excelFilePath) && fi.Length != 0)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFilePath, true))
{
Sheet sheet = document.WorkbookPart.Workbook.Descendants <Sheet>()
.FirstOrDefault(s => s.Name == EscapeSheetNameInvalidChar(dt.TableName));
if (sheet != null)
{
var worksheetPart = (WorksheetPart)(document.WorkbookPart.GetPartById(sheet.Id));
sheet.Remove();
document.WorkbookPart.DeletePart(worksheetPart);
document.WorkbookPart.Workbook.Save();
}
uint sheetId = 1;
WorksheetPart wsp = document.WorkbookPart.AddNewPart <WorksheetPart>();
Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
if (sheets == null)
{
throw new OpenXmlPackageException("已有Excel文件损坏，请删除");
}
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
//create worksheet part, and add it to the sheets collection in workbook
Sheet sheet1new = new Sheet {
Id = document.WorkbookPart.GetIdOfPart(wsp), SheetId = sheetId, Name = EscapeSheetNameInvalidChar(dt.TableName)
};
sheets.Append(sheet1new);
// List<OpenXmlAttribute> oxa;
OpenXmlWriter writer = OpenXmlWriter.Create(wsp);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
///TODO:auto row page
for (int i = 0; i < dt.Rows.Count; ++i)
{
// oxa = new List<OpenXmlAttribute>();
// oxa.Add(new OpenXmlAttribute("r", null, (i + 1).ToString(CultureInfo.InvariantCulture)));
writer.WriteStartElement(new Row());
for (int j = 0; j < dt.Columns.Count; ++j)
{
//if use WriteStartElement must manual add OpenXmlAttribute
// oxa = new List<OpenXmlAttribute>();
// oxa.Add(new OpenXmlAttribute("t", null, "str"));
writer.WriteElement(new Cell {
CellValue = new CellValue(dt.Rows[i].ItemArray[j].ToString()), DataType = CellValues.String
});
// writer.WriteStartElement(new Cell());
// writer.WriteEndElement();
}
writer.WriteEndElement();
}
// this is for SheetData
writer.WriteEndElement();
// this is for Worksheet
writer.WriteEndElement();
writer.Close();
}
}
#endregion
#region 新建文件
else
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook))
{
document.AddWorkbookPart();
// List<OpenXmlAttribute> oxa;
document.WorkbookPart.Workbook = new Workbook();
WorksheetPart wsp = document.WorkbookPart.AddNewPart <WorksheetPart>();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
//create worksheet part, and add it to the sheets collection in workbook
Sheet sheet = new Sheet {
Id = document.WorkbookPart.GetIdOfPart(wsp), SheetId = 1, Name = dt.TableName
};
sheets.Append(sheet);
OpenXmlWriter writer = OpenXmlWriter.Create(wsp);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
///TODO:auto row page
for (int i = 0; i < dt.Rows.Count; ++i)
{
// oxa = new List<OpenXmlAttribute>();
// oxa.Add(new OpenXmlAttribute("r", null, (i + 1).ToString(CultureInfo.InvariantCulture)));
writer.WriteStartElement(new Row());
for (int j = 0; j < dt.Columns.Count; ++j)
{
//if use WriteStartElement must manual add OpenXmlAttribute
// oxa = new List<OpenXmlAttribute>();
// oxa.Add(new OpenXmlAttribute("t", null, "str"));
// you can use object initialisers like this only when the properties
// are actual properties. SDK classes sometimes have property-like properties
// but are actually classes. For example, the Cell class has the CellValue
// "property" but is actually a child class internally.
// If the properties correspond to actual XML attributes, then you're fine.
writer.WriteElement(new Cell {
CellValue = new CellValue(dt.Rows[i].ItemArray[j].ToString()), DataType = CellValues.String
});
// writer.WriteStartElement(new Cell());
// writer.WriteEndElement();
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
}
}
#endregion
}
public static void GetDataResultsAdjust(IList <RecordAdjust> lstRecord, string filePath, string templatePath, string status, string fromdate, string todate)
{
if ((!File.Exists(templatePath)))
{
//If we do not have a template file, we need to create one
CreateSpreadsheetWorkbook(templatePath);
}
//Copy the template file to the output file location
File.Copy(templatePath, filePath, true);
//Open the copied file
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, true))
{
//Navigate to the workbook part
WorkbookPart workbookPart = myDoc.WorkbookPart;
//open the first worksheet
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
//Get the id of this sheet. We need this because we are going to add a new
//worksheet to this workbook, then we are going to delete this worksheet
//This ID will tell us which one to delete
string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
//Add the new worksheet
WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>();
//This is the ID of the new worksheet
string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
//We are going to read from the original worksheet to get the
//templated items that we added to the worksheet in the traditional way
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//We are goint to copy the items from the original worksheet by using
//an XML writer, this overcomes the memory limitations of having
//an extremely large dataset.
OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
//The template does not have any data so we will be creating new rows and cells
//Then writing them using XML.
Row r = new Row();
Cell c = new Cell();
while ((reader.Read()))
{
//This iterates through the sheet data and copies it
if ((object.ReferenceEquals(reader.ElementType, typeof(SheetData))))
{
//Exit the loop if we hit a sheetdata end element
if ((reader.IsEndElement))
{
break; // TODO: might not be correct. Was : Exit While
}
//We create a new sheetdata element (basically this is the reoot container for a sheet)
writer.WriteStartElement(new SheetData());
if (status == "0")
{
int rowIndex2 = 2;
r.RowIndex = (UInt32)rowIndex2;
writer.WriteStartElement(r);
c = CreateCell(5, "BÁO CÁO HÓA ĐƠN THAY THẾ", rowIndex2);
writer.WriteElement(c);
writer.WriteEndElement();
}
else if (status == "1")
{
int rowIndex2 = 2;
r.RowIndex = (UInt32)rowIndex2;
writer.WriteStartElement(r);
c = CreateCell(5, "BÁO CÁO HÓA ĐƠN ĐIỀU CHỈNH", rowIndex2);
writer.WriteElement(c);
writer.WriteEndElement();
}
int rowIndextieudea = 3;
r.RowIndex = (UInt32)rowIndextieudea;
writer.WriteStartElement(r);
c = CreateCell(0, "Từ ngày:" + fromdate, rowIndextieudea);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndextieudeb = 4;
r.RowIndex = (UInt32)rowIndextieudeb;
writer.WriteStartElement(r);
c = CreateCell(0, "Đến ngày:" + todate, rowIndextieudeb);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndextieudec = 5;
r.RowIndex = (UInt32)rowIndextieudec;
writer.WriteStartElement(r);
c = CreateCell(0, "Tổng số hóa đơn:" + lstRecord.Count(), rowIndextieudec);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndex = 6;
r.RowIndex = (UInt32)rowIndex;
//Start the first row.
writer.WriteStartElement(r);
//Iterate through the columns to write a row conaining the column names
c = CreateCell(0, "STT", rowIndex);
writer.WriteElement(c);
c = CreateCell(1, "Ký hiệu mẫu", rowIndex);
writer.WriteElement(c);
c = CreateCell(2, "Ký hiệu hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(3, "Số hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(4, "Khách hàng", rowIndex);
writer.WriteElement(c);
c = CreateCell(5, "Ký hiệu mẫu", rowIndex);
writer.WriteElement(c);
c = CreateCell(6, "Ký hiệu hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(7, "Số hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(8, "Khách hàng", rowIndex);
writer.WriteElement(c);
c = CreateCell(9, "Người thực hiện", rowIndex);
writer.WriteElement(c);
c = CreateCell(10, "Ngày thực hiện", rowIndex);
writer.WriteElement(c);
c = CreateCell(11, "Trạng thái", rowIndex);
writer.WriteElement(c);
//End first row
writer.WriteEndElement();
foreach (var item in lstRecord)
{
if (item == null)
{
continue;
}
rowIndex = rowIndex + 1;
r.RowIndex = (UInt32)rowIndex;
writer.WriteStartElement(r);
//iterate through the columns to write their data
c = CreateCell(0, item.stt.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(1, item.patternOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(2, item.serialOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(3, string.Format("{0:0000000}", Convert.ToInt32(item.noOlder)), rowIndex); writer.WriteElement(c);
//c = CreateCell(4, item.cusnameOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(5, item.patternNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(6, item.serialNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(7, string.Format("{0:0000000}", Convert.ToInt32(item.noNew)), rowIndex); writer.WriteElement(c);
c = CreateCell(8, item.cusnameNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(9, item.username.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(10, String.Format("{0:dd/MM/yyyy}", DateTime.Parse(item.proccessdate)), rowIndex); writer.WriteElement(c);
if (item.status == 1)
{
c = CreateCell(11, "Lập hóa đơn thay thế", rowIndex); writer.WriteElement(c);
}
else if (item.status == 2)
{
c = CreateCell(11, "Lập hóa đơn điều chỉnh", rowIndex); writer.WriteElement(c);
}
//write the end of the row
writer.WriteEndElement();
}
writer.WriteEndElement();
}
else if ((reader.IsStartElement))
{
//Start elements are directly copied
writer.WriteStartElement(reader);
}
else if ((reader.IsEndElement))
{
//End elements are directly copied
writer.WriteEndElement();
}
}
//Close the reader and writer
reader.Close();
writer.Close();
//Get the newly created sheet (same id as the old sheet, but it is the first one)
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
//Assign it the new sheet id
sheet.Id.Value = replacementPartId;
//remove the old sheet
workbookPart.DeletePart(worksheetPart);
//Done
myDoc.Close();
//Delete template file
if (File.Exists(templatePath))
{
File.Delete(templatePath);
}
}
}
