private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
{
writer.WriteElement(new Cell
{
CellValue = new CellValue(cellStringValue),
CellReference = cellReference,
DataType = CellValues.String
});
}
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);
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];
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");
}
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();
// 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
{
// 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();
}
/// <summary>
/// OpenWriter Style, for Multiple DataTable into Multiple Worksheets in a single Workbook. A real f*****g pain.
/// </summary>
/// <param name="filePath">Where your file will be.</param>
/// <param name="listDataTables">List of dataTables. Can contain just 1, doesn't matter.</param>
/// <param name="yesHeader">You want headers?</param>
/// <param name="yesZero">You want zero instead of null?</param>
public void LargeExportOneWorkbook(
string filePath,
IEnumerable <DataTable> listDataTables,
bool yesHeader = false,
bool yesZero = false)
{
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(
filePath,
SpreadsheetDocumentType.Workbook))
{
document.AddWorkbookPart();
OpenXmlWriter writerXb = OpenXmlWriter.Create(document.WorkbookPart);
writerXb.WriteStartElement(new Workbook());
writerXb.WriteStartElement(new Sheets());
var count = 0;
foreach (DataTable dt in listDataTables)
{
var dicColName = new Dictionary <int, string>();
for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
int dividend = colIndex + 1;
string columnName = string.Empty;
while (dividend > 0)
{
int modifier = (dividend - 1) % 26;
columnName =
$"{Convert.ToChar(65 + modifier).ToString(CultureInfo.InvariantCulture)}{columnName}";
dividend = (dividend - modifier) / 26;
}
dicColName.Add(colIndex + 1, columnName);
}
// var dicType = new Dictionary<Type, CellValues>(4)
// {
// { typeof(DateTime), CellValues.Date },
// { typeof(string), CellValues.InlineString },
// { typeof(double), CellValues.Number },
// { typeof(int), CellValues.Number },
// { typeof(bool), CellValues.Boolean }
// };
var dicType = new Dictionary <Type, string>(4)
{
{ typeof(DateTime), "d" },
{ typeof(string), "s" },
{ typeof(double), "n" },
{ typeof(int), "n" },
{ typeof(bool), "b" }
};
// this list of attributes will be used when writing a start element
List <OpenXmlAttribute> attributes;
var workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
if (yesHeader)
{
// create a new list of attributes
attributes = new List <OpenXmlAttribute>
{
// add the row index attribute to the list
new OpenXmlAttribute("r", null, 1.ToString())
};
// write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
for (var columnNum = 1; columnNum <= dt.Columns.Count; ++columnNum)
{
// reset the list of attributes
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", string.Empty, $"{dicColName[columnNum]}1")
};
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
// add the cell reference attribute
// write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
// write the cell value
writer.WriteElement(new CellValue(dt.Columns[columnNum - 1].ColumnName));
// writer.WriteElement(new CellValue(string.Format("This is Row {0}, Cell {1}", rowNum, columnNum)));
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
}
for (var rowNum = 1; rowNum <= dt.Rows.Count; rowNum++)
{
// create a new list of attributes
attributes = new List <OpenXmlAttribute> {
new OpenXmlAttribute("r", null, (yesHeader ? rowNum + 1 : rowNum).ToString())
};
// add the row index attribute to the list
// write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
DataRow dr = dt.Rows[rowNum - 1];
for (var columnNum = 1; columnNum <= dt.Columns.Count; columnNum++)
{
Type type = dt.Columns[columnNum - 1].DataType;
// reset the list of attributes
attributes = new List <OpenXmlAttribute>
{
// Add data type attribute - in this case inline string (you might want to look at the shared strings table)
new OpenXmlAttribute("t", null, type == typeof(string) ? "str" : dicType[type]),
// Add the cell reference attribute
new OpenXmlAttribute("r", string.Empty, $"{dicColName[columnNum]}{(yesHeader ? rowNum + 1 : rowNum).ToString(CultureInfo.InvariantCulture)}")
};
// write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
// write the cell value
if (yesZero | (dr[columnNum - 1].ToString() != "0"))
{
writer.WriteElement(new CellValue(dr[columnNum - 1].ToString()));
}
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
}
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
writer.Close();
writerXb.WriteElement(
new Sheet
{
Name = dt.TableName,
SheetId = Convert.ToUInt32(count + 1),
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
count++;
}
// End Sheets
writerXb.WriteEndElement();
// End Workbook
writerXb.WriteEndElement();
writerXb.Close();
document.WorkbookPart.Workbook.Save();
document.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
private static void AppendHeaderTextCell(string cellReference, string cellStringValue, OpenXmlWriter writer)
{
// Add a new "text" Cell to the first row in our Excel worksheet
// We set these cells to use "Style # 3", so they have a gray background color & white text.
writer.WriteElement(new Cell
{
CellValue = new CellValue(cellStringValue),
CellReference = cellReference,
DataType = CellValues.String
});
}
private static void AppendDateCell(string cellReference, DateTime dateTimeValue, OpenXmlWriter writer)
{
// Add a new "datetime" Excel Cell to our Row.
//
string cellStringValue = dateTimeValue.ToShortDateString();
writer.WriteElement(new Cell
{
CellValue = new CellValue(cellStringValue),
CellReference = cellReference,
DataType = CellValues.String
});
}
public void ExportToExcel()
{
try
{
using (SaveFileDialog sfd = new SaveFileDialog()
{
Filter = "Excel|*.xlsx",
ValidateNames = true,
})
{
if (sfd.ShowDialog() == DialogResult.OK)
{
SpreadsheetDocument xl = SpreadsheetDocument.Create(sfd.FileName, SpreadsheetDocumentType.Workbook);
List <OpenXmlAttribute> oxa;
OpenXmlWriter oxw;
xl.AddWorkbookPart();
WorksheetPart wsp = xl.WorkbookPart.AddNewPart <WorksheetPart>();
oxw = OpenXmlWriter.Create(wsp);
oxw.WriteStartElement(new Worksheet());
oxw.WriteStartElement(new SheetData());
foreach (ListViewItem item in listView1.Items)
{
oxa = new List <OpenXmlAttribute>();
oxw.WriteStartElement(new Row(), oxa);
foreach (ListViewItem.ListViewSubItem subItem in item.SubItems)
{
oxa = new List <OpenXmlAttribute>();
oxw.WriteStartElement(new Cell(), oxa);
oxw.WriteElement(new CellValue(subItem.ToString()));
// this is for Cell
oxw.WriteEndElement();
}
// this is for Row
oxw.WriteEndElement();
}
// this is for SheetData
oxw.WriteEndElement();
// this is for Worksheet
oxw.WriteEndElement();
oxw.Close();
oxw = OpenXmlWriter.Create(xl.WorkbookPart);
oxw.WriteStartElement(new Workbook());
oxw.WriteStartElement(new Sheets());
oxw.WriteElement(new Sheet()
{
Name = "Sheet1",
SheetId = 1,
Id = xl.WorkbookPart.GetIdOfPart(wsp)
});
// this is for Sheets
oxw.WriteEndElement();
// this is for Workbook
oxw.WriteEndElement();
oxw.Close();
xl.Close();
MessageBox.Show("Veriler Excel Dosyasına Başarıyla Kaydedildi", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
throw;
}
}
private static 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);
}
}
/// <summary>
/// Conducts the import of the file to the workbook
/// </summary>
public override void Import()
{
Trace.Indent();
Trace.WriteLine(string.Format("Importing file {0}", FileInfo.AbsolutePath), TraceCategory);
if (!File.Exists(FileInfo.AbsolutePath))
{
Trace.TraceWarning("Importing file {0}", FileInfo.AbsolutePath);
return;
}
var csvSourcePath = File.OpenRead(FileInfo.AbsolutePath);
// get the existing sheets collection
var sheets = TargetWorkbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
// used as the name for the sheet and import tracking display
var sheetName = GetSheetName(sheets);
// we need to check to see if a sheet with this name already exists
if (sheets.Cast <Sheet>().Where(s => s.Name.Value.Equals(sheetName, StringComparison.OrdinalIgnoreCase)).Any())
{
throw new TargetSheetExistsException(sheetName);
}
var totalLineCount = File.ReadAllLines(FileInfo.AbsolutePath).LongCount();
_progressCallback(new SMATGeneratorProgressInfo()
{
CurrentFileMax = totalLineCount,
CurrentFileName = FileInfo.Filename,
CurrentFilePosition = 0,
TotalFilesMax = 0,
TotalFilesPosition = 0,
});
// create the new worksheet
var worksheetPart = TargetWorkbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheet = new Sheet()
{
Id = TargetWorkbook.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheets.NextAvailableSheetId(), Name = sheetName
};
sheets.Append(sheet);
using (var csvReader = new TextFieldParser(csvSourcePath))
{
csvReader.Delimiters = new[] { "," };
csvReader.TextFieldType = FieldType.Delimited;
csvReader.HasFieldsEnclosedInQuotes = true;
csvReader.TrimWhiteSpace = true;
// used to track the current row
uint currentTargetRowPointer = 1;
var typeEnumMapping = new List <CellValues>();
string cellAddress;
string[] fields;
using (var writer = OpenXmlWriter.Create(worksheetPart))
{
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
while (!csvReader.EndOfData)
{
fields = csvReader.ReadFields();
try
{
// line 2 means "fields" actually has the values from row 1 but the pointer has been advanced in LineNumber
if (csvReader.LineNumber == 2)
{
using (writer.StartRow(currentTargetRowPointer))
{
// the original template sheets had two empty columns, so we match that
writer.WriteCell(string.Format("B{0}", currentTargetRowPointer), "Remediation", CellValues.InlineString);
for (var i = 0; i < fields.Length; i++)
{
// the +3 here is derived from a need to +1 for the 1 based columns and +2 as we skip the first two columns by design
cellAddress = GetCellAddress((uint)(i + 3), currentTargetRowPointer);
writer.WriteCell(cellAddress, fields[i], CellValues.InlineString);
}
}
}
else
{
// line 3 means "fields" actually has the values from row 2 but the pointer has been advanced in LineNumber
if (typeEnumMapping.Count < 1)
{
// we need to use the first row of data to determine as best we can the column types
for (var i = 0; i < fields.Length; i++)
{
typeEnumMapping.Add(GetCellDataType(fields[i]));
}
}
using (writer.StartRow(currentTargetRowPointer))
{
for (var i = 0; i < fields.Length; i++)
{
// the +3 here is derived from a need to +1 for the 1 based columns and +2 as we skip the first two columns by design
cellAddress = GetCellAddress((uint)(i + 3), currentTargetRowPointer);
writer.WriteCell(cellAddress, fields[i], typeEnumMapping[i]);
}
}
}
}
catch (Exception err)
{
Trace.TraceError(string.Format("Error writing row {0} (csv line number: {1}) for target sheet {2}. Error: {3}", currentTargetRowPointer, csvReader.LineNumber, sheetName, err));
}
finally
{
// move to the next row
currentTargetRowPointer++;
}
if (csvReader.LineNumber > 0)
{
_progressCallback(new SMATGeneratorProgressInfo()
{
CurrentFileMax = totalLineCount,
CurrentFileName = FileInfo.Filename,
CurrentFilePosition = currentTargetRowPointer,
TotalFilesMax = 0,
TotalFilesPosition = 0,
});
}
}
writer.WriteEndElement(); // end sheet data
writer.WriteEndElement(); // end worksheet
}
}
// always end with full progress reported
_progressCallback(new SMATGeneratorProgressInfo()
{
CurrentFileMax = totalLineCount,
CurrentFileName = FileInfo.Filename,
CurrentFilePosition = totalLineCount,
TotalFilesMax = 0,
TotalFilesPosition = 0,
});
Trace.Unindent();
}
private void WriteCommentPart(WorksheetCommentsPart wcp, VmlDrawingPart vdp)
{
List <SLCellPoint> listCommentKeys = slws.Comments.Keys.ToList <SLCellPoint>();
listCommentKeys.Sort(new SLCellReferencePointComparer());
bool bAuthorFound = false;
int iAuthorIndex = 0;
SLComment comm;
int i = 0;
SLRowProperties rp;
SLColumnProperties cp;
SLCellPoint pt;
// just in case
if (slws.Authors.Count == 0)
{
if (this.DocumentProperties.Creator.Length > 0)
{
slws.Authors.Add(this.DocumentProperties.Creator);
}
else
{
slws.Authors.Add(SLConstants.ApplicationName);
}
}
int iDataRange = 1;
// hah! optional... we'll see...
int iOptionalShapeTypeId = 202;
string sShapeTypeId = string.Format("_x0000_t{0}", iOptionalShapeTypeId);
int iShapeIdBase = iDataRange * 1024;
int iRowID = 0;
int iColumnID = 0;
double fRowRemainder = 0;
double fColumnRemainder = 0;
long lRowEMU = 0;
long lColumnEMU = 0;
long lRowRemainder = 0;
long lColumnRemainder = 0;
int iEMU = 0;
double fMargin = 0;
double fFrac = 0;
int iFrac = 0;
string sFrac = string.Empty;
ImagePart imgp;
string sFileName = string.Empty;
// image data in base 64, relationship ID
Dictionary <string, string> dictImageData = new Dictionary <string, string>();
// not supporting existing VML drawings. But if supporting, process the VmlDrawingPart for
// ImageParts.
// Apparently, Excel chokes if a "non-standard" relationship ID is given
// to VML drawings. It seems to only accept the form "rId{num}", and {num} seems
// to have to start from 1. I don't know if Excel will also choke if you jump
// from 1 to 3, but I *do* know you can't even start from rId3.
// Excel VML seems to be particularly strict on this...
// The error originated by having a "non-standard" relationship ID for a
// VML image. Say "R2dk723lgsjg2" or whatever. Then fire up Excel and open
// that spreadsheet. Then save. Then open it again. You'll get an error.
// Apparently, Excel will put "rId1" on the tag o:relid. The error is that
// the original o:relid with "R2dk723lgsjg2" as the value is still there.
// Meaning the o:relid attribute is duplicated, hence the error.
// Why don't I just use the number of vdp.Parts or even vdp.ImageParts to
// get the next valid relationship ID? I don't know. Paranoia?
// The existing relationship IDs *might* be in sequential order, but you never
// know what Excel accepts... If you can get me the Microsoft Excel developer
// who can explain this, I'll gladly change the algorithm...
// So why the dictionary? Apparently, Excel also chokes if there are duplicates of
// the VML image. So even 2 unique relationship IDs that *happens* to have identical
// image data will tie Excel into knots. I am so upset with Excel right now...
// I know it will keep file size down if only unique image data is stored, but still...
StringBuilder sbVml = new StringBuilder();
sbVml.Append("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sbVml.Append("<o:shapelayout v:ext=\"edit\">");
sbVml.AppendFormat("<o:idmap v:ext=\"edit\" data=\"{0}\"/>", iDataRange);
sbVml.Append("</o:shapelayout>");
sbVml.AppendFormat("<v:shapetype id=\"{0}\" coordsize=\"21600,21600\" o:spt=\"{1}\" path=\"m,l,21600r21600,l21600,xe\">", sShapeTypeId, iOptionalShapeTypeId);
sbVml.Append("<v:stroke joinstyle=\"miter\"/><v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>");
sbVml.Append("</v:shapetype>");
using (OpenXmlWriter oxwComment = OpenXmlWriter.Create(wcp))
{
oxwComment.WriteStartElement(new Comments());
oxwComment.WriteStartElement(new Authors());
for (i = 0; i < slws.Authors.Count; ++i)
{
oxwComment.WriteElement(new Author(slws.Authors[i]));
}
oxwComment.WriteEndElement();
oxwComment.WriteStartElement(new CommentList());
for (i = 0; i < listCommentKeys.Count; ++i)
{
pt = listCommentKeys[i];
comm = slws.Comments[pt];
bAuthorFound = false;
for (iAuthorIndex = 0; iAuthorIndex < slws.Authors.Count; ++iAuthorIndex)
{
if (comm.Author.Equals(slws.Authors[iAuthorIndex]))
{
bAuthorFound = true;
break;
}
}
if (!bAuthorFound)
{
iAuthorIndex = 0;
}
oxwComment.WriteStartElement(new Comment()
{
Reference = SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex),
AuthorId = (uint)iAuthorIndex
});
oxwComment.WriteElement(comm.rst.ToCommentText());
oxwComment.WriteEndElement();
sbVml.AppendFormat("<v:shape id=\"_x0000_s{0}\" type=\"#{1}\"", iShapeIdBase + i + 1, sShapeTypeId);
sbVml.Append(" style='position:absolute;");
if (!comm.HasSetPosition)
{
comm.Top = pt.RowIndex - 1 + SLConstants.DefaultCommentTopOffset;
comm.Left = pt.ColumnIndex + SLConstants.DefaultCommentLeftOffset;
if (comm.Top < 0)
{
comm.Top = 0;
}
if (comm.Left < 0)
{
comm.Left = 0;
}
}
if (comm.UsePositionMargin)
{
sbVml.AppendFormat("margin-left:{0}pt;", comm.LeftMargin.ToString("0.##", CultureInfo.InvariantCulture));
sbVml.AppendFormat("margin-top:{0}pt;", comm.TopMargin.ToString("0.##", CultureInfo.InvariantCulture));
}
else
{
iRowID = (int)Math.Floor(comm.Top);
fRowRemainder = comm.Top - iRowID;
iColumnID = (int)Math.Floor(comm.Left);
fColumnRemainder = comm.Left - iColumnID;
lRowEMU = 0;
lColumnEMU = 0;
for (iEMU = 1; iEMU <= iRowID; ++iEMU)
{
if (slws.RowProperties.ContainsKey(iEMU))
{
rp = slws.RowProperties[iEMU];
lRowEMU += rp.HeightInEMU;
}
else
{
lRowEMU += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
if (slws.RowProperties.ContainsKey(iRowID + 1))
{
rp = slws.RowProperties[iRowID + 1];
lRowRemainder = Convert.ToInt64(fRowRemainder * rp.HeightInEMU);
lRowEMU += lRowRemainder;
}
else
{
lRowRemainder = Convert.ToInt64(fRowRemainder * slws.SheetFormatProperties.DefaultRowHeightInEMU);
lRowEMU += lRowRemainder;
}
for (iEMU = 1; iEMU <= iColumnID; ++iEMU)
{
if (slws.ColumnBreaks.ContainsKey(iEMU))
{
cp = slws.ColumnProperties[iEMU];
lColumnEMU += cp.WidthInEMU;
}
else
{
lColumnEMU += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
if (slws.ColumnProperties.ContainsKey(iColumnID + 1))
{
cp = slws.ColumnProperties[iColumnID + 1];
lColumnRemainder = Convert.ToInt64(fColumnRemainder * cp.WidthInEMU);
lColumnEMU += lColumnRemainder;
}
else
{
lColumnRemainder = Convert.ToInt64(fColumnRemainder * slws.SheetFormatProperties.DefaultColumnWidthInEMU);
lColumnEMU += lColumnRemainder;
}
fMargin = (double)lColumnEMU / (double)SLConstants.PointToEMU;
sbVml.AppendFormat("margin-left:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture));
fMargin = (double)lRowEMU / (double)SLConstants.PointToEMU;
sbVml.AppendFormat("margin-top:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture));
}
if (comm.AutoSize)
{
sbVml.Append("width:auto;height:auto;");
}
else
{
sbVml.AppendFormat("width:{0}pt;", comm.Width.ToString("0.##", CultureInfo.InvariantCulture));
sbVml.AppendFormat("height:{0}pt;", comm.Height.ToString("0.##", CultureInfo.InvariantCulture));
}
sbVml.AppendFormat("z-index:{0};", i + 1);
sbVml.AppendFormat("visibility:{0}'", comm.Visible ? "visible" : "hidden");
if (!comm.Fill.HasFill)
{
// use #ffffff ?
sbVml.Append(" fillcolor=\"window [65]\"");
}
else if (comm.Fill.Type == SLA.SLFillType.NoFill)
{
sbVml.Append(" filled=\"f\"");
sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
}
else if (comm.Fill.Type == SLA.SLFillType.SolidFill)
{
sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
}
else if (comm.Fill.Type == SLA.SLFillType.GradientFill)
{
if (comm.Fill.GradientColor.GradientStops.Count > 0)
{
sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"",
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"),
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"),
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2"));
}
}
else if (comm.Fill.Type == SLA.SLFillType.BlipFill)
{
// don't have to do anything
}
else if (comm.Fill.Type == SLA.SLFillType.PatternFill)
{
sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"",
comm.Fill.PatternForegroundColor.DisplayColor.R.ToString("x2"),
comm.Fill.PatternForegroundColor.DisplayColor.G.ToString("x2"),
comm.Fill.PatternForegroundColor.DisplayColor.B.ToString("x2"));
}
if (comm.LineColor != null)
{
sbVml.AppendFormat(" strokecolor=\"#{0}{1}{2}\"",
comm.LineColor.Value.R.ToString("x2"),
comm.LineColor.Value.G.ToString("x2"),
comm.LineColor.Value.B.ToString("x2"));
}
if (comm.fLineWeight != null)
{
sbVml.AppendFormat(" strokeweight=\"{0}pt\"", comm.fLineWeight.Value.ToString("0.##", CultureInfo.InvariantCulture));
}
sbVml.Append(" o:insetmode=\"auto\">");
sbVml.Append("<v:fill");
if (comm.Fill.Type == SLA.SLFillType.SolidFill || comm.Fill.Type == SLA.SLFillType.GradientFill)
{
if (comm.Fill.Type == SLA.SLFillType.SolidFill)
{
fFrac = 100.0 - (double)comm.Fill.SolidColor.Transparency;
}
else
{
fFrac = 100.0 - comm.bFromTransparency;
}
iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0);
if (iFrac <= 0)
{
sFrac = "0";
}
else if (iFrac >= 65536)
{
sFrac = "1";
}
else
{
sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture));
}
// default is 1
if (!sFrac.Equals("1"))
{
sbVml.AppendFormat(" opacity=\"{0}\"", sFrac);
}
}
if (comm.Fill.Type == SLA.SLFillType.SolidFill)
{
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
}
else if (comm.Fill.Type == SLA.SLFillType.GradientFill)
{
if (comm.Fill.GradientColor.GradientStops.Count > 0)
{
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"),
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"),
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2"));
}
else
{
// shouldn't happen, but you know, in case...
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
}
fFrac = 100.0 - comm.bToTransparency;
iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0);
if (iFrac <= 0)
{
sFrac = "0";
}
else if (iFrac >= 65536)
{
sFrac = "1";
}
else
{
sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture));
}
// default is 1
if (!sFrac.Equals("1"))
{
sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac);
}
sbVml.Append(" rotate=\"t\"");
if (comm.Fill.GradientColor.GradientStops.Count > 0)
{
sbVml.Append(" colors=\"");
for (int iGradient = 0; iGradient < comm.Fill.GradientColor.GradientStops.Count; ++iGradient)
{
// you take the position/gradient value straight
fFrac = (double)comm.Fill.GradientColor.GradientStops[iGradient].Position;
iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0);
if (iFrac <= 0)
{
sFrac = "0";
}
else if (iFrac >= 65536)
{
sFrac = "1";
}
else
{
sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture));
}
if (iGradient > 0)
{
sbVml.Append(";");
}
sbVml.AppendFormat("{0} #{1}{2}{3}", sFrac,
comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.R.ToString("x2"),
comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.G.ToString("x2"),
comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.B.ToString("x2"));
}
sbVml.Append("\"");
}
if (comm.Fill.GradientColor.IsLinear)
{
// use temporarily
// VML increases angles in counter-clockwise direction,
// otherwise we'd just use the angle straight from the property
//...fFrac = 360.0 - (double)comm.Fill.GradientColor.Angle;
fFrac = (double)comm.Fill.GradientColor.Angle;
sbVml.AppendFormat(" angle=\"{0}\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture));
sbVml.Append(" focus=\"100%\" type=\"gradient\"");
}
else
{
switch (comm.Fill.GradientColor.PathType)
{
case A.PathShadeValues.Shape:
sbVml.Append(" focusposition=\"50%,50%\" focus=\"100%\" type=\"gradientradial\"");
break;
case A.PathShadeValues.Rectangle:
case A.PathShadeValues.Circle:
// because there's no way to do a circular gradient with VML...
switch (comm.Fill.GradientColor.Direction)
{
case SLA.SLGradientDirectionValues.Center:
sbVml.Append(" focusposition=\"50%,50%\"");
break;
case SLA.SLGradientDirectionValues.CenterToBottomLeftCorner:
// so the "centre" is at the top-right
sbVml.Append(" focusposition=\"100%,0%\"");
break;
case SLA.SLGradientDirectionValues.CenterToBottomRightCorner:
// so the "centre" is at the top-left
sbVml.Append(" focusposition=\"0%,0%\"");
break;
case SLA.SLGradientDirectionValues.CenterToTopLeftCorner:
// so the "centre" is at the bottom-right
sbVml.Append(" focusposition=\"100%,100%\"");
break;
case SLA.SLGradientDirectionValues.CenterToTopRightCorner:
// so the "centre" is at the bottom-left
sbVml.Append(" focusposition=\"0%,100%\"");
break;
}
sbVml.Append(" focus=\"100%\" type=\"gradientradial\"");
break;
}
}
}
else if (comm.Fill.Type == SLA.SLFillType.BlipFill)
{
string sRelId = "rId1";
using (FileStream fs = new FileStream(comm.Fill.BlipFileName, FileMode.Open))
{
byte[] ba = new byte[fs.Length];
fs.Read(ba, 0, ba.Length);
string sImageData = Convert.ToBase64String(ba);
if (dictImageData.ContainsKey(sImageData))
{
sRelId = dictImageData[sImageData];
comm.Fill.BlipRelationshipID = sRelId;
}
else
{
// if we haven't found a viable relationship ID by 10 million iterations,
// then we have serious issues...
for (int iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum)
{
sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture));
// we could use a hashset to store the relationship IDs so we
// don't use the ContainsValue() because ContainsValue() is supposedly
// slow... I'm not gonna care because if this algorithm slows enough
// that ContainsValue() is inefficient, that means there are enough VML
// drawings to choke a modestly sized art museum.
if (!dictImageData.ContainsValue(sRelId))
{
break;
}
}
imgp = vdp.AddImagePart(SLA.SLDrawingTool.GetImagePartType(comm.Fill.BlipFileName), sRelId);
fs.Position = 0;
imgp.FeedData(fs);
comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp);
dictImageData[sImageData] = sRelId;
}
}
sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID);
// all this to get from "myawesomepicture.jpg" to "myawesomepicture"
sFileName = comm.Fill.BlipFileName;
// use temporarily
iFrac = sFileName.LastIndexOfAny("\\/".ToCharArray());
sFileName = sFileName.Substring(iFrac + 1);
iFrac = sFileName.LastIndexOf(".");
sFileName = sFileName.Substring(0, iFrac);
sbVml.AppendFormat(" o:title=\"{0}\"", sFileName);
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
sbVml.Append(" recolor=\"t\" rotate=\"t\"");
fFrac = 100.0 - (double)comm.Fill.BlipTransparency;
iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0);
if (iFrac <= 0)
{
sFrac = "0";
}
else if (iFrac >= 65536)
{
sFrac = "1";
}
else
{
sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture));
}
// default is 1
if (!sFrac.Equals("1"))
{
sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac);
}
if (comm.Fill.BlipTile)
{
sbVml.Append(" type=\"tile\"");
sbVml.AppendFormat(" size=\"{0}%,{1}%\"",
comm.Fill.BlipScaleX.ToString("0.##", CultureInfo.InvariantCulture),
comm.Fill.BlipScaleY.ToString("0.##", CultureInfo.InvariantCulture));
}
else
{
sbVml.Append(" type=\"frame\"");
// use temporarily
//fFrac = (50.0 - (double)comm.Fill.BlipLeftOffset) + (50.0 - (double)comm.Fill.BlipRightOffset);
fFrac = 100.0 - (double)comm.Fill.BlipLeftOffset - (double)comm.Fill.BlipRightOffset;
sbVml.AppendFormat(" size=\"{0}%,", fFrac.ToString("0.##", CultureInfo.InvariantCulture));
fFrac = 100.0 - (double)comm.Fill.BlipTopOffset - (double)comm.Fill.BlipBottomOffset;
sbVml.AppendFormat("{0}%\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture));
}
}
else if (comm.Fill.Type == SLA.SLFillType.PatternFill)
{
string sRelId = "rId1";
using (MemoryStream ms = new MemoryStream())
{
using (System.Drawing.Bitmap bm = SLA.SLDrawingTool.GetVmlPatternFill(comm.Fill.PatternPreset))
{
bm.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
}
byte[] ba = new byte[ms.Length];
ms.Read(ba, 0, ba.Length);
string sImageData = Convert.ToBase64String(ba);
if (dictImageData.ContainsKey(sImageData))
{
sRelId = dictImageData[sImageData];
comm.Fill.BlipRelationshipID = sRelId;
}
else
{
// go check the "normal" image part additions for comments...
for (int iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum)
{
sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture));
if (!dictImageData.ContainsValue(sRelId))
{
break;
}
}
imgp = vdp.AddImagePart(ImagePartType.Png, sRelId);
ms.Position = 0;
imgp.FeedData(ms);
comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp);
dictImageData[sImageData] = sRelId;
}
}
sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID);
sbVml.AppendFormat(" o:title=\"{0}\"", SLA.SLDrawingTool.ConvertToVmlTitle(comm.Fill.PatternPreset));
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.PatternBackgroundColor.DisplayColor.R.ToString("x2"),
comm.Fill.PatternBackgroundColor.DisplayColor.G.ToString("x2"),
comm.Fill.PatternBackgroundColor.DisplayColor.B.ToString("x2"));
sbVml.Append(" recolor=\"t\" type=\"pattern\"");
}
sbVml.Append("/>");
if (comm.LineStyle != StrokeLineStyleValues.Single || comm.vLineDashStyle != null)
{
sbVml.Append("<v:stroke");
switch (comm.LineStyle)
{
case StrokeLineStyleValues.Single:
// don't have to do anything
break;
case StrokeLineStyleValues.ThickBetweenThin:
sbVml.Append(" linestyle=\"thickBetweenThin\"/>");
break;
case StrokeLineStyleValues.ThickThin:
sbVml.Append(" linestyle=\"thickThin\"/>");
break;
case StrokeLineStyleValues.ThinThick:
sbVml.Append(" linestyle=\"thinThick\"/>");
break;
case StrokeLineStyleValues.ThinThin:
sbVml.Append(" linestyle=\"thinThin\"/>");
break;
}
if (comm.vLineDashStyle != null)
{
switch (comm.vLineDashStyle.Value)
{
case SLDashStyleValues.Solid:
sbVml.Append(" dashstyle=\"solid\"/>");
break;
case SLDashStyleValues.ShortDash:
sbVml.Append(" dashstyle=\"shortdash\"/>");
break;
case SLDashStyleValues.ShortDot:
sbVml.Append(" dashstyle=\"shortdot\"/>");
break;
case SLDashStyleValues.ShortDashDot:
sbVml.Append(" dashstyle=\"shortdashdot\"/>");
break;
case SLDashStyleValues.ShortDashDotDot:
sbVml.Append(" dashstyle=\"shortdashdotdot\"/>");
break;
case SLDashStyleValues.Dot:
sbVml.Append(" dashstyle=\"dot\"/>");
break;
case SLDashStyleValues.Dash:
sbVml.Append(" dashstyle=\"dash\"/>");
break;
case SLDashStyleValues.LongDash:
sbVml.Append(" dashstyle=\"longdash\"/>");
break;
case SLDashStyleValues.DashDot:
sbVml.Append(" dashstyle=\"dashdot\"/>");
break;
case SLDashStyleValues.LongDashDot:
sbVml.Append(" dashstyle=\"longdashdot\"/>");
break;
case SLDashStyleValues.LongDashDotDot:
sbVml.Append(" dashstyle=\"longdashdotdot\"/>");
break;
}
}
if (comm.vEndCap != null)
{
switch (comm.vEndCap.Value)
{
case StrokeEndCapValues.Flat:
sbVml.Append(" endcap=\"flat\"/>");
break;
case StrokeEndCapValues.Round:
sbVml.Append(" endcap=\"round\"/>");
break;
case StrokeEndCapValues.Square:
sbVml.Append(" endcap=\"square\"/>");
break;
}
}
sbVml.Append("/>");
}
if (comm.HasShadow)
{
sbVml.AppendFormat("<v:shadow on=\"t\" color=\"#{0}{1}{2}\" obscured=\"t\"/>",
comm.ShadowColor.R.ToString("x2"),
comm.ShadowColor.G.ToString("x2"),
comm.ShadowColor.B.ToString("x2"));
}
sbVml.Append("<v:path o:connecttype=\"none\"/>");
sbVml.Append("<v:textbox style='mso-direction-alt:auto;");
switch (comm.Orientation)
{
case SLCommentOrientationValues.Horizontal:
// don't have to do anything
break;
case SLCommentOrientationValues.TopDown:
sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:top-to-bottom;");
break;
case SLCommentOrientationValues.Rotated270Degrees:
sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:bottom-to-top;");
break;
case SLCommentOrientationValues.Rotated90Degrees:
sbVml.Append("layout-flow:vertical;");
break;
}
if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft)
{
sbVml.Append("direction:RTL;");
}
// no else because don't have to do anything
if (comm.AutoSize)
{
sbVml.Append("mso-fit-shape-to-text:t;");
}
sbVml.Append("'><div");
if (comm.HorizontalTextAlignment != SLHorizontalTextAlignmentValues.Distributed ||
comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft)
{
sbVml.Append(" style='");
switch (comm.HorizontalTextAlignment)
{
case SLHorizontalTextAlignmentValues.Left:
sbVml.Append("text-align:left;");
break;
case SLHorizontalTextAlignmentValues.Justify:
sbVml.Append("text-align:justify;");
break;
case SLHorizontalTextAlignmentValues.Center:
sbVml.Append("text-align:center;");
break;
case SLHorizontalTextAlignmentValues.Right:
sbVml.Append("text-align:right;");
break;
case SLHorizontalTextAlignmentValues.Distributed:
// don't have to do anything
break;
}
if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft)
{
sbVml.Append("direction:rtl;");
}
sbVml.Append("'");
}
sbVml.Append("></div>");
sbVml.Append("</v:textbox>");
sbVml.Append("<x:ClientData ObjectType=\"Note\">");
sbVml.Append("<x:MoveWithCells/>");
sbVml.Append("<x:SizeWithCells/>");
// anchors are bloody hindering awkward inconvenient to calculate...
//sbVml.Append("<x:Anchor>");
//sbVml.Append("2, 15, 2, 14, 4, 23, 6, 19");
//sbVml.Append("</x:Anchor>");
sbVml.Append("<x:AutoFill>False</x:AutoFill>");
switch (comm.HorizontalTextAlignment)
{
case SLHorizontalTextAlignmentValues.Left:
// don't have to do anything
break;
case SLHorizontalTextAlignmentValues.Justify:
sbVml.Append("<x:TextHAlign>Justify</x:TextHAlign>");
break;
case SLHorizontalTextAlignmentValues.Center:
sbVml.Append("<x:TextHAlign>Center</x:TextHAlign>");
break;
case SLHorizontalTextAlignmentValues.Right:
sbVml.Append("<x:TextHAlign>Right</x:TextHAlign>");
break;
case SLHorizontalTextAlignmentValues.Distributed:
sbVml.Append("<x:TextHAlign>Distributed</x:TextHAlign>");
break;
}
switch (comm.VerticalTextAlignment)
{
case SLVerticalTextAlignmentValues.Top:
// don't have to do anything
break;
case SLVerticalTextAlignmentValues.Justify:
sbVml.Append("<x:TextVAlign>Justify</x:TextVAlign>");
break;
case SLVerticalTextAlignmentValues.Center:
sbVml.Append("<x:TextVAlign>Center</x:TextVAlign>");
break;
case SLVerticalTextAlignmentValues.Bottom:
sbVml.Append("<x:TextVAlign>Bottom</x:TextVAlign>");
break;
case SLVerticalTextAlignmentValues.Distributed:
sbVml.Append("<x:TextVAlign>Distributed</x:TextVAlign>");
break;
}
sbVml.AppendFormat("<x:Row>{0}</x:Row>", pt.RowIndex - 1);
sbVml.AppendFormat("<x:Column>{0}</x:Column>", pt.ColumnIndex - 1);
if (comm.Visible)
{
sbVml.Append("<x:Visible/>");
}
sbVml.Append("</x:ClientData>");
sbVml.Append("</v:shape>");
}
oxwComment.WriteEndElement();
// this is for Comments
oxwComment.WriteEndElement();
}
sbVml.Append("</xml>");
using (MemoryStream mem = new MemoryStream(Encoding.ASCII.GetBytes(sbVml.ToString())))
{
vdp.FeedData(mem);
}
}
public MergeCellWriter(OpenXmlWriter writer)
{
this.writer = writer;
}
/// <summary>
/// 将对象保存到文件中。
/// </summary>
/// <typeparam name="TModel">模型列表类型。</typeparam>
/// <param name="models">模型列表实例。</param>
/// <param name="path">路径。</param>
public void Save <TModel>(IEnumerable <TModel> models, string path) where TModel : class, new()
{
var data = models as ExcelEnumerable <TModel> ?? new ExcelEnumerable <TModel>(models);
using (var document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
var index = 1U;
var workbookPart = document.AddWorkbookPart();
//写入样式
WriteStylesheet(workbookPart, data);
//工作表
var workSheetPart = workbookPart.AddNewPart <WorksheetPart>();
var writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
//字段定义
var descriptors = data.Descriptors.OrderBy(x => x.Index).ToList();
var sharedStrings = new List <string>();
//第一行标题
var row = new Row();
row.RowIndex = index;
for (var i = 0; i < data.Columns; i++)
{
var descriptor = descriptors[i];
var cell = new Cell();
cell.StyleIndex = descriptor.HeadCellFormat.FormatId;
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(descriptor.ColumnName);
cell.CellReference = $"{(char)('A' + i)}{index}";
row.AppendChild(cell);
}
writer.WriteElement(row);
index++;
//写入数据
foreach (var model in data)
{
row = new Row();
row.RowIndex = index;
for (var i = 0; i < data.Columns; i++)
{
var descriptor = descriptors[i];
var value = descriptor.Get(model);
if (value == null)
{
continue;
}
var type = CellValues.Error;
if (value is DateTime date)
{
value = date.ToOADate();
}
else if (value is DateTimeOffset dateTimeOffset)
{
value = dateTimeOffset.DateTime.ToOADate();
}
else if (value is bool bValue)
{
value = bValue ? 1 : 0;
type = CellValues.Boolean;
}
else if (!value.GetType().IsValueType)
{
type = CellValues.SharedString;
var current = value.ToString();
var si = sharedStrings.IndexOf(current);
if (si == -1)
{
si = sharedStrings.Count;
sharedStrings.Add(current);
}
value = si;
}
var cell = new Cell();
cell.StyleIndex = descriptor.CellFormat.FormatId;
if (type != CellValues.Error)
{
cell.DataType = type;
}
cell.CellReference = $"{(char)('A' + i)}{index}";
cell.CellValue = new CellValue(value.ToString());
row.AppendChild(cell);
}
writer.WriteElement(row);
index++;
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
//工作区
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet
{
Name = data.SheetName,
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
//写入字符串
var shared = workbookPart.AddNewPart <SharedStringTablePart>();
var table = new SharedStringTable();
foreach (var sharedString in sharedStrings)
{
table.AppendChild(new SharedStringItem(new Text(sharedString)));
}
table.Save(shared);
}
}
private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
{
// Add a new Excel Cell to our Row
writer.WriteElement(new Cell
{
CellValue = new CellValue(StripURL(cellStringValue)),
CellReference = cellReference,
DataType = CellValues.String
});
}
/*
* report.GetReportData()
*/
public ActionResult Export(Reports report)
{
try
{
var reportData = report.GetReportData(true);
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("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
CharSet = "utf-8"
}.ToString();
context.AppendHeader("Content-Disposition",
new ContentDisposition
{
DispositionType = DispositionTypeNames.Attachment,
FileName = string.Format(CultureInfo.InvariantCulture, RemoveInvalidFilePathCharacters(reportData.title) + "_{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(new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}), // 0
new Font(new Bold()), // 1
new Font(new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}) // 2
),
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 = new Dictionary <string, object>()
{
{ Words.Reports_Name, "" },
{ Words.Reports_Value, "" }
};
if (firstRow != null)
{
int row = 1;
AddLine(writer, row, new string[] { reportData.title }); row++;
AddLine(writer, row, new string[] { "" }); row++;
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++;
for (int i = 0; i < reportData.labels.Length; i++)
{
attributes =
new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col = 1;
var row2 = new Dictionary <string, object>()
{
{ "Name", reportData.labels[i] },
{ "Value", reportData.datasets[0].data[i] }
};
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);
}
}
}
catch (Exception ex)
{
ex.Log();
}
return(null);
}
private void CreateDrawingBySAX(DrawingsPart drawingsPart)
{
var data = _data as IReadOnlyList <Sheet3Data>;
var dataDic = new Dictionary <C.Values, C.SeriesText>();
for (uint i = 1; i <= data.First().DataDic.Count; i++)
{
var columnName = OpenXMLExcels.GetColumnNameByIndex(i);
dataDic[new C.Values()
{
NumberReference = new C.NumberReference()
{
Formula = new C.Formula($"{_sheetName}!${columnName}$2:${columnName}${data.Count + 2}")
}
}]
= new C.SeriesText()
{
StringReference = new C.StringReference()
{
Formula = new C.Formula($"{_sheetName}!${columnName}$1")
}
};
}
var chartPart = drawingsPart.AddNewPart <ChartPart>();
drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
var twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new Xdr.TwoCellAnchor()
{
FromMarker = new Xdr.FromMarker()
{
ColumnId = new Xdr.ColumnId("5"),
ColumnOffset = new Xdr.ColumnOffset("581025"),
RowId = new Xdr.RowId("4"),
RowOffset = new Xdr.RowOffset("114300")
},
ToMarker = new Xdr.ToMarker()
{
ColumnId = new Xdr.ColumnId("13"),
ColumnOffset = new Xdr.ColumnOffset("276225"),
RowId = new Xdr.RowId("19"),
RowOffset = new Xdr.RowOffset("0")
}
});
twoCellAnchor.Append(new Xdr.GraphicFrame()
{
NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties()
{
NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties()
{
Id = 2, Name = "Chart 1", Title = "产品每月产量折线图"
},
NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties()
},
Transform = new Xdr.Transform()
{
Offset = new Offset()
{
X = 0, Y = 0
},
Extents = new Extents()
{
Cx = 0, Cy = 0
}
},
Graphic = new Graphic(new GraphicData(new C.ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart",
})
});
twoCellAnchor.Append(new Xdr.ClientData());
using (var writer = OpenXmlWriter.Create(chartPart))
{
//S: ChartSpace
writer.WriteStartElement(new C.ChartSpace());
writer.WriteElement(new C.EditingLanguage()
{
Val = "zh-CN"
});
//S: Chart
writer.WriteStartElement(new C.Chart());
//S: PlotArea
writer.WriteStartElement(new C.PlotArea());
writer.WriteElement(new C.Layout());
//S: LineChart
writer.WriteStartElement(new C.LineChart());
writer.WriteElement(new C.Grouping()
{
Val = C.GroupingValues.Standard
});
uint index = 0;
foreach (var dataKvp in dataDic)
{
//S: LineChartSeries
writer.WriteStartElement(new C.LineChartSeries());
writer.WriteElement(dataKvp.Key);
writer.WriteElement(new C.Index()
{
Val = index
});
writer.WriteElement(new C.Order()
{
Val = index
});
writer.WriteElement(dataKvp.Value);
if (index++ == 0)
{
var axisData = new C.CategoryAxisData()
{
StringReference = new C.StringReference()
{
Formula = new C.Formula($"{_sheetName}!$A$2:$A${data.Count + 2}")
}
};
writer.WriteElement(axisData);
}
//E: LineChartSeries
writer.WriteEndElement();
}
writer.WriteElement(new C.AxisId()
{
Val = 0
});
writer.WriteElement(new C.AxisId()
{
Val = 1
});
//E: LineChart
writer.WriteEndElement();
writer.WriteElement(new C.CategoryAxis(
new C.Crosses()
{
Val = C.CrossesValues.AutoZero
},
new C.AutoLabeled()
{
Val = true
},
new C.LabelAlignment()
{
Val = C.LabelAlignmentValues.Center
},
new C.LabelOffset()
{
Val = 100
})
{
AxisId = new C.AxisId()
{
Val = 0
},
Scaling = new C.Scaling(new C.Orientation()
{
Val = C.OrientationValues.MinMax
}),
AxisPosition = new C.AxisPosition()
{
Val = C.AxisPositionValues.Bottom
},
TickLabelPosition = new C.TickLabelPosition()
{
Val = C.TickLabelPositionValues.NextTo
},
CrossingAxis = new C.CrossingAxis()
{
Val = 1
},
});
writer.WriteElement(new C.ValueAxis(
new C.Crosses()
{
Val = C.CrossesValues.AutoZero
},
new C.CrossBetween()
{
Val = C.CrossBetweenValues.Between
})
{
AxisId = new C.AxisId()
{
Val = 1
},
Scaling = new C.Scaling(new C.Orientation()
{
Val = C.OrientationValues.MinMax
}),
AxisPosition = new C.AxisPosition()
{
Val = C.AxisPositionValues.Left
},
MajorGridlines = new C.MajorGridlines(),
NumberingFormat = new C.NumberingFormat()
{
FormatCode = "General", SourceLinked = true
},
TickLabelPosition = new C.TickLabelPosition()
{
Val = C.TickLabelPositionValues.NextTo
},
CrossingAxis = new C.CrossingAxis()
{
Val = 0
},
});
//E: PlotArea
writer.WriteEndElement();
writer.WriteElement(new C.Legend(
new C.LegendPosition()
{
Val = C.LegendPositionValues.Right
},
new C.Layout()
));
writer.WriteElement(new C.PlotVisibleOnly()
{
Val = true
});
//E: Chart
writer.WriteEndElement();
//E: ChartSpace
writer.WriteEndElement();
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);
}
/// <summary>
/// 将对象保存到文件中。
/// </summary>
/// <param name="path">路径。</param>
/// <param name="models">模型数据表格。</param>
/// <param name="sheetName">工作表名称。</param>
/// <param name="sheetId">索引Id。</param>
public void Save(string path, DataTable models, string sheetName = "sheet1", uint sheetId = 1)
{
using var document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook);
var index = 1U;
var workbookPart = document.AddWorkbookPart();
//写入样式
WriteStylesheet(workbookPart, null);
//工作表
var workSheetPart = workbookPart.AddNewPart <WorksheetPart>();
var writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
//字段定义
var sharedStrings = new List <string>();
//第一行标题
var row = new Row();
row.RowIndex = index;
for (var i = 0; i < models.Columns.Count; i++)
{
var descriptor = models.Columns[i];
var cell = new Cell();
cell.StyleIndex = 11;
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(descriptor.ColumnName);
cell.CellReference = $"{(char)('A' + i)}{index}";
row.AppendChild(cell);
}
writer.WriteElement(row);
index++;
//写入数据
foreach (DataRow model in models.Rows)
{
row = new Row();
row.RowIndex = index;
for (var i = 0; i < models.Columns.Count; i++)
{
var descriptor = models.Columns[i];
var value = model[descriptor.ColumnName];
if (value == null)
{
continue;
}
var type = CellValues.Error;
if (value is DateTime date)
{
value = date.ToOADate();
}
else if (value is DateTimeOffset dateTimeOffset)
{
value = dateTimeOffset.DateTime.ToOADate();
}
else if (value is bool bValue)
{
value = bValue ? 1 : 0;
type = CellValues.Boolean;
}
else if (!value.GetType().IsValueType)
{
type = CellValues.SharedString;
var current = value.ToString();
var si = sharedStrings.IndexOf(current);
if (si == -1)
{
si = sharedStrings.Count;
sharedStrings.Add(current);
}
value = si;
}
var cell = new Cell();
cell.StyleIndex = 10;
if (type != CellValues.Error)
{
cell.DataType = type;
}
cell.CellReference = $"{(char)('A' + i)}{index}";
cell.CellValue = new CellValue(value.ToString());
row.AppendChild(cell);
}
writer.WriteElement(row);
index++;
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
//工作区
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet
{
Name = sheetName,
SheetId = UInt32Value.FromUInt32(sheetId),
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
//写入字符串
var shared = workbookPart.AddNewPart <SharedStringTablePart>();
var table = new SharedStringTable();
foreach (var sharedString in sharedStrings)
{
table.AppendChild(new SharedStringItem(new Text(sharedString)));
}
table.Save(shared);
}
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 = "";
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
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
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");
}
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;
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
AppendNumericCell(cellReference, cellValue, writer);
}
}
else if (IsDateColumn[colInx])
{
// This is a date value.
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();
}
private static async Task <MemoryStream> DataToExcelStreamAsync(WriteRowsDelegate writeRows, IList <String> headers, string sheetName, List <int> columnWidths = null)
{
var xmlStream = ReportingHelper.GetResourceStream("Shesha.Web.DataTable.Excel.template.xlsx", typeof(ExcelUtility).Assembly);
using (var document = SpreadsheetDocument.Open(xmlStream, true))
{
var workbookPart = document.WorkbookPart;
var worksheetPart = workbookPart.WorksheetParts.First();
var originalSheetId = workbookPart.GetIdOfPart(worksheetPart);
var replacementPart = workbookPart.AddNewPart <WorksheetPart>();
var replacementPartId = workbookPart.GetIdOfPart(replacementPart);
// Configure the spreadsheet
SetSheetName(sheetName, document);
SetStyleSheet(document);
// Fit to page
var sp = new SheetProperties(new PageSetupProperties());
var ws = worksheetPart.Worksheet;
ws.SheetProperties = sp;
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true);
var pgOr = new PageSetup
{
// Page size A4 landscape
PaperSize = 9,
Orientation = OrientationValues.Landscape,
// Scale to fit to page width
FitToWidth = 1,
FitToHeight = 0
};
ws.AppendChild(pgOr);
var maxWidth = 0;
if (columnWidths != null)
{
var idx = 1;
var columns =
columnWidths
.Select(
w => new Column
{
CustomWidth = true,
Min = Convert.ToUInt32(idx),
Max = Convert.ToUInt32(idx++),
Width = w,
BestFit = false
})
.ToList();
var cols = new DocumentFormat.OpenXml.Spreadsheet.Columns(columns);
worksheetPart.Worksheet.InsertBefore(cols, worksheetPart.Worksheet.GetFirstChild <SheetData>());
}
else
{
maxWidth = headers.Select(h => h.Length).Max();
AddCellWidthStyles(Convert.ToUInt32(1), Convert.ToUInt32(headers.Count), maxWidth, document, worksheetPart);
}
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
using (var xmlReader = OpenXmlReader.Create(worksheetPart))
{
using (var xmlWriter = OpenXmlWriter.Create(replacementPart))
{
while (xmlReader.Read())
{
if (xmlReader.ElementType == typeof(SheetData))
{
if (xmlReader.IsEndElement)
{
continue;
}
xmlWriter.WriteStartElement(new SheetData());
var headerCell = new Cell(new CellValue());
headerCell.DataType = new EnumValue <CellValues>(CellValues.String);
// write headers
xmlWriter.WriteStartElement(new Row());
SetHeaderStyle(document, headerCell);
foreach (var header in headers)
{
headerCell.CellValue.Text = header;
xmlWriter.WriteElement(headerCell);
}
xmlWriter.WriteEndElement();
await writeRows.Invoke(xmlWriter);
xmlWriter.WriteEndElement();
}
else
{
if (xmlReader.IsStartElement)
{
xmlWriter.WriteStartElement(xmlReader);
}
else if (xmlReader.IsEndElement)
{
xmlWriter.WriteEndElement();
}
}
}
}
}
var sheet = workbookPart.Workbook.Descendants <Sheet>().First(s => s.Id.Value.Equals(originalSheetId));
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
return(xmlStream);
}
private static void AppendTextCell(string cellReference, string cellStringValue, OpenXmlWriter writer)
{
// Add a new "text" Cell to our Row
#if DATA_CONTAINS_FORMULAE
// If this item of data looks like a formula, let's store it in the Excel file as a formula rather than a string.
if (cellStringValue.StartsWith("="))
{
AppendFormulaCell(cellReference, cellStringValue, writer);
return;
}
#endif
// Add a new Excel Cell to our Row
writer.WriteElement(new Cell
{
CellValue = new CellValue(cellStringValue),
CellReference = cellReference,
DataType = CellValues.String
});
}
public static void CreateExcel()
{
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create("CustomerReport_Hard.xlsx", SpreadsheetDocumentType.Workbook))
{
OpenXmlWriter oxw;
List <OpenXmlAttribute> oxa;
spreadsheetDoc.AddWorkbookPart();
WorksheetPart wsp = spreadsheetDoc.WorkbookPart.AddNewPart <WorksheetPart>();
oxw = OpenXmlWriter.Create(wsp);
oxw.WriteStartElement(new Worksheet());
//columnas
oxw.WriteStartElement(new Columns());
oxa = new List <OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("min", null, "1"));
oxa.Add(new OpenXmlAttribute("max", null, "4"));
oxa.Add(new OpenXmlAttribute("width", null, "25"));
oxw.WriteStartElement(new Column(), oxa);
oxw.WriteEndElement();
oxa = new List <OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("min", null, "6"));
oxa.Add(new OpenXmlAttribute("max", null, "6"));
oxa.Add(new OpenXmlAttribute("width", null, "40"));
oxw.WriteStartElement(new Column(), oxa);
oxw.WriteEndElement();
oxw.WriteEndElement();
oxw.WriteStartElement(new SheetData());
oxw.WriteStartElement(new Row(), new List <OpenXmlAttribute>());
oxa = new List <OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("t", null, "str"));
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Name")); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Register Date")); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Last Buy")); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Product")); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Cost")); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Quantity")); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Total")); oxw.WriteEndElement();
oxw.WriteEndElement();
foreach (Customer customer in Report.GetCustomers())
{
oxw.WriteStartElement(new Row(), new List <OpenXmlAttribute>());
oxa = new List <OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("t", null, "str"));
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.Name)); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.RegisterDate)); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.LastBuy)); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.Item)); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.ItemCost.ToString())); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.Quantity.ToString())); oxw.WriteEndElement();
oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue((customer.Quantity * customer.ItemCost).ToString())); oxw.WriteEndElement();
oxw.WriteEndElement();
}
oxw.WriteEndElement();
oxw.WriteEndElement();
oxw.Close();
oxw = OpenXmlWriter.Create(spreadsheetDoc.WorkbookPart);
oxw.WriteStartElement(new Workbook());
oxw.WriteStartElement(new Sheets());
oxw.WriteElement(new Sheet()
{
Name = "Sheet1",
SheetId = 1,
Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(wsp)
});
oxw.WriteEndElement();
oxw.WriteEndElement();
oxw.Close();
spreadsheetDoc.Close();
}
}
private static void AppendFormulaCell(string cellReference, string cellStringValue, OpenXmlWriter writer)
{
// Add a new "formula" Excel Cell to our Row
writer.WriteElement(new Cell
{
CellFormula = new CellFormula(cellStringValue),
CellReference = cellReference,
DataType = CellValues.Number
});
}
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);
// Create cell with data
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(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
private static void AppendNumericCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
{
// Add a new Excel Cell to our Row
writer.WriteElement(new Cell {
CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.Number
});
}
public static void Write(this SpreadsheetDocument spreadsheet, string sheetName, IDataReader reader)
{
var fieldCount = reader.FieldCount;
var columns = new List <ExcelColumn>(fieldCount);
var rowRead = reader.Read();
for (var index = 0; index < fieldCount; index++)
{
columns.Add(new ExcelColumn(index, reader.GetName(index), reader.GetFieldType(index)));
}
//create workbook part
var wbp = spreadsheet.AddWorkbookPart();
wbp.Workbook = new Workbook();
wbp.Workbook.Append(new BookViews(new WorkbookView()));
var sheets = wbp.Workbook.AppendChild <Sheets>(new Sheets());
var stylesPart = wbp.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
//create worksheet part, and add it to the sheets collection in workbook
var wsp = wbp.AddNewPart <WorksheetPart>();
var sheet = new Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(wsp), SheetId = 1, Name = sheetName
};
sheets.Append(sheet);
var sheetCount = 1;
// Start Writing Values
var writer = OpenXmlWriter.Create(wsp);
writer.WriteStartElement(new Worksheet());
WriteFreezeTopRow(writer);
writer.WriteStartElement(new SheetData());
WriteColumnsHeader(writer, columns);
var sheetRowCount = 1;
if (rowRead)
{
do
{
writer.WriteStartElement(new Row());
foreach (var col in columns)
{
var cell = new Cell()
{
DataType = col.CellValues
};
if (col.CellValues == CellValues.Date)
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 7; // Date (Stylesheet)
}
if (!reader.IsDBNull(col.Index))
{
if (col.CellValues == CellValues.Date)
{
cell.CellValue = new CellValue(reader.GetDateTime(col.Index).ToOADate().ToString(CultureInfo.InvariantCulture));
}
else if (col.CellValues == CellValues.Boolean)
{
cell.CellValue = new CellValue(reader.GetBoolean(col.Index) ? "1" : "0");
}
else
{
var str = reader[col.Index].ToString().Replace("\a", "");
cell.CellValue = new CellValue(str);
}
}
writer.WriteElement(cell);
}
writer.WriteEndElement(); // end of Row
sheetRowCount++;
if (sheetRowCount == 1048576) // MAX ROW
{
writer.WriteEndElement(); //end of SheetData
WriteAutoFilter(writer, columns.Count);
writer.WriteEndElement(); //end of Worksheet
writer.Close();
sheetCount++;
wsp = wbp.AddNewPart <WorksheetPart>();
sheet = new Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(wsp), SheetId = (uint)sheetCount, Name = sheetName + sheetCount.ToString()
};
sheets.Append(sheet);
writer = OpenXmlWriter.Create(wsp);
writer.WriteStartElement(new Worksheet());
WriteFreezeTopRow(writer);
writer.WriteStartElement(new SheetData());
WriteColumnsHeader(writer, columns);
sheetRowCount = 1;
}
} while (reader.Read());
}
writer.WriteEndElement(); //end of SheetData
if (sheetRowCount > 0)
{
WriteAutoFilter(writer, columns.Count);
}
writer.WriteEndElement(); //end of worksheet
writer.Close();
}
