private static WorkbookInfo ParseWorkbook(string fileName)
{
var xmlParser = new XmlParser(fileName);
var worksheetInfos = new List <WorksheetInfo>();
var definedNames = new List <DefinedName>();
foreach (var sheetNode in xmlParser.GetNodes("sheet"))
{
var sheetNodeAttributes = xmlParser.GetAttributes(sheetNode);
var worksheetInfo = new WorksheetInfo(sheetNodeAttributes);
worksheetInfos.Add(worksheetInfo);
}
foreach (var definedNameNode in xmlParser.GetNodes("definedName"))
{
var definedNameNodeAttributes = xmlParser.GetAttributes(definedNameNode);
var name = definedNameNodeAttributes["name"];
var hidden = definedNameNodeAttributes.TryGetValue("hidden", out var hidden2) && hidden2 == "1";
var definedName = new DefinedName(name, definedNameNode.InnerText, hidden);
definedNames.Add(definedName);
}
return(new WorkbookInfo(worksheetInfos.ToArray(), definedNames.ToArray()));
}
/// <summary>
/// http://stackoverflow.com/questions/8405025/set-print-area-openxml-with-excel
/// https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname(v=office.14).aspx
/// </summary>
/// <param name="spreadsheetDoc"></param>
/// <param name="sheetName"></param>
/// <param name="columnStart">Ex: "$A"</param>
/// <param name="columnEnd">EX: "$B"</param>
/// null if just not set print areas column or row
public static void SetPrintArea(WorkbookPart workbookPart, string sheetName, string columnStart, string columnEnd, string rowStart, string rowEnd)
{
String definedName = sheetName;
var definedNames = workbookPart.Workbook.Descendants <DefinedNames>().FirstOrDefault();
DefinedName name = null;
UInt32Value locSheetId;
if (definedNames == null)
{
definedNames = new DefinedNames();
workbookPart.Workbook.Append(definedNames);
workbookPart.Workbook.Save();
locSheetId = UInt32Value.FromUInt32(0);
}
else
{
int defineNameCount = definedNames.Descendants <DefinedName>().Count();
locSheetId = UInt32Value.FromUInt32((UInt32)defineNameCount);
}
//_xlnm.Print_Area la tham so de set up cho khoang khong in
if (rowStart != null && rowEnd != null && columnEnd != null && columnStart != null)
{
name = new DefinedName()
{
Name = "_xlnm.Print_Area",
LocalSheetId = locSheetId
,
Text = String.Format("{0}!${1}${2}:${3}${4}", sheetName, columnStart.Replace("$", ""), rowStart.Replace("$", ""), columnEnd.Replace("$", ""), rowEnd.Replace("$", ""))
};
}
definedNames.Append(name);
workbookPart.Workbook.Save();
}
/// <summary>
/// The defined name range comprises sheet and excel reference. This method decomposes this into contituents relevent for rows.
/// Maybe requires extension to columns in the future.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="dn">The dn.</param>
/// <param name="SheetName">Name of the sheet.</param>
/// <param name="RowStart">The row start.</param>
/// <param name="RowEnd">The row end.</param>
/// <returns></returns>
public static bool BreakDownDefinedName(this Workbook workbook, DefinedName dn, ref string SheetName, ref uint RowStart, ref uint RowEnd)
{
string[] DefinedNameRefElements = dn.Text.Split('!');
SheetName = DefinedNameRefElements[0];
if (SheetName.StartsWith("'") & SheetName.EndsWith("'"))
{
SheetName = SheetName.Substring(1, SheetName.Length - 2);
}
if (DefinedNameRefElements.Length > 0)
{
string[] DefinedNameCellElements = DefinedNameRefElements[1].Split(':');
bool ok = true;
if (DefinedNameCellElements.Length > 0)
{
ok = TryGetRowIndex(DefinedNameCellElements[0], out RowStart);
if (ok && DefinedNameCellElements.Length > 1)
{
ok = TryGetRowIndex(DefinedNameCellElements[1], out RowEnd);
return(ok);
}
}
}
return(false);
}
private static object GetDataValueByBookmark(DefinedName bookmark, SOARolePropertyRow row)
{
object result = null;
SOARolePropertyValue propertyValue = row.Values.FindByColumnName(bookmark.Name);
if (propertyValue != null)
{
if (propertyValue.Column.DataType != ColumnDataType.String)
{
result = DataConverter.ChangeType(typeof(string), propertyValue.Value, propertyValue.Column.RealDataType);
}
else
{
result = propertyValue.Value;
}
}
else
{
switch (bookmark.Name.ToLower())
{
case "operatortype":
result = row.OperatorType.ToString();
break;
case "operator":
result = row.Operator;
break;
}
}
return(result);
}
public void SetDefinedNameRange(string name, CellRangeRef value)
{
DefinedName definedName = (
from item in document.WorkbookPart.Workbook.DefinedNames.Elements <DefinedName>()
where item.Name == name
select item).Single();
definedName.Text = value.ToString();
}
protected override Boolean LocateRange(IWorkbook book)
{
if (book.DefinedNames.Contains(this.Name))
{
this.dname = book.DefinedNames.GetDefinedName(Name);
return(true);
}
return(false);
}
public CellRangePosition GetDefinedName(string name)
{
DefinedName definedName = (
from item in document.WorkbookPart.Workbook.DefinedNames.Elements <DefinedName>()
//from item in documentTemplate.WorkbookPart.Workbook.DefinedNames.Elements<DefinedName>()
where item.Name == name
select item).Single();
return(new CellRangePosition(definedName.Text));
}
/// <summary>
/// Get real reference of defined name
/// </summary>
/// <param name="doc">Spread sheet document</param>
/// <param name="name">Defined name</param>
/// <returns>Real reference</returns>
public static String GetDefinedName(this SpreadsheetDocument doc, String name)
{
DefinedName dn = doc.WorkbookPart.Workbook.Descendants <DefinedName>().FirstOrDefault(a => a.Name == name);
if (dn == null)
{
return(null);
}
return(dn.InnerText);
}
public void ReadContentFromBookmark()
{
WorkSheet sheet = GetFirstBooksSheet();
Assert.IsNotNull(sheet.Names["Content"]);
DefinedName bookmark = sheet.Names["Content"];
sheet.Output(bookmark.Address.StartRow, bookmark.Address.StartColumn);
}
public CellRangeRef FindDefinedNameRange(string name)
{
DefinedName definedName = (
from item in document.WorkbookPart.Workbook.DefinedNames.Elements <DefinedName>()
where item.Name == name
select item).Single();
CellRangeRef range = new CellRangeRef(definedName.Text);
return(range);
}
protected override Boolean LocateRange(IWorkbook book)
{
if (book.DefinedNames.Contains(this.Name))
{
this.dname = book.DefinedNames.GetDefinedName(Name);
return(true);
}
else
{
System.Windows.Forms.MessageBox.Show("无法找到" + this.Name + "所对应的区域");
}
return(false);
}
void EnableControls()
{
Worksheet sheet = spreadsheetControl1.ActiveWorksheet;
if (sheet.Name == "Invoice")
{
DefinedName invoiceItems = sheet.DefinedNames.GetDefinedName("InvoiceItems");
btnRemoveRecord.Enabled = invoiceItems != null && invoiceItems.Range.RowCount > 1 && invoiceItems.Range.IsIntersecting(sheet.SelectedCell);
}
else
{
btnRemoveRecord.Enabled = false;
}
}
/// <summary>
/// used the defined name within the workbook to show rows of the DefinedName (Range of cells)
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="dn">The dn.</param>
public static void ShowRowsOfDefinedName(this Workbook workbook, DefinedName dn)
{
string SheetName = "";
uint RowStart = 0;
uint RowEnd = 0;
if (BreakDownDefinedName(workbook, dn, ref SheetName, ref RowStart, ref RowEnd))
{
WorksheetPart ws = workbook.GetWorksheetPartByName(SheetName);
if (ws != null)
{
ws.Worksheet.ShowRows(RowStart, RowEnd);
}
}
}
static void CreateNamedRange(Workbook workbook)
{
#region #NamedRange
Worksheet worksheet = workbook.Worksheets[0];
// Create a range.
CellRange rangeB3D6 = worksheet.Range["B3:D6"];
// Specify the name for the created range.
rangeB3D6.Name = "rangeB3D6";
// Create a new defined name with the specifed range name and absolute reference.
DefinedName definedName = worksheet.DefinedNames.Add("rangeB17D20", "Sheet1!$B$17:$D$20");
// Use the specified defined name to obtain the cell range.
CellRange B17D20 = worksheet.Range[definedName.Name];
#endregion #NamedRange
}
public DefinedNameVal(DefinedName defName)
{
this.Key = defName.Name;
this.Reference = defName.InnerText;
this.SheetName = this.Reference.Split('!')[0].Trim('\'');
//Assumption: None of the defined names are relative defined names (i.e. A1).
string range = this.Reference.Split('!')[1];
string[] rangeArray = range.Split('$');
this.StartColumn = rangeArray[1];
this.StartRow = rangeArray[2].TrimEnd(':');
if (rangeArray.Length > 3)
{
this.EndColumn = rangeArray[3];
this.EndRow = rangeArray[4];
}
}
public void WorksheetPrintAreaTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteText("A1", "Set print area to A1:B9");
//Test setting the print area
DefinedName area = writer.SetPrintArea("Sheet1", "A1", "B9");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\printarea.xlsx", GetOutputFolder()), stream);
//Assert.IsTrue(area != null, "Print area reference not returned.");
}
// Add a new record to the invoice.
void AddRecord(Worksheet sheet)
{
spreadsheetControl1.BeginUpdate();
try {
DefinedName invoiceItems = sheet.DefinedNames.GetDefinedName("InvoiceItems");
int rowIndex = invoiceItems.Range.BottomRowIndex;
sheet.Rows.Insert(rowIndex);
sheet.Rows[rowIndex].Height = sheet.Rows[rowIndex + 1].Height;
CellRange range = invoiceItems.Range;
CellRange itemRange = sheet.Range.FromLTRB(range.LeftColumnIndex, range.BottomRowIndex, range.RightColumnIndex, range.BottomRowIndex);
MoveUpLastRecord(itemRange);
InitializeRecord(itemRange);
spreadsheetControl1.SelectedCell = itemRange[1];
}
finally {
spreadsheetControl1.EndUpdate();
}
}
/// <summary>
/// Private ctor. Prevents public construction.<br/>
/// Loads the model from the supplied <see cref="DefinedName"/>
/// </summary>
/// <param name="wb">The workbool</param>
/// <param name="definedName">The named range</param>
private DefinedNameModel(Workbook wb, DefinedName definedName)
{
this.IsDefined = wb.BreakDownDefinedName(definedName, ref worksheetName, ref rowStart, ref rowEnd, ref colStart, ref colEnd);
if (this.IsDefined)
{
var wsPart = wb.GetWorksheetPartByName(worksheetName);
this.Worksheet = wsPart.Worksheet;
this.SheetData = this.Worksheet.GetFirstChild <SheetData>();
//get cells to be cloned according to the specified rows and columns
this.Cells = this.SheetData.Descendants <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c =>
CellExtensions.GetRowIndex(c.CellReference) >= rowStart &&
CellExtensions.GetRowIndex(c.CellReference) <= rowEnd &&
CellExtensions.GetColumnIndex(c.CellReference) >= colStart &&
CellExtensions.GetColumnIndex(c.CellReference) <= colEnd)
.ToList <DocumentFormat.OpenXml.Spreadsheet.Cell>();
}
}
private void WriteDefinedName(WorkbookPart workbookPart, WorksheetPart wssheatpart)
{
try
{
int rows = wssheatpart.Worksheet.GetFirstChild <SheetData>().Elements <Row>().Count();
int cols = wssheatpart.Worksheet.GetFirstChild <SheetData>().Elements <Row>().First().Elements <Cell>().Count();
var dfname = new DefinedName()
{
Name = "mydata"
};
string colname = ColumnIndexToColumnLetter(cols);
dfname.Text = $"rawdata!$A$1:${colname}${rows}";
workbookPart.Workbook.DefinedNames.Append(dfname);
}
catch (Exception ex)
{
}
}
internal void FromDefinedName(DefinedName dn)
{
this.SetAllNull();
this.Text = dn.Text ?? string.Empty;
this.Name = dn.Name.Value;
if (dn.Comment != null) this.Comment = dn.Comment.Value;
if (dn.CustomMenu != null) this.CustomMenu = dn.CustomMenu.Value;
if (dn.Description != null) this.Description = dn.Description.Value;
if (dn.Help != null) this.Help = dn.Help.Value;
if (dn.StatusBar != null) this.StatusBar = dn.StatusBar.Value;
if (dn.LocalSheetId != null) this.LocalSheetId = dn.LocalSheetId.Value;
if (dn.Hidden != null) this.Hidden = dn.Hidden.Value;
if (dn.Function != null) this.Function = dn.Function.Value;
if (dn.VbProcedure != null) this.VbProcedure = dn.VbProcedure.Value;
if (dn.Xlm != null) this.Xlm = dn.Xlm.Value;
if (dn.FunctionGroupId != null) this.FunctionGroupId = dn.FunctionGroupId.Value;
if (dn.ShortcutKey != null) this.ShortcutKey = dn.ShortcutKey.Value;
if (dn.PublishToServer != null) this.PublishToServer = dn.PublishToServer.Value;
if (dn.WorkbookParameter != null) this.WorkbookParameter = dn.WorkbookParameter.Value;
}
/// <summary>
/// Returns an instance of a <see cref="DefinedNameModel"/> for a defined name (named range) with in a specified workbook.<br/>
/// TODO: Does not take defined name scope into consideration yet. Investigate Worksheet scope defined names.
/// </summary>
/// <param name="wb"></param>
/// <param name="definedName"></param>
/// <returns></returns>
public static DefinedNameModel GetDefinedNameModel(Workbook wb, string definedName)
{
if (wb == null)
{
throw new ArgumentNullException("wb");
}
if (string.IsNullOrEmpty(definedName))
{
throw new ArgumentNullException("definedName");
}
DefinedNameModel definedNameModel = null;
DefinedName dn = wb.GetDefinedNameByName(definedName);
if (dn != null)
{
definedNameModel = new DefinedNameModel(wb, dn);
definedNameModel.Name = definedName;
}
return(definedNameModel);
}
/// <summary>
/// Breaks the name of down defined.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="dn">The dn.</param>
/// <param name="SheetName">Name of the sheet.</param>
/// <param name="RowStart">The row start.</param>
/// <param name="RowEnd">The row end.</param>
/// <param name="ColStart">The col start.</param>
/// <param name="ColEnd">The col end.</param>
/// <returns></returns>
public static bool BreakDownDefinedName(this Workbook workbook, DefinedName dn, ref string SheetName, ref uint RowStart, ref uint RowEnd, ref uint ColStart, ref uint ColEnd)
{
bool val = BreakDownDefinedName(workbook, dn, ref SheetName, ref RowStart, ref RowEnd);
// Example "Disclaimer!$A$3:$AW$35"
string RangePart = dn.Text.Split('!')[1]; // Table the string to right of the !
string StartPart = RangePart.Split(':')[0]; // the LHS of the :
string EndPart = RangePart.Split(':')[1]; // the RHS of the :
string StartColLetter = StartPart.Split('$')[1].ToString();
string EndColLetter = EndPart.Split('$')[1].ToString();
char[] StartColChars = StartColLetter.ToCharArray();
char[] EndColChars = EndColLetter.ToCharArray();
ColStart = Helpers.ColLetterNumber(StartColChars);
ColEnd = Helpers.ColLetterNumber(EndColChars);
return(val);
}
internal DefinedName ToDefinedName()
{
DefinedName dn = new DefinedName();
dn.Text = this.Text;
dn.Name = this.Name;
if (this.Comment != null) dn.Comment = this.Comment;
if (this.CustomMenu != null) dn.CustomMenu = this.CustomMenu;
if (this.Description != null) dn.Description = this.Description;
if (this.Help != null) dn.Help = this.Help;
if (this.StatusBar != null) dn.StatusBar = this.StatusBar;
if (this.LocalSheetId != null) dn.LocalSheetId = this.LocalSheetId.Value;
if (this.Hidden != null && this.Hidden != false) dn.Hidden = this.Hidden.Value;
if (this.Function != null && this.Function != false) dn.Function = this.Function.Value;
if (this.VbProcedure != null && this.VbProcedure != false) dn.VbProcedure = this.VbProcedure.Value;
if (this.Xlm != null && this.Xlm != false) dn.Xlm = this.Xlm.Value;
if (this.FunctionGroupId != null) dn.FunctionGroupId = this.FunctionGroupId.Value;
if (this.ShortcutKey != null) dn.ShortcutKey = this.ShortcutKey;
if (this.PublishToServer != null && this.PublishToServer != false) dn.PublishToServer = this.PublishToServer.Value;
if (this.WorkbookParameter != null && this.WorkbookParameter != false) dn.WorkbookParameter = this.WorkbookParameter.Value;
return dn;
}
private void CreateAutoFilterDefinedName()
{
var workbook = document.WorkbookPart.Workbook;
if (workbook.DefinedNames == null)
{
workbook.DefinedNames = new DefinedNames();
}
var lastColumnIndex = rowsWriter.ColumnCount - 1;
var lastColumnLetter = OXLHelper.GetColumnLetter(lastColumnIndex);
var rowCount = rowsWriter.RowCount;
var definedName = new DefinedName($"'{sheetName}'!$A$1:${lastColumnLetter}${rowCount}")
{
Name = "_xlnm._FilterDatabase",
LocalSheetId = 0,
Hidden = true
};
workbook.DefinedNames.AppendChild(definedName);
}
void SpreadsheetControl_CustomDrawCell(object sender, XtraSpreadsheet.CustomDrawCellEventArgs e)
{
Worksheet sheet = e.Cell.Worksheet;
if (sheet.Name != "Invoice")
{
return;
}
DefinedName invoiceItems = sheet.DefinedNames.GetDefinedName("InvoiceItems");
// Add Order Item
if (e.Cell.ColumnIndex == 2 &&
e.Cell.RowIndex == (invoiceItems == null ? 21 : invoiceItems.Range.BottomRowIndex + 1))
{
DrawLink(e, e.Cache, "Add Order Item");
}
// Delete Order Item
if (invoiceItems != null && e.Cell.ColumnIndex == 13 && invoiceItems.Range.RowCount > 1 &&
e.Cell.RowIndex >= invoiceItems.Range.TopRowIndex && e.Cell.RowIndex <= invoiceItems.Range.BottomRowIndex)
{
DrawLink(e, e.Cache, "Delete Order Item");
}
}
private void LoadWorkbook(String path, String relType)
{
//Process workbook relationships
Dictionary <String, CT_Relationship> sheetRels = new Dictionary <string, CT_Relationship>();
String filePath;
String fileName;
PathUtil.SplitFilePath(path, out filePath, out fileName);
var relsPath = PathUtil.CombinePaths(filePath, String.Format("/_rels/{0}.rels", fileName));
if (FileExists(relsPath))
{
//Debug.WriteLine("");
//Debug.WriteLine("Workbook relationships:");
var workbookRels = ReadFile <CT_Relationships>(relsPath);
sharedStrings = new SharedStrings();
foreach (var rel in workbookRels.Relationship)
{
//Debug.WriteLine(String.Format("{0} {1} {2}", rel.Id, rel.Target, rel.Type));
var partPath = PathUtil.CombinePaths(filePath, rel.Target);
var contentType = contentTypes.ResolvePartType(partPath);
switch (contentType)
{
case ContentTypes.SharedStrings:
ReadSharedStrings(partPath);
break;
case ContentTypes.Sheet:
sheetRels.Add(rel.Id, rel);
break;
case ContentTypes.Styles:
LoadStyles(partPath);
break;
}
}
}
//Process workbook data
var wb = ReadFile <CT_Workbook>(path);
//Debug.WriteLine("");
//Debug.WriteLine("Sheets: ");
foreach (var sheetInfo in wb.sheets)
{
//Debug.WriteLine(String.Format("{2} {0}: {1}", sheetInfo.name, sheetInfo.sheetId, sheetInfo.id));
var sheet = new Sheet(sheetInfo.name);
CT_Relationship rel;
if (sheetRels.TryGetValue(sheetInfo.id, out rel))
{
var partPath = PathUtil.CombinePaths(filePath, rel.Target);
ReadSheet(partPath, sheet);
}
workbook.Sheets.AddSheet(sheet);
}
if (wb.definedNames != null)
{
foreach (var dn in wb.definedNames)
{
DefinedName definedName = new DefinedName
{
Name = dn.name,
Value = dn.Value
};
if (dn.localSheetIdSpecified)
{
workbook.Sheets[(int)dn.localSheetId].DefinedNames.AddDefinedName(definedName);
}
else
{
workbook.DefinedNames.AddDefinedName(definedName);
}
}
}
}
internal void FromDefinedName(DefinedName dn)
{
this.SetAllNull();
this.Text = dn.Text ?? string.Empty;
this.Name = dn.Name.Value;
if (dn.Comment != null)
{
this.Comment = dn.Comment.Value;
}
if (dn.CustomMenu != null)
{
this.CustomMenu = dn.CustomMenu.Value;
}
if (dn.Description != null)
{
this.Description = dn.Description.Value;
}
if (dn.Help != null)
{
this.Help = dn.Help.Value;
}
if (dn.StatusBar != null)
{
this.StatusBar = dn.StatusBar.Value;
}
if (dn.LocalSheetId != null)
{
this.LocalSheetId = dn.LocalSheetId.Value;
}
if (dn.Hidden != null)
{
this.Hidden = dn.Hidden.Value;
}
if (dn.Function != null)
{
this.Function = dn.Function.Value;
}
if (dn.VbProcedure != null)
{
this.VbProcedure = dn.VbProcedure.Value;
}
if (dn.Xlm != null)
{
this.Xlm = dn.Xlm.Value;
}
if (dn.FunctionGroupId != null)
{
this.FunctionGroupId = dn.FunctionGroupId.Value;
}
if (dn.ShortcutKey != null)
{
this.ShortcutKey = dn.ShortcutKey.Value;
}
if (dn.PublishToServer != null)
{
this.PublishToServer = dn.PublishToServer.Value;
}
if (dn.WorkbookParameter != null)
{
this.WorkbookParameter = dn.WorkbookParameter.Value;
}
}
internal DefinedName ToDefinedName()
{
DefinedName dn = new DefinedName();
dn.Text = this.Text;
dn.Name = this.Name;
if (this.Comment != null)
{
dn.Comment = this.Comment;
}
if (this.CustomMenu != null)
{
dn.CustomMenu = this.CustomMenu;
}
if (this.Description != null)
{
dn.Description = this.Description;
}
if (this.Help != null)
{
dn.Help = this.Help;
}
if (this.StatusBar != null)
{
dn.StatusBar = this.StatusBar;
}
if (this.LocalSheetId != null)
{
dn.LocalSheetId = this.LocalSheetId.Value;
}
if (this.Hidden != null && this.Hidden != false)
{
dn.Hidden = this.Hidden.Value;
}
if (this.Function != null && this.Function != false)
{
dn.Function = this.Function.Value;
}
if (this.VbProcedure != null && this.VbProcedure != false)
{
dn.VbProcedure = this.VbProcedure.Value;
}
if (this.Xlm != null && this.Xlm != false)
{
dn.Xlm = this.Xlm.Value;
}
if (this.FunctionGroupId != null)
{
dn.FunctionGroupId = this.FunctionGroupId.Value;
}
if (this.ShortcutKey != null)
{
dn.ShortcutKey = this.ShortcutKey;
}
if (this.PublishToServer != null && this.PublishToServer != false)
{
dn.PublishToServer = this.PublishToServer.Value;
}
if (this.WorkbookParameter != null && this.WorkbookParameter != false)
{
dn.WorkbookParameter = this.WorkbookParameter.Value;
}
return(dn);
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
// To demonstrate how to set column-widths in Excel, here's how to set the width of all columns to our default of "25":
UInt32 inx = 1;
writer.WriteStartElement(new Columns());
foreach (DataColumn dc in dt.Columns)
{
writer.WriteElement(new Column {
Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH
});
inx++;
}
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
// We'll set the row-height to 20px, and (using the "AppendHeaderTextCell" function) apply some formatting to the cells.
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex, Height = 20, CustomHeight = true
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
// Uncomment the following lines, for an example of how to create some Named Ranges in your Excel file
#if FALSE
// For each column of data in this worksheet, let's create a Named Range, showing where there are values in this column
// eg "NamedRange_UserID" = "Drivers!$A2:$A6"
// "NamedRange_Surname" = "Drivers!$B2:$B6"
string columnHeader = col.ColumnName.Replace(" ", "_");
string NamedRange = string.Format("{0}!${1}2:${2}{3}", worksheetName, excelColumnNames[colInx], excelColumnNames[colInx], dt.Rows.Count + 1);
DefinedName definedName = new DefinedName()
{
Name = "NamedRange_" + columnHeader,
Text = NamedRange
};
definedNamesCol.Append(definedName);
#endif
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellFloatValue = 0;
CultureInfo ci = new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
// Create cell with data
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
// For numeric cells without any decimal places.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellFloatValue = 0;
bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer);
}
}
else if (IsDateColumn[colInx])
{
// For date values, we save the value to Excel as a number, but need to set the cell's style to format
// it as either a date or a date-time.
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
AppendDateCell(cellReference, dateValue, writer);
}
else
{
// This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
AppendTextCell(cellReference, cellValue, writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(cellReference, cellValue, writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
bool IsRangeForAddCommand(Cell cell, DefinedName invoiceItems)
{
return(cell.ColumnIndex == 6 && cell.RowIndex == (invoiceItems == null || invoiceItems.Range == null ? 21 : invoiceItems.Range.BottomRowIndex + 1));
}
bool IsRangeForDeleteCommand(Cell cell, DefinedName invoiceItems)
{
return(invoiceItems != null && invoiceItems.Range != null && cell.ColumnIndex == 10 && invoiceItems.Range.RowCount > 1 &&
cell.RowIndex >= invoiceItems.Range.TopRowIndex && cell.RowIndex <= invoiceItems.Range.BottomRowIndex);
}
private void GenerateWorkbookPartContent(WorkbookPart workbookPart, SaveContext context)
{
if (workbookPart.Workbook == null)
workbookPart.Workbook = new Workbook();
var workbook = workbookPart.Workbook;
if (
!workbook.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
{
workbook.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
}
#region WorkbookProperties
if (workbook.WorkbookProperties == null)
workbook.WorkbookProperties = new WorkbookProperties();
if (workbook.WorkbookProperties.CodeName == null)
workbook.WorkbookProperties.CodeName = "ThisWorkbook";
if (Use1904DateSystem)
workbook.WorkbookProperties.Date1904 = true;
#endregion
if (workbook.BookViews == null)
workbook.BookViews = new BookViews();
if (workbook.Sheets == null)
workbook.Sheets = new Sheets();
var worksheets = WorksheetsInternal;
workbook.Sheets.Elements<Sheet>().Where(s => worksheets.Deleted.Contains(s.Id)).ToList().ForEach(
s => s.Remove());
foreach (var sheet in workbook.Sheets.Elements<Sheet>())
{
var sheetId = (Int32)sheet.SheetId.Value;
if (WorksheetsInternal.All<XLWorksheet>(w => w.SheetId != sheetId)) continue;
var wks = WorksheetsInternal.Single<XLWorksheet>(w => w.SheetId == sheetId);
wks.RelId = sheet.Id;
sheet.Name = wks.Name;
}
foreach (var xlSheet in
WorksheetsInternal.Cast<XLWorksheet>().Where(s => s.SheetId == 0).OrderBy(w => w.Position))
{
var rId = context.RelIdGenerator.GetNext(RelType.Workbook);
while (WorksheetsInternal.Cast<XLWorksheet>().Any(w => w.SheetId == Int32.Parse(rId.Substring(3))))
rId = context.RelIdGenerator.GetNext(RelType.Workbook);
xlSheet.SheetId = Int32.Parse(rId.Substring(3));
xlSheet.RelId = rId;
var newSheet = new Sheet
{
Name = xlSheet.Name,
Id = rId,
SheetId = (UInt32)xlSheet.SheetId
};
workbook.Sheets.AppendChild(newSheet);
}
var sheetElements = from sheet in workbook.Sheets.Elements<Sheet>()
join worksheet in ((IEnumerable<XLWorksheet>)WorksheetsInternal) on sheet.Id.Value
equals worksheet.RelId
orderby worksheet.Position
select sheet;
UInt32 firstSheetVisible = 0;
var activeTab =
(from us in UnsupportedSheets where us.IsActive select (UInt32)us.Position - 1).FirstOrDefault();
var foundVisible = false;
var totalSheets = sheetElements.Count() + UnsupportedSheets.Count;
for (var p = 1; p <= totalSheets; p++)
{
if (UnsupportedSheets.All(us => us.Position != p))
{
var sheet = sheetElements.ElementAt(p - UnsupportedSheets.Count(us => us.Position <= p) - 1);
workbook.Sheets.RemoveChild(sheet);
workbook.Sheets.AppendChild(sheet);
var xlSheet = Worksheet(sheet.Name);
if (xlSheet.Visibility != XLWorksheetVisibility.Visible)
sheet.State = xlSheet.Visibility.ToOpenXml();
if (foundVisible) continue;
if (sheet.State == null || sheet.State == SheetStateValues.Visible)
foundVisible = true;
else
firstSheetVisible++;
}
else
{
var sheetId = UnsupportedSheets.First(us => us.Position == p).SheetId;
var sheet = workbook.Sheets.Elements<Sheet>().First(s => s.SheetId == sheetId);
workbook.Sheets.RemoveChild(sheet);
workbook.Sheets.AppendChild(sheet);
}
}
var workbookView = workbook.BookViews.Elements<WorkbookView>().FirstOrDefault();
if (activeTab == 0)
{
activeTab = firstSheetVisible;
foreach (var ws in worksheets)
{
if (!ws.TabActive) continue;
activeTab = (UInt32)(ws.Position - 1);
break;
}
}
if (workbookView == null)
{
workbookView = new WorkbookView {ActiveTab = activeTab, FirstSheet = firstSheetVisible};
workbook.BookViews.AppendChild(workbookView);
}
else
{
workbookView.ActiveTab = activeTab;
workbookView.FirstSheet = firstSheetVisible;
}
var definedNames = new DefinedNames();
foreach (var worksheet in WorksheetsInternal)
{
var wsSheetId = (UInt32)worksheet.SheetId;
UInt32 sheetId = 0;
foreach (var s in workbook.Sheets.Elements<Sheet>().TakeWhile(s => s.SheetId != wsSheetId))
{
sheetId++;
}
if (worksheet.PageSetup.PrintAreas.Any())
{
var definedName = new DefinedName {Name = "_xlnm.Print_Area", LocalSheetId = sheetId};
var worksheetName = worksheet.Name;
var definedNameText = worksheet.PageSetup.PrintAreas.Aggregate(String.Empty,
(current, printArea) =>
current +
("'" + worksheetName + "'!" +
printArea.RangeAddress.
FirstAddress.ToStringFixed(
XLReferenceStyle.A1) +
":" +
printArea.RangeAddress.
LastAddress.ToStringFixed(
XLReferenceStyle.A1) +
","));
definedName.Text = definedNameText.Substring(0, definedNameText.Length - 1);
definedNames.AppendChild(definedName);
}
if (worksheet.AutoFilter.Enabled)
{
var definedName = new DefinedName
{
Name = "_xlnm._FilterDatabase",
LocalSheetId = sheetId,
Text = "'" + worksheet.Name + "'!" +
worksheet.AutoFilter.Range.RangeAddress.FirstAddress.ToStringFixed(
XLReferenceStyle.A1) +
":" +
worksheet.AutoFilter.Range.RangeAddress.LastAddress.ToStringFixed(
XLReferenceStyle.A1),
Hidden = BooleanValue.FromBoolean(true)
};
definedNames.AppendChild(definedName);
}
foreach (var nr in worksheet.NamedRanges.Where(n => n.Name != "_xlnm._FilterDatabase"))
{
var definedName = new DefinedName
{
Name = nr.Name,
LocalSheetId = sheetId,
Text = nr.ToString()
};
if (!XLHelper.IsNullOrWhiteSpace(nr.Comment))
definedName.Comment = nr.Comment;
definedNames.AppendChild(definedName);
}
var definedNameTextRow = String.Empty;
var definedNameTextColumn = String.Empty;
if (worksheet.PageSetup.FirstRowToRepeatAtTop > 0)
{
definedNameTextRow = "'" + worksheet.Name + "'!" + worksheet.PageSetup.FirstRowToRepeatAtTop
+ ":" + worksheet.PageSetup.LastRowToRepeatAtTop;
}
if (worksheet.PageSetup.FirstColumnToRepeatAtLeft > 0)
{
var minColumn = worksheet.PageSetup.FirstColumnToRepeatAtLeft;
var maxColumn = worksheet.PageSetup.LastColumnToRepeatAtLeft;
definedNameTextColumn = "'" + worksheet.Name + "'!" +
XLHelper.GetColumnLetterFromNumber(minColumn)
+ ":" + XLHelper.GetColumnLetterFromNumber(maxColumn);
}
string titles;
if (definedNameTextColumn.Length > 0)
{
titles = definedNameTextColumn;
if (definedNameTextRow.Length > 0)
titles += "," + definedNameTextRow;
}
else
titles = definedNameTextRow;
if (titles.Length <= 0) continue;
var definedName2 = new DefinedName
{
Name = "_xlnm.Print_Titles",
LocalSheetId = sheetId,
Text = titles
};
definedNames.AppendChild(definedName2);
}
foreach (var nr in NamedRanges)
{
var definedName = new DefinedName
{
Name = nr.Name,
Text = nr.ToString()
};
if (!XLHelper.IsNullOrWhiteSpace(nr.Comment))
definedName.Comment = nr.Comment;
definedNames.AppendChild(definedName);
}
workbook.DefinedNames = definedNames;
if (workbook.CalculationProperties == null)
workbook.CalculationProperties = new CalculationProperties {CalculationId = 125725U};
if (CalculateMode == XLCalculateMode.Default)
workbook.CalculationProperties.CalculationMode = null;
else
workbook.CalculationProperties.CalculationMode = CalculateMode.ToOpenXml();
if (ReferenceStyle == XLReferenceStyle.Default)
workbook.CalculationProperties.ReferenceMode = null;
else
workbook.CalculationProperties.ReferenceMode = ReferenceStyle.ToOpenXml();
}
private UInt32 SetValueFromDataTable(DataTable dataTable, SpreadsheetDocument RecultDoc, SpreadsheetDocument TemleytDoc, UInt32 CurOffset)
{
UInt32 newOffset = 0;
string RangeName = dataTable.TableName;
// SpreadsheetDocument TemleytDoc = SpreadsheetDocument.Open(Temleyt, true);
DefinedName DefName = SpreadsheetReader.GetDefinedName(TemleytDoc, RangeName);
if (DefName != null)
{
if (DefName.Text.IndexOf("#REF!") < 0)
{
string definedName = DefName.Text;
string WorkSheetName = definedName.Substring(0, definedName.IndexOf("!")).TrimEnd('\'').TrimStart('\'');
string Range = definedName.Substring(definedName.IndexOf("!") + 1);
UInt32 startRowInRange = FirstRowInRange(Range);
UInt32 endRowInRange = LastRowInRange(Range);
UInt32 serviceRow = 1;
UInt32 CountRangeData = (endRowInRange - startRowInRange + 1) - serviceRow;
WorksheetPart TemleytWorkSheet = SpreadsheetReader.GetWorksheetPartByName(TemleytDoc, WorkSheetName);
List <Row> ListRowInRange = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
.Where(r => r.RowIndex >= startRowInRange && r.RowIndex < endRowInRange).ToList();
List <Row> ListRowBottom = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
.Where(r => r.RowIndex > endRowInRange).ToList();
List <Row> ServiceRow = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
.Where(r => r.RowIndex == endRowInRange).ToList();
List <MergeCell> ListMCellInRange = new List <MergeCell>();
List <MergeCell> ListMCellBottom = new List <MergeCell>();
MergeCells mergeCells = TemleytWorkSheet.Worksheet.GetFirstChild <MergeCells>();
if (mergeCells != null)
{
ListMCellInRange = TemleytWorkSheet.Worksheet.Elements <MergeCells>().First().Elements <MergeCell>()
.Where(r => SpreadsheetReader.RowFromReference(FirstRefFromRange(r.Reference.Value)) >= startRowInRange && SpreadsheetReader.RowFromReference(LastRefFromRange(r.Reference.Value)) <= endRowInRange).ToList();
ListMCellBottom = TemleytWorkSheet.Worksheet.Elements <MergeCells>().First().Elements <MergeCell>()
.Where(r => SpreadsheetReader.RowFromReference(FirstRefFromRange(r.Reference.Value)) > endRowInRange).ToList();
}
UInt32 NewStartRow = startRowInRange + CurOffset;
WorksheetPart RecultWorkSheetPart = SpreadsheetReader.GetWorksheetPartByName(RecultDoc, WorkSheetName);
WorksheetWriter RecultWriter = new WorksheetWriter(RecultDoc, RecultWorkSheetPart);
DeleteRows(RecultDoc, RecultWriter, NewStartRow, CountRangeData + serviceRow);
if (ListRowBottom.Count() > 0)
{
DeleteRows(RecultDoc, RecultWriter, NewStartRow, ListRowBottom.Last().RowIndex.Value + CurOffset - NewStartRow + 1);
}
MaximumCount = dataTable.Rows.Count;
UInt32 InsideOffset = NewStartRow;
foreach (DataRow dataRow in dataTable.Rows)
{
UInt32 Offset = (InsideOffset - NewStartRow) + CurOffset;
if (dataRow.RowState.ToString() != "Deleted")
{
AppendRows(dataRow, ListRowInRange, ListMCellInRange, RecultWorkSheetPart, Offset);
InsideOffset += CountRangeData;
}
++ProgressValue;
}
newOffset = (InsideOffset - NewStartRow - CountRangeData) + CurOffset;
AppendRows(null, ListRowBottom, ListMCellBottom, RecultWorkSheetPart, newOffset);
DefName = SpreadsheetReader.GetDefinedName(RecultDoc, RangeName);
definedName = DefName.Text;
DefName.Text = definedName.Substring(0, definedName.LastIndexOf('$') + 1) + InsideOffset.ToString();
// DefName.Text = definedName.Substring(0, definedName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(FirstRefFromRange(Range)) + '$' + NewStartRow.ToString() + ":$" + SpreadsheetReader.ColumnFromReference(LastRefFromRange(Range)) + '$' + StartClone.ToString();
//Обновляем ссилки начала и конца рабочих областей DefinedNames
foreach (DefinedName CurdefName in RecultDoc.WorkbookPart.Workbook.DefinedNames)
{
if (CurdefName.Text.IndexOf("#REF!") < 0)
{
// if (CurdefName.Text == definedName)//Текущая облать DefinedName
// {
// CurdefName.Text = definedName.Substring(0, definedName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(FirstRefFromRange(Range)) + '$' + NewStartRow.ToString() + ":$" + SpreadsheetReader.ColumnFromReference(LastRefFromRange(Range)) + '$' + StartOffset.ToString();
// }
// else //Все остальные DefinedName текущего листа Sheet
// {
string strDefName = CurdefName.Text;
string sheetName = strDefName.Substring(0, strDefName.IndexOf("!")).Trim('\'');
string range = strDefName.Substring(strDefName.IndexOf("!") + 1);
if (sheetName == WorkSheetName)
{
string firstRefInRange = FirstRefFromRange(range);
string lastRefInRange = LastRefFromRange(range);
UInt32 firstRow = SpreadsheetReader.RowFromReference(firstRefInRange);
UInt32 lastRow = SpreadsheetReader.RowFromReference(lastRefInRange);
if (firstRow + newOffset > InsideOffset + CurOffset)
{
CurdefName.Text = strDefName.Substring(0, strDefName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(firstRefInRange) + '$' + (firstRow + newOffset).ToString() + ":$" + SpreadsheetReader.ColumnFromReference(lastRefInRange) + '$' + (lastRow + newOffset).ToString();
}
}
// }
}
}
}
}
// TemleytDoc.Close();
return(newOffset);
}
