//.....................................................................
/// <summary>
///
/// </summary>
/// <param name="wbookpart"></param>
private void GenerateWorkBook(WorkbookPart wbookpart)
{
Workbook workbook1 = new Workbook( );
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion( )
{
ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9303"
};
WorkbookProperties workbookProperties1 = new WorkbookProperties( )
{
DefaultThemeVersion = ( UInt32Value )124226U
};
BookViews bookViews1 = new BookViews( );
WorkbookView workbookView1 = new WorkbookView( )
{
XWindow = 0, YWindow = 45, WindowWidth = ( UInt32Value )15075U, WindowHeight = ( UInt32Value )4680U
};
bookViews1.Append(workbookView1);
//.............................................
Sheets sheets1 = new Sheets( );
Sheet sheet1 = new Sheet( )
{
Name = "Sheet1", SheetId = ( UInt32Value )1U, Id = "rId1"
};
Sheet sheet2 = new Sheet( )
{
Name = "Sheet2", SheetId = ( UInt32Value )2U, Id = "rId2"
};
Sheet sheet3 = new Sheet( )
{
Name = "Sheet3", SheetId = ( UInt32Value )3U, Id = "rId3"
};
sheets1.Append(sheet1);
sheets1.Append(sheet2);
sheets1.Append(sheet3);
//.............................................
CalculationProperties calculationProperties1 = new CalculationProperties( )
{
CalculationId = ( UInt32Value )145621U
};
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
wbookpart.Workbook = workbook1;
return;
}
private void LoadWorkbookView()
{
//workbook = SpreadsheetGear.Factory.GetWorkbook(this.tmpXlsName);
workbook = SpreadsheetGear.Factory.GetWorkbook();
worksheet = workbook.ActiveWorksheet;
wbView = new SpreadsheetGear.Windows.Forms.WorkbookView(workbook);
wbView.GetLock();
try
{
SpreadsheetGear.IWorkbookWindowInfo windowInfo = wbView.ActiveWorkbookWindowInfo;
windowInfo.DisplayWorkbookTabs = false;
worksheet.WindowInfo.Zoom = 100;
wbView.Parent = this;;
wbView.BringToFront();
wbView.Dock = DockStyle.Fill;
wbView.CellBeginEdit += new CellBeginEditEventHandler(wbView_CellBeginEdit);
wbView.RangeChanged += new RangeChangedEventHandler(wbView_RangeChanged);
wbView.RangeSelectionChanged += wbView_RangeSelectionChanged;
}
finally
{
wbView.ReleaseLock();
}
}
private void efBtn_excel_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = "c:\\";
openFileDialog.Filter = "txt files (*.xls)|*.xls";
openFileDialog.FilterIndex = 2;
openFileDialog.Multiselect = false;
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
try
{
efGroupBox3.Controls.Clear();
m_workbookView = new WorkbookView();
m_workbookView.GetLock();
efGroupBox3.Controls.Add(m_workbookView);
m_workbookView.Parent = efGroupBox3;
m_workbookView.Dock = DockStyle.Fill;
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(openFileDialog.FileName, System.Globalization.CultureInfo.CurrentCulture);
m_workbookView.ActiveWorkbook = workbook;
m_workbookView.ReleaseLock();
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="view"></param>
public RangeStatus(WorkbookView view)
{
_WorkbookView = view;
_WorkbookView.RangeSelectionChanged += _WorkbookView_RangeSelectionChanged;
_WorkbookView.RangeChanged += _WorkbookView_RangeChanged;
}
internal WorkbookView ToWorkbookView()
{
var wv = new WorkbookView();
if (Visibility != VisibilityValues.Visible)
{
wv.Visibility = Visibility;
}
if (Minimized)
{
wv.Minimized = Minimized;
}
if (!ShowHorizontalScroll)
{
wv.ShowHorizontalScroll = ShowHorizontalScroll;
}
if (!ShowVerticalScroll)
{
wv.ShowVerticalScroll = ShowVerticalScroll;
}
if (!ShowSheetTabs)
{
wv.ShowSheetTabs = ShowSheetTabs;
}
if (XWindow != null)
{
wv.XWindow = XWindow.Value;
}
if (YWindow != null)
{
wv.YWindow = YWindow.Value;
}
if (WindowWidth != null)
{
wv.WindowWidth = WindowWidth.Value;
}
if (WindowHeight != null)
{
wv.WindowHeight = WindowHeight.Value;
}
if (TabRatio != 600)
{
wv.TabRatio = TabRatio;
}
if (FirstSheet != 0)
{
wv.FirstSheet = FirstSheet;
}
if (ActiveTab != 0)
{
wv.ActiveTab = ActiveTab;
}
if (!AutoFilterDateGrouping)
{
wv.AutoFilterDateGrouping = AutoFilterDateGrouping;
}
return(wv);
}
internal WorkbookView ToWorkbookView()
{
WorkbookView wv = new WorkbookView();
if (this.Visibility != VisibilityValues.Visible)
{
wv.Visibility = this.Visibility;
}
if (this.Minimized)
{
wv.Minimized = this.Minimized;
}
if (!this.ShowHorizontalScroll)
{
wv.ShowHorizontalScroll = this.ShowHorizontalScroll;
}
if (!this.ShowVerticalScroll)
{
wv.ShowVerticalScroll = this.ShowVerticalScroll;
}
if (!this.ShowSheetTabs)
{
wv.ShowSheetTabs = this.ShowSheetTabs;
}
if (this.XWindow != null)
{
wv.XWindow = this.XWindow.Value;
}
if (this.YWindow != null)
{
wv.YWindow = this.YWindow.Value;
}
if (this.WindowWidth != null)
{
wv.WindowWidth = this.WindowWidth.Value;
}
if (this.WindowHeight != null)
{
wv.WindowHeight = this.WindowHeight.Value;
}
if (this.TabRatio != 600)
{
wv.TabRatio = this.TabRatio;
}
if (this.FirstSheet != 0)
{
wv.FirstSheet = this.FirstSheet;
}
if (this.ActiveTab != 0)
{
wv.ActiveTab = this.ActiveTab;
}
if (!this.AutoFilterDateGrouping)
{
wv.AutoFilterDateGrouping = this.AutoFilterDateGrouping;
}
return(wv);
}
public frmDownloadFares()
{
InitializeComponent();
objWBView = new WorkbookView();
FormatGrid();
}
internal void FromWorkbookView(WorkbookView wv)
{
this.SetAllNull();
if (wv.Visibility != null)
{
this.Visibility = wv.Visibility.Value;
}
if (wv.Minimized != null)
{
this.Minimized = wv.Minimized.Value;
}
if (wv.ShowHorizontalScroll != null)
{
this.ShowHorizontalScroll = wv.ShowHorizontalScroll.Value;
}
if (wv.ShowVerticalScroll != null)
{
this.ShowVerticalScroll = wv.ShowVerticalScroll.Value;
}
if (wv.ShowSheetTabs != null)
{
this.ShowSheetTabs = wv.ShowSheetTabs.Value;
}
if (wv.XWindow != null)
{
this.XWindow = wv.XWindow.Value;
}
if (wv.YWindow != null)
{
this.YWindow = wv.YWindow.Value;
}
if (wv.WindowWidth != null)
{
this.WindowWidth = wv.WindowWidth.Value;
}
if (wv.WindowHeight != null)
{
this.WindowHeight = wv.WindowHeight.Value;
}
if (wv.TabRatio != null)
{
this.TabRatio = wv.TabRatio.Value;
}
if (wv.FirstSheet != null)
{
this.FirstSheet = wv.FirstSheet.Value;
}
if (wv.ActiveTab != null)
{
this.ActiveTab = wv.ActiveTab.Value;
}
if (wv.AutoFilterDateGrouping != null)
{
this.AutoFilterDateGrouping = wv.AutoFilterDateGrouping.Value;
}
}
public void WorkbookViewSheetClear(WorkbookView wbv)
{
wbv.GetLock();
wbv.ActiveWorksheet.Cells.Clear();
foreach (IShape shape in wbv.ActiveWorksheet.Shapes)
{
shape.Delete();
}
wbv.ReleaseLock();
}
public void SheetClear(WorkbookView wbv)
{
wbv.GetLock();
wbv.ActiveWorksheet.Cells.Clear();
wbv.ActiveWorksheet.Cells.Borders.Color = SpreadsheetGear.Colors.White;
foreach (IShape shape in wbv.ActiveWorksheet.Shapes)
{
shape.Delete();
}
wbv.ReleaseLock();
}
private static void GenerateWorkbookPartContent(WorkbookPart workbookPart)
{
Workbook workbook = new Workbook()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x15"
}
};
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion = new FileVersion()
{
ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420"
};
WorkbookProperties workbookProperties = new WorkbookProperties()
{
FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U
};
BookViews bookViews = new BookViews();
WorkbookView workbookView = new WorkbookView()
{
XWindow = 240, YWindow = 105, WindowWidth = (UInt32Value)14805U, WindowHeight = (UInt32Value)8010U
};
bookViews.Append(workbookView);
Sheets sheets = new Sheets();
Sheet sheet = new Sheet()
{
Name = "Лист1", SheetId = (UInt32Value)1U, Id = "rId1"
};
sheets.Append(sheet);
CalculationProperties calculationProperties = new CalculationProperties()
{
CalculationId = (UInt32Value)152511U,
ForceFullCalculation = true,
FullCalculationOnLoad = true
};
workbook.Append(fileVersion);
workbook.Append(workbookProperties);
workbook.Append(bookViews);
workbook.Append(sheets);
workbook.Append(calculationProperties);
workbookPart.Workbook = workbook;
}
public frmImportBooking()
{
InitializeComponent();
this.Load += new EventHandler(frmComcabBooking_Load);
objWBView = new WorkbookView();
objMaster = new BookingBO();
this.SetProperties((INavigation)objMaster);
grdBookings.TableElement.RowHeight = 70;
grdBookings.TableElement.AlternatingRowColor = Color.AliceBlue;
grdBookings.EnableAlternatingRowColor = true;
grdBookings.ViewCellFormatting += new CellFormattingEventHandler(grdBookings_ViewCellFormatting);
this.InitializeForm("frmBooking");
grdBookings.ShowRowHeaderColumn = true;
}
// Generates content of workbookPart1.
private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
var workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
var fileVersion1 = new FileVersion {
ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9303"
};
var workbookProperties1 = new WorkbookProperties {
DefaultThemeVersion = 124226U
};
var bookViews1 = new BookViews();
var workbookView1 = new WorkbookView {
XWindow = 120, YWindow = 45, WindowWidth = 23715U, WindowHeight = 10035U
};
bookViews1.Append(workbookView1);
var sheets1 = new Sheets();
var sheet1 = new Sheet {
Name = "Tabelle1", SheetId = 1U, Id = "rId1"
};
var sheet2 = new Sheet {
Name = "Tabelle2", SheetId = 2U, Id = "rId2"
};
var sheet3 = new Sheet {
Name = "Tabelle3", SheetId = 3U, Id = "rId3"
};
sheets1.Append(sheet1);
sheets1.Append(sheet2);
sheets1.Append(sheet3);
var calculationProperties1 = new CalculationProperties {
CalculationId = 145621U
};
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
/// <summary>
/// Default document initialization.
/// </summary>
private DocumentContext InitDocument(SpreadsheetDocument document)
{
if (document.WorkbookPart == null)
{
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbookPart.Workbook = workbook;
BookViews bookViews = new BookViews();
WorkbookView workbookView = new WorkbookView()
{
XWindow = -120,
YWindow = -120,
WindowWidth = (UInt32Value)19440U,
WindowHeight = (UInt32Value)15000U,
ActiveTab = 0,
};
bookViews.Append(workbookView);
Sheets sheets = new Sheets();
workbook.AppendChild(bookViews);
workbook.AppendChild(sheets);
}
var documentContext = new DocumentContext(document, _documentMetadata);
// Init Stylesheet.
InitStylesheet(documentContext);
// Add empty string.
documentContext.GetOrAddSharedString(string.Empty);
// External customization
var customizeFunc = _documentMetadata?.GetValue(ExcelDocumentMetadata.ConfigureDocument);
customizeFunc?.Invoke(documentContext);
return(documentContext);
}
private void LoadWorkbookView()
{
//workbook = SpreadsheetGear.Factory.GetWorkbook(this.tmpXlsName);
// workbook = SpreadsheetGear.Factory.GetWorkbook();
worksheet = workbook.ActiveWorksheet;
wbView = new SpreadsheetGear.Windows.Forms.WorkbookView(workbook);
wbView.GetLock();
try
{
//SpreadsheetGear.IWorkbookWindowInfo windowInfo = wbView.ActiveWorkbookWindowInfo;
//windowInfo.DisplayWorkbookTabs = false;
worksheet.WindowInfo.Zoom = 100;
wbView.Parent = this;;
wbView.BringToFront();
wbView.Dock = DockStyle.Fill;
}
finally
{
wbView.ReleaseLock();
}
}
private void efBtn_excel_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = "c:\\";
openFileDialog.Filter = "txt files (*.xls)|*.xls";
openFileDialog.FilterIndex = 2;
openFileDialog.Multiselect = false;
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
try
{
efGroupBox3.Controls.Clear();
m_workbookView = new WorkbookView();
m_workbookView.GetLock();
efGroupBox3.Controls.Add(m_workbookView);
m_workbookView.Parent = efGroupBox3;
m_workbookView.Dock = DockStyle.Fill;
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(openFileDialog.FileName, System.Globalization.CultureInfo.CurrentCulture);
m_workbookView.ActiveWorkbook = workbook;
m_workbookView.ReleaseLock();
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ CodeName = "ThisWorkbook", DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Pivot\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "Query", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName1.Text = "Sheet1!$B$2:$I$13";
definedNames1.Append(definedName1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId2" };
pivotCaches1.Append(pivotCache1);
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{FCE2AD5D-F65C-4FA6-A056-5C36A1767C68}" };
workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.DataModel dataModel1 = new X15.DataModel();
X15.ModelTables modelTables1 = new X15.ModelTables();
X15.ModelTable modelTable1 = new X15.ModelTable(){ Id = "Query_7c41ad89-7105-4c9f-ab5a-881bd3e6a1b9", Name = "Query", Connection = "DAT105 Timestamp - Foodmart 2000 account" };
modelTables1.Append(modelTable1);
dataModel1.Append(modelTables1);
workbookExtension1.Append(dataModel1);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{69C81A23-63F3-4edf-8378-127667AE99B5}" };
OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:workbookPr15 chartTrackingRefBase=\"1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
workbookExtension2.Append(openXmlUnknownElement1);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(pivotCaches1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties();
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Timeline\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "data", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet(){ Name = "data2", SheetId = (UInt32Value)3U, Id = "rId2" };
Sheet sheet3 = new Sheet(){ Name = "Cache", SheetId = (UInt32Value)4U, Id = "rId3" };
Sheet sheet4 = new Sheet(){ Name = "Level", SheetId = (UInt32Value)2U, Id = "rId4" };
Sheet sheet5 = new Sheet(){ Name = "Caption", SheetId = (UInt32Value)5U, Id = "rId5" };
Sheet sheet6 = new Sheet(){ Name = "ShowHeader", SheetId = (UInt32Value)6U, Id = "rId6" };
Sheet sheet7 = new Sheet(){ Name = "ShowSelectionLabel", SheetId = (UInt32Value)7U, Id = "rId7" };
Sheet sheet8 = new Sheet(){ Name = "ShowTimeLevel", SheetId = (UInt32Value)8U, Id = "rId8" };
Sheet sheet9 = new Sheet(){ Name = "ShowHorizontalScrollbar", SheetId = (UInt32Value)9U, Id = "rId9" };
Sheet sheet10 = new Sheet(){ Name = "ScrollPosition", SheetId = (UInt32Value)10U, Id = "rId10" };
Sheet sheet11 = new Sheet(){ Name = "Style", SheetId = (UInt32Value)11U, Id = "rId11" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
sheets1.Append(sheet3);
sheets1.Append(sheet4);
sheets1.Append(sheet5);
sheets1.Append(sheet6);
sheets1.Append(sheet7);
sheets1.Append(sheet8);
sheets1.Append(sheet9);
sheets1.Append(sheet10);
sheets1.Append(sheet11);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "NativeTimeline_Date" };
definedName1.Text = "#N/A";
DefinedName definedName2 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate" };
definedName2.Text = "#N/A";
DefinedName definedName3 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate1" };
definedName3.Text = "#N/A";
DefinedName definedName4 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate2" };
definedName4.Text = "#N/A";
DefinedName definedName5 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate3" };
definedName5.Text = "#N/A";
DefinedName definedName6 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate4" };
definedName6.Text = "#N/A";
DefinedName definedName7 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate5" };
definedName7.Text = "#N/A";
DefinedName definedName8 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate6" };
definedName8.Text = "#N/A";
DefinedName definedName9 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate7" };
definedName9.Text = "#N/A";
definedNames1.Append(definedName1);
definedNames1.Append(definedName2);
definedNames1.Append(definedName3);
definedNames1.Append(definedName4);
definedNames1.Append(definedName5);
definedNames1.Append(definedName6);
definedNames1.Append(definedName7);
definedNames1.Append(definedName8);
definedNames1.Append(definedName9);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId12" };
PivotCache pivotCache2 = new PivotCache(){ CacheId = (UInt32Value)1U, Id = "rId13" };
pivotCaches1.Append(pivotCache1);
pivotCaches1.Append(pivotCache2);
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();
workbookExtension1.Append(workbookProperties2);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{D0CA8CA8-9F24-4464-BF8E-62219DCF47F9}" };
workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineCacheReferences timelineCacheReferences1 = new X15.TimelineCacheReferences();
X15.TimelineCacheReference timelineCacheReference1 = new X15.TimelineCacheReference(){ Id = "rId14" };
X15.TimelineCacheReference timelineCacheReference2 = new X15.TimelineCacheReference(){ Id = "rId15" };
X15.TimelineCacheReference timelineCacheReference3 = new X15.TimelineCacheReference(){ Id = "rId16" };
X15.TimelineCacheReference timelineCacheReference4 = new X15.TimelineCacheReference(){ Id = "rId17" };
X15.TimelineCacheReference timelineCacheReference5 = new X15.TimelineCacheReference(){ Id = "rId18" };
X15.TimelineCacheReference timelineCacheReference6 = new X15.TimelineCacheReference(){ Id = "rId19" };
X15.TimelineCacheReference timelineCacheReference7 = new X15.TimelineCacheReference(){ Id = "rId20" };
X15.TimelineCacheReference timelineCacheReference8 = new X15.TimelineCacheReference(){ Id = "rId21" };
X15.TimelineCacheReference timelineCacheReference9 = new X15.TimelineCacheReference(){ Id = "rId22" };
timelineCacheReferences1.Append(timelineCacheReference1);
timelineCacheReferences1.Append(timelineCacheReference2);
timelineCacheReferences1.Append(timelineCacheReference3);
timelineCacheReferences1.Append(timelineCacheReference4);
timelineCacheReferences1.Append(timelineCacheReference5);
timelineCacheReferences1.Append(timelineCacheReference6);
timelineCacheReferences1.Append(timelineCacheReference7);
timelineCacheReferences1.Append(timelineCacheReference8);
timelineCacheReferences1.Append(timelineCacheReference9);
workbookExtension2.Append(timelineCacheReferences1);
WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension3.Append(workbookProperties3);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbookExtensionList1.Append(workbookExtension3);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(pivotCaches1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
/// <summary>
/// 生成二维码
/// </summary>
/// <param name="wbv">传入WorkbookView控件</param>
/// <param name="Title">标题</param>
/// <param name="ColumnNames">表头列表</param>
/// <param name="Values">值列表</param>
/// <param name="QRCodeValue">二维码值</param>
public void LoadBarcode(WorkbookView wbv, string Title,
string[] ColumnNames, string[] Values, string QRCodeValue, string[] Checkouts)
{
double Proportion = 0.6;
WorkbookViewSheetClear(wbv);
wbv.GetLock();
IRange range = wbv.ActiveWorksheet.Cells;
range[0, 0].RowHeight = 42 * Proportion;
range.Borders.LineStyle = LineStyle.None;
range.Font.Name = "楷体";
range.Font.Bold = true;
range.HorizontalAlignment = HAlign.Left;
range.Font.Size = 12 * Proportion;
range.ColumnWidth = 10 * Proportion;
range.RowHeight = 16 * Proportion;
range[0, 0, 0, 4].Merge();
range[0, 0].RowHeight = 32 * Proportion;
range[0, 0].HorizontalAlignment = HAlign.Center;
range[0, 0].VerticalAlignment = VAlign.Center;
range[0, 0].Value = Title;
range[0, 0].Font.Size = 24 * Proportion;
range[2, 0, 2 + ColumnNames.Length - 1, 0].RowHeight = 20 * Proportion;
range[1, 0].Value = ColumnNames[0] + ":";
range[1, 1, 1, 5].Merge();
range[1, 1].Value = Values[0];
range[2, 0].Value = ColumnNames[6] + ":";
range[2, 1].Value = Values[6];
range[2, 1, 2, 5].Merge();
range[3, 0].Value = ColumnNames[2] + ":";
range[3, 1, 3, 2].Merge();
range[3, 1].Value = Values[2];
range[3, 3].Value = ColumnNames[3] + ":";
range[3, 4].Value = Values[3];
range[4, 2].Value = ColumnNames[1] + ":";
range[4, 3].Value = Values[1];
range[4, 0].Value = ColumnNames[7] + ":";
range[4, 1].Value = Values[7];
//range[4, 0].Value = ColumnNames[4] + ":";
//range[4, 1].Value = Values[4];
//range[4, 3].Value = ColumnNames[5] + ":";
//range[4, 4].Value = Values[5];
for (int i = 0; i < Checkouts.Length; i++)
{
range[5 + (i / 6 * 2), 0 + i % 6].Value = Checkouts[i];
range[6 + (i / 6 * 2), 0 + i % 6].Value = "□";
range[6 + (i / 6 * 2), 0 + i % 6].Font.Size = range[6 + (i / 6 * 2), 0 + i % 6].Font.Size * 1.2;
}
range[0, 0, 12, 5].Borders.Color = SpreadsheetGear.Colors.Black;
range[0, 0, 12, 5].Borders[SpreadsheetGear.BordersIndex.InsideHorizontal].LineStyle = LineStyle.None;
range[0, 0, 12, 5].Borders[SpreadsheetGear.BordersIndex.InsideVertical].LineStyle = LineStyle.None;
range[3 + ColumnNames.Length, 0].Select();
MemoryStream ms = new MemoryStream();
try
{
QRCodeEncoder qrCodeEncoder = new QRCodeEncoder();
qrCodeEncoder.QRCodeEncodeMode = QRCodeEncoder.ENCODE_MODE.BYTE;
qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.M;
qrCodeEncoder.QRCodeVersion = 0;
qrCodeEncoder.QRCodeScale = 3;
//生成图像
qrCodeEncoder.Encode(QRCodeValue, Encoding.UTF8).
Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);;
}
catch (Exception ex)
{
ReturnValue.ShowMessage(new ReturnValue(false, -1, ex.Message));
}
byte[] bytes = ms.GetBuffer();
wbv.ActiveWorksheet.Shapes.AddPicture(bytes, 335 * Proportion,
2 * Proportion, 80 * Proportion, 80 * Proportion);
wbv.ActiveWorksheetWindowInfo.Zoom = (int)(100 / Proportion);
wbv.ReleaseLock();
}
internal WorkbookView ToWorkbookView()
{
WorkbookView wv = new WorkbookView();
if (this.Visibility != VisibilityValues.Visible) wv.Visibility = this.Visibility;
if (this.Minimized) wv.Minimized = this.Minimized;
if (!this.ShowHorizontalScroll) wv.ShowHorizontalScroll = this.ShowHorizontalScroll;
if (!this.ShowVerticalScroll) wv.ShowVerticalScroll = this.ShowVerticalScroll;
if (!this.ShowSheetTabs) wv.ShowSheetTabs = this.ShowSheetTabs;
if (this.XWindow != null) wv.XWindow = this.XWindow.Value;
if (this.YWindow != null) wv.YWindow = this.YWindow.Value;
if (this.WindowWidth != null) wv.WindowWidth = this.WindowWidth.Value;
if (this.WindowHeight != null) wv.WindowHeight = this.WindowHeight.Value;
if (this.TabRatio != 600) wv.TabRatio = this.TabRatio;
if (this.FirstSheet != 0) wv.FirstSheet = this.FirstSheet;
if (this.ActiveTab != 0) wv.ActiveTab = this.ActiveTab;
if (!this.AutoFilterDateGrouping) wv.AutoFilterDateGrouping = this.AutoFilterDateGrouping;
return wv;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
WorkbookProperties workbookProperties1 = new WorkbookProperties();
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 600, YWindow = 105, WindowWidth = (UInt32Value)13995U, WindowHeight = (UInt32Value)8190U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Hoja1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)0U };
definedName1.Text = "Hoja1!$A$1:$AK$31";
definedNames1.Append(definedName1);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)124519U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\WorkBookPr\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties2 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension1.Append(workbookProperties2);
workbookExtensionList1.Append(workbookExtension1);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
public SpreadsheetGearWorkbookViewController(WorkbookView workbookView)
{
WorkbookViewModel = workbookView;
}
private void generateWorkbookPartContent(WorkbookPart workbookPart)
{
var workbook = new Workbook();
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
var fileVersion = new FileVersion { ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9303" };
var workbookProperties = new WorkbookProperties { DefaultThemeVersion = 124226 };
var bookViews = new BookViews();
var workbookView = new WorkbookView { XWindow = 630, YWindow = 600, WindowWidth = 27495, WindowHeight = 11955 };
bookViews.Append(workbookView);
var sheets = new Sheets();
var sheet = new Sheet { Name = "Документ", SheetId = 1, Id = "rId1" };
sheets.Append(sheet);
var calculationProperties = new CalculationProperties { CalculationId = 0 };
workbook.Append(fileVersion);
workbook.Append(workbookProperties);
workbook.Append(bookViews);
workbook.Append(sheets);
workbook.Append(calculationProperties);
workbookPart.Workbook = workbook;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" };
WorkbookProperties workbookProperties1 = new WorkbookProperties() { DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 0, YWindow = 45, WindowWidth = (UInt32Value)19155U, WindowHeight = (UInt32Value)11820U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
//Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
//Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
//Sheet sheet3 = new Sheet() { Name = "Sheet3", SheetId = (UInt32Value)3U, Id = "rId3" };
//sheets1.Append(sheet1);
//sheets1.Append(sheet2);
//sheets1.Append(sheet3);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 240, YWindow = 45, WindowWidth = (UInt32Value)28320U, WindowHeight = (UInt32Value)12855U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "NOMBRE DE SOLAPA", SheetId = (UInt32Value)1U, Id = "rId1" };
switch (_Estado.ToUpper())
{
case "ORDENADO":
{
sheet1.Name = "O" + _oCabecera.AnoMes + " " + _oCabecera.IdentifEspacio;
break;
}
case "ESTIMADO":
{
sheet1.Name = "E" + _eCabecera.AnoMes + " V" + _eCabecera.Version + " " + _eCabecera.IdentifEspacio;
break;
}
case "CERTIFICADO":
{
sheet1.Name = "C" + _cCabecera.AnoMes + " " + _cCabecera.IdentifEspacio + _cCabecera.IdentifOrigen;
break;
}
}
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)124519U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
private static void Paste(WorkbookView wbView)
{
// check to see if clipboard contains text
if (!Clipboard.ContainsText())
return;
// Confirm the pending changes before going on
//ConfirmChanges();
// Get the selected start row and col
IRange selectedRng = wbView.RangeSelection;
int startRow = selectedRng.Row;
int startCol = selectedRng.Column;
// Do not paste if the row and column is out of the data area
// If nRow < LOCATION_ROW_OFFSET OrElse nCol <= ColumnIndex.Order OrElse nCol > ColumnIndex.InspectionRequest Then Return
//if (startCol > ColumnIndex.InspectionRequest)
// return;
// Get content from clipboard
string[] lines = Clipboard.GetText().Split('\r');
if (lines.Length < 65535)
{
wbView.PasteSpecial(PasteType.Values, PasteOperation.Add, false, false);
return;
}
// get lock before pasting.
wbView.GetLock();
try
{
IRange cells = wbView.ActiveWorksheet.Cells;
for (int k = 0; k < lines.Length; k++)
{
if (!string.IsNullOrEmpty(lines[k]))
{
// get current row index
int row = startRow + k;
// split the line
string[] aValue = lines[k].Split('\t');
for (int i = 0; i < aValue.Length; i++)
{
// get current column index
int col = startCol + i;
cells[row, col].Value = aValue[col];
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
// If showProgressBar AndAlso frmBar IsNot Nothing Then frmBar.Close()
wbView.ReleaseLock();
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" };
WorkbookProperties workbookProperties1 = new WorkbookProperties() { DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 360, YWindow = 150, WindowWidth = (UInt32Value)14355U, WindowHeight = (UInt32Value)4680U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
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();
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Slicer\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "Slicer_Column1" };
definedName1.Text = "#N/A";
DefinedName definedName2 = new DefinedName(){ Name = "Slicer_Column2" };
definedName2.Text = "#N/A";
DefinedName definedName3 = new DefinedName(){ Name = "Slicer_Column3" };
definedName3.Text = "#N/A";
definedNames1.Append(definedName1);
definedNames1.Append(definedName2);
definedNames1.Append(definedName3);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();
workbookExtension1.Append(workbookProperties2);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{46BE6895-7355-4a93-B00E-2C351335B9C9}" };
workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.SlicerCaches slicerCaches1 = new X15.SlicerCaches();
slicerCaches1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerCache slicerCache1 = new X14.SlicerCache(){ Id = "rId2" };
X14.SlicerCache slicerCache2 = new X14.SlicerCache(){ Id = "rId3" };
X14.SlicerCache slicerCache3 = new X14.SlicerCache(){ Id = "rId4" };
slicerCaches1.Append(slicerCache1);
slicerCaches1.Append(slicerCache2);
slicerCaches1.Append(slicerCache3);
workbookExtension2.Append(slicerCaches1);
WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension3.Append(workbookProperties3);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbookExtensionList1.Append(workbookExtension3);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 240, YWindow = 105, WindowWidth = (UInt32Value)14805U, WindowHeight = (UInt32Value)8010U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)152511U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
internal void FromWorkbookView(WorkbookView wv)
{
this.SetAllNull();
if (wv.Visibility != null) this.Visibility = wv.Visibility.Value;
if (wv.Minimized != null) this.Minimized = wv.Minimized.Value;
if (wv.ShowHorizontalScroll != null) this.ShowHorizontalScroll = wv.ShowHorizontalScroll.Value;
if (wv.ShowVerticalScroll != null) this.ShowVerticalScroll = wv.ShowVerticalScroll.Value;
if (wv.ShowSheetTabs != null) this.ShowSheetTabs = wv.ShowSheetTabs.Value;
if (wv.XWindow != null) this.XWindow = wv.XWindow.Value;
if (wv.YWindow != null) this.YWindow = wv.YWindow.Value;
if (wv.WindowWidth != null) this.WindowWidth = wv.WindowWidth.Value;
if (wv.WindowHeight != null) this.WindowHeight = wv.WindowHeight.Value;
if (wv.TabRatio != null) this.TabRatio = wv.TabRatio.Value;
if (wv.FirstSheet != null) this.FirstSheet = wv.FirstSheet.Value;
if (wv.ActiveTab != null) this.ActiveTab = wv.ActiveTab.Value;
if (wv.AutoFilterDateGrouping != null) this.AutoFilterDateGrouping = wv.AutoFilterDateGrouping.Value;
}
