public Cell(Worksheet sheet, string cellReference)
{
Sheet = sheet;
Xml = new Spreadsheet.Cell(
new Spreadsheet.Value { })
{
CellReference = new StringValue(cellReference)
};
}
private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateBoolean(int nColumnIndex, int nRowIndex, string sText)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString();
cell.DataType = CellValues.Boolean;
cell.CellValue = new CellValue(sText);
return cell;
}
// Add a cell with the specified address to a row.
private static DocumentFormat.OpenXml.Spreadsheet.Cell CreateCell(Row row, String address)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cellResult;
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
// Cells must be in sequential order according to CellReference.
// Determine where to insert the new cell.
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
// if (string.Compare(cell.CellReference.Value, address, true) > 0)
if (CompareCellName(cell.CellReference.Value, address) > 0)
{
refCell = cell;
break;
}
}
cellResult = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cellResult.CellReference = address;
row.InsertBefore(cellResult, refCell);
return cellResult;
}
private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateDate(int nColumnIndex, int nRowIndex, DateTime dtValue, UInt32Value styleId)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString();
cell.DataType = CellValues.Date;
cell.CellValue = new CellValue(dtValue.ToUniversalTime().ToString("s"));
cell.StyleIndex = styleId;
return cell;
}
private void ExportDatatableToExcel(DataTable table, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
uint sheetId = 1;
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
private static bool Render(TextElement element, ICollection <Style> styles, SheetData shData, WorkbookPart workbookpart)
{
uint styleId;
var row = shData.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Row>(new DocumentFormat.OpenXml.Spreadsheet.Row());
styleId = GetStyleId(element.Style, styles);
var value = element.Value;
if (element.Value == InternalContants.NewLine)
{
value = String.Empty;
}
if (element.Value == InternalContants.NewSection)
{
value = String.Empty;
}
var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value),
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
StyleIndex = UInt32Value.FromUInt32(styleId + 1)
};
row.AppendChild(cell);
workbookpart.Workbook.Save();
return(false);
}
/// <summary>
/// Gets cell values.
/// </summary>
/// <param name="cFormula">
/// Cell range formula (c:f).
/// <c:cat>
/// <c:strRef>
/// <c:f>
/// Sheet1!$A$2:$A$3
/// </c:f>
/// </c:strRef>
/// </c:cat>
/// </param>
/// <param name="slideChart"></param>
internal static List <X.Cell> GetXCellsByFormula(C.Formula cFormula, SlideChart slideChart)
{
// Get all <x:c> elements of formula sheet
string filteredFormula = GetFilteredFormula(cFormula);
string[] sheetNameAndCellsRange = filteredFormula.Split('!'); //eg: Sheet1!A2:A5 -> ['Sheet1', 'A2:A5']
WorkbookPart workbookPart = slideChart.ChartWorkbook.WorkbookPart;
string chartSheetName = sheetNameAndCellsRange[0];
string chartSheetId = workbookPart.Workbook.Sheets.Elements <X.Sheet>()
.First(xSheet => xSheet.Name == chartSheetName).Id;
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(chartSheetId);
IEnumerable <X.Cell> allXCells = worksheetPart.Worksheet.GetFirstChild <X.SheetData>().ChildElements
.SelectMany(e => e.Elements <X.Cell>()); //TODO: use HashSet
List <string> formulaCellAddressList = new CellFormulaParser(sheetNameAndCellsRange[1]).GetCellAddresses();
var xCells = new List <X.Cell>(formulaCellAddressList.Count);
foreach (string address in formulaCellAddressList)
{
X.Cell xCell = allXCells.First(xCell => xCell.CellReference == address);
xCells.Add(xCell);
}
return(xCells);
}
private DocumentFormat.OpenXml.Spreadsheet.Row MakeXLSXInfoRow(string title, string value, uint rowindex)
{
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowindex
};
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "A" + rowindex.ToString()
};
row.InsertBefore(newCell, refCell);
string cellValue = title + ": ";
if (!String.IsNullOrEmpty(value))
{
cellValue += value;
}
else
{
cellValue += "N/A";
}
newCell.CellValue = new CellValue(cellValue);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = 3;
return(row);
}
public static string ReadCell(Excel.Cell cell, StringKeyList buffer = null)
{
string value = cell.CellValue?.InnerText ?? string.Empty;
if (cell.DataType != null)
{
if (cell.DataType.Value == Excel.CellValues.SharedString)
{
// shared strings table.
if (int.TryParse(value, out int val))
{
//if (strings.SharedStringTable.ChildElements.Count > val)
if (buffer != null)
{
value = buffer[val]?.Key;
}
}
}
else if (cell.DataType.Value == Excel.CellValues.Boolean)
{
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
}
}
return(value);
}
/// <summary>
/// Sets the data type and value of the cell according to the type of the supplied value
/// </summary>
/// <param name="cell">The cell into which the value is to be written</param>
/// <param name="value">The value to be written into the cell</param>
public void SetCellValue(OpenXmlSpreadsheet.Cell cell, object value)
{
// Convert the supplied type to some underlying type that Excel can interpret.
value = ToUnderlyingTypeValue(value);
// Convert this underlying type to an Excel string
string excelString = ToExcelString(value);
// Set the DataType only if we are setting the value to a string.
if ((value is string) || (value is bool) || (value is bool?))
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue <OpenXmlSpreadsheet.CellValues>(OpenXmlSpreadsheet.CellValues.String);
}
else
{
cell.DataType = null;
}
cell.CellValue = new OpenXmlSpreadsheet.CellValue(excelString);
// Preserve white space in our cell values if it is a string.
if (value is string)
{
cell.CellValue.Space = DocumentFormat.OpenXml.SpaceProcessingModeValues.Preserve;
}
}
private static ColRow ReverseColumnRow(Cell cell)
{
int columnCode, rowCode;
ColumnRow(cell, out columnCode, out rowCode);
return(new ColRow(rowCode, columnCode));
}
private void Summarize(MSOpenXML.SheetData ws, int row, List <int[]> totais)
{
for (int i = 0; i < _columns.Count; i++)
{
string lCommand = "";
switch (_columns[i].SummaryOperation)
{
case ESummaryOperation.None: break;
case ESummaryOperation.Sum: lCommand = "SUBTOTAL(9,"; break;
case ESummaryOperation.Average: lCommand = "AVERAGE("; break;
case ESummaryOperation.Count: lCommand = ""; break;
case ESummaryOperation.Multiply: lCommand = "PRODUCT("; break;
case ESummaryOperation.Acumulate: throw new InvalidOperationException("Acumulate is not supported by MS Excel.");
case ESummaryOperation.Mode: lCommand = "MODE("; break;
default: break;
}
MSOpenXML.Cell c = CreateCellIfNotExist(ws.Parent as MSOpenXML.Worksheet, CellName(row, i + _groups.Count));
if (lCommand != "")
{
c.CellFormula = new MSOpenXML.CellFormula("{0}{1})".Formata(lCommand,
totais.Aggregate <int[], string>("", (r, n) => r = r + "," + GetColumnName(i + _groups.Count) + n[0] + ":" + GetColumnName(i + _groups.Count) + n[1]).Right(-1)));
}
if (_columns[i].SummaryOperation == ESummaryOperation.Count)
{
c.CellFormula = new MSOpenXML.CellFormula(totais.Aggregate <int[], string>("", (r, n) => r = r + "+ROWS(" + GetColumnName(i + _groups.Count) + n[0] + ":" + GetColumnName(i + _groups.Count) + n[1] + ")").Right(-1));
}
}
}
private static async Task ProcessRow(Row row, int Id)
{
using (HttpClient client = new HttpClient())
{
client.DefaultRequestHeaders.Accept.Add(
new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic",
Convert.ToBase64String(
System.Text.ASCIIEncoding.ASCII.GetBytes(
string.Format("{0}:{1}", "", ConfigurationManager.AppSettings["PersonalAccessToken"]))));
using (HttpResponseMessage response = await client.GetAsync(
String.Format("{0}/_apis/wit/workItems/{1}", ConfigurationManager.AppSettings["RestApiBaseUri"], Id)))
{
response.EnsureSuccessStatusCode();
string responseBody = await response.Content.ReadAsStringAsync();
dynamic data = JObject.Parse(responseBody);
DocumentFormat.OpenXml.Spreadsheet.Cell newcell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
newcell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
newcell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(
Regex.Replace((string)(data.fields["System.Description"].Value), "<.*?>", String.Empty).Replace(""", "\"").Replace(" ", ""));
row.Append(newcell);
}
}
}
private static void ColumnRow(Cell cell, out string column, out string row)
{
var m = Regex.Match(cell.CellReference.Value, @"^(?<Column>[A-Z]+)(?<Row>[0-9]+)$", RegexOptions.IgnoreCase);
column = m.Groups["Column"].Value;
row = m.Groups["Row"].Value;
}
public static void UpdateValue(
WorkbookPart wbPart,
Worksheet ws,
string addressName,
string value,
UInt32Value styleIndex,
bool isString)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = InsertCellInWorksheet(ws, addressName);
if (isString)
{
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = InsertSharedStringItem(wbPart, value);
cell.CellValue = new CellValue(stringIndex.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(value);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
if (styleIndex > 0)
{
cell.StyleIndex = styleIndex;
}
// Save the worksheet.
ws.Save();
}
//new
public void ExportDSToExcel(DataSet ds, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
uint sheetId = 1;
foreach (DataTable table in ds.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List<String> columns = new List<string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
}
private string GetSharedString(eCell cell, WorkbookPart wbPart)
{
var stringTable = wbPart.GetPartsOfType <SharedStringTablePart>()
.First();
var value = int.Parse(cell.InnerText);
return(stringTable.SharedStringTable.ElementAt(value).InnerText);
}
private static string GetCellValue(SharedStringTable sharedStrings, DocumentFormat.OpenXml.Spreadsheet.Cell cell)
{
return(cell.DataType != null &&
cell.DataType.HasValue &&
cell.DataType == CellValues.SharedString
? sharedStrings.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
}
// parameters:
// nRowIndex 行号。从 0 开始计算
public void WriteExcelLine(
int nRowIndex,
List <CellData> cells,
WriteExcelLineStyle style = WriteExcelLineStyle.AutoString)
{
// 创建一个 Row。不负责查重
// row 的 cell 创建完成后，记得 ws.Save()
Row row = ExcelUtil.CreateRow(
worksheetPart.Worksheet,
(UInt32)(nRowIndex + 1));
foreach (CellData data in cells)
{
string strCellName = GetColumnName(data.Col) + (nRowIndex + 1).ToString();
// 追加一个 Cell
DocumentFormat.OpenXml.Spreadsheet.Cell cell = ExcelUtil.AppendCell(
row,
strCellName);
bool isString = data.IsString;
if ((style & WriteExcelLineStyle.AutoString) == WriteExcelLineStyle.AutoString)
{
isString = !IsExcelNumber(data.Value);
}
if (isString)
{
if ((style & WriteExcelLineStyle.ShareString) == WriteExcelLineStyle.ShareString)
{
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = ExcelUtil.InsertSharedStringItem(this.workbookpart, data.Value);
cell.CellValue = new CellValue(stringIndex.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(data.Value);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
}
else
{
cell.CellValue = new CellValue(data.Value);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
if (data.StyleIndex > 0)
{
cell.StyleIndex = data.StyleIndex;
}
}
}
public Excel.Cell GetCell(object value, int c, int r, uint styleIndex, StringKeyList sharedStrings)
{
Excel.Cell cell = new Excel.Cell()
{
CellReference = Helper.IntToChar(c) + (r).ToString(),
StyleIndex = styleIndex,
};
WriteCell(cell, value, sharedStrings);
return(cell);
}
private static Cell GetCell(Row row, DocumentFormat.OpenXml.Spreadsheet.Cell p)
{
return(new Cell {
OXmlCell = p,
Row = row,
Column = row.Sheet.Columns().First(t
=> t.ColumnIndex == ColumnAddressToIndex(SplitAddress(p.CellReference)[0])),
Value = p.CellValue != null ? p.CellValue.Text : p.InnerText
});
}
private static DocumentFormat.OpenXml.Spreadsheet.Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
string cellReference = columnName + rowIndex;
DocumentFormat.OpenXml.Spreadsheet.Row lastRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().LastOrDefault();
// If the worksheet does not contain a row with the specified row index, insert one.
DocumentFormat.OpenXml.Spreadsheet.Row row;
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).First();
//set auto height -- don't know how this line is worked
sheetData.InsertAfter(new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = (lastRow.RowIndex + 1)
}, lastRow);
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return(row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == cellReference).First());
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellReference
};
row.InsertBefore(newCell, refCell);
worksheet.Save();
return(newCell);
}
}
public void OpenXmlSax()
{
using var xl = SpreadsheetDocument.Create(Stream, SpreadsheetDocumentType.Workbook);
xl.CompressionOption = CompressionOption.SuperFast;
var workbookPart = xl.AddWorkbookPart();
var wsp = workbookPart.AddNewPart<WorksheetPart>();
var oxw = OpenXmlWriter.Create(wsp);
oxw.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Worksheet());
oxw.WriteStartElement(new SheetData());
var rowObject = new Row();
var cellAttributes = new[] { new OpenXmlAttribute("t", "", "inlineStr") };
var cell = new OpenXmlCell();
var inlineString = new InlineString();
for (var row = 0; row < NumberOfRows; ++row)
{
var rowAttributes = new[] { new OpenXmlAttribute("r", "", (row + 1).ToString()) };
oxw.WriteStartElement(rowObject, rowAttributes);
var rowValues = Values[row];
for (var col = 0; col < rowValues.Count; ++col)
{
oxw.WriteStartElement(cell, cellAttributes);
oxw.WriteStartElement(inlineString);
oxw.WriteElement(new Text(rowValues[col]));
oxw.WriteEndElement();
oxw.WriteEndElement();
}
oxw.WriteEndElement();
}
oxw.WriteEndElement();
oxw.WriteEndElement();
oxw.Close();
oxw = OpenXmlWriter.Create(workbookPart);
oxw.WriteStartElement(new Workbook());
oxw.WriteStartElement(new Sheets());
oxw.WriteElement(new Sheet()
{
Name = "Sheet1",
SheetId = 1,
Id = workbookPart.GetIdOfPart(wsp)
});
oxw.WriteEndElement();
oxw.WriteEndElement();
oxw.Close();
xl.Close();
}
static void ReadCell(Row row, Excel.Cell importCell, Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats)
{
string cellIndex = importCell.CellReference;
string columnIndex = cellIndex.Substring(0, cellIndex.IndexOfAny("0123456789".ToCharArray())).ToUpper();
int index = 0;
while (columnIndex.Length > 1)
{
index += ((byte)columnIndex[0]) - 64;
index = index * 26;
columnIndex = columnIndex.Substring(1);
}
index += ((byte)columnIndex[0]) - 65;
Column column = row._rows._sheet.Column(index);
Cell cell = row.Cell(column);
if (importCell.CellValue != null)
{
if (importCell.DataType == null)
{
importCell.DataType = new DocumentFormat.OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.Number);
}
switch (importCell.DataType.Value)
{
case DocumentFormat.OpenXml.Spreadsheet.CellValues.String:
cell.StringValue = importCell.CellValue.Text;
break;
case DocumentFormat.OpenXml.Spreadsheet.CellValues.Number:
cell.NumberValue = Convert.ToDouble(importCell.CellValue.Text);
break;
case DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString:
cell.StringValue = sharedStringTable[importCell.CellValue.Text];
break;
case DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean:
cell.BooleanValue = importCell.CellValue.Text == "1" ? true : false;
break;
case DocumentFormat.OpenXml.Spreadsheet.CellValues.Error:
break;
default:
throw new Exception("Unsupported type");
}
if (importCell.StyleIndex != null && importCell.StyleIndex >= 0 && cellFormats.ContainsKey((int)(uint)importCell.StyleIndex))
{
cell.CellFormat = cellFormats[(int)(uint)importCell.StyleIndex];
}
}
}
private static bool Render(TableElement element, ICollection <Style> styles, SheetData shData, WorkbookPart workbookpart)
{
uint styleId;
if (element.Table.Rows.Count == 0)
{
//throw new Exception("The table is empty");
return(true);
}
if (element.Headers.Count != 0)
{
styleId = GetStyleId(element.HeaderStyle, styles);
var row = shData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
foreach (var head in element.Headers)
{
var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(head),
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
StyleIndex = UInt32Value.FromUInt32(styleId + 1)
};
row.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Cell>(cell);
}
workbookpart.Workbook.Save();
}
styleId = GetStyleId(element.TableStyle, styles);
for (int i = 0; i < element.Table.Rows.Count; i++)
{
var row = shData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
for (int j = 0; j < element.Table.Columns.Count; j++)
{
var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(element.Table.Rows[i][j].ToString()),
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
StyleIndex = UInt32Value.FromUInt32(styleId + 1)
};
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
return(false);
}
private int PrintGroupFooter(MSOpenXML.SheetData ws, int row, object[] lLastKey, List <int[]>[] lGroupIndexes, int i)
{
MergeRange(ws.Parent as MSOpenXML.Worksheet, CellName(row, i), CellName(row, _groups.Count - 1));
//ws.Range[ws.Cells[row, i + 1], ws.Cells[row, _columns.Count + _groups.Count]].Font.Bold = true;
//ws.Range[ws.Cells[row, i + 1], ws.Cells[row, _columns.Count + _groups.Count]].Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
MSOpenXML.Cell c = CreateCellIfNotExist(ws.Parent as MSOpenXML.Worksheet, CellName(row, i));
c.CellFormula = new MSOpenXML.CellFormula("CONCATENATE(\"Total \'" + lLastKey[i] + "\': \"," + lGroupIndexes[i].Aggregate <int[], string>("", (r, n) => r = r + "+ROWS(" + GetColumnName(_groups.Count) + n[0] + ":" + GetColumnName(_groups.Count + _columns.Count - 1) + n[1] + ")").Right(-1) + ")");
Summarize(ws, row, lGroupIndexes[i]);
(c.Parent as MSOpenXML.Row).Descendants <MSOpenXML.Cell>().ForEach(x => x.StyleIndex = 6);
lGroupIndexes[i].Clear();
return(++row);
}
public string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
{
CellValue cellValue = cell.CellValue;
string text = (cellValue == null) ? cell.InnerText : cellValue.Text;
string textEmail = (cellValue == null) ? cell.InnerText : cellValue.Text;
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
text = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Convert.ToInt32(cell.CellValue.Text)).InnerText;
}
return((text ?? string.Empty).Trim());
}
/// <summary>
/// Processes the paragraph part.
/// </summary>
/// <param name="wkb">The WKB.</param>
/// <param name="part">The part.</param>
/// <param name="placeholder">The placeholder.</param>
/// <param name="mapping">The mapping.</param>
/// <param name="worksheetPart">The worksheet part.</param>
/// <param name="reportWSPart">The report ws part.</param>
/// <returns></returns>
private static DrawingSpreadsheet.Shape ProcessParagraphPart(OpenXmlSpreadsheet.Workbook wkb, ExportPart part, MappingPlaceholder placeholder, ParagraphMapping mapping, WorksheetPart worksheetPart, WorksheetPart reportWSPart)
{
string SheetName = "";
uint RowStart = 0;
uint RowEnd = 0;
uint ColStart = 0;
uint ColEnd = 0;
// if no placeholder is specified then return now
if (placeholder == null)
{
return(null);
}
DrawingSpreadsheet.Shape matchShape = worksheetPart.GetShapeByName(placeholder.Id);
if (matchShape == null)
{
return(null);
}
// save the location of this shape,
// this information will be used to position the incoming chart
Extents extents = matchShape.ShapeProperties.Transform2D.Extents;
Offset offset = matchShape.ShapeProperties.Transform2D.Offset;
OpenXmlSpreadsheet.DefinedName rtfXmlDefinedName = wkb.GetDefinedNameByName(string.Format("{0}_{1}", part.DataSheetName, mapping.SourceFieldName));
wkb.BreakDownDefinedName(rtfXmlDefinedName, ref SheetName, ref RowStart, ref RowEnd, ref ColStart, ref ColEnd);
WorksheetPart wksp = wkb.GetWorksheetPartByName(SheetName);
OpenXmlSpreadsheet.SheetData sheetData = wksp.Worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>();
OpenXmlSpreadsheet.Cell rtfXmlCell = sheetData.GetCell(ColStart, RowStart + 1);
// Use the cell on the hidden data sheet as source for the XAML reader
Section RTFSection = XamlSectionDocumentReader(rtfXmlCell.CellValue.InnerText);
// The paragraph in the cell.inlinestring have a very different class structure to the paragraphs in the shape.textbody
// So, the paragraph will need to go through a converter to do this.
DrawingSpreadsheet.Shape targetShape = ConvertParagraph(worksheetPart, RTFSection, matchShape);
// positon the new graphic frame after the shape its going to replace
matchShape.Parent.InsertAfter <OpenXmlElement>(targetShape, matchShape);
matchShape.Remove();
return(targetShape);
}
private static void GenerateExcel(string excelFilePath, DataTable dataTable)
{
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(excelFilePath, true))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
Sheet sheet = sheets.SingleOrDefault(s => s.Name == "EC 2 SAP Analysis");
if (sheet == null)
{
return;
}
string relationshipId = sheet.Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
//DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in dataTable.Columns)
{
columns.Add(column.ColumnName);
//DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
//cell.DataType = CellValues.String;
//cell.CellValue = new CellValue(column.ColumnName);
//headerRow.AppendChild(cell);
}
//sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in dataTable.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookPart.Workbook.Save();
}
}
}//end method
public static MemoryStream ExportDataSet(DataSet ds) {
MemoryStream ms = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) {
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
foreach (System.Data.DataTable table in ds.Tables) {
var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 1) {
sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() {
Id = relationshipId,
SheetId = sheetId,
Name = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List<String> columns = new List<String>();
foreach (System.Data.DataColumn column in table.Columns) {
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}//end loop
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows) {
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns) {
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}//end loop
sheetData.AppendChild(newRow);
}//end loop
}//end loop
workbookPart.Workbook.Save();
workbook.Close();
}//end using
ms.Position = 0;
return ms;
}//end method
/// <summary>
/// Inserts the text.
/// </summary>
/// <param name="spreadSheetData">The spread sheet data.</param>
/// <param name="sharedStringPart">The share string part.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="column">The column.</param>
/// <param name="text">The text.</param>
/// <param name="cellValues">The cell values.</param>
protected static void InsertText(SpreadSheet.SheetData spreadSheetData, SharedStringTablePart sharedStringPart, string column, int rowIndex, string text, SpreadSheet.CellValues cellValues)
{
int sharedStringIndex = 0;
rowIndex += 1;
if (cellValues == SpreadSheet.CellValues.SharedString)
{
sharedStringIndex = InsertSharedStringItem(sharedStringPart, text);
}
SpreadSheet.Cell cell = InsertCellInWorksheet(spreadSheetData, column, (uint)rowIndex);
cell.CellValue = new SpreadSheet.CellValue(cellValues == SpreadSheet.CellValues.SharedString ? sharedStringIndex.ToString() : text);
cell.DataType = new EnumValue <SpreadSheet.CellValues>(cellValues);
}
private string GetValue(SpreadsheetDocument doc, Cell cell)
{
//if (cell.CellValue.InnerText == "")
//{
// string emptymsg = "Required";
// var text = doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
//}
string value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return(doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText);
}
return(value);
}
private string GetCellValue(WorkbookPart workbook, OpenXmlReader reader)
{
OpenXML.Cell c = (OpenXML.Cell)reader.LoadCurrentElement();
if (c.DataType != null && c.DataType == OpenXML.CellValues.SharedString)
{
OpenXML.SharedStringItem ssi = workbook.SharedStringTablePart.
SharedStringTable.Elements <OpenXML.SharedStringItem>().ElementAt
(int.Parse(c.CellValue.InnerText));
return(ssi.Text.Text);
}
else
{
return(c.CellValue.InnerText);
}
}
/* To create cell in Excel */
private void InsertCell(string value, CellValues dataType, int cell_num, Row row_index)
{
var t = row_index.RowIndex.ToString() + ":" + cell_num.ToString();
Cell refCell = null;
var newCell = new Cell()
{
CellValue = new CellValue(value),
DataType = new EnumValue <CellValues>(dataType),
CellReference = t
};
row_index.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue(value);
newCell.DataType = new EnumValue <CellValues>(dataType);
}
private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateNumber(int nColumnIndex, int nRowIndex, string sText, UInt32Value styleId)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString();
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(sText);
cell.StyleIndex = styleId;
return cell;
}
/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="valueType">Type of the value</param>
/// <param name="value">The actual value</param>
/// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
/// <param name="save">Save the worksheet?</param>
/// <returns>True if succesful</returns>
private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint? styleIndex, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
string cellAddress = Excel.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex };
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == Excel.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress };
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements<DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements<DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue<DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return true;
}
// 追加一个 Cell
public static DocumentFormat.OpenXml.Spreadsheet.Cell AppendCell(
// Worksheet ws,
Row row,
string addressName)
{
// SheetData sheetData = ws.GetFirstChild<SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Cell cell = null;
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.CellReference = addressName;
row.InsertBefore(cell, null);
return cell;
}
// 11/29/2013 Use dictionary to increase performance.
private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateText(int nColumnIndex, int nRowIndex, SharedStringTablePart shareStringPart, string sText, Dictionary<string, int> dictStringToInt)
{
//int nSharedIndex = OpenXML_InsertSharedStringItem(shareStringPart, sText, dictStringToInt);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString();
// 12/02/2013 SharedString got very slow after 2000 records. 7000 records was taking an hour to export.
// http://social.msdn.microsoft.com/Forums/office/en-US/0dcb58a0-5193-4ab7-b2c6-2742dd9e1be8/openxmlwriter-performance-issue-while-writing-large-excel-file-35000-rows-and-100-cells?forum=oxmlsdk
//cell.DataType = CellValues.SharedString;
//cell.CellValue = new CellValue(nSharedIndex.ToString());
cell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr"));
// 12/02/2013 Try and filter invalid characters.
if ( sText.Length > 0 )
{
char[] arr = sText.ToCharArray();
for ( int i = 0; i < arr.Length; i++ )
{
char ch = arr[i];
// http://social.technet.microsoft.com/Forums/en-US/4a51a8e8-7697-44a2-813f-d3704c8cfc02/hexadecimal-value-is-an-invalid-character-cant-generate-reports?forum=map
if ( ch == ControlChars.Cr || ch == ControlChars.Lf || ch == ControlChars.Tab )
{
continue;
}
else if ( ch < ' ' )
{
throw(new Exception("Invalid character 0x" + Convert.ToInt32(ch).ToString("x")));
}
}
}
cell.InlineString = new DocumentFormat.OpenXml.Spreadsheet.InlineString { Text = new Text { Text = sText } };
return cell;
}
private static void ColumnRow(Cell cell, out int column, out int row)
{
string columnCode, rowCode;
ColumnRow(cell, out columnCode, out rowCode);
column = columnCode.Aggregate(0, (sum, c) =>
sum * ('Z' - 'A' + 1) + (char.ToUpper(c) - 'A') + 1) - 1;
row = Convert.ToInt32(rowCode) - 1;
}