public void Dispose()
{
if (this.appSheets != null)
{
Marshal.ReleaseComObject(this.appSheets);
this.appSheets = null;
}
if (this.appWork != null)
{
Marshal.ReleaseComObject(this.appWork);
this.appWork = null;
}
if (this.appExcel != null)
{
Marshal.ReleaseComObject(this.appExcel);
try
{
Process.GetProcessById(this.k).Kill();
}
catch (Exception)
{
}
this.appExcel = null;
}
}
public bool CreateOrOpenExcelFile(bool mode, string path = "")
{
try
{
if (mode == true)
{
ExcelWorkBook = ExcelWorkbooks.Add(mode);
}
else if (path != "")
{
ExcelWorkBook = ExcelWorkbooks.Add(path);
}
else
{
System.Windows.Forms.MessageBox.Show("路径不能为空");
return false;
}
ExcelSheets = ExcelWorkBook.Sheets;
return true;
}
catch(Exception ex)
{
return false;
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
public void InformeGeneralObras()
{
oExcel = new Application();
oBooks = oExcel.Workbooks;
oBook = oBooks.Add(1);
oSheets = (Sheets)oBook.Worksheets;
oSheet = oSheets.get_Item(1);
this.oSheet.Cells[1,1] = "Consecutivo";
this.oSheet.Cells[1,2] = "Título";
this.oSheet.Cells[1,3] = "Núm. de Material";
this.oSheet.Cells[1,4] = "Año";
this.oSheet.Cells[1,5] = "Tiraje";
int ind = 2;
for (int j = 0; j < obrasImprimir.Count; j++)
{
oSheet.Cells[1][ind] = obrasImprimir[j].Consecutivo;
oSheet.Cells[2][ind] = obrasImprimir[j].Titulo;
oSheet.Cells[3][ind] = obrasImprimir[j].NumMaterial;
oSheet.Cells[4][ind] = obrasImprimir[j].AnioPublicacion;
oSheet.Cells[5][ind] = obrasImprimir[j].Tiraje;
ind++;
}
this.oExcel.ActiveWorkbook.Save();
this.oExcel.Quit();
}
public Func<IController, bool> GetControllerPredicate(Sheets sheet)
{
Func<IController, bool> temp;
return !_internalMap.TryGetValue(sheet, out temp)
? (ctrl => false)
: temp;
}
/// <summary>
/// 指定されたパラメタでインスタンスを生成します。
/// </summary>
/// <param name="interopWorkbook">実体ワークブック</param>
internal Workbook(object interopWorkbook)
{
// リソース解放フラグ設定
this.disposed = false;
// 実体ワークブックインスタンス設定
this.interopWorkbook = interopWorkbook;
// シートコレクション生成
this.sheets = new Sheets(this.interopWorkbook.Sheets);
}
// Generates content of workbookPart1.
private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
private WorksheetPart wsSheet = null; // WorkSheetPart
#endregion Fields
#region Constructors
public LogGenerator(string fn)
{
//// get spreadsheet path from constructor
//path = folder;
//// File name is based on date and time
//DateTime now = DateTime.Now;
//// Construct the spreadsheet filename
//string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx",
// path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
if (File.Exists(fn))
{
doc = SpreadsheetDocument.Open(fn, true);
}
else
{
//
// Create the Spreadsheet document
//
doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook);
//
// Add WoorkBookPart to the document
//
wbPart = doc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add WorkSheetPart to the WorkBookPart
//
wsSheet = wbPart.AddNewPart<WorksheetPart>();
wsSheet.Worksheet = new Worksheet(new SheetData());
wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add sheets to the WorkBook
//
sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
//
// Append new sheet and associate it with the workbook
//
sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName };
sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
sheets.Append(sheet);
wbPart.Workbook.Save();
//CreatePackage(fn);
//if (File.Exists(fn))
//{
// TestXls();
//}
}
}
public void Create(string fileName)
{
FileName = fileName;
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = doc.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// Create Styles
var stylesPart = doc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
Style = new CustomStylesheet();
LoadCustomFonts();
LoadCustomBorders();
LoadCustomStyles();
Style.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
// create workbook and sheet
var workbook = new Workbook();
var worksheet = new Worksheet();
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
// create columns
var columns = new Columns();
CreateColumns(columns);
worksheet.Append(columns);
// create Sheet
var sheets = new Sheets();
var sheet = new Sheet { Name = "My sheet", SheetId = 1, Id = relId };
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
var sheetData = new SheetData();
LoadData(sheetData);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
doc.WorkbookPart.Workbook = workbook;
doc.WorkbookPart.Workbook.Save();
//doc.Close();
}
}
public static void runScenarioSummary()
{
runCalcAction( ()=>{
shts = oWB.Worksheets;
oSheet = (Worksheet) shts["Projection Input Options"];
InputOptionsPage userOptionsAndVerification = new InputOptionsPage(oSheet);
RatingGroups groups = new RatingGroups("Rating Groups", true);
PriorSurcharge priorSurcharge = new PriorSurcharge("Rating Groups", userOptionsAndVerification.modalAdj);
NewSurcharge newSurcharge = new NewSurcharge("Rating Groups", userOptionsAndVerification.modalAdj);
PriorPremiums priorPremiums = new PriorPremiums("Prior Premiums");
NewPremiums newPremiums = new NewPremiums("New Premiums");
Census census = new Census("Census");
System.Data.DataTable rfTableMultipliers = groups.RFactors;
System.Data.DataTable newPrm = Utility.ConvertListToDataTable(newPremiums.NewPrmLst);
System.Data.DataTable oldPrm = Utility.ConvertListToDataTable(priorPremiums.OldPrmLst);
newPrm = Utility.mdlPrm(newPrm, userOptionsAndVerification.modalAdj);
oldPrm = Utility.mdlPrm(oldPrm, userOptionsAndVerification.modalAdj);
System.Data.DataTable cns = Utility.ConvertListToDataTable(census.CnLst);
List<System.Data.DataTable> prSurChrg = priorSurcharge.PSCharge;
List<System.Data.DataTable> nwSurChrg = newSurcharge.NSCharge;
//Code works in following way
//1) optimalCarrierRates chooses best CARRIER rates for MIGRATION ONLY
//2) ratesToAnchorOffOf is the CARRIER rates to anchor off of (this could be the same as 1) or it could be the second lowest cost carrier)
// these are all metallic levels at this point, the anchor plan level is selected later
//3) optimalRatesAndFactors joins the subsidy factors to the optimal rates
//4) anchoredPlans takes ratesToAnchorOffOf and only keeps the correct metallic level that the user selected
//5) nwPrm uses the Anchored plans, calcs the subsidy based on the user selected subsidy option and then applies this to the optimalCarrierRates
//6) fnlCns migrates the population to the correct plan level, then attaches surcharge amounts to the premiums
//7) finalRateGrid simply attaches the non lowest cost carriers to the rates to provide a complete set of carrier rates. These are treated as a
// buy up with the EE shouldering any burden of buying a non anchored plan.
//8) prjTb produces exhibit output tabs
//9) sendCalcLog logs the model use with the model logger
System.Data.DataTable optimalCarrierRates = FindOptimalCarrierPremiums.FindPremiumCarrier(newPrm, userOptionsAndVerification.defaultAnchorPlan); //Finds optimal rates
System.Data.DataTable ratesToAnchorOffOf = FindOptimalCarrierPremiums.FindPremiumCarrier(newPrm, userOptionsAndVerification.ancPln); //Allows the anchored plan to be different than optimal carrier
System.Data.DataTable optimalRatesAndFactors = Utility.Join(rfTableMultipliers, optimalCarrierRates); //Join on factors to optimal rates
System.Data.DataTable anchoredPlans = AnchorPlans.GetAnchorPlans(ratesToAnchorOffOf, userOptionsAndVerification.ancPln, groups.baselineCoverageTier); //Get anchor plan and join onto optimal rates and factors
System.Data.DataTable nwPrm = SubsidyCalculation.CalcBaseSubsidy(optimalRatesAndFactors, anchoredPlans, userOptionsAndVerification); //calculate Subsidy
System.Data.DataTable fnlCns = SurchargeCombine.SurChrgCombine(MigrationEngine.mrgCnsPrm(cns, oldPrm, nwPrm, ref userOptionsAndVerification), prSurChrg, nwSurChrg);
System.Data.DataTable finalRateGrid = Utility.CreateCombinedPremiumExhibit(newPrm, nwPrm);
ProjectionResults.prjTb(fnlCns, finalRateGrid, userOptionsAndVerification, oWB);
AxCalcEngineAPI.sendCalcLog(userOptionsAndVerification, urlName, modVersion);
});
}
public MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
{
MemoryStream stream = new MemoryStream();
UInt32 rowcount = 0;
// Create the Excel document
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var relId = workbookPart.GetIdOfPart(worksheetPart);
var workbook = new Workbook();
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
var worksheet = new Worksheet();
var sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
var sheets = new Sheets();
var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
document.WorkbookPart.Workbook = workbook;
document.WorkbookPart.Workbook.Save();
// Add header to the sheet
var row = new Row { RowIndex = ++rowcount };
for (int i = 0; i < fieldsToExpose.Length; i++)
{
row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
}
sheetData.AppendChild(row);
worksheetPart.Worksheet.Save();
// Add data to the sheet
foreach (DataRow dataRow in data.Rows)
{
row = new Row { RowIndex = ++rowcount };
for (int i = 0; i < fieldsToExpose.Length; i++)
{
row.Append(CreateTextCell(ColumnLetter(i), rowcount, dataRow[fieldsToExpose[i]].ToString()));
}
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
return stream;
}
/// <summary>
/// Creates the sheet
/// </summary>
/// <param name="sheets">The sheet for this item</param>
/// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
/// <param name="workbookPart">The workbookpart associated with the spreadsheet</param>
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 3U, // <-- Change for each sheet that is created
Name = _groupOrder.SheetName
};
sheets.Append(sheet);
//Add cells to the sheet
InsertTextIntoCells(spreadsheetDocument, worksheetPart);
}
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 4U,
Name = _resources.SheetName
};
sheets.Append(sheet);
//Add cells to the sheet
InsertTextIntoCells(spreadsheetDocument, worksheetPart);
}
public void DisposeExcelObject()
{
try
{
if (workbook != null)
workbook.Close(true);
if (application != null)
{
if (application.Workbooks != null)
application.Workbooks.Close();
application.Quit();
}
if (range != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
if (workbook != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
if (workbooks != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
if (sheets != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
if (sheets != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
if (application != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
range = null;
workbook = null;
workbooks = null;
sheets = null;
worksheet = null;
application = null;
Process[] pProcess;
pProcess = Process.GetProcessesByName("Excel");
foreach (var process in pProcess)
{
process.Kill();
}
}
catch (Exception ex)
{
}
}
public void CreateCustomerData(ref ISelenium browser)
{
try
{
var admincommonObj = new AdminSuite.Common();
string user, password = "******";
_testDataFilePath = _currentDirPath.Parent.FullName;
_app = new Application();
//var testDataObj = new TestData();
_app.DisplayAlerts = false;
_workBook = _app.Workbooks.Open(_testDataFilePath + "\\TestData\\TestData.xls", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
_workSheets = _workBook.Worksheets;
var _userSheet = (Microsoft.Office.Interop.Excel.Worksheet)_workSheets.get_Item("Users");
Random rnd = new Random();
int rndNumber;
for (int i = 0; i < 10; i++)
{
rndNumber = rnd.Next(10000);
user = "******" + rndNumber;
admincommonObj.CreateCustomer(browser, user, password);
_userSheet.Cells[i + 2, "B"] = user;
_userSheet.Cells[i +2, "C"] = password;
admincommonObj.PerformManualAdjustment(browser, user, "Test Accounts", "100", "Yes", "Test Accounts", "", "", "", "Today");
_workBook.Save();
}
}
catch (Exception Ex)
{
Framework.BaseTest.CaptureScreenshot(browser, "");
Console.WriteLine(Ex.StackTrace);
Framework.BaseTest.Fail(Ex.Message);
}
finally
{
_workBook.Save();
_workBook.Close();
_workSheet = null;
_workBook = null;
_app.Quit();
}
}
public void ExcelFromDataTable(DataTable dt, string reportName)
{
try
{
_oXl = new Application {DisplayAlerts = false};
//opens the required exel document
_mWorkBook = _oXl.Workbooks.Open(Path, 0, false, 5, "", "", false, XlPlatform.xlWindows, true, false, 0,
true, false, false);
_mWorkSheets = _mWorkBook.Worksheets;
_mWSheet1 = (Worksheet)_mWorkSheets.Item["sheet1"];
//add the columns and rows from the datatable to the excel template
for (int i = 0; i < dt.Columns.Count; i++)
{
_mWSheet1.Range["A4"].Offset[0, i].Value = dt.Columns[i].ColumnName;
}
for (var i = 0; i < dt.Rows.Count; i++)
{
_mWSheet1.Range["A5"].Offset[i].Resize[1, dt.Columns.Count].Value = dt.Rows[i].ItemArray;
}
//applies the reports name in the template
_mWSheet1.Cells[2, 1] = reportName;
//shows the workbook
_oXl.Visible = true;
_mWSheet1 = null;
_mWorkBook = null;
//clear up operations;
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
var errorMessage = ErrorLog.CreateErrorMessage(err);
ErrorLog.LogFileWriter(errorMessage);
}
}
/// <summary>
/// Generate all inspection reports
/// </summary>
/// <param name="templatePath">Path of template</param>
/// <param name="itemsource">Item source</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateAllInspectionReports(ExportInspectionReportsModel itemsource,string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms,SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
int sheetId = 1;
Sheets sheets = new Sheets();
if (itemsource.IsSelectedServiceOrder)
{
//Generate service order report
GenerateServiceOrder(itemsource, workbookPart, sheets,sheetId,logoPath);
sheetId++;
}
//Generate inspection reports
GenerateAllInspectionReports(itemsource, workbookPart, sheets, sheetId, logoPath);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
internal override void Create(WorkbookPart workBookPart, Sheets sheets)
{
Create(workBookPart, sheets, "Histogram data");
}
static void Main(string[] args)
{
IEnumerable <Customer> reportData = Report.GetCustomers();
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create("CustomersReport_Styled.xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart wBookPart = spreadsheetDoc.AddWorkbookPart();
wBookPart.Workbook = new Workbook();
//Creamos nuevo objeto de hojas
spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets();
//para facilitar la manipulación del objeto sheets
Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
//para facilitar el manejo del worksheetpart
WorksheetPart wSheetPart = wBookPart.AddNewPart <WorksheetPart>();
//crea parte para estilos
WorkbookStylesPart stylesPart = spreadsheetDoc.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = StylesheetGenerator.GenerateStyleSheet(); //método para hoja de estilos
stylesPart.Stylesheet.Save();
Columns columns = new Columns();
columns.Append(new Column {
Width = 30, Min = 1, Max = 8
});
Sheet sheet = new Sheet()
{
Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(wSheetPart),
SheetId = 1,
Name = "Hoja_Con_Estilos",
};
sheets.Append(sheet);
SheetData sheetData = new SheetData();
wSheetPart.Worksheet = new Worksheet(columns, sheetData);
Row headerRow = new Row();
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Name"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Register Date"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Last Buy"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Product"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Cost"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Quantity"
}
});
headerRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = "Total"
}
});
sheetData.AppendChild(headerRow);
foreach (Customer data in reportData)
{
Row contentRow = new Row();
contentRow.Append(new Cell {
StyleIndex = 3, DataType = CellValues.String, CellValue = new CellValue {
Text = data.Name
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.RegisterDate
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.LastBuy
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.Item
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.Quantity.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.ItemCost.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = string.Format("{0}", data.Quantity * data.ItemCost)
}
});
sheetData.AppendChild(contentRow);
}
}
}
public FileResult ExportLocationReport(int CompanyId, string sCurrDate)
{
byte[] b;
DateTime curr;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
using (MemoryStream memStream = new MemoryStream())
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook))
{
// Build Excel File
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
// declare locals
Row row;
Cell cell;
string loc;
int rr;
// Build sheet
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Locations"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Location {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("A3", "Name"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("B3", "Short Name"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("C3", "Phone"); row.Append(cell);
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal("D3", "Email"); row.Append(cell);
worksheet.Append(oxl.columns);
sd.Append(row);
List <Presenter> locations = db.Presenters.Where(x => x.CompanyId == CompanyId).ToList();
// Content
for (int i = 0; i < locations.Count(); i++)
{
row = new Row();
rr = 4 + i;
loc = "A" + rr; cell = oxl.SetCellVal(loc, locations[i].Name); row.Append(cell);
loc = "B" + rr; cell = oxl.SetCellVal(loc, locations[i].ShortName); row.Append(cell);
loc = "C" + rr; cell = oxl.SetCellVal(loc, locations[i].Phone); row.Append(cell);
loc = "D" + rr; cell = oxl.SetCellVal(loc, locations[i].Email); row.Append(cell);
sd.Append(row);
}
worksheet.Append(sd);
// Autofit columns - ss:AutoFitWidth="1"
worksheetPart.Worksheet = worksheet;
workbookPart.Workbook.Save();
document.Close();
b = memStream.ToArray();
Company company = db.FindCompany(CompanyId);
return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
company.Name + $" Locations as of {currDate}.xlsx"));
}
}
}
private byte[] Export <T>(IEnumerable <T> datas, Dictionary <string, string> headerDict)
where T : class, new()
{
byte[] result = null;
var ms = new MemoryStream();
var fileUrl = Path.Combine(Environment.CurrentDirectory, DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx");
using (var workbook = SpreadsheetDocument.Create(fileUrl, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)
)
{
var type = typeof(T);
var props = type.GetProperties();
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = "sheet1"
};
sheets.Append(sheet);
Row headerRow = new Row();
List <string> columns = new List <string>();
foreach (var kvp in headerDict)
{
columns.Add(kvp.Value);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(kvp.Value);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (var item in datas)
{
Row newRow = new Row();
foreach (string col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
var propName = headerDict.FirstOrDefault(x => x.Value == col).Key;
var prop = props.FirstOrDefault(x => x.Name == propName);
cell.CellValue = new CellValue(
prop.GetValue(item).ToString()
);
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
//var options = new ParallelOptions()
//{
// MaxDegreeOfParallelism = 8,
//};
//ConcurrentBag<Row> rows = new ConcurrentBag<Row>();
//Parallel.ForEach(datas, options,data => {
// Row newRow = new Row();
// foreach (string col in columns)
// {
// Cell cell = new Cell();
// cell.DataType = CellValues.String;
// var propName = headerDict.FirstOrDefault(x => x.Value == col).Key;
// var prop = props.FirstOrDefault(x => x.Name == propName);
// cell.CellValue = new CellValue(
// col
// );
// newRow.AppendChild(cell);
// }
// rows.Add(newRow);
//});
//sheetData.Append(rows);
}
result = File.ReadAllBytes(fileUrl);
try
{
var tf = new TaskFactory();
tf.StartNew(() =>
{
File.Delete(fileUrl);
});
}
catch
{
}
return(result);
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0 ?
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First() :
spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets =
spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "I1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "ФИО клиента",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A2",
CellToName = "C2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = 2,
Text = "Дата заказа",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "D2",
CellToName = "E2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = 2,
Text = "Сумма к оплате",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "F2",
CellToName = "G2"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "H",
RowIndex = 2,
Text = "Статус",
StyleIndex = 0U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "H2",
CellToName = "I2"
});
uint rowIndex = 1;
foreach (var sr in info.Orders)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex + 2,
Text = sr.ClientFIO,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex + 2,
Text = sr.DateCreate.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = rowIndex + 2,
Text = (sr.Price - sr.Sum).ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "H",
RowIndex = rowIndex + 2,
Text = sr.Status.ToString(),
StyleIndex = 0U
});
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
private static void AssemblyHoursExcelTask(string message)
{
var filePath = @"C:\Dev\Source\DotNetTestProjectRepo\ExcelInterop\Assembly_time_calculator.xlsx";
Debug.WriteLine(message);
Application
xlAppObject = new Application();
xlAppObject.Visible = false;
xlAppObject.ScreenUpdating = true;
Workbook wb = xlAppObject.Workbooks.Open(filePath, Editable: true, IgnoreReadOnlyRecommended: true);
Debug.WriteLine($"Nr of Sheet: {wb.Sheets.Count}");
Sheets sheets = wb.Worksheets;
Worksheet worksheet = (Worksheet)sheets.Item[1];
var rangeCells = worksheet.Range["J20:L29"];
//var findCell = (rangeCells.Find("Conveyor type") as Range);
//var addressForCell = findCell.AddressLocal[false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1];
int i = 0;
int j = 100;
foreach (Range cell in rangeCells.Rows)
{
Debug.WriteLine($"Column:{cell.Address}");
for (int k = 1; k <= cell.Columns.Count; k++)
{
Debug.WriteLine($"Column: {cell.Columns[k].Address}");
Range cellRng = worksheet.Range[cell.Columns[k].Address];
cellRng.Value2 = j++;
}
//foreach (Range cellColumn in cell.Columns)
//{
// Debug.WriteLine($"Column: {cellColumn.Address}");
// Range cellRng = worksheet.Range[cellColumn.Address];
// cellRng.Value2 = i++;
//}
wb.Save();
}
Debug.WriteLine($"Sheet Name: {worksheet.Name}");
////Get cell value
//Range excelRange = worksheet.UsedRange;
var cellsTuple = (
conveyorTypeCell : worksheet.Range["$C$5"].Value,
totalLengthOfTheConveyorCell : worksheet.Range["$C$6"].Value,
nrOfTheConveyorCell : worksheet.Range["$C$7"].Value,
nrOfBendsInTheSystemCell : worksheet.Range["$C$8"].Value,
tSlotCoveringCell : worksheet.Range["$C$9"].Value,
steelSlideRailCell : worksheet.Range["$C$10"].Value,
typeOfSupportsCell : worksheet.Range["$C$11"].Value,
distanceBetweenTheSupportsCell : worksheet.Range["$C$12"].Value,
nrOdSupportCell : worksheet.Range["$D$12"].Value,
assemplyOnTheHeightCell : worksheet.Range["$C$13"].Value,
connectionOfTwoConveyorBeamCell : worksheet.Range["$C$14"].Value,
guideRail : worksheet.Range["$B$292"].Value
);
var cellResult = worksheet.Range["$J$39"]?.Value;
Debug.WriteLine($"Value: " +
$"{cellsTuple.conveyorTypeCell}\n" +
$"{cellsTuple.totalLengthOfTheConveyorCell}\n" +
$"{cellsTuple.nrOfTheConveyorCell}\n" +
$"{cellsTuple.nrOfBendsInTheSystemCell}\n" +
$"{cellsTuple.tSlotCoveringCell}\n" +
$"{cellsTuple.steelSlideRailCell}\n" +
$"{cellsTuple.typeOfSupportsCell}\n" +
$"{cellsTuple.nrOdSupportCell}\n" +
$"{cellsTuple.assemplyOnTheHeightCell}\n" +
$"{cellsTuple.distanceBetweenTheSupportsCell}\n" +
$"{cellsTuple.connectionOfTwoConveyorBeamCell}" +
$"{cellsTuple.guideRail}" +
$"Result: {Math.Round(cellResult)}"
);
//Parse value to cell
Range rng = worksheet.Range["$C$5"];
rng.Value2 = "XLX-X85X";
rng = worksheet.Range["$B$292"];
rng.Value2 = true;
rng = worksheet.Range["$C$6"];
rng.Value2 = 100;
wb.Save();
cellsTuple = (
conveyorTypeCell : worksheet.Range["$C$5"].Value,
totalLengthOfTheConveyorCell : worksheet.Range["$C$6"].Value,
nrOfTheConveyorCell : worksheet.Range["$C$7"].Value,
nrOfBendsInTheSystemCell : worksheet.Range["$C$8"].Value,
tSlotCoveringCell : worksheet.Range["$C$9"].Value,
steelSlideRailCell : worksheet.Range["$C$10"].Value,
typeOfSupportsCell : worksheet.Range["$C$11"].Value,
distanceBetweenTheSupportsCell : worksheet.Range["$C$12"].Value,
nrOdSupportCell : worksheet.Range["$D$12"].Value,
assemplyOnTheHeightCell : worksheet.Range["$C$13"].Value,
connectionOfTwoConveyorBeamCell : worksheet.Range["$C$14"].Value,
guideRail : worksheet.Range["$B$292"].Value
);
cellResult = worksheet.Range["$J$39"]?.Value;
Debug.WriteLine($"Value: {cellsTuple.conveyorTypeCell}\n" +
$"{cellsTuple.totalLengthOfTheConveyorCell}\n" +
$"{cellsTuple.guideRail}\n" +
$"Result: {Math.Round(cellResult)}");
//wb.Save();
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(worksheet);
wb.Close(0);
xlAppObject.Quit();
}
public async Task <IActionResult> CarsBase()
{
string outFileName = "CarBase_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Hour + DateTime.Now.Minute + ".xlsx";
DataTable dtServices = new System.Data.DataTable();
var outpathLocal = Path.Combine(
Directory.GetCurrentDirectory(), @"wwwroot/OutFiles/CarBasesReports",
outFileName);
//CreateCarsBaseReport(dtServices);
// Формирование табличной части
dtServices.Columns.Add("Модель");
dtServices.Columns.Add("Гос.номер");
dtServices.Columns.Add("Эксплуатирует");
dtServices.Columns.Add("Подразделение");
dtServices.Columns.Add("Владелец");
dtServices.Columns.Add("Закрепление");
dtServices.Columns.Add("Размещение");
dtServices.Columns.Add("Статус");
var cars = _context.Cars
.Include(c => c.CarModel).ThenInclude(c => c.Manufacturer)
.Include(c => c.CarOwner)
.Include(c => c.CarUsers).ThenInclude(c => c.Employee)
.Include(c => c.CarStatuses).ThenInclude(c => c.Unit).ThenInclude(c => c.Firm).ThenInclude(c => c.Employee)
.Include(c => c.CarStatuses).ThenInclude(c => c.Unit).ThenInclude(c => c.Department)
.Include(c => c.CarStatuses).ThenInclude(c => c.Status)
.Include(c => c.CarStatuses).ThenInclude(c => c.Location)
.ToList();
//var cars = _context.Cars.Include(c => c.CarOwner).ToList();
foreach (var item in cars)
{
DataRow row = dtServices.NewRow();
row["Модель"] = item.CarModel.Manufacturer.Name + " " + item.CarModel.Model;
row["Гос.номер"] = item.RegistrationNumber;
row["Владелец"] = item.CarOwner.FullName;
row["Закрепление"] = item.CarUserForView;
if (item.CarStatuses.Count > 0)
{
row["Эксплуатирует"] = item.CarStatuses.LastOrDefault().Unit.Firm.Employee.FullName;
row["Подразделение"] = item.CarStatuses.LastOrDefault().Unit.Department.Name;
row["Размещение"] = item.CarStatuses.LastOrDefault().Location.LocationName;
//row["Статус"] = item.CarStatuses.LastOrDefault().Status.State;
string _outStat = String.Empty;
if (KernelCars.Infrastructure.Utils.carStatus.TryGetValue(item.CarStatuses.LastOrDefault().Status.State, out _outStat))
{
row["Статус"] = _outStat;
}
}
dtServices.Rows.Add(row);
}
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(outpathLocal, SpreadsheetDocumentType.Workbook);
//Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var sheetData = new SheetData();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Автомобили"
};
sheets.Append(sheet);
Row firstRow = new Row();
Cell headerCell = new Cell();
headerCell.DataType = CellValues.String;
headerCell.CellValue = new CellValue("Список легковых авто по состоянию на " + DateTime.Now.ToShortDateString());
firstRow.AppendChild(headerCell);
sheetData.AppendChild(firstRow);
Row headerRow = new Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in dtServices.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in dtServices.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
//if (col == "Гос.номер" || col == "Модель")
//{
cell.DataType = CellValues.String;
//}
//else
//{
// cell.DataType = CellValues.Number;
//}
cell.CellValue = new CellValue(dsrow[col].ToString());//.Replace(",", "."));
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
var memory = new MemoryStream();
//var outpath = Path.Combine(
// Directory.GetCurrentDirectory(), @"wwwroot/OutFiles",
// outFileName);
using (var stream = new FileStream(outpathLocal, FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", Path.GetFileName(outpathLocal)));
}
private bool KiemTraCuPhapFile(string path)
{
/*
* Kiểm tra cú pháp file:
* Đọc dữ liệu từng dòng -- tạo đối tượng question và đưa vào mảng
* Đóng các tiến trình và ngừng thao tác
* Trả về mảng Question
*
*/
if (!File.Exists(path))
{
return(false);
}
//
//
//
bool kqCheck = false;
// nhận đối tượng rỗng
Application myApp = null;
Workbooks myBooks = null;
Workbook myBook = null;
Sheets mySheets = null;
Worksheet mySheet = null;
//Cells cells = null;
//object range = null;
try
{
//bool kqCheck = false;
// nạp dữ liệu lên
myApp = new Application();
myBooks = myApp.Workbooks;
myBook = myBooks.Open(path);
mySheets = myBook.Worksheets;
mySheet = (Worksheet)mySheets[1];
var cells = mySheet.Cells;
var range = cells.SpecialCells(XlCellType.xlCellTypeLastCell);
// kiểm tra dữ liệu
int lastRow = range.Row;
int lastColumn = range.Column;
var mangHeaderChuan = new string[9]
{
"STT",
"Question Type",
"Score",
"QuestionContent",
"Choice A",
"Choice B",
"Choice C",
"Choice D",
"Anser Key"
};
int i = 1;
int colToCheck = mangHeaderChuan.Length;
for (i = 1; i <= colToCheck; i++)
{
if (mySheet.Cells[1, i].Value2 != mangHeaderChuan[i - 1])
{
break;
}
}
if (i == lastColumn + 1)
{
kqCheck = true;
}
//object MissingTypeLocal = System.Reflection.Missing.Value;
//myBook.Close(true, MissingTypeLocal, MissingTypeLocal);
//myBooks.Close();
//myApp.Quit();
//
object MissingTypeLocal = System.Reflection.Missing.Value;
myBook.Close(true, MissingTypeLocal, MissingTypeLocal);
myBooks.Close();
myApp.Quit();
releaseObject(range);
releaseObject(cells);
releaseObject(mySheet);
releaseObject(mySheets);
releaseObject(myBook);
releaseObject(myBooks);
releaseObject(myApp);
}
catch
{
}
// trả bộ nhớ lại
//
//foreach(var Cell in mySheet.Cells)
//{
// Marshal.ReleaseComObject(Cell);
//}
//
//
return(kqCheck);
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "Е1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "№",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = 2,
Text = "Название маршрута",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "С",
RowIndex = 2,
Text = "Цена",
StyleIndex = 0U
});
uint i = 1;
foreach (var route in info.Routes)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = i + 2,
Text = i.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = i + 2,
Text = route.RouteName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = i + 2,
Text = route.Cost.ToString(),
StyleIndex = 0U
});
i++;
}
workbookpart.Workbook.Save();
}
}
private static IEnumerable <EntryChange> ImportMultipleSheets(ResourceManager resourceManager, Sheets sheets, WorkbookPart workbookPart, IList <SharedStringItem>?sharedStrings)
{
var entities = GetMultipleSheetEntities(resourceManager).ToArray();
var changes = sheets.OfType <Sheet>()
.SelectMany(sheet => FindResourceEntity(entities, sheet).ImportTable(_fixedColumnHeaders, sheet.GetTable(workbookPart, sharedStrings)));
return(changes);
}
public static string[][][] leerExcel(string archivo)
{
// Creamos objetos necesarios.
Application xlApp = new Application();
Workbooks workbooks = xlApp.Workbooks;
Workbook xlWorkbook = workbooks.Open(archivo);
Sheets sheets = xlWorkbook.Sheets;
int sheetCount = sheets.Count;
string[][][] arr = new string[sheetCount][][];
for (int k = 1; k <= sheetCount; k++)
{
Worksheet xlWorksheet = sheets[k];
Range xlRange = xlWorksheet.UsedRange;
Range rows = xlRange.Rows;
Range columns = xlRange.Columns;
int rowCount = rows.Count;
int colCount = columns.Count;
arr[k - 1] = new string[rowCount][];
// Iteramos sobre las columnas y filas para guardarlas en el arreglo.
for (int i = 1; i <= rowCount; i++)
{
arr[k - 1][i - 1] = new string[colCount];
for (int j = 1; j <= colCount; j++)
{
var celda = (xlRange.Cells[i, j]);
// Guardar el valor en el arreglo
if (celda != null && celda.Value2 != null)
{
arr[k - 1][i - 1][j - 1] = celda.Value2.ToString();
}
Marshal.ReleaseComObject(celda);
}
}
// rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
// release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(columns);
Marshal.ReleaseComObject(rows);
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
// Limpieza
GC.Collect();
GC.WaitForPendingFinalizers();
}
// Cerramos y liberamos
Marshal.ReleaseComObject(sheets);
xlWorkbook.Close(0);
Marshal.ReleaseComObject(xlWorkbook);
workbooks.Close();
Marshal.ReleaseComObject(workbooks);
int id;
GetWindowThreadProcessId(xlApp.Hwnd, out id);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
Process p = Process.GetProcessById(id);
p.Kill();
// Limpieza
GC.Collect();
GC.WaitForPendingFinalizers();
return(arr);
}
private void BuildWorkbook(string filename)
{
try
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
var wbp = xl.AddWorkbookPart();
var wsp = wbp.AddNewPart <WorksheetPart>();
var wb = new Workbook();
var fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
var ws = new Worksheet();
var sd = new SheetData();
var wbsp = wbp.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
var sImagePath = "polymathlogo.png";
var dp = wsp.AddNewPart <DrawingsPart>();
var imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
{
imgp.FeedData(fs);
}
var nvdp = new NonVisualDrawingProperties
{
Id = 1025,
Name = "Picture 1",
Description = "polymathlogo"
};
var picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks
{
NoChangeAspect = true,
NoChangeArrowheads = true
};
var nvpdp = new NonVisualPictureDrawingProperties
{
PictureLocks = picLocks
};
var nvpp = new NonVisualPictureProperties
{
NonVisualDrawingProperties = nvdp,
NonVisualPictureDrawingProperties = nvpdp
};
var stretch = new DocumentFormat.OpenXml.Drawing.Stretch
{
FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle()
};
var blip = new DocumentFormat.OpenXml.Drawing.Blip
{
Embed = dp.GetIdOfPart(imgp),
CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
};
var blipFill = new BlipFill
{
Blip = blip,
SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle()
};
blipFill.Append(stretch);
var offset = new DocumentFormat.OpenXml.Drawing.Offset
{
X = 0,
Y = 0
};
var t2d = new DocumentFormat.OpenXml.Drawing.Transform2D
{
Offset = offset
};
var bm = Xwt.Drawing.Image.FromFile(sImagePath).ToBitmap();
//http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
//http://stackoverflow.com/questions/1341930/pixel-to-centimeter
//http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
var extents = new DocumentFormat.OpenXml.Drawing.Extents
{
Cx = (long)bm.Width * (long)((float)914400 / bm.PixelWidth),
Cy = (long)bm.Height * (long)((float)914400 / bm.PixelHeight)
};
bm.Dispose();
t2d.Extents = extents;
var prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry
{
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle,
AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList()
};
var sp = new ShapeProperties
{
BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto,
Transform2D = t2d
};
sp.Append(prstGeom);
sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
var picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture
{
NonVisualPictureProperties = nvpp,
BlipFill = blipFill,
ShapeProperties = sp
};
var pos = new Position {
X = 0, Y = 0
};
Extent ext = new Extent {
Cx = extents.Cx, Cy = extents.Cy
};
var anchor = new AbsoluteAnchor
{
Position = pos,
Extent = ext
};
anchor.Append(picture);
anchor.Append(new ClientData());
var wsd = new WorksheetDrawing();
wsd.Append(anchor);
var drawing = new Drawing {
Id = dp.GetIdOfPart(imgp)
};
wsd.Save(dp);
UInt32 index;
Random rand = new Random();
sd.Append(CreateHeader(10));
sd.Append(CreateColumnHeader(11));
for (index = 12; index < 30; ++index)
{
sd.Append(CreateContent(index, ref rand));
}
ws.Append(sd);
ws.Append(drawing);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet
{
Name = "Sheet1",
SheetId = 1,
Id = wbp.GetIdOfPart(wsp)
};
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
}
}
public override void Export(string filename, LayoutList list)
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = xl.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
//add styles
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Add a SharedStringTablePart to the WorkbookPart.
var stringPart = workbookpart.AddNewPart <SharedStringTablePart>();
var stringTable = new StringKeyList();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sd = new SheetData();
Worksheet worksheet = new Worksheet(sd);
worksheetPart.Worksheet = worksheet;
// Add Sheets to the Workbook.
Sheets sheets = xl.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = xl.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DataSheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
//List<ILayoutItem> cols = LayoutMapTool.GetVisibleItems(list.ListInfo.Columns);
//columns
ExpMapLayout(sd, list.ListInfo.Columns, 0, 1, out int mc, out int ind, null, null, stringTable);
//data
if (list.ListInfo.GroupVisible)
{
foreach (LayoutGroup g in list.Groups)
{
ind++;
Cell cell = GetCell(g.TextValue, 0, (int)ind, 8, stringTable);
GetRow(sd, ind, false).Append(cell);
MergeCells mcells = GetMergeCells(worksheet);
MergeCell mcell = new MergeCell()
{
Reference = new StringValue(cell.CellReference + ":" + Helper.IntToChar(mc) + (ind).ToString())
};
mcells.Append(mcell);
for (int i = g.IndexStart; i <= g.IndexEnd; i++)
{
ind++;
ExpMapLayout(sd, list.ListInfo.Columns, 0, ind, out mc, out ind, list, list.ListSource[i], stringTable);
}
}
}
else
{
foreach (object o in list.ListSource)
{
ind++;
ExpMapLayout(sd, list.ListInfo.Columns, 0, ind, out mc, out ind, list, o, stringTable);
}
}
worksheet.Save();
OpenXmlValidator validator = new OpenXmlValidator();
var errors = validator.Validate(xl);
StringBuilder sb = new StringBuilder();
foreach (var error in errors)
{
sb.AppendLine(error.Description);
sb.AppendLine(error.Path.XPath.ToString());
sb.AppendLine();
}
if (sb.Length > 0)
{
//System.Windows.Forms.MessageDialog.ShowMessage(sb.ToString());
}
xl.Close();
}
}
static void Main(string[] args)
{
string filenameLocal = AppDomain.CurrentDomain.BaseDirectory + "../../radio.xlsx";
string filenameOptic = AppDomain.CurrentDomain.BaseDirectory + "../../optic.xlsx";
var xlsApp = new Microsoft.Office.Interop.Excel.Application();
if (xlsApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
//Displays Excel so you can see what is happening
//xlsApp.Visible = true;
Workbook wb = xlsApp.Workbooks.Open(filenameLocal,
0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true);
Sheets sheets = wb.Worksheets;
Worksheet ws = (Worksheet)sheets.get_Item(1);
double[] azimutLocalVector = getExcelColumn(ws, 9, false);
double[] timeLocalVector = getExcelColumn(ws, 2, false);
double[] rangeVector = getExcelColumn(ws, 23, false);
for (int i = 0; i < azimutLocalVector.Length; i++)
{
if (azimutLocalVector[i] != 0)
{
azimutLocalVector[i] = azimutLocalVector[i] * Math.PI / 180;
radioAzimut.Add(new Measure(Math.Round(timeLocalVector[i], e), azimutLocalVector[i]));
}
}
double[] ugolMestaLocalVector = getExcelColumn(ws, 10, false);
for (int i = 0; i < ugolMestaLocalVector.Length; i++)
{
if (ugolMestaLocalVector[i] != 0)
{
ugolMestaLocalVector[i] = ugolMestaLocalVector[i] * Math.PI / 180;
radioUgolMesta.Add(new Measure(Math.Round(timeLocalVector[i], e), ugolMestaLocalVector[i]));
}
}
for (int i = 0; i < rangeVector.Length; i++)
{
if (rangeVector[i] != 0)
{
range.Add(new Measure(Math.Round(timeLocalVector[i], e), rangeVector[i]));
}
}
wb = xlsApp.Workbooks.Open(filenameOptic,
0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true);
sheets = wb.Worksheets;
ws = (Worksheet)sheets.get_Item(1);
double[] timeOpticVector = getExcelColumn(ws, 2, false);
double[] azimutOpticVector = getExcelColumn(ws, 6, false);
double[] ugolMestaOpticVector = getExcelColumn(ws, 7, false);
for (int i = 0; i < timeOpticVector.Length; i++)
{
//azimutOpticVector[i] = azimutOpticVector[i] * Math.PI / 180;
//ugolMestaOpticVector[i] = ugolMestaOpticVector[i] * Math.PI / 180;
opticAzimut.Add(new Measure(timeOpticVector[i], azimutOpticVector[i]));
opticUgolMesta.Add(new Measure(timeOpticVector[i], ugolMestaOpticVector[i]));
}
CalculateByRange(20000);
CalculateByRange(10000);
CalculateByRange(5000);
CalculateByRange(3000);
//CalculateByRange(2000);
//CalculateByRange(1200);
//getSkolz(20000, 5);
Console.ReadKey();
}
// 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;
}
/// <summary>
/// Generate an excel file with the list of users
/// </summary>
/// <param name="dataSource">The list of users</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateUserReport(List<UserModel> dataSource, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VocStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("A2", Resources.Common.UserList, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 45));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 42));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 10));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 32));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.Email, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.FullName, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.Active, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.Role, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Email, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.FullName, rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.IsActive, rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Role, rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = Resources.Common.UserList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
public static void CreateDoc(Info info)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
// Создаем книгу (в ней хранятся листы)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
// Получаем/создаем хранилище текстов для книги
SharedStringTablePart shareStringPart =
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
?
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
:
spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
// Создаем SharedStringTable, если его нет
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
// Создаем лист в книгу
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Добавляем лист в книгу
Sheets sheets =
spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "C1"
});
uint rowIndex = 2;
foreach (var pc in info.Routes)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = pc.Name,
StyleIndex = 0U
});
rowIndex++;
foreach (var reserve in pc.Reserves)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = reserve.Name,
StyleIndex = 1U
});
rowIndex++;
}
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
private static void CheckAllProfiles(Hashtable oldBook, Sheets newSheets)
{
try
{
var newRows = new Hashtable();
Worksheet saveSheet = null;
#region Läs in alla nya, och hitta sheetet "AllProfiles"
// Läs in alla nya
var numOfNewSheets = newLog.Worksheets.Count;
//// get the first worksheet from the collection of worksheets
//// loop through 10 rows of the spreadsheet and place each row in the list view
// _compareProgress.StartTotal("Loading new AllProfiles Log sheets and compares...", numOfNewSheets);//-1 );
// int sheetsDone = 0;//För progress
// Compare to old rows
for (var sheetNr = 1; sheetNr <= numOfNewSheets; sheetNr++)
{
// if (MainForm.StopGracefully)
// break;
var name = ((Worksheet)newSheets.Item[sheetNr]).Name;
var newWorksheet = (Worksheet)newSheets.Item[sheetNr]; // (Excel.Worksheet)newSheets.get_Item(1);
// Läs in hela nuv. nya arket till en HT
// Specialfall för AllProfiles-flikar
if (name.StartsWith("AllProfiles"))
{
GetExcelRows(newWorksheet, newRows);
}
if (name == "AllProfiles")
{
saveSheet = newWorksheet;
}
// Ev. Rensa _part1...X
// _compareProgress.SetTotal(sheetsDone++);
}
#endregion
#region Lägg ihop alla gamla
var oldRows = oldBook["AllProfiles"] as Hashtable;
foreach (DictionaryEntry item in oldBook)
{
var name = item.Key as string;
var rows = item.Value as Hashtable;
// if (name == "AllProfiles")//Specialfall för AllProfiles-flikar
// {
// oldRows = rows;
// }
// Specialfall för AllProfiles-flikar
if (name != null && name.StartsWith("AllProfiles_"))
{
if (rows != null)
{
foreach (DictionaryEntry innerItem in rows)
{
if (oldRows != null
// ReSharper disable AssignNullToNotNullAttribute
&& oldRows.ContainsKey(innerItem.Key as string))
// ReSharper restore AssignNullToNotNullAttribute
{
// Här finns en dublett! Det ska ladrig inträffa, för det ska vara unika som läggs till, även om det iofs är olika blad
Console.WriteLine(
"Double fond in CheckAllProfiles old rows: " + name + ". Key: >" + innerItem.Key
+ "<");
}
else
{
if (oldRows != null)
{
// ReSharper disable AssignNullToNotNullAttribute
if (!oldRows.ContainsKey(innerItem.Key as string))
{
oldRows.Add(innerItem.Key as string, innerItem.Value as ExcelRowEntry);
// ReSharper restore AssignNullToNotNullAttribute
}
}
}
}
}
}
}
#endregion
CompareExcelRows(saveSheet, oldRows, newRows);
}
catch (Exception allPexcp)
{
throw new Exception(
"Error in comparing AllProfiles log with new log. Was the log opened in Excel during compare processing?\r\n\r\n(Sys err: "
+ allPexcp.Message + ").",
allPexcp);
}
}
public byte[] GenerateExcel(List <System.Data.DataTable> _DataSet, string Classification)
{
var stream = new MemoryStream();
SpreadsheetDocument document = SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = document.AddWorkbookPart();
wbp.Workbook = new Workbook();
Sheets sheets = new Sheets();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
UInt32 TotalSheets = 1;
WorksheetPart[] wsp = new WorksheetPart[TotalSheets];
//Worksheet[] ws = new Worksheet[TotalSheets];
SheetData[] sd = new SheetData[TotalSheets];
Sheet[] sheet = new Sheet[TotalSheets];
Columns thisColumn = new Columns();
for (int i = 0; i < TotalSheets; i++)
{
wsp[i] = wbp.AddNewPart <WorksheetPart>();
sd[i] = new SheetData();
wsp[i].Worksheet = new Worksheet();
wsp[i].Worksheet.Append(thisColumn);
wsp[i].Worksheet.Append(sd[i]);
sheet[i] = new Sheet();
}
WorkbookStylesPart wbsp = wbp.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
_GetExcelInfo _ge = new _GetExcelInfo();
UInt32 HeaderRow = 1;
for (int i = 0; i < TotalSheets; i++)
{
DataColumnCollection _dccColumnID = _DataSet[i].Columns;
if (_dccColumnID.Contains("rownumb"))
{
_DataSet[i].Columns.Remove("rownumb");
}
if (_dccColumnID.Contains("SSN"))
{
_DataSet[i].Columns.Remove("SSN");
}
if (_dccColumnID.Contains("DOC_MAP")) //Catch all to rename this column whenever present.
{
_DataSet[i].Columns["DOC_MAP"].ColumnName = "ASG";
}
CreateColumnHeader(_DataSet[i], sd[i], _ge, HeaderRow);
CreateHeaderFooter(Classification, wsp, sd, i);
CreateContent(_DataSet[i], sd[i], HeaderRow, _ge);
AutoSizeColumns(wsp, sd, thisColumn, i);
}
for (UInt32 i = 0; i < TotalSheets; i++)
{
//wsp[i].Worksheet.Append(sd[i]);
wsp[i].Worksheet.Save();
sheet[i].SheetId = i + 1;
sheet[i].Name = "Sheet " + (i + 1);
sheet[i].Id = wbp.GetIdOfPart(wsp[i]);
sheets.Append(sheet[i]);
}
wbp.Workbook.Append(fv);
wbp.Workbook.Append(sheets);
document.WorkbookPart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
public virtual void GetSheet(string nsheetname)
{
try
{
this.appSheets = this.appWork.Worksheets;
for (int i = 1; i <= this.appSheets.Count; i++)
{
if (((Worksheet) this.appSheets[i]).Name == nsheetname)
{
this.appCurSheet = (Worksheet) this.appSheets[i];
}
}
}
catch (Exception exception)
{
new QxClassErrLog { ErrClass = base.GetType().ToString(), MethodName = "GetSheet(string nsheetname)", ErrContent = exception.Message }.WriteLog();
throw new ExcelException("获取工作区失败");
}
}
public Result ImportExcelFile(string path)
{
if (!KiemTraCuPhapFile(path))
{
throw new Exception("Loi Cu Phap File");
}
List <Question> myGottenQuestions = new List <Question>();
List <string> myCannotAddQuestionList = new List <string>();
// nhận đối tượng rỗng
Application myApp = null;
Workbooks myBooks = null;
Workbook myBook = null;
Sheets mySheets = null;
Worksheet mySheet = null;
try
{
//bool kqCheck = false;
// nạp dữ liệu lên
myApp = new Application();
myBooks = myApp.Workbooks;
myBook = myBooks.Open(path);
mySheets = myBook.Worksheets;
mySheet = (Worksheet)mySheets[1];
var cells = mySheet.Cells;
var range = cells.SpecialCells(XlCellType.xlCellTypeLastCell);
// kiểm tra dữ liệu
int lastRow = range.Row;
int lastColumn = range.Column;
var mangHeaderChuan = new string[9]
{
"STT",
"Question Type",
"Score",
"QuestionContent",
"Choice A",
"Choice B",
"Choice C",
"Choice D",
"Anser Key"
};
int i = 4;
int colToCheck = mangHeaderChuan.Length;
for (i = 2; i <= lastRow; i++)
{
string type = mySheet.Cells[i, 2].Value2;
Question myQuestion = Question.CreateNewQuestion(type);
if (myQuestion != null)
{
myQuestion.Score = (int)mySheet.Cells[i, 3].Value2;
myQuestion.QuestionContent = (string)mySheet.Cells[i, 4].Value2;
myQuestion.AnswerKey = mySheet.Cells[i, 9].Value2 + "";
if (myQuestion is Multiple_Choice)
{
Multiple_Choice myMultipleChoiceQuestion = (Multiple_Choice)myQuestion;
myMultipleChoiceQuestion.ChoiceA = mySheet.Cells[i, 5].Value2;
myMultipleChoiceQuestion.ChoiceB = mySheet.Cells[i, 6].Value2;
myMultipleChoiceQuestion.ChoiceC = mySheet.Cells[i, 7].Value2;
myMultipleChoiceQuestion.ChoiceD = mySheet.Cells[i, 8].Value2;
}
if (myQuestion is Compound)
{
Compound myCompoundQuesion = (Compound)myQuestion;
myCompoundQuesion.ChoiceA = mySheet.Cells[i, 5].Value2;
myCompoundQuesion.ChoiceB = mySheet.Cells[i, 6].Value2;
myCompoundQuesion.ChoiceC = mySheet.Cells[i, 7].Value2;
myCompoundQuesion.ChoiceD = mySheet.Cells[i, 8].Value2;
}
myGottenQuestions.Add(myQuestion);
}
else
{
myCannotAddQuestionList.Add(i.ToString());
}
}
if (i == lastColumn + 1)
{
//kqCheck = true;
}
//object MissingTypeLocal = System.Reflection.Missing.Value;
//myBook.Close(true, MissingTypeLocal, MissingTypeLocal);
//myBooks.Close();
//myApp.Quit();
//
/*
* string type = mySheet.Cells[i, 2].Value2;
* Question myQuestion = Question.CreateNewQuestion(type);
* if (myQuestion != null)
* {
* myQuestion.Score = (int)mySheet.Cells[i, 3].Value2;
* myQuestion.QuestionContent = (string)mySheet.Cells[i, 4].Value2;
* myQuestion.AnswerKey = mySheet.Cells[i, 9].Value2 + "";
* if (myQuestion is Multiple_Choice)
* {
* Multiple_Choice myMultipleChoiceQuestion = (Multiple_Choice)myQuestion;
* myMultipleChoiceQuestion.ChoiceA = mySheet.Cells[i, 5].Value2;
* myMultipleChoiceQuestion.ChoiceB = mySheet.Cells[i, 6].Value2;
* myMultipleChoiceQuestion.ChoiceC = mySheet.Cells[i, 7].Value2;
* myMultipleChoiceQuestion.ChoiceD = mySheet.Cells[i, 8].Value2;
* }
* if (myQuestion is Compound)
* {
* Compound myCompoundQuesion = (Compound)myQuestion;
* myCompoundQuesion.ChoiceA = mySheet.Cells[i, 5].Value2;
* myCompoundQuesion.ChoiceB = mySheet.Cells[i, 6].Value2;
* myCompoundQuesion.ChoiceC = mySheet.Cells[i, 7].Value2;
* myCompoundQuesion.ChoiceD = mySheet.Cells[i, 8].Value2;
* }
* myGottenQuestions.Add(myQuestion);
* }
* else
* myCannotAddQuestionList.Add(i.ToString());
*/
object MissingTypeLocal = System.Reflection.Missing.Value;
myBook.Close(true, MissingTypeLocal, MissingTypeLocal);
myBooks.Close();
myApp.Quit();
releaseObject(range);
releaseObject(cells);
releaseObject(mySheet);
releaseObject(mySheets);
releaseObject(myBook);
releaseObject(myBooks);
releaseObject(myApp);
}
catch
{
}
// trả bộ nhớ lại
//
//foreach(var Cell in mySheet.Cells)
//{
// Marshal.ReleaseComObject(Cell);
//}
//
return(new Result
{
GottenQuestions = myGottenQuestions,
NotGottenQuestion = myCannotAddQuestionList
});
//
}
public static bool CreateResultSheet(List <KeywordResultValue> keywordStats)
{
var deleteResult = DeleteWorksheetPart("Results");
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = "Results";
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
//sheets.Append(sheet);
sheets.InsertAt(sheet, 1);
// Save the new worksheet.
newWorksheetPart.Worksheet.Save();
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>();
}
for (uint i = 0; i < keywordStats.Count(); i++)
{
Cell statB = InsertCellInWorksheet("B", i + 2, newWorksheetPart);
Cell statC = InsertCellInWorksheet("C", i + 2, newWorksheetPart);
// Insert the text into the SharedStringTablePart.
var index = InsertSharedStringItem(keywordStats.ElementAt((int)i).Keyword, shareStringPart);
// Set the value of cell B*
statB.CellValue = new CellValue(index.ToString());
statB.DataType = new EnumValue <CellValues>(CellValues.SharedString);
statC.CellValue = new CellValue(keywordStats.ElementAt((int)i).Volume.ToString());
statC.DataType = new EnumValue <CellValues>(CellValues.Number);
}
// Save the new worksheet.
newWorksheetPart.Worksheet.Save();
return(true);
}
public void CreateExcelDoc(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Export"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Заявка", CellValues.String),
ConstructCell("Статус", CellValues.String),
ConstructCell("Дата Создания", CellValues.String),
ConstructCell("Создатель", CellValues.String),
ConstructCell("Улица", CellValues.String),
ConstructCell("Дом", CellValues.String),
ConstructCell("Корпус", CellValues.String),
ConstructCell("Квартира", CellValues.String),
ConstructCell("Телефоны", CellValues.String),
ConstructCell("Услуга", CellValues.String),
ConstructCell("Причина", CellValues.String),
ConstructCell("Примечание", CellValues.String),
ConstructCell("Дата", CellValues.String),
ConstructCell("Время", CellValues.String),
ConstructCell("Мастер", CellValues.String),
ConstructCell("Исполнитель", CellValues.String),
ConstructCell("Выполнение С", CellValues.String),
ConstructCell("Выполнение По", CellValues.String),
ConstructCell("Потрачено Времени", CellValues.String),
ConstructCell("Гарантийная", CellValues.String),
ConstructCell("Оценка", CellValues.String),
ConstructCell("Комментарий К Оценке", CellValues.String),
ConstructCell("Повторная", CellValues.String),
ConstructCell("Аварийная", CellValues.String)
);
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var request in RequestList)
{
{
row = new Row();
row.Append(
ConstructCell(request.Id.ToString(), CellValues.Number),
ConstructCell(request.Status, CellValues.String),
ConstructCell(request.CreateTime.ToString("dd.MM.yyyy HH:mm"), CellValues.String),
ConstructCell(request.CreateUser.ShortName, CellValues.String),
ConstructCell(request.StreetName, CellValues.String),
ConstructCell(request.Building, CellValues.String),
ConstructCell(request.Corpus, CellValues.String),
ConstructCell(request.Flat, CellValues.String),
ConstructCell(request.ContactPhones, CellValues.String),
ConstructCell(request.ParentService, CellValues.String),
ConstructCell(request.Service, CellValues.String),
ConstructCell(request.Description, CellValues.String),
ConstructCell(request.ExecuteTime?.Date.ToString("dd.MM.yyyy") ?? "", CellValues.String),
ConstructCell(request.ExecutePeriod, CellValues.String),
ConstructCell(request.Master?.ShortName, CellValues.String),
ConstructCell(request.Executer?.ShortName, CellValues.String),
ConstructCell(request.FromTime?.ToString("HH:mm:ss") ?? "", CellValues.String),
ConstructCell(request.ToTime?.ToString("HH:mm:ss") ?? "", CellValues.String),
ConstructCell(request.SpendTime, CellValues.String),
ConstructCell(request.GarantyTest, CellValues.String),
ConstructCell(request.Rating, CellValues.String),
ConstructCell(request.RatingDescription, CellValues.String),
ConstructCell(request.IsRetry ? "Да" : "", CellValues.String),
ConstructCell(request.ImmediateText, CellValues.String));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
}
}
}
//заполняет лист Excel данными
protected void AddNewSheet(string sheetName, SpreadsheetDocument spreadsheetDocument, Stream stream, GroupReport report)
{
if (spreadsheetDocument == null)
{
throw new ArgumentNullException("spreadsheetDocument", string.Format("Параметр {0} имеет значение null.", "spreadsheetDocument"));
}
if (report == null)
{
throw new ArgumentNullException("report", string.Format("Параметр {0} имеет значение null.", "report"));
}
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
foreach (Sheet sheet in sheets)
{
if (sheetName == sheet.Name)
{
WorksheetPart workSheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
Worksheet workSheet = workSheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
Row headerRow = new Row()
{
RowIndex = 1
};
addHeaderRow(headerRow, report);
sheetData.AppendChild(headerRow);
//записать в Excel строки с данными
addDataRows(sheetData, report);
return;
}
}
// добавить новый worksheet.
WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorksheetPart>();
SheetData newSheetData = new SheetData();
Worksheet newWorkSheet = new Worksheet(newSheetData);
newWorksheetPart.Worksheet = newWorkSheet;
newWorksheetPart.Worksheet.Save();
string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);
// создать уникальный ID для worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet newSheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(newSheet);
spreadsheetDocument.WorkbookPart.Workbook.Save();
//записать в Excel заголовки полей
Row newHeaderRow = new Row();
addHeaderRow(newHeaderRow, report);
newSheetData.AppendChild(newHeaderRow);
//записать в Excel строки с данными
addDataRows(newSheetData, report);
}
/// <summary>
/// Generate an excel file with the information of certificates
/// </summary>
/// <param name="dataSource">The list of certificates</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCertificateReport(CertificateListModel model, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VocStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
List<CertificateDocument> dataSource = model.Certificates.Collection;
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the title
UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5);
//set min date and max date in header
Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5);
string minDate, maxDate;
//get dates
if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo))
{
minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy");
maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy");
}
else
{
minDate = model.IssuanceDateFrom;
maxDate = model.IssuanceDateTo;
}
//write both dates
UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7);
UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Certificate.Sequential, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.Certificate.IssuanceDate.HasValue ? item.Certificate.IssuanceDate.Value.ToString("dd/MM/yyyy") : "", rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.Certificate.CertificateStatusId.ToString(), rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Certificate.EntryPoint != null ? item.Certificate.EntryPoint.Name : "", rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = Resources.Common.CertificateList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
public List <List <string> > mtdConvertirExcel(byte[] luFile, string puName = null)
{
System.IO.MemoryStream fileXls = new MemoryStream(luFile);
List <List <string> > luResult = new List <List <string> >();
try
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileXls, false))
{
//Read the first Sheet from Excel file.
Sheet sheet = new Sheet();
if (puName == null)
{
sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>();
}
else
{
Sheets luHojas = doc.WorkbookPart.Workbook.Sheets;
foreach (Sheet hoja in luHojas)
{
if (hoja.Name.ToString().ToUpper() == puName.ToUpper())
{
sheet = hoja;
}
}
}
SharedStringTablePart sstpart = doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
//Get the Worksheet instance.
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
//Fetch all the rows present in the Worksheet.
IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>();
//Create a new DataTable.
luResult = new List <List <string> >();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
//Use the first row to add columns to DataTable.
List <string> luFila = new List <string>();
int columnIndex = 0;
int cont = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
for (int c = 0; c <= cont; c++)
{
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
cellColumnIndex--;
if (columnIndex < cellColumnIndex)
{
do
{
//luFila[columnIndex] = null;
luFila.Add(null);
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
string lsValor = sst.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText;
luFila.Add(lsValor);
//luFila[columnIndex] = lsValor;
}
else
if (cell.CellValue != null)
{
luFila.Add(cell.CellValue.Text);
}
else if (cell.CellValue == null)
{
luFila.Add(null);
}
columnIndex++;
}
}
if (luFila.Count > 0)
{
luResult.Add(luFila);
cont = luResult[0].Count;
}
//#region "MI codigo anterior"
//foreach (Cell cell in row.Descendants<Cell>())
//{
// if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
// {
// //string lsValor = sst.ChildElements(Convert.ToInt32(cell.CellValue.Text)).InnerText;
// string lsValor = sst.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText;
// luFila.Add(lsValor);
// }
// else
// if (cell.CellValue != null)
// {
// luFila.Add(cell.CellValue.Text);
// }
// else if (cell.CellValue == null)
// {
// luFila.Add(null);
// }
//}
//if (luFila.Count > 0)
//{
// luResult.Add(luFila);
//}
//#endregion
}
}
}
catch (Exception ex)
{
return(null);
}
return(luResult);
}
public virtual void GetSheet(int nindex)
{
try
{
this.appSheets = this.appWork.Worksheets;
}
catch (Exception exception)
{
new QxClassErrLog { ErrClass = base.GetType().ToString(), MethodName = "GetSheet(int nindex)", ErrContent = exception.Message }.WriteLog();
throw new ExcelException("获得工作区集合失败");
}
if (nindex > this.appSheets.Count)
{
throw new ExcelException("不存在的工作区");
}
try
{
this.appCurSheet = (Worksheet) this.appSheets[nindex];
}
catch (Exception exception2)
{
new QxClassErrLog { ErrClass = base.GetType().ToString(), MethodName = "Open()", ErrContent = exception2.Message }.WriteLog();
throw new ExcelException("获取工作区失败");
}
}
//var numberCell = new Cell {
// DataType = CellValues.Number,
// CellReference = header + index,
// CellValue = new CellValue(text),
// StyleIndex = 3
//};
public void Execute()
{
List <Person> personen = GetData();
String pathToTemp = @"..\..\testFolder";
if (!Directory.Exists(pathToTemp))
{
Directory.CreateDirectory(pathToTemp);
}
String tempFile = pathToTemp + "\\" + DateTime.Now.Ticks / TimeSpan.TicksPerMillisecond + ".xlsx";
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(tempFile, SpreadsheetDocumentType.Workbook)) {
// create the workbook
var dateFormat = new NumberingFormat()
{
NumberFormatId = (UInt32Value)0,
FormatCode = StringValue.FromString("dd.MM.yyyy")
};
WorkbookPart part = spreadSheet.AddWorkbookPart();
part.Workbook = new Workbook();
part.AddNewPart <WorksheetPart>();
part.WorksheetParts.First().Worksheet = new Worksheet();
WorkbookStylesPart sp = spreadSheet.WorkbookPart.AddNewPart <WorkbookStylesPart>();
sp.Stylesheet = new Stylesheet();
sp.Stylesheet.NumberingFormats = new NumberingFormats();
sp.Stylesheet.NumberingFormats.Append(dateFormat);
CellFormat cellFormat = new CellFormat();
cellFormat.NumberFormatId = dateFormat.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
sp.Stylesheet.CellFormats = new CellFormats();
sp.Stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
sp.Stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)sp.Stylesheet.CellFormats.ChildElements.Count);
sp.Stylesheet.Save();
Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// create sheet data
foreach (Person p in personen)
{
WorksheetPart tabPart = part.AddNewPart <WorksheetPart>();
Worksheet workSheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheet sheet1 = new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(tabPart),
SheetId = 1,
Name = p.Name
};
sheets.Append(sheet1);
Row r = new Row();
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.Name), DataType = CellValues.String
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.BirthDay.ToOADate().ToString()), StyleIndex = 0
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.HeightInCm.ToString(CultureInfo.InvariantCulture)), DataType = CellValues.Number
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.Weight.ToString(CultureInfo.InvariantCulture)), DataType = CellValues.Number
});
sheetData1.AppendChild(r);
workSheet1.AppendChild(sheetData1);
tabPart.Worksheet = workSheet1;
}
part.Workbook.Save();
}
Process.Start(@tempFile);
Environment.Exit(0);
}
public virtual string[] GetSheets()
{
string[] strArray = null;
try
{
this.appSheets = this.appWork.Worksheets;
strArray = new string[this.appSheets.Count];
for (int i = 1; i <= this.appSheets.Count; i++)
{
strArray[i - 1] = ((Worksheet) this.appSheets[i]).Name;
}
}
catch (Exception exception)
{
new QxClassErrLog { ErrClass = base.GetType().ToString(), MethodName = "GetSheets()", ErrContent = exception.Message }.WriteLog();
throw new ExcelException(exception.Message);
}
return strArray;
}
public bool WriteReports(IEnumerable <CaseReportForListing> reports, string[] fields, Stream stream)
{
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbook = document.AddWorkbookPart();
workbook.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
// Create a sheet in the document
var data = new SheetData();
WorksheetPart worksheet = workbook.AddNewPart <WorksheetPart>();
worksheet.Worksheet = new Worksheet(data);
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheet), SheetId = 1, Name = "Case Reports"
};
sheets.Append(sheet);
uint rowIndex = 0;
// Add some headers
{
var row = new Row {
RowIndex = ++rowIndex
};
data.Append(row);
var headers = new SortedDictionary <string, string>
{
{ "A", "Timestamp" },
{ "B", "Status" },
{ "C", "Data Collector" },
{ "D", "Region" },
{ "E", "District" },
{ "F", "Village" },
{ "G", "Health Risk" },
{ "H", "Males < 5" },
{ "I", "Males ≥ 5" },
{ "J", "Females < 5" },
{ "K", "Females ≥ 5" },
{ "L", "Lat. / Long." },
{ "M", "Message" },
{ "N", "Errors" }
};
foreach (var header in headers)
{
var cell = new Cell {
CellReference = header.Key + rowIndex
};
row.Append(cell);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
cell.CellValue = new CellValue(header.Value);
}
}
// Insert data
foreach (var report in reports.OrderByDescending(e => e.Timestamp))
{
var row = new Row {
RowIndex = ++rowIndex
};
data.Append(row);
var timestamp = new Cell {
CellReference = "A" + rowIndex
};
row.Append(timestamp);
timestamp.DataType = new EnumValue <CellValues>(CellValues.String);
timestamp.CellValue = new CellValue(report.Timestamp.ToString("yyyy MMMM dd, hh:mm:ss tt"));
var status = new Cell {
CellReference = "B" + rowIndex
};
row.Append(status);
status.DataType = new EnumValue <CellValues>(CellValues.String);
var origin = new Cell {
CellReference = "C" + rowIndex
};
row.Append(origin);
origin.DataType = new EnumValue <CellValues>(CellValues.String);
origin.CellValue = new CellValue(report.DataCollectorId != null ? report.DataCollectorDisplayName : "Origin: " + report.Origin);
var region = new Cell {
CellReference = "D" + rowIndex
};
row.Append(region);
region.DataType = new EnumValue <CellValues>(CellValues.String);
region.CellValue = new CellValue(report.DataCollectorId != null ? report.DataCollectorRegion : "");
var district = new Cell {
CellReference = "E" + rowIndex
};
row.Append(district);
district.DataType = new EnumValue <CellValues>(CellValues.String);
district.CellValue = new CellValue(report.DataCollectorId != null ? report.DataCollectorDistrict : "");
var village = new Cell {
CellReference = "F" + rowIndex
};
row.Append(village);
village.DataType = new EnumValue <CellValues>(CellValues.String);
village.CellValue = new CellValue(report.DataCollectorId != null ? report.DataCollectorVillage : "");
var healthrisk = new Cell {
CellReference = "G" + rowIndex
};
row.Append(healthrisk);
healthrisk.DataType = new EnumValue <CellValues>(CellValues.String);
var malesUnder5 = new Cell {
CellReference = "H" + rowIndex
};
row.Append(malesUnder5);
malesUnder5.DataType = new EnumValue <CellValues>(CellValues.Number);
var malesOver5 = new Cell {
CellReference = "I" + rowIndex
};
row.Append(malesOver5);
malesOver5.DataType = new EnumValue <CellValues>(CellValues.Number);
var femalesUnder5 = new Cell {
CellReference = "J" + rowIndex
};
row.Append(femalesUnder5);
femalesUnder5.DataType = new EnumValue <CellValues>(CellValues.Number);
var femalesOver5 = new Cell {
CellReference = "K" + rowIndex
};
row.Append(femalesOver5);
femalesOver5.DataType = new EnumValue <CellValues>(CellValues.Number);
var location = new Cell {
CellReference = "L" + rowIndex
};
row.Append(location);
location.DataType = new EnumValue <CellValues>(CellValues.String);
location.CellValue = new CellValue(report.Location != null ? report.Location.Latitude + "/" + report.Location.Longitude : "");
var message = new Cell {
CellReference = "M" + rowIndex
};
row.Append(message);
message.DataType = new EnumValue <CellValues>(CellValues.String);
message.CellValue = new CellValue(report.Message);
var error = new Cell {
CellReference = "N" + rowIndex
};
row.Append(error);
error.DataType = new EnumValue <CellValues>(CellValues.String);
if (report.HealthRiskId != null)
{
status.CellValue = new CellValue("Success");
healthrisk.CellValue = new CellValue(report.HealthRisk);
malesUnder5.CellValue = new CellValue(report.NumberOfMalesUnder5.ToString());
malesOver5.CellValue = new CellValue(report.NumberOfMalesAged5AndOlder.ToString());
femalesUnder5.CellValue = new CellValue(report.NumberOfFemalesUnder5.ToString());
femalesOver5.CellValue = new CellValue(report.NumberOfFemalesAged5AndOlder.ToString());
error.CellValue = new CellValue("");
}
else
{
status.CellValue = new CellValue("Error");
healthrisk.CellValue = new CellValue("");
malesUnder5.CellValue = new CellValue("");
malesOver5.CellValue = new CellValue("");
femalesUnder5.CellValue = new CellValue("");
femalesOver5.CellValue = new CellValue("");
error.CellValue = new CellValue(report.ParsingErrorMessage != null ? string.Join(".", report.ParsingErrorMessage) : "");
}
}
// Save the document in memory, and serve to client
workbook.Workbook.Save();
document.Close();
return(true);
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
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);
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache() { CacheId = (UInt32Value)2U, Id = "rId4" };
pivotCaches1.Append(pivotCache1);
workbook1.Append(sheets1);
workbook1.Append(pivotCaches1);
workbookPart1.Workbook = workbook1;
}
static public void CopySheet(string filename, string sheetName, string clonedSheetName)
{
//Open workbook
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
//Get the source sheet to be copied
WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sheetName);
//Take advantage of AddPart for deep cloning
SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), mySpreadsheet.DocumentType);
WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
//Add cloned sheet and all associated parts to workbook
WorksheetPart clonedSheet = workbookPart.AddPart <WorksheetPart>(tempWorksheetPart);
//Table definition parts are somewhat special and need unique ids...so let's make an id based on count
int numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();
tableId = numTableDefParts;
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0)
{
FixupTableParts(clonedSheet, numTableDefParts);
}
//There should only be one sheet that has focus
CleanView(clonedSheet);
//Add new sheet to main workbook part
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
Sheet copiedSheet = new Sheet();
copiedSheet.Name = clonedSheetName;
copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
sheets.Append(copiedSheet);
//Save Changes
workbookPart.Workbook.Save();
}
}
/// <summary>
/// Opens Excel Template File
/// </summary>
private void OpenExcel()
{
string appID = DateTime.Now.Ticks.ToString();
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Application.Caption = appID;
excelApp.Application.Visible = true;
using (var process = Process.GetProcesses().Where(p => p.MainWindowTitle.Equals(appID)).First())
{
XLS_PROCESS = (process == null) ? 0 : process.Id;
}
//excelPID = getProcessID(appID);
excelApp.Application.Visible = false;
//excelApp.Visible = true;
excelApp.DisplayAlerts = false;
var path = System.IO.Directory.GetCurrentDirectory();
//Uncomment below for testing
// path = System.IO.Directory.GetParent(path).ToString();
// path = System.IO.Directory.GetParent(path).ToString();
//
path = path + @"\" + templateFile;
// if (!File.Exists(path))
if (!File.Exists(templateFile))
{
// workBook = excelApp.Workbooks.Add();
}
else
{
var workbooks = excelApp.Workbooks;
workBook = workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
workSheets = workBook.Worksheets;
// currentSheet = workBook.ActiveSheet;
// currentSheet.Name = "CurrentSheet";
Marshal.ReleaseComObject(workbooks);
}
}
public static void Main(string[] args)
{
Application
ExcelApp = null;
Workbook
Workbook = null;
Sheets
Sheets = null,
Charts = null;
Worksheet
Sheet = null;
Chart
Chart = null;
Series
Series = null;
Axes
Axes = null;
Axis
Axis = null;
Range
Range = null,
Range2 = null;
object
tmpObject;
try
{
try
{
string
CurrentDirectory = System.IO.Directory.GetCurrentDirectory();
CurrentDirectory = CurrentDirectory.Substring(0, CurrentDirectory.LastIndexOf("bin", CurrentDirectory.Length - 1));
string
//InputFileName = CurrentDirectory + "test.xls",
InputFileName = "d:\\result.xlsx",
OutputDbf = CurrentDirectory + "xls2dbf.dbf";
try
{
ExcelApp = (Application)Marshal.GetActiveObject("Excel.Application");
}
catch (COMException eException)
{
if (eException.ErrorCode == -2147221021)
{
ExcelApp = new Application();
}
}
ExcelApp.Visible = true;
ExcelApp.DisplayAlerts = false;
ExcelApp.UserControl = false;
#if TEST_DATE
Workbook = ExcelApp.Workbooks.Add(Type.Missing);
Sheet = (Worksheet)Workbook.ActiveSheet;
Sheet.Columns.get_Range("A1", "A1").ColumnWidth = 3;
Sheet.get_Range("A1", Type.Missing).Value = "01.01.2001";
tmpObject = Sheet.get_Range("A1", Type.Missing).Value;
Workbook.Close(Type.Missing, Type.Missing, Type.Missing);
#endif
Workbook = ExcelApp.Workbooks.Open(InputFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
#if TEST_SAVE_AS
if (File.Exists(OutputDbf))
{
File.Delete(OutputDbf);
}
Workbook.SaveAs(OutputDbf, XlFileFormat.xlDBF4, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
#endif
Sheets = Workbook.Worksheets;
Sheet = (Worksheet)Workbook.ActiveSheet;
double
points = ExcelApp.CentimetersToPoints(1.93d);
points = ExcelApp.CentimetersToPoints(2.70d);
points = ExcelApp.CentimetersToPoints(3.55d);
int
MaxRow = Sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row,
MaxCol = Sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Column;
Range = Sheet.get_Range("A1", Type.Missing);
Range = Range.get_End(XlDirection.xlToRight);
Range = Range.get_End(XlDirection.xlDown);
string
CurrAddress = Range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
Range = Sheet.get_Range("A1", CurrAddress);
CurrAddress = ColNoToName(MaxCol) + Convert.ToString(MaxRow);
Range = Sheet.get_Range("A1", CurrAddress);
object[,]
values = (object[, ])Range.Value2;
int
MaxI = values.GetLength(0), // Row
MaxJ = values.GetLength(1); // Col
string
tmpString = string.Empty;
for (int i = 1; i <= MaxI; ++i) // Row
{
for (int j = 1; j <= MaxJ; ++j) // Col
{
tmpString += "[" + i + "," + j + "]=" + (values[i, j] != null ? values[i, j] : "null");
if (j <= MaxJ - 1)
{
tmpString += "\t";
}
}
tmpString += Environment.NewLine;
}
Console.WriteLine(tmpString);
Range = Sheet.get_Range("A10", "E18");
//Range.Formula=values;
Range.Value = values;
Range = Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[10, 10]);
tmpObject = ((Range)Range.get_Item(1, 1)).Value;
Sheet.get_Range("A23", Type.Missing).Value = "A23";
tmpString = Convert.ToString(Sheet.get_Range("A23", Type.Missing).Value);
Sheet.get_Range("A24", "C24").Merge(true);
for (int _i = 1; _i <= 10; ++_i)
{
Sheet.get_Range("F" + _i, Type.Missing).Value = _i;
}
Sheet.get_Range("F11", Type.Missing).Formula = "=яслл(F1:F10)";
Sheet.get_Range("F11", Type.Missing).Orientation = 30;
Sheet.get_Range("F11", Type.Missing).Font.Bold = true;
Sheet.get_Range("F11", Type.Missing).Font.Size = 25;
Range = Sheet.get_Range("F2", Type.Missing);
tmpObject = -4121; // 0xFFFFEFE7; /* xlShiftDown */
Range.Rows.Insert(tmpObject);
Range.Rows.Insert(Type.Missing);
Sheet.get_Range("F2", Type.Missing).Value = 999;
Sheet = (Worksheet)Sheets.get_Item(2);
Sheet.Activate();
string
CellDirect,
CellObject;
#region bool
CellDirect = "E1";
CellObject = "E2";
tmpString = "bool";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F1";
CellObject = "F2";
Sheet.get_Range(CellDirect, Type.Missing).Value = true;
bool
tmpBool = true;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpBool;
#endregion
#region byte
CellDirect = "E3";
CellObject = "E4";
tmpString = "byte";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F3";
CellObject = "F4";
Sheet.get_Range(CellDirect, Type.Missing).Value = 255;
byte
tmpByte = 255;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpByte;
#endregion
#region sbyte
CellDirect = "E5";
CellObject = "E6";
tmpString = "sbyte";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F5";
CellObject = "F6";
Sheet.get_Range(CellDirect, Type.Missing).Value = 127;
sbyte
tmpSByte = 127;
// !!!
Sheet.get_Range(CellObject, Type.Missing).Value = (int)tmpSByte;
#endregion
#region short
CellDirect = "E7";
CellObject = "E8";
tmpString = "short";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F7";
CellObject = "F8";
Sheet.get_Range(CellDirect, Type.Missing).Value = 32767;
short
tmpShort = 32767;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpShort;
#endregion
#region ushort
CellDirect = "E9";
CellObject = "E10";
tmpString = "ushort";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F9";
CellObject = "F10";
Sheet.get_Range(CellDirect, Type.Missing).Value = 65535;
ushort
tmpUShort = 65535;
// !!!
Sheet.get_Range(CellObject, Type.Missing).Value = (int)tmpUShort;
#endregion
#region int
CellDirect = "E11";
CellObject = "E12";
tmpString = "int";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F11";
CellObject = "F12";
Sheet.get_Range(CellDirect, Type.Missing).Value = 2147483647;
int
tmpInt = 2147483647;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpInt;
#endregion
#region uint
CellDirect = "E13";
CellObject = "E14";
tmpString = "uint";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F13";
CellObject = "F14";
// !!!
Sheet.get_Range(CellDirect, Type.Missing).Value = (double)4294967295U;
uint
tmpUInt = 4294967295U;
// !!!
Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpUInt;
#endregion
#region long
CellDirect = "E15";
CellObject = "E16";
tmpString = "long";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F15";
CellObject = "F16";
Sheet.get_Range(CellDirect, Type.Missing).Value = (double)999999999999999L;
//Sheet.get_Range(CellDirect,Type.Missing).Value=9223372036854775807L;
long
tmpLong = 999999999999999L; //9223372036854775807L;
Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpLong;
#endregion
#region ulong
CellDirect = "E17";
CellObject = "E18";
tmpString = "ulong";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F17";
CellObject = "F18";
//Sheet.get_Range(CellDirect,Type.Missing).Value=18446744073709551615UL;
ulong
tmpULong = 18446744073709551615UL;
//Sheet.get_Range(CellObject,Type.Missing).Value=tmpULong;
#endregion
#region DateTime
CellDirect = "E19";
CellObject = "E20";
tmpString = "DateTime";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F19";
CellObject = "F20";
Sheet.get_Range(CellDirect, Type.Missing).Value = DateTime.Now;
DateTime
tmpDateTime = DateTime.Now;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpDateTime;
#endregion
#region TimeSpan
CellDirect = "E21";
CellObject = "E22";
tmpString = "TimeSpan";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F21";
CellObject = "F22";
tmpDateTime = new DateTime(0L);
// !!!
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpDateTime + (new TimeSpan(1, 2, 3));
TimeSpan
tmpTimeSpan = new TimeSpan(1, 2, 3);
// !!!
tmpDateTime = new DateTime(0L);
tmpDateTime += tmpTimeSpan;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpDateTime;
#endregion
#region float
CellDirect = "E23";
CellObject = "E24";
tmpString = "float";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F23";
CellObject = "F24";
Sheet.get_Range(CellDirect, Type.Missing).Value = 9999.9999F;
float
tmpFloat = 9999.9999F;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpFloat;
#endregion
#region double
CellDirect = "E25";
CellObject = "E26";
tmpString = "double";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F25";
CellObject = "F26";
Sheet.get_Range(CellDirect, Type.Missing).Value = 1.7E+3;
double
tmpDouble = 1.7E+3;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpDouble;
#endregion
#region decimal
CellDirect = "E27";
CellObject = "E28";
tmpString = "decimal";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F27";
CellObject = "F28";
// !!!
Sheet.get_Range(CellDirect, Type.Missing).Value = (double)999999999.99m;
decimal
tmpDecimal = 999999999.99m;
// !!!
Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpDecimal;
#endregion
Sheet = (Worksheet)Sheets.get_Item(3);
Range = Sheet.get_Range("A1", "A5");
Range.Value = "Some Value";
Range2 = Sheet.get_Range("A11", "A15");
Range.Copy(Range2);
Range = Sheet.get_Range("B1", "B5");
Range.Value = "Some Value";
Range.Copy(Type.Missing);
Range2 = Sheet.get_Range("B11", "B15");
Sheet.Paste(Range2, Type.Missing);
Range2 = Sheet.get_Range("B20", Type.Missing);
Sheet.Activate();
Range2.Select();
Sheet.Paste(Type.Missing, Type.Missing);
#if TEST_CHART
Sheet = (Excel.Worksheet)Sheets.get_Item(3);
Sheet.Activate();
for (int Col = 1; Col <= 20; ++Col)
{
Sheet.get_Range("A" + Col, Type.Missing).Value = Col;
Sheet.get_Range("B" + Col, Type.Missing).Formula = "=sin(A" + Col + ")";
Sheet.get_Range("C" + Col, Type.Missing).Formula = "=cos(A" + Col + ")";
Sheet.get_Range("D" + Col, Type.Missing).Formula = "=B" + Col + "+C" + Col;
}
Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Charts = ExcelApp.Charts;
Chart = (Excel.Chart)Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Chart.Type=65; // xlLineMarkers
Chart.Type = 4; // xlLine
Chart.SetSourceData(Sheet.get_Range("B1", "C20"), 2 /*xlColumns*/);
Chart = Chart.Location(XlChartLocation.xlLocationAsObject, "кХЯР4");
Series = ((Excel.SeriesCollection)Chart.SeriesCollection(Type.Missing)).NewSeries();
MaxI = ((Excel.SeriesCollection)Chart.SeriesCollection(Type.Missing)).Count;
Range = Sheet.get_Range("A1", "A20");
for (int i = MaxI; i > 0; --i)
{
((Excel.Series)Chart.SeriesCollection(i)).XValues = Range;
switch (i)
{
case 1:
{
((Excel.Series)Chart.SeriesCollection(i)).Name = "sin";
break;
}
case 2:
{
((Excel.Series)Chart.SeriesCollection(i)).Name = "cos";
break;
}
}
}
Range = Sheet.get_Range("D1", "D20");
Series.Values = Range;
Series.Name = "sin+cos";
Chart.HasTitle = true;
Chart.ChartTitle.Caption = "Name of Chart";
Axis = (Excel.Axis)Chart.Axes(1 /*xlCategory*/, XlAxisGroup.xlPrimary);
Axis.HasTitle = true;
Axis.CategoryNames = "Name of Category";
Axis = (Excel.Axis)Chart.Axes(2 /*xlValue*/, XlAxisGroup.xlPrimary);
Axis.HasTitle = true;
Chart.HasLegend = true;
Chart.Legend.Position = XlLegendPosition.xlLegendPositionBottom;
#endif
ExcelApp.Quit();
}
catch (COMException eException)
{
string
tmp = eException.GetType().FullName + Environment.NewLine + "ErrorCode: " + eException.ErrorCode + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;
Console.WriteLine(tmp);
}
catch (ArgumentException eException)
{
string
tmp = eException.GetType().FullName + Environment.NewLine + "ParamName: " + eException.ParamName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;
Console.WriteLine(tmp);
}
catch (Exception eException)
{
string
tmp = eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;
Console.WriteLine(tmp);
}
}
finally
{
if (Axes != null)
{
Marshal.ReleaseComObject(Axes);
Axes = null;
}
if (Axis != null)
{
Marshal.ReleaseComObject(Axis);
Axis = null;
}
if (Series != null)
{
Marshal.ReleaseComObject(Series);
Series = null;
}
if (Chart != null)
{
Marshal.ReleaseComObject(Chart);
Chart = null;
}
if (Charts != null)
{
Marshal.ReleaseComObject(Charts);
Charts = null;
}
if (Range2 != null)
{
Marshal.ReleaseComObject(Range2);
Range2 = null;
}
if (Range != null)
{
Marshal.ReleaseComObject(Range);
Range = null;
}
if (Sheets != null)
{
Marshal.ReleaseComObject(Sheets);
Sheets = null;
}
if (Sheet != null)
{
Marshal.ReleaseComObject(Sheet);
Sheet = null;
}
if (Workbook != null)
{
Marshal.ReleaseComObject(Workbook);
Workbook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
//GC.Collect();
GC.GetTotalMemory(true);
}
}
public static void runScenarioSummary()
{
runCalcAction(delegate {
AxCalcEngineAPI.AxCalcEngineAPI.Initialize();
shts = oWB.Worksheets;
oSheet = (Worksheet) shts["Projection Input Options"];
string ancPln = (string) oSheet.Range("anchorPlan").Value;
string option = (string) oSheet.Range("subType").Value;
string mgOpt = (string) oSheet.Range("migType").Value;
string str4 = (string) oSheet.Range("projNme").Value;
string inMode = (string) oSheet.Range("modal").Value;
string outMode = (string) oSheet.Range("modalOut").Value;
string str7 = (string) oSheet.Range("clientNme").Value;
string str8 = oSheet.Range("clientID").Value.ToString();
string str9 = "";
double modalAdj = Utility.modalConvert(inMode, outMode);
if ((string.IsNullOrEmpty(str7) || string.IsNullOrEmpty(str8)) || (str8.Length != 8))
{
throw new Exception("Enter valid client name and ID");
}
if (string.IsNullOrEmpty(str4))
{
throw new Exception("You must enter a projection name");
}
string displayName = "";
try
{
displayName = AxCalcEngineAPI.AxCalcEngineAPI._user.DisplayName;
}
catch
{
throw new Exception("Establishing a Connection");
}
if (displayName.Equals("Offline User"))
{
throw new Exception("You must be connected to the Aon Network to run Ax Model");
}
RatingGroups groups = new RatingGroups("Rating Groups", true);
PriorSurcharge surcharge = new PriorSurcharge("Rating Groups", modalAdj);
NewSurcharge surcharge2 = new NewSurcharge("Rating Groups", modalAdj);
PriorPremiums premiums = new PriorPremiums("Prior Premiums");
NewPremiums premiums2 = new NewPremiums("New Premiums");
Census census = new Census("Census");
System.Data.DataTable first = groups.RFactors;
System.Data.DataTable prm = Utility.convertListToDataTable(premiums2.NewPrmLst);
System.Data.DataTable table3 = Utility.convertListToDataTable(premiums.OldPrmLst);
prm = Utility.mdlPrm(prm, modalAdj);
table3 = Utility.mdlPrm(table3, modalAdj);
System.Data.DataTable cns = Utility.convertListToDataTable(census.CnLst);
List<System.Data.DataTable> prSurChrg = surcharge.PSCharge;
List<System.Data.DataTable> nwSurChrg = surcharge2.NSCharge;
System.Data.DataTable second = Utility.findPremiumCarrier(prm, str9);
System.Data.DataTable dt = Utility.findPremiumCarrier(prm, ancPln);
System.Data.DataTable table7 = Utility.Join(first, second);
System.Data.DataTable anc = Utility.getAnchorPlans(dt, ancPln, groups.bsCvTr);
System.Data.DataTable nwPrm = Utility.calcBaseSubsidy(table7, anc, option, modalAdj);
System.Data.DataTable fnlCns = Utility.SurChrgCombine(Utility.mrgCnsPrm(cns, table3, nwPrm, mgOpt), prSurChrg, nwSurChrg);
groups = null;
surcharge = null;
surcharge2 = null;
premiums = null;
premiums2 = null;
census = null;
prjTb(fnlCns, nwPrm, str4);
AxCalcEngineAPI.AxCalcEngineAPI.sendCalcLog(str7, str8, "http://axmodellogger.azurewebsites.net/Default.aspx", "AxModel_0.9");
});
}
public static bool ExportForDataGridview(System.Data.DataTable dt, string fileName, bool isShowExcle)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return(false);
}
app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return(false);
}
string sLen = "";
//取得最后一列列名
char H = (char)(64 + dt.Columns.Count / 26);
char L = (char)(64 + dt.Columns.Count % 26);
if (dt.Columns.Count < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
//标题
string sTmp = sLen + "1";
Range ranCaption = worksheet.get_Range(sTmp, "A1");
string[] asCaption = new string[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
asCaption[i] = dt.Columns[i].ColumnName;
}
ranCaption.Value2 = asCaption;
//数据
object[] obj = new object[dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count - 1; r++)
{
for (int l = 0; l < dt.Columns.Count; l++)
{
if (dt.Rows[r][l].GetType() == typeof(DateTime))
{
obj[l] = dt.Rows[r][l].ToString();
}
else
{
obj[l] = dt.Rows[r][l].ToString();
}
}
string cell1 = sLen + ((int)(r + 2)).ToString();
string cell2 = "A" + ((int)(r + 2)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.Value2 = obj;
}
//保存
workbook.SaveCopyAs(fileName);
workbook.Saved = true;
}
finally
{
//关闭
app.UserControl = false;
app.Quit();
}
return(true);
}
internal WorksheetList(Sheets sheets)
{
this.sheets = sheets;
}
/// <summary>
/// Initialize File
/// </summary>
private void CreateExcel()
{
app = new Excel.Application();
workbook = app.Workbooks.Open(ExcelFilePath, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheets = workbook.Sheets;
//get first sheet of file excel
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worksheets.get_Item(1);
}
// 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;
}
/// <summary>
/// Process the submissions absolute path
/// </summary>
/// <returns>Was the submission able to be loaded?</returns>
public override bool Process()
{
_loaded = false;
try
{
_document = SpreadsheetDocument.Open(_target.AbsolutePath, false);
// TODO : Content might be something where we need to write somethign per file type
// TODO : We should look at sheet names?
WorkbookPart workbookPart = _document.WorkbookPart;
Sheets workbookSheets = workbookPart.Workbook.Sheets;
foreach (Sheet s in workbookSheets)
{
OpenXmlPart part = workbookPart.GetPartById(s.Id);
if (!(part is WorksheetPart))
{
continue;
}
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(s.Id);
var sharedStringPart = workbookPart.SharedStringTablePart;
var values = sharedStringPart.SharedStringTable.Elements <SharedStringItem>().ToArray();
var cells = worksheetPart.Worksheet.Descendants <Cell>();
foreach (var cell in cells)
{
//// The cells contains a string input that is not a formula
//if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
//{
// var index = int.Parse(cell.CellValue.Text);
// var value = values[index].InnerText;
// Target.Content += value;
//}
//else if (cell.CellValue != null )
//{
// Target.Content += cell.CellValue.Text;
//}
if (cell.CellFormula != null)// && cell.CellFormula.Text.StartsWith("=",StringComparison.Ordinal))
{
_target.Content += cell.CellFormula.Text;
}
}
}
// - Look for cell selection to be identical
// - Reference formulas that werent there (pages removed)
_target.ContentLength = _target.Content.Length;
_target.ContentHash = Compare.Hash(_target.Content);
// Collect Meta Data
_target.MetaCreator = _document.PackageProperties.Creator;
_target.MetaLastModifiedBy = _document.PackageProperties.LastModifiedBy;
if (_document.PackageProperties.LastPrinted != null)
{
_target.MetaDateLastPrinted = (DateTime)_document.PackageProperties.LastPrinted;
}
if (_document.PackageProperties.Created != null)
{
_target.MetaDateCreated = (DateTime)_document.PackageProperties.Created;
}
if (_document.PackageProperties.Modified != null)
{
_target.MetaDateModified = (DateTime)_document.PackageProperties.Modified;
}
_loaded = true;
}
catch (Exception e)
{
_target.Logging.Add("- " + Markdown.Bold("An exception occured when processing " + _target.AbsolutePath + ", " + e.Message));
}
return(_loaded);
}
protected override bool OpenInternal(string fileName, bool write)
{
if (_obj != null) return false;
_app = new Application();
_app.Visible = false;
_app.DisplayAlerts = false;
_app.ScreenUpdating = false;
_workbooks = _app.Workbooks;
if (!_write && File.Exists(fileName))
{
_obj = _workbooks.Open(fileName);
}
else
{
_obj = _workbooks.Add();
}
_worksheets = _obj.Worksheets;
return true;
}
public MemoryStream CreateExcelDoc(List <RemittanceExcelModel> Remittances, MemoryStream mem)
{
SpreadsheetDocument document = SpreadsheetDocument.
Create(mem, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns columns = new Columns(
new Column // Id column
{
Min = 1,
Max = 1,
Width = 4,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 2,
Max = 3,
Width = 15,
CustomWidth = true
},
new Column // Salary column
{
Min = 4,
Max = 5,
Width = 15,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Remittance"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Id", CellValues.String, 2),
ConstructCell("Дата перевода", CellValues.String, 2),
ConstructCell("Сумма", CellValues.String, 2),
ConstructCell("Счет 1", CellValues.String, 2),
ConstructCell("Счет 2", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var remittance in Remittances)
{
row = new Row();
row.Append(
ConstructCell(remittance.Id.ToString(), CellValues.Number, 1),
ConstructCell(remittance.ActionDate.ToString("yyyy/MM/dd"), CellValues.String, 1),
ConstructCell(remittance.Sum.ToString(), CellValues.String, 1),
ConstructCell(remittance.Score, CellValues.String, 1),
ConstructCell(remittance.Score2, CellValues.String, 1));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
return(mem);
}
public void CreateExcelDoc(string fileName)
{
List <Student> students = new List <Student>();
Initizalize(students);
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Students"
};
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Add drawing part to WorksheetPart
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
sheets.Append(sheet);
workbookPart.Workbook.Save();
// Add a new chart and set the chart language
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.AppendChild(new EditingLanguage()
{
Val = "en-US"
});
Chart chart = chartPart.ChartSpace.AppendChild(new Chart());
chart.AppendChild(new AutoTitleDeleted()
{
Val = true
}); // We don't want to show the chart title
// Create a new Clustered Column Chart
PlotArea plotArea = chart.AppendChild(new PlotArea());
Layout layout = plotArea.AppendChild(new Layout());
BarChart barChart = plotArea.AppendChild(new BarChart(
new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
},
new VaryColors()
{
Val = false
}
));
// Constructing header
Row row = new Row();
int rowIndex = 1;
row.AppendChild(ConstructCell(string.Empty, CellValues.String));
foreach (var month in Months.Short)
{
row.AppendChild(ConstructCell(month, CellValues.String));
}
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
rowIndex++;
// Create chart series
for (int i = 0; i < students.Count; i++)
{
BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(
new Index()
{
Val = (uint)i
},
new Order()
{
Val = (uint)i
},
new SeriesText(new NumericValue()
{
Text = students[i].Name
})
));
// Adding category axis to the chart
CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData());
// Category
// Constructing the chart category
string formulaCat = "Students!$B$1:$G$1";
StringReference stringReference = categoryAxisData.AppendChild(new StringReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
{
Text = formulaCat
}
});
StringCache stringCache = stringReference.AppendChild(new StringCache());
stringCache.Append(new PointCount()
{
Val = (uint)Months.Short.Length
});
for (int j = 0; j < Months.Short.Length; j++)
{
stringCache.AppendChild(new NumericPoint()
{
Index = (uint)j
}).Append(new NumericValue(Months.Short[j]));
}
}
var chartSeries = barChart.Elements <BarChartSeries>().GetEnumerator();
for (int i = 0; i < students.Count; i++)
{
row = new Row();
row.AppendChild(ConstructCell(students[i].Name, CellValues.String));
chartSeries.MoveNext();
string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex);
DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());
NumberReference numberReference = values.AppendChild(new NumberReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
{
Text = formulaVal
}
});
NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache());
numberingCache.Append(new PointCount()
{
Val = (uint)Months.Short.Length
});
for (uint j = 0; j < students[i].Values.Length; j++)
{
var value = students[i].Values[j];
row.AppendChild(ConstructCell(value.ToString(), CellValues.Number));
numberingCache.AppendChild(new NumericPoint()
{
Index = j
}).Append(new NumericValue(value.ToString()));
}
sheetData.AppendChild(row);
rowIndex++;
}
barChart.AppendChild(new DataLabels(
new ShowLegendKey()
{
Val = false
},
new ShowValue()
{
Val = false
},
new ShowCategoryName()
{
Val = false
},
new ShowSeriesName()
{
Val = false
},
new ShowPercent()
{
Val = false
},
new ShowBubbleSize()
{
Val = false
}
));
barChart.Append(new AxisId()
{
Val = 48650112u
});
barChart.Append(new AxisId()
{
Val = 48672768u
});
// Adding Category Axis
plotArea.AppendChild(
new CategoryAxis(
new AxisId()
{
Val = 48650112u
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = 48672768u
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new AutoLabeled()
{
Val = true
},
new LabelAlignment()
{
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
}
));
// Adding Value Axis
plotArea.AppendChild(
new ValueAxis(
new AxisId()
{
Val = 48672768u
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = 48650112u
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new CrossBetween()
{
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
}
));
chart.Append(
new PlotVisibleOnly()
{
Val = true
},
new DisplayBlanksAs()
{
Val = new EnumValue <DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap)
},
new ShowDataLabelsOverMaximum()
{
Val = false
}
);
chartPart.ChartSpace.Save();
// Positioning the chart on the spreadsheet
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(
new ColumnId("0"),
new ColumnOffset("0"),
new RowId((rowIndex + 2).ToString()),
new RowOffset("0")
));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(
new ColumnId("8"),
new ColumnOffset("0"),
new RowId((rowIndex + 12).ToString()),
new RowOffset("0")
));
// Append GraphicFrame to TwoCellAnchor
GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame());
graphicFrame.Macro = string.Empty;
graphicFrame.Append(new NonVisualGraphicFrameProperties(
new NonVisualDrawingProperties()
{
Id = 2u,
Name = "Sample Chart"
},
new NonVisualGraphicFrameDrawingProperties()
));
graphicFrame.Append(new Transform(
new DocumentFormat.OpenXml.Drawing.Offset()
{
X = 0L, Y = 0L
},
new DocumentFormat.OpenXml.Drawing.Extents()
{
Cx = 0L, Cy = 0L
}
));
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic(
new DocumentFormat.OpenXml.Drawing.GraphicData(
new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
}
)
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}
));
twoCellAnchor.Append(new ClientData());
drawingsPart.WorksheetDrawing.Save();
worksheetPart.Worksheet.Save();
}
}
public void ExportarCodigoBarras()
{
CodigoBarras[] cbs = cCB.BuscarTodos();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
//string workbookPath = @"C:\Users\Vitor\Documents\Visual Studio 2012\Projects\ControleMoldagem\RelatorioCodigoBarras.xlsx";
Workbook excelWorkbook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets excelSheets = excelWorkbook.Worksheets;
//string currentSheet = "FormCB";
Worksheet excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = "Codigo Barras";
Range excelCell;
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "Codigo de Barras";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Série";
excelCell = (Range)excelWorksheet.get_Range("C1");
excelCell.Value2 = "Situação";
for (int i = 0; i < cbs.Length; i++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(cbs[i].IdCodigoBarras);
excelCell = (Range)excelWorksheet.get_Range("B" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(cbs[i].IdSerie);
excelCell = (Range)excelWorksheet.get_Range("C" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(cbs[i].Situacao);
}
excelWorksheet = (Worksheet)excelSheets.Add();
excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = "Traço";
Traco[] traco = cTraco.BuscarTodos();
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "ID Traço";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Codigo Traço";
excelCell = (Range)excelWorksheet.get_Range("C1");
excelCell.Value2 = "Usina";
excelCell = (Range)excelWorksheet.get_Range("D1");
excelCell.Value2 = "FCK";
excelCell = (Range)excelWorksheet.get_Range("E1");
excelCell.Value2 = "Fator AC";
excelCell = (Range)excelWorksheet.get_Range("F1");
excelCell.Value2 = "Idade Controle";
excelCell = (Range)excelWorksheet.get_Range("G1");
excelCell.Value2 = "Consumo Cimento";
excelCell = (Range)excelWorksheet.get_Range("H1");
excelCell.Value2 = "Consistencia";
excelCell = (Range)excelWorksheet.get_Range("I1");
excelCell.Value2 = "Tolerancia";
for (int i = 0; i < traco.Length; i++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].IdTraco);
excelCell = (Range)excelWorksheet.get_Range("B" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].CodigoTraco);
excelCell = (Range)excelWorksheet.get_Range("C" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].Usina);
excelCell = (Range)excelWorksheet.get_Range("D" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].Fck);
excelCell = (Range)excelWorksheet.get_Range("E" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].FatorAC);
excelCell = (Range)excelWorksheet.get_Range("F" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].IdadeControle);
excelCell = (Range)excelWorksheet.get_Range("G" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].ConsumoCimento);
excelCell = (Range)excelWorksheet.get_Range("H" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].Consistencia);
excelCell = (Range)excelWorksheet.get_Range("I" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(traco[i].Tolerancia);
}
excelWorksheet = (Worksheet)excelSheets.Add();
excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = "Lote";
Lote[] lote = cLote.BuscarTodosLotes();
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "ID Lote";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Data Controle";
excelCell = (Range)excelWorksheet.get_Range("C1");
excelCell.Value2 = "FCK";
excelCell = (Range)excelWorksheet.get_Range("D1");
excelCell.Value2 = "FCK Estimado";
excelCell = (Range)excelWorksheet.get_Range("E1");
excelCell.Value2 = "Volume Lote";
for (int i = 0; i < lote.Length; i++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(lote[i].IdSerie);
excelCell = (Range)excelWorksheet.get_Range("B" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(lote[i].DataControle);
excelCell = (Range)excelWorksheet.get_Range("C" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(lote[i].Fck);
excelCell = (Range)excelWorksheet.get_Range("D" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(lote[i].FckEstimado);
excelCell = (Range)excelWorksheet.get_Range("E" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(lote[i].Volume);
}
excelWorksheet = (Worksheet)excelSheets.Add();
excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = "Obra";
Obra[] obra = cObra.BuscarTodos();
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "ID Obra";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Nome Obra";
for (int i = 0; i < obra.Length; i++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(obra[i].IdObra);
excelCell = (Range)excelWorksheet.get_Range("B" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(obra[i].NomeObra);
}
excelWorksheet = (Worksheet)excelSheets.Add();
excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = " Obra Eixo";
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "ID Eixo";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Nome Obra";
excelCell = (Range)excelWorksheet.get_Range("C1");
excelCell.Value2 = "Nome Eixo";
int linha = 0;
for (int i = 0; i < obra.Length; i++)
{
Eixo[] eixo = cEixo.BuscarTodos(obra[i].IdObra);
for (int j = 0; j < eixo.Length; j++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (linha + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(eixo[j].IdEixo);
excelCell = (Range)excelWorksheet.get_Range("B" + (linha + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(obra[i].NomeObra);
excelCell = (Range)excelWorksheet.get_Range("C" + (linha + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(eixo[j].NomeEixo);
linha++;
}
}
excelWorksheet = (Worksheet)excelSheets.Add();
excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = " Obra Eixo Peça";
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "ID Peça";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Nome Obra";
excelCell = (Range)excelWorksheet.get_Range("C1");
excelCell.Value2 = "Nome Eixo";
excelCell = (Range)excelWorksheet.get_Range("D1");
excelCell.Value2 = "Nome Peça";
linha = 0;
for (int i = 0; i < obra.Length; i++)
{
Eixo[] eixo = cEixo.BuscarTodos(obra[i].IdObra);
for (int j = 0; j < eixo.Length; j++)
{
Peca[] peca = cPeca.BuscarTodos(obra[i].IdObra, eixo[j].IdEixo);
for (int k = 0; k < peca.Length; k++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (linha + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(peca[k].IdPeca);
excelCell = (Range)excelWorksheet.get_Range("B" + (linha + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(obra[i].NomeObra);
excelCell = (Range)excelWorksheet.get_Range("C" + (linha + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(eixo[j].NomeEixo);
excelCell = (Range)excelWorksheet.get_Range("D" + (linha + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(peca[k].NomePeca);
linha++;
}
}
}
excelWorksheet = (Worksheet)excelSheets.Add();
excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = "Moldagem";
Moldagem[] molde = cMolde.BuscarTudo();
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "ID Serie";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Data Moldagem";
excelCell = (Range)excelWorksheet.get_Range("C1");
excelCell.Value2 = "Hora Moldagem";
excelCell = (Range)excelWorksheet.get_Range("D1");
excelCell.Value2 = "Lote";
excelCell = (Range)excelWorksheet.get_Range("E1");
excelCell.Value2 = "Traço";
excelCell = (Range)excelWorksheet.get_Range("F1");
excelCell.Value2 = "FCK";
excelCell = (Range)excelWorksheet.get_Range("G1");
excelCell.Value2 = "Obra";
excelCell = (Range)excelWorksheet.get_Range("H1");
excelCell.Value2 = "Eixo";
excelCell = (Range)excelWorksheet.get_Range("I1");
excelCell.Value2 = "Peca";
excelCell = (Range)excelWorksheet.get_Range("J1");
excelCell.Value2 = "Quantidade CP";
excelCell = (Range)excelWorksheet.get_Range("K1");
excelCell.Value2 = "Idade Controle";
excelCell = (Range)excelWorksheet.get_Range("L1");
excelCell.Value2 = "Idade A";
excelCell = (Range)excelWorksheet.get_Range("M1");
excelCell.Value2 = "Idade B";
excelCell = (Range)excelWorksheet.get_Range("N1");
excelCell.Value2 = "Idade C";
excelCell = (Range)excelWorksheet.get_Range("O1");
excelCell.Value2 = "Volume Betonada";
excelCell = (Range)excelWorksheet.get_Range("P1");
excelCell.Value2 = "Temperatura do Ar";
excelCell = (Range)excelWorksheet.get_Range("Q1");
excelCell.Value2 = "Temperatura do Concreto";
excelCell = (Range)excelWorksheet.get_Range("R1");
excelCell.Value2 = "Nota Fiscal";
for (int i = 0; i < molde.Length; i++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].IdSerie);
excelCell = (Range)excelWorksheet.get_Range("B" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = molde[i].DataMoldagem.ToString("dd/MM/yyyy");
excelCell = (Range)excelWorksheet.get_Range("C" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].HoraMoldagem);
excelCell = (Range)excelWorksheet.get_Range("D" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].Lote);
excelCell = (Range)excelWorksheet.get_Range("E" + (i + 2));
excelCell.NumberFormat = "@";
Traco [] mTraco = cTraco.BuscarTraco(Convert.ToString(molde[i].IdTraco), "cIDTraco");
excelCell.Value2 = Convert.ToString(mTraco[0].CodigoTraco);
excelCell = (Range)excelWorksheet.get_Range("F" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].Fck);
excelCell = (Range)excelWorksheet.get_Range("G" + (i + 2));
excelCell.NumberFormat = "@";
Obra[] mObra = cObra.BuscarObra(Convert.ToString(molde[i].IdObra), "cIDObra");
excelCell.Value2 = Convert.ToString(mObra[0].NomeObra);
excelCell = (Range)excelWorksheet.get_Range("H" + (i + 2));
excelCell.NumberFormat = "@";
Eixo[] mEixo = cEixo.BuscarEixo(Convert.ToString(molde[i].IdEixo), Convert.ToString(molde[i].IdObra), "cIDEixo");
excelCell.Value2 = Convert.ToString(mEixo[0].NomeEixo);
excelCell = (Range)excelWorksheet.get_Range("I" + (i + 2));
excelCell.NumberFormat = "@";
Peca[] mPeca = cPeca.BuscarPeca(Convert.ToString(molde[i].IdPeca), Convert.ToString(molde[i].IdEixo), Convert.ToString(molde[i].IdObra), "cIDPeca");
excelCell.Value2 = Convert.ToString(mPeca[0].NomePeca);
excelCell = (Range)excelWorksheet.get_Range("J" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].QuantidadeCP);
excelCell = (Range)excelWorksheet.get_Range("K" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].IdadeControle);
excelCell = (Range)excelWorksheet.get_Range("L" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].IdadeA);
excelCell = (Range)excelWorksheet.get_Range("M" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].IdadeB);
excelCell = (Range)excelWorksheet.get_Range("N" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].IdadeC);
excelCell = (Range)excelWorksheet.get_Range("O" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].VolumeBetonada);
excelCell = (Range)excelWorksheet.get_Range("P" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].TemperaturaAr);
excelCell = (Range)excelWorksheet.get_Range("Q" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].TemperaturaCimento);
excelCell = (Range)excelWorksheet.get_Range("R" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(molde[i].Nota);
}
excelWorksheet = (Worksheet)excelSheets.Add();
excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = "Ruptura";
Ruptura[] ruptura = cRuptura.BuscarTudo();
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "Codigo Barras";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Data Ruptura";
excelCell = (Range)excelWorksheet.get_Range("C1");
excelCell.Value2 = "Hora Ruptura";
excelCell = (Range)excelWorksheet.get_Range("D1");
excelCell.Value2 = "Serie";
excelCell = (Range)excelWorksheet.get_Range("E1");
excelCell.Value2 = "Diametro";
excelCell = (Range)excelWorksheet.get_Range("F1");
excelCell.Value2 = "Altura";
excelCell = (Range)excelWorksheet.get_Range("G1");
excelCell.Value2 = "Correção";
excelCell = (Range)excelWorksheet.get_Range("H1");
excelCell.Value2 = "Carga";
for (int i = 0; i < ruptura.Length; i++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(ruptura[i].IdCodigoBarras);
excelCell = (Range)excelWorksheet.get_Range("B" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(ruptura[i].DataRuptura);
excelCell = (Range)excelWorksheet.get_Range("C" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(ruptura[i].Hora);
excelCell = (Range)excelWorksheet.get_Range("D" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(ruptura[i].IdSerie);
excelCell = (Range)excelWorksheet.get_Range("E" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(ruptura[i].DiametroCP);
excelCell = (Range)excelWorksheet.get_Range("F" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(ruptura[i].AlturaCP);
excelCell = (Range)excelWorksheet.get_Range("G" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(ruptura[i].Correcao);
excelCell = (Range)excelWorksheet.get_Range("H" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(ruptura[i].Carga);
}
excelWorksheet = (Worksheet)excelSheets.Add();
excelWorksheet = (Worksheet)excelSheets[1];
excelWorksheet.Name = "Resistencias";
Resistencia[] resist = cResist.BuscarTudo();
excelCell = (Range)excelWorksheet.get_Range("A1");
excelCell.Value2 = "Serie";
excelCell = (Range)excelWorksheet.get_Range("B1");
excelCell.Value2 = "Idade A CP1";
excelCell = (Range)excelWorksheet.get_Range("C1");
excelCell.Value2 = "Idade A CP2";
excelCell = (Range)excelWorksheet.get_Range("D1");
excelCell.Value2 = "Idade B CP1";
excelCell = (Range)excelWorksheet.get_Range("E1");
excelCell.Value2 = "Idade B CP2";
excelCell = (Range)excelWorksheet.get_Range("F1");
excelCell.Value2 = "Idade C CP1";
excelCell = (Range)excelWorksheet.get_Range("G1");
excelCell.Value2 = "Idade C CP2";
for (int i = 0; i < resist.Length; i++)
{
excelCell = (Range)excelWorksheet.get_Range("A" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = Convert.ToString(resist[i].IdSerie);
excelCell = (Range)excelWorksheet.get_Range("B" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(resist[i].RA1);
excelCell = (Range)excelWorksheet.get_Range("C" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(resist[i].RA2);
excelCell = (Range)excelWorksheet.get_Range("D" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(resist[i].RB1);
excelCell = (Range)excelWorksheet.get_Range("E" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(resist[i].RB2);
excelCell = (Range)excelWorksheet.get_Range("F" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(resist[i].RC1);
excelCell = (Range)excelWorksheet.get_Range("G" + (i + 2));
excelCell.NumberFormat = "@";
excelCell.Value2 = excelCell.Value2 = Convert.ToString(resist[i].RC2);
}
excelApp.Visible = true;
}
