///<summary>Inserts one Sheet into the database. Provides option to use the existing priKey.</summary>
internal static long Insert(Sheet sheet,bool useExistingPK)
{
if(!useExistingPK && PrefC.RandomKeys) {
sheet.SheetNum=ReplicationServers.GetKey("sheet","SheetNum");
}
string command="INSERT INTO sheet (";
if(useExistingPK || PrefC.RandomKeys) {
command+="SheetNum,";
}
command+="SheetType,PatNum,DateTimeSheet,FontSize,FontName,Width,Height,IsLandscape,InternalNote,Description,ShowInTerminal,IsWebForm) VALUES(";
if(useExistingPK || PrefC.RandomKeys) {
command+=POut.Long(sheet.SheetNum)+",";
}
command+=
POut.Int ((int)sheet.SheetType)+","
+ POut.Long (sheet.PatNum)+","
+ POut.DateT (sheet.DateTimeSheet)+","
+ POut.Float (sheet.FontSize)+","
+"'"+POut.String(sheet.FontName)+"',"
+ POut.Int (sheet.Width)+","
+ POut.Int (sheet.Height)+","
+ POut.Bool (sheet.IsLandscape)+","
+"'"+POut.String(sheet.InternalNote)+"',"
+"'"+POut.String(sheet.Description)+"',"
+ POut.Byte (sheet.ShowInTerminal)+","
+ POut.Bool (sheet.IsWebForm)+")";
if(useExistingPK || PrefC.RandomKeys) {
Db.NonQ(command);
}
else {
sheet.SheetNum=Db.NonQ(command,true);
}
return sheet.SheetNum;
}
public void UpdateSupport(Sheet supported) {
// Update the support sets of cells referred from an array formula only once
if (!supportAdded) {
formula.AddToSupportSets(supported, formulaCol, formulaRow, 1, 1);
supportAdded = true;
}
}
public override void InsertRowCols(Dictionary<Expr, Adjusted<Expr>> adjusted,
Sheet modSheet,
bool thisSheet,
int R,
int N,
int r,
bool doRows) {}
///<summary>Inserts one Sheet into the database. Returns the new priKey.</summary>
internal static long Insert(Sheet sheet)
{
if(DataConnection.DBtype==DatabaseType.Oracle) {
sheet.SheetNum=DbHelper.GetNextOracleKey("sheet","SheetNum");
int loopcount=0;
while(loopcount<100){
try {
return Insert(sheet,true);
}
catch(Oracle.DataAccess.Client.OracleException ex){
if(ex.Number==1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated")){
sheet.SheetNum++;
loopcount++;
}
else{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else {
return Insert(sheet,false);
}
}
public abstract void InsertRowCols(Dictionary<Expr, Adjusted<Expr>> adjusted, Sheet modSheet, bool thisSheet, int R, int N, int r, bool doRows);
public static bool SetCellValue(this SpreadsheetDocument document, Sheet metadataSheet, string addressName, string value)
{
// If the string exists in the shared string table, get its index.
// If the string doesn't exist in the shared string table, add it and get the next index.
// Assume failure.
bool returnValue = false;
// Open the document for editing.
WorkbookPart wbPart = document.WorkbookPart;
if (metadataSheet != null)
{
Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(metadataSheet.Id))).Worksheet;
Cell theCell = ws.InsertCellInWorksheet(addressName);
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = wbPart.InsertSharedStringItem(value);
theCell.CellValue = new CellValue(stringIndex.ToString());
theCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
// Save the worksheet.
ws.Save();
returnValue = true;
}
return returnValue;
}
public override bool RemoveCell(SupportSet set, Sheet sheet, int col, int row) {
if (Contains(sheet, col, row)) {
// To exclude cell at sheet[col, row], split into up to 4 support ranges
if (rowInt.min < row) // North, column above [col,row]
{
set.Add(Make(sheet, new Interval(col, col), new Interval(rowInt.min, row - 1)));
}
if (row < rowInt.max) // South, column below [col,row]
{
set.Add(Make(sheet, new Interval(col, col), new Interval(row + 1, rowInt.max)));
}
if (colInt.min < col) // West, block to the left of [col,row]
{
set.Add(Make(sheet, new Interval(colInt.min, col - 1), rowInt));
}
if (col < colInt.max) // East, block to the right of [col,row]
{
set.Add(Make(sheet, new Interval(col + 1, colInt.max), rowInt));
}
return true;
}
else {
return false;
}
}
public void RemoveFromSupportSets(Sheet sheet, int col, int row) {
// Update the support sets of cells referred from an array formula only once
if (!supportRemoved) {
formula.RemoveFromSupportSets(sheet, col, row);
supportRemoved = true;
}
}
/**
* Called when a column is inserted on the specified sheet. Notifies all
* RCIR cells of this change. The default implementation here does nothing
*
* @param s the sheet on which the column was inserted
* @param sheetIndex the sheet index on which the column was inserted
* @param col the column number which was inserted
*/
public override void columnInserted(Sheet s, int sheetIndex, int col)
{
try
{
if (parser == null)
{
byte[] formulaData = formula.getFormulaData();
byte[] formulaBytes = new byte[formulaData.Length - 16];
System.Array.Copy(formulaData, 16,
formulaBytes, 0, formulaBytes.Length);
parser = new FormulaParser(formulaBytes,
this,
getSheet().getWorkbook(),
getSheet().getWorkbook(),
getSheet().getWorkbookSettings());
parser.parse();
}
parser.columnInserted(sheetIndex, col, s == getSheet());
}
catch (FormulaException e)
{
//logger.warn("cannot insert column within formula: " + e.Message);
}
}
// Mark cell, dirty if non-empty
public static void MarkCellDirty(Sheet sheet, int col, int row)
{
// Console.WriteLine("MarkDirty({0})", new FullCellAddr(sheet, col, row));
Cell cell = sheet[col, row];
if (cell != null)
cell.MarkDirty();
}
public static void CreateSpreadsheetWorkbook(string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new 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 = "mySheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
public override void InsertRowCols(Dictionary<Expr, Adjusted<Expr>> adjusted,
Sheet modSheet,
bool thisSheet,
int R,
int N,
int r,
bool doRows) {
Adjusted<Expr> ae;
if (adjusted.ContainsKey(e) && r < adjusted[e].upper) {
// There is a valid cached adjusted expression
ae = adjusted[e];
}
else {
// Compute a new adjusted expression and insert into the cache
ae = e.InsertRowCols(modSheet, thisSheet, R, N, r, doRows);
Console.WriteLine("Making new adjusted at rowcol " + r
+ "; upper = " + ae.upper);
if (ae.same) { // For better sharing, reuse unadjusted e if same
ae = new Adjusted<Expr>(e, ae.upper, ae.same);
Console.WriteLine("Reusing expression");
}
adjusted[e] = ae;
}
Debug.Assert(r < ae.upper, "Formula.InsertRowCols");
e = ae.e;
}
private static void InsertWorksheet(string docName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
{
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = spreadSheet.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 = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
}
}
/**
* Constructs this object from the raw data
*
* @param t the raw data
* @param s the sheet
*/
public MergedCellsRecord(Record t, Sheet s)
: base(t)
{
byte[] data = getRecord().getData();
int numRanges = IntegerHelper.getInt(data[0],data[1]);
ranges = new Range[numRanges];
int pos = 2;
int firstRow = 0;
int lastRow = 0;
int firstCol = 0;
int lastCol = 0;
for (int i = 0; i < numRanges; i++)
{
firstRow = IntegerHelper.getInt(data[pos],data[pos + 1]);
lastRow = IntegerHelper.getInt(data[pos + 2],data[pos + 3]);
firstCol = IntegerHelper.getInt(data[pos + 4],data[pos + 5]);
lastCol = IntegerHelper.getInt(data[pos + 6],data[pos + 7]);
ranges[i] = new SheetRangeImpl(s,firstCol,firstRow,
lastCol,lastRow);
pos += 8;
}
}
public void TextCellsAreStored()
{
var sheet = new Sheet();
const string theCell = "A21";
sheet.Put(theCell, "A string");
Assert.That(sheet.Get(theCell), Is.EqualTo("A string"));
}
public void AddNewWorksheet()
{
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
CurrentWorksheetPart = newWorksheetPart;
//workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1;
//workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1;
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
}
public static void OpenAndAddToSpreadsheetStream(Stream stream)
{
// Open a SpreadsheetDocument based on a stream.
SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(stream, true);
// Add a new worksheet.
WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = spreadsheetDocument.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 = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
spreadsheetDocument.WorkbookPart.Workbook.Save();
// Close the document handle.
spreadsheetDocument.Close();
// Caller must close the stream.
}
// Use this for initialization
void Start()
{
sheetsDirectoryPath = Application.dataPath + "/../Fiches";
System.IO.Path.GetFullPath(sheetsDirectoryPath);
sheetsPath = System.IO.Directory.GetFiles(sheetsDirectoryPath, "*.xml", System.IO.SearchOption.AllDirectories);
currentSheet= new Sheet(sheetsPath[0]);
}
// Use this for initialization
void Start()
{
questionnaire = GameObject.Find("Navigator").GetComponent<Questionnaire>();
currentSheet = questionnaire.currentSheet;
questionScene = SceneManager.GetSceneByName("Question");
exempleScene = SceneManager.GetSceneByName("Exemple");
}
public static SupportRange Make(Sheet sheet, Interval colInt, Interval rowInt) {
if (colInt.min == colInt.max && rowInt.min == rowInt.max) {
return new SupportCell(sheet, colInt.min, rowInt.min);
}
else {
return new SupportArea(sheet, colInt, rowInt);
}
}
public static int SaveSheet(Sheet sheet)
{
Sheet newSheet = Sheet.LoadById(sheet.Id);
newSheet.Overview = sheet.Overview.Replace("\n", "<br/>");
newSheet.Summary = sheet.Summary.Replace("\n", "<br/>");
newSheet.Save();
return newSheet.Id;
}
public void ReplaceSortOrder(Sheet sheet, int oldSortOrder, int newSortOrder)
{
this._dbContext.Database.ExecuteSqlCommand(
"UPDATE SheetEntry SET SortOrder = @newSortOrder WHERE SortOrder = @oldSortOrder WHERE Sheet_Id = @sheetId",
new SqlParameter("newSortOrder", newSortOrder),
new SqlParameter("oldSortOrder", oldSortOrder),
new SqlParameter("sheetId", sheet.Id));
}
public override Adjusted<Expr> InsertRowCols(Sheet modSheet,
bool thisSheet,
int R,
int N,
int r,
bool doRows) {
return new Adjusted<Expr>(this);
}
/// <summary>
/// Создает экземпляр класса для работы с текущим файлом
/// </summary>
/// <param name="filePath">Путь к документу</param>
/// <param name="removeAfterDestroy">Удалять ли файл после окончания работы с ним</param>
public ExcellWorker(string filePath, bool removeAfterDestroy)
{
_currentFilePath = filePath;
_currentDocument = SpreadsheetDocument.Open(filePath, true);
_currentWorkBookPart = _currentDocument.WorkbookPart;
_currentSheet = _currentWorkBookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
RemoveAfterDestroy = removeAfterDestroy;
}
public Sheet Convert()
{
Sheet result = new Sheet();
result.Width = SizeB;
result.Height = SizeA;
result.Thickness = Thickness;
return result;
}
public static void CreateConditionalFormatting()
{
var workbook = new Workbook(); //creating workbook and adding sheet
var companiesSheet = new Sheet("CompaniesByRevenue");
workbook.Sheets.AddSheet(companiesSheet);
companiesSheet.Columns[0].Width = 5;
companiesSheet.Columns[1].Width = 30;
companiesSheet.Columns[2].Width = 25;
companiesSheet.Columns[3].Width = 25;
companiesSheet.Columns[4].Width = 15;
var headingStyle = new CellStyle(); //define bold and centered header style
headingStyle.Font.Bold = true;
headingStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var thousandsFormatStyle = new CellStyle(); //adding thousands separator format on Employees column
thousandsFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
thousandsFormatStyle.Format = "#,#0";
var currencyFormatStyle = new CellStyle(); //adding $ (dollar) sign on Revenue and Capitalization columns
currencyFormatStyle.Format = "$#,#0";
currencyFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var table = companiesSheet["A2", "E6"];
table.GetRow(-1).SetValues("Rank", "Company", "Revenue (USD billions)", "Capitalization (USD billions)", "Employees").SetStyle(headingStyle);
table.GetColumn(0).SetValues(1, 2, 3, 4, 5);
table.GetColumn(1).SetValues("Wal-Mart Stores, Inc. (USA)", "Royal Dutch Shell (NLD)", "Exxon Mobil Corporation (USA)", "China National Petroleum (CHN)", "Sinopec Group (CHN)");
table.GetColumn(2).SetValues(469, 467, 453, 425, 411).SetStyle(currencyFormatStyle);
table.GetColumn(3).SetValues(248, 132, 406, null, 81).SetStyle(currencyFormatStyle);
table.GetColumn(4).SetValues(2200000, 90000, 76900, 1668072, 401000).SetStyle(thousandsFormatStyle);
var originFormating = new ConditionalFormatting(Range.Parse("B2:B6")); //creating ConditionalFormatting for specified range
originFormating.AddContainsText("USA", new CellStyle { Fill = CellFill.BackColor(Color.Red) });
originFormating.AddEndsWithText("(NLD)", new CellStyle { Fill = CellFill.BackColor(new Color("#0096FF")) });
originFormating.AddContainsText("CHN", new CellStyle { Fill = CellFill.BackColor(new Color("#FFFF96")) });
var revenueFormatting = new ConditionalFormatting();
revenueFormatting.AddRange("C2:C6"); //multiple ranges or cells can be added
revenueFormatting.AddDefaultIconSet(IconSetType.Item5Arrows); //add IconSet with 5 arrows
revenueFormatting.AddGreaterThan("460", new CellStyle { Font = new CellFont { Underline = FontUnderline.Single } });
var capitalizationFormatting = new ConditionalFormatting(Range.Parse("D1:D6")); //specifying range in constructor
capitalizationFormatting.AddColorScale(Color.Yellow, Color.Orange);
capitalizationFormatting.AddDefaultIconSet();
capitalizationFormatting.AddIsBlank(new CellStyle { Fill = CellFill.BackColor(Color.Red) }); //add style applied on blank cell
ConditionalFormatting employeesNumberFormating = new ConditionalFormatting(Range.Parse("E1:E6"));
employeesNumberFormating.AddDefaultDataBar(Color.Blue); //adding blue data bar conditional formatting rule
//adding different conditional formattings to sheet
companiesSheet.ConditionalFormatting.Add(originFormating);
companiesSheet.ConditionalFormatting.Add(revenueFormatting);
companiesSheet.ConditionalFormatting.Add(capitalizationFormatting);
companiesSheet.ConditionalFormatting.Add(employeesNumberFormating);
workbook.Save(@"ConditionalFormatting.xlsx");
}
protected void Page_Load(object sender, EventArgs e)
{
//load url arguments in variables
string INSTANCE_NAME = @Request.QueryString["instance"];
string CATALOG_NAME = Request.QueryString["catalog"];
string CUBE_NAME = Request.QueryString["cube"];
//Create Excel file name
string ConnectionName = INSTANCE_NAME.Replace("\\","_") + "_" + CATALOG_NAME + "_" + CUBE_NAME;
Response.Write(ConnectionName);
//Create Workbook
string filename = Server.MapPath(@"tmp/" + ConnectionName + ".xlsx");
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new 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 = "mySheet" };
sheets.Append(sheet);
//Add a connectionPart to the workbookpart
ConnectionsPart connectionsPart1 = workbookpart.AddNewPart<ConnectionsPart>();
Connections connections1 = new Connections();
Connection connection1 = new Connection() { Id = (UInt32Value)1U, KeepAlive = true, Name = ConnectionName, Type = (UInt32Value)5U, RefreshedVersion = 5, Background = true };
DatabaseProperties databaseProperties1 = new DatabaseProperties() { Connection = "Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + CATALOG_NAME + ";Data Source=" + @INSTANCE_NAME + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error", Command = CUBE_NAME, CommandType = (UInt32Value)1U };
OlapProperties olapProperties1 = new OlapProperties() { SendLocale = true, RowDrillCount = (UInt32Value)1000U };
connection1.Append(databaseProperties1);
connection1.Append(olapProperties1);
connections1.Append(connection1);
connectionsPart1.Connections = connections1;
//Add a PivottableCache part
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = workbookpart.AddNewPart<PivotTableCacheDefinitionPart>();
PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition() { SaveData = false, BackgroundQuery = true, SupportSubquery = true, SupportAdvancedDrill = true };
pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1;
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + @"D:\MyBI\SSAS\SSAS2012_MyBI\tmp\test.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.WriteFile(Server.MapPath(@"tmp/" + ConnectionName + ".xlsx"));
Response.End();
}
unsafe public static CuttingScheme ConvertLayout(_Layout * layout, ParametersCollection parameters, Sheet sheet)
{
CuttingScheme result = new CuttingScheme();
result.Sheet = sheet;
result.Parameters = parameters;
CuttingResultBuilder builder = new CuttingResultBuilder();
builder.LoadSections(layout, result);
return result;
}
public void ManyCellsExist()
{
var sheet = new Sheet();
sheet.Put("A1", "First");
sheet.Put("X27", "Second");
Assert.That(sheet.Get("A1"), Is.EqualTo("First"));
Assert.That(sheet.Get("X27"), Is.EqualTo("Second"));
}
/**
* Constructor
* @param s the sheet containing the range
* @param c1 the column number of the top left cell of the range
* @param r1 the row number of the top left cell of the range
* @param c2 the column number of the bottom right cell of the range
* @param r2 the row number of the bottomr right cell of the range
*/
public SheetRangeImpl(Sheet s,int c1,int r1,
int c2,int r2)
{
sheet = s;
row1 = r1;
row2 = r2;
column1 = c1;
column2 = c2;
}
private string ExportToOxml(bool firstTime, string fileName, string RutaArchivos)
{
//Check if the file exists.
if (firstTime)
{
Random rnd = new Random();
fileName = "Reporte_" + DateTime.Now.ToString("yyyyMMddHHmmss") + rnd.Next(1000).ToString().PadLeft(4, Convert.ToChar("0")) + ".xlsx";
while (System.IO.File.Exists(fileName))
{
fileName = "Reporte_" + DateTime.Now.ToString("yyyyMMddHHmmss") + rnd.Next(1000).ToString().PadLeft(4, Convert.ToChar("0")) + ".xlsx";
}
}
uint sheetId = 1; //Start at the first sheet in the Excel workbook.
if (firstTime)
{
//This is the first time of creating the excel file and the first sheet.
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(RutaArchivos + fileName, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var bold1 = new Bold();
CellFormat cf = new CellFormat();
// Add Sheets to the Workbook.
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Hoja" + sheetId
};
sheets.Append(sheet);
//Add Header Row.
var headerRow = new Row();
foreach (DataColumn column in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
else
{
// Open the Excel file that we created before, and start to add sheets to it.
var spreadsheetDocument = SpreadsheetDocument.Open(RutaArchivos + fileName, true);
var workbookpart = spreadsheetDocument.WorkbookPart;
if (workbookpart.Workbook == null)
{
workbookpart.Workbook = new Workbook();
}
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
if (sheets.Elements <Sheet>().Any())
{
//Set the new sheet id
sheetId = sheets.Elements <Sheet>().Max(s => s.SheetId.Value) + 1;
}
else
{
sheetId = 1;
}
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Hoja" + sheetId
};
sheets.Append(sheet);
//Add the header row here.
var headerRow = new Row();
foreach (DataColumn column in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
return(fileName);
}
public void importdatafromexcel(string excelfilepath)
{
GridView GridView1 = new GridView();
int r = Request.Files.Count;
HttpPostedFileBase file = Request.Files[0]; //Uploaded file
//Use the following properties to get file's name, size and MIMEType
int fileSize = file.ContentLength;
string fileName = file.FileName;
string[] values = fileName.Split('.');
string SheetName = values[0];
string filetype = file.GetType().ToString();
string ExistedNumber = string.Empty;
//fileName = Server.MapPath(Path.Combine("~/Content/",Path.ChangeExtension(fileName, ".xlsx")));
//fileName = Server.MapPath(Path.Combine("~/Content/MyExcelFile_files/", Path.ChangeExtension(fileName, ".xlsx")));
//file.SaveAs(fileName);
fileName = Server.MapPath(Path.Combine("~/Content/", fileName));
file.SaveAs(Path.ChangeExtension(fileName, ".xlsx"));
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
//Read the first Sheet from Excel file.
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>();
//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.
DataTable dt = new DataTable();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
//Use the first row to add columns to DataTable.
if (row.RowIndex.Value == 1)
{
foreach (Cell cell in row.Descendants <Cell>())
{
dt.Columns.Add(GetValue(doc, cell));
}
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
i++;
}
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
for (int i = 0; i < dt.Rows.Count; i++)
{
//DateTime today = System.DateTime.Now;
DateTime today = Convert.ToDateTime(dt.Rows[i][4]);
bool IsPresent = false;
if (dt.Rows[i][5].ToString() == "P" || dt.Rows[i][5].ToString() == "true")
{
IsPresent = true;
}
else
{
IsPresent = false;
}
if (!rm.IsStudentAttendenceExists(dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString(), today))
{
rm.InsertStudentAttendence(dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString(), today, IsPresent, dt.Rows[i][6].ToString());
}
}
doc.Close();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
if (System.IO.File.Exists(fileName))
{
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
}
}
//string path = Path.ChangeExtension(excelfilepath, ".xlsx");
////string excelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
//string excelConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
////string path = @"E:\Blink\Blink\Content\MyExcelFile.xls";
////string path = Server.MapPath(excelfilepath);
//string ssqltable = "CandidateAttendances";
//HttpPostedFileBase file = Request.Files[0]; //Uploaded file
////Use the following properties to get file's name, size and MIMEType
//int fileSize = file.ContentLength;
//string fileName = file.FileName;
//string[] values = fileName.Split('.');
//string SheetName = values[0];
//string filetype = file.GetType().ToString();
//OleDbConnection objConn = null;
//System.Data.DataTable dt1 = null;
//objConn = new OleDbConnection(excelConnection);
//// Open connection with the database.
//objConn.Open();
//// Get the data table containg the schema guid.
//dt1 = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//String[] excelSheets = new String[dt1.Rows.Count];
//int i1 = 0;
//// Add the sheet name to the string array.
//foreach (DataRow row in dt1.Rows)
//{
// excelSheets[i1] = row["TABLE_NAME"].ToString();
// i1++;
//}
//// make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
//string myexceldataquery = "select * from [" + excelSheets[0] + "]";
//try
//{
// //create our connection strings
// string ssqlconnectionstring = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
// //execute a query to erase any previous data from our destination table
// string sclearsql = "select * from " + ssqltable;
// SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
// SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
// sqlconn.Open();
// sqlcmd.ExecuteNonQuery();
// sqlconn.Close();
// using (OleDbDataAdapter adaptor = new OleDbDataAdapter(myexceldataquery, excelConnection))
// {
// DataSet ds = new DataSet();
// adaptor.Fill(ds);
// System.Data.DataTable dt = ds.Tables[0];
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// //DateTime today = System.DateTime.Now;
// DateTime today = Convert.ToDateTime(dt.Rows[i][4]);
// bool IsPresent = false;
// if (dt.Rows[i][5].ToString() == "P" || dt.Rows[i][5].ToString() == "true")
// {
// IsPresent = true;
// }
// else
// {
// IsPresent = false;
// }
// if (!rm.IsStudentAttendenceExists(dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString(), today))
// rm.InsertStudentAttendence(dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString(), today, IsPresent, dt.Rows[i][6].ToString());
// }
// }
//}
//catch (Exception ex)
//{
// ex.ToString();
// //handle exception
//}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
HSSFWorkbook excel = new HSSFWorkbook(fileUpload.PostedFile.InputStream);
Sheet sheet = excel.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
Row custrow = sheet.GetRow(2);
string cust = custrow.GetCell(1).StringCellValue;//客户代码
Customer customer = TheCustomerMgr.LoadCustomer(cust);
Row row_startdate = sheet.GetRow(3);
string startdate = row_startdate.GetCell(1).StringCellValue; //开始日期
Row row_enddate = sheet.GetRow(4);
string enddate = row_enddate.GetCell(1).StringCellValue; //结束日期
startdate = startdate == string.Empty ? DateTime.Now.AddMonths(-1).ToShortDateString() : startdate;
enddate = enddate == string.Empty ? DateTime.Now.ToShortDateString() : enddate;
IList <ActingBill> actingBillList = TheActingBillMgr.GetActingBill(cust, "", DateTime.Parse(startdate), DateTime.Parse(enddate), "", "", this.ModuleType, this.billNo);
var actbill = (from i in actingBillList//按ASN ITME聚合 得到总的可用数量
group i by new
{
i.IpNo,
i.Item.Code
} into k
select new
{
Item = k.Key.Code,
IpNo = k.Key.IpNo,
Amt = k.Sum(i => i.BillQty - i.BilledQty)
}).ToList();
ImportHelper.JumpRows(rows, 7);
IList <ActingBill> createBillList = new List <ActingBill>();
IList <Bill> createdBill = new List <Bill>();
while (rows.MoveNext())
{
Row curow = (HSSFRow)rows.Current;
string asn = curow.GetCell(0).StringCellValue;
string item = curow.GetCell(1).NumericCellValue.ToString();
decimal qty = decimal.Parse(curow.GetCell(2).NumericCellValue.ToString());
if (asn != string.Empty)
{
var temp_qty = actbill.Where(i => i.Item == item && i.IpNo == asn).ToList();
if (temp_qty.Count > 0)
{
if (Math.Abs(temp_qty[0].Amt) - Math.Abs(qty) >= 0)
{
IList <ActingBill> result = actingBillList.Where(i => i.Item.Code == item && i.IpNo == asn).ToList();
foreach (ActingBill _actbill in result)
{
if (qty == 0)
{
break; //扣减完了
}
if (_actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty == 0)
{
continue; //actbill可用数量用完了
}
//if (_actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty - qty >= 0)
//{
// _actbill.CurrentBillQty = _actbill.CurrentBillQty + qty;
// qty = 0;
//}
//else
//{
// _actbill.CurrentBillQty = _actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty;
// qty = qty - _actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty;
//}
if (_actbill.BillQty > 0)
{
if (_actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty - qty >= 0)
{
_actbill.CurrentBillQty = _actbill.CurrentBillQty + qty;
qty = 0;
}
else
{
_actbill.CurrentBillQty = _actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty;
qty = qty - _actbill.CurrentBillQty;
}
}
else
{
if (_actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty - qty <= 0)
{
_actbill.CurrentBillQty = _actbill.CurrentBillQty + qty;
qty = 0;
}
else
{
_actbill.CurrentBillQty = _actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty;
qty = qty - _actbill.CurrentBillQty;
}
}
createBillList.Add(_actbill);
}
}
else
{
ShowErrorMessage("行" + (curow.RowNum + 1).ToString() + "数量大于剩余开票数量！");
return;
}
}
else
{
ShowErrorMessage("行" + (curow.RowNum + 1).ToString() + " ASN或零件不存在！请查询对应记录后再导入！");
return;
}
}
}
int cnt = 0;
try
{
while (0 < createBillList.Count)
{
cnt++;
var t = from i in createBillList
group i by new
{
i.Item.Code
} into k
select new
{
Item = k.Key.Code,
Amt = k.Sum(i => i.CurrentBillQty * i.UnitPrice)
};
List <string> abM = new List <string>();
List <string> zeroM = new List <string>();
foreach (var i in t)
{
if (i.Amt > 1000000)
{
abM.Add(i.Item);
}
if (i.Amt == 0 && cnt == 1)
{
zeroM.Add(i.Item);
}
}
#region 超过100w的
foreach (string s in abM)
{
IList <ActingBill> tempList = createBillList.Where(i => i.Item.Code == s).OrderByDescending(i => i.UnitPrice * i.CurrentBillQty).ToList();
IList <ActingBill> amtList = new List <ActingBill>();
decimal amount = 0;
foreach (ActingBill act in tempList)
{
if (amount + act.CurrentBillQty * act.UnitPrice <= 1000000)
{
amtList.Add(act);
amount += act.CurrentBillQty * act.UnitPrice;
}
else if (act.CurrentBillQty * act.UnitPrice > 1000000)
{
decimal qty = Math.Round(1000000 / act.UnitPrice, 0);
act.CurrentBillQty = qty;
amtList.Add(act);
}
else
{
continue;
}
}
if (amtList.Count > 0)
{
IList <Bill> billList = TheBillMgr.CreateBill(amtList, this.CurrentUser);
createdBill.Add(billList[0]);
}
foreach (ActingBill act in amtList)
{
if (Math.Round(1000000 / act.UnitPrice, 0) > act.CurrentBillQty)
{
createBillList.Remove(act);
}
else
{
act.CurrentBillQty = act.BillQty - act.BilledQty - Math.Round(1000000 / act.UnitPrice, 0) * cnt;
}
}
}
#endregion
#region 未超过100w的
List <string> normal = new List <string>();
var bAm = (from i in createBillList
group i by new
{
i.Item.Code
} into k
select new
{
Item = k.Key.Code,
Amt = k.Sum(i => i.CurrentBillQty * i.UnitPrice)
}).Where(i => i.Amt < 1000000 && i.Amt != 0).OrderByDescending(i => i.Amt).ToList();
while (bAm.Count > 0)
{
decimal tempAmt = 0;
IList <string> tempGroup = new List <string>();
foreach (var i in bAm)
{
if (i.Amt + tempAmt <= 1000000)
{
tempGroup.Add(i.Item);
tempAmt += i.Amt;
}
else
{
continue;
}
}
List <ActingBill> tempAct = new List <ActingBill>();
foreach (string item in tempGroup)
{
List <ActingBill> _tempAct = (createBillList.Where(i => i.Item.Code == item)).ToList();
foreach (ActingBill bill in _tempAct)
{
tempAct.Add(bill);
}
}
for (int i = bAm.Count; i > 0; i--)
{
if (tempGroup.Contains(bAm[i - 1].Item))
{
bAm.Remove(bAm[i - 1]);
i = bAm.Count + 1;
}
}
if (tempAct.Count > 0)
{
IList <Bill> billList = TheBillMgr.CreateBill(tempAct, this.CurrentUser);
createdBill.Add(billList[0]);
}
foreach (ActingBill bill in tempAct)
{
createBillList.Remove(bill);
}
}
#endregion
if (zeroM.Count > 0 && cnt == 1)
{
foreach (string code in zeroM)
{
IList <ActingBill> tempList = createBillList.Where(i => i.Item.Code == code).OrderByDescending(i => i.UnitPrice * i.CurrentBillQty).ToList();
if (tempList.Count > 0)
{
if (createdBill.Count > 0)
{
TheBillMgr.AddBillDetail(createdBill[0], tempList, this.CurrentUser);
}
else
{
IList <Bill> billList = TheBillMgr.CreateBill(tempList, this.CurrentUser);
createdBill.Add(billList[0]);
}
}
foreach (ActingBill actb in tempList)
{
createBillList.Remove(actb);
}
}
}
}
}
catch (BusinessErrorException ex)
{
ShowErrorMessage(ex);
}
catch (Exception ex)
{
ShowErrorMessage(ex.Message);
}
_createdBillNo += " 已创建以下账单：<br/>";
foreach (Bill b in createdBill)
{
_createdBillNo += b.BillNo + "<br />";
}
#region output result xls
if (createdBill != null && createdBill.Count > 0)
{
this.btnBack_Click(sender, e);
ExportResult(createdBill);
}
#endregion
}
catch (Exception exception)
{
ShowErrorMessage(exception.Message);
}
}
public FileStreamResult ToExcel(IQueryable query, string fileName = null)
{
var columns = GetProperties(query.ElementType);
var stream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
GenerateWorkbookStylesPartContent(workbookStylesPart);
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
var headerRow = new Row();
foreach (var column in columns)
{
headerRow.Append(new Cell()
{
CellValue = new CellValue(column.Key),
DataType = new EnumValue <CellValues>(CellValues.String)
});
}
sheetData.AppendChild(headerRow);
foreach (var item in query)
{
var row = new Row();
foreach (var column in columns)
{
var value = GetValue(item, column.Key);
var stringValue = $"{value}".Trim();
var cell = new Cell();
var underlyingType = column.Value.IsGenericType &&
column.Value.GetGenericTypeDefinition() == typeof(Nullable <>) ?
Nullable.GetUnderlyingType(column.Value) : column.Value;
var typeCode = Type.GetTypeCode(underlyingType);
if (typeCode == TypeCode.DateTime)
{
if (stringValue != string.Empty)
{
cell.CellValue = new CellValue()
{
Text = DateTime.Parse(stringValue).ToOADate().ToString()
};
cell.StyleIndex = (UInt32Value)1U;
}
}
else if (typeCode == TypeCode.Boolean)
{
cell.CellValue = new CellValue(stringValue.ToLower());
cell.DataType = new EnumValue <CellValues>(CellValues.Boolean);
}
else if (IsNumeric(typeCode))
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
row.Append(cell);
}
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
}
if (stream?.Length > 0)
{
stream.Seek(0, SeekOrigin.Begin);
}
var result = new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
result.FileDownloadName = (!string.IsNullOrEmpty(fileName) ? fileName : "Export") + ".xlsx";
return(result);
}
private CutLineAndSheet(ICutLine line, Sheet sheet, ISheet[] outputSheets)
{
Line = line;
Sheet = sheet;
OutputSheets = outputSheets;
}
public static IEnumerable <IDictionary <string, object> > ReadToDictionaries(string fileName, string tabName)
{
var rowDictionaries = new List <IDictionary <string, object> >();
using (var spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))
{
var indexedStringTable = IndexStringTable(spreadSheetDocument);
var alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var alphabetHash = new Hashtable();
foreach (var chr in alphabet)
{
alphabetHash.Add(chr, alphabet.IndexOf(chr));
}
var workbookPart = spreadSheetDocument.WorkbookPart;
var sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
Sheet sheet = null;
foreach (var sheetTemp in sheets)
{
if (sheetTemp.Name?.Value == tabName)
{
sheet = sheetTemp;
}
}
if (sheet == null)
{
throw new NullReferenceException("Could not find sheet with name " + tabName);
}
string relationshipId = sheet.Id.Value;
var worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
var workSheet = worksheetPart.Worksheet;
var sheetData = workSheet.GetFirstChild <SheetData>();
var rows = sheetData.Descendants <Row>().ToArray();
var columnIndexes = new Dictionary <int, string>();
var i = 0;
foreach (Cell cell in rows.ElementAt(0))
{
var cellValue = GetCellValue(indexedStringTable, cell);
if (!string.IsNullOrWhiteSpace(cellValue))
{
columnIndexes.Add(i, cellValue);
}
i++;
}
foreach (var row in rows) //this will also include your header row...
{
var newRow = new ConcurrentDictionary <string, object>();
var rowHasValue = false;
var cells = row.Descendants <Cell>().ToArray();
Parallel.ForEach(cells, (cell) =>
{
if (cell.CellReference.HasValue)
{
var index = -1;
switch (cell.CellReference.Value[1])
{
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
{
index = (int)alphabetHash[cell.CellReference.Value[0]];
break;
}
default:
{
index =
(((int)alphabetHash[cell.CellReference.Value[0]] + 1) * alphabetHash.Count)
+ (int)alphabetHash[cell.CellReference.Value[1]];
break;
}
}
if (columnIndexes.ContainsKey(index))
{
var cellValue = GetCellValue(indexedStringTable, cell);
newRow[columnIndexes[index]] = cellValue;
if (!string.IsNullOrWhiteSpace(cellValue))
{
rowHasValue = true;
}
}
}
});
if (!rowHasValue)
{
break;
}
rowDictionaries.Add(newRow);
}
}
rowDictionaries.RemoveAt(0); //...so i'm taking it out here.
return(rowDictionaries);
}
public object getExcel(TransportationBill tb, IList <TransportationBillDetail> detailList, bool isExportExcel)
{
string path = Server.MapPath(".") + @"\Reports\Templates\YFKExcelTemplates\TransportationBill.xls";
if (File.Exists(path))
{
string filename = @"/Reports/Templates/TempFiles/temp_" + DateTime.Now.ToString("yyyyMMddhhmmss") + tb.BillNo + ".xls";
string _wpath = Server.MapPath(".") + filename;
File.Copy(path, _wpath);
FileStream file = new FileStream(_wpath, FileMode.Open, FileAccess.ReadWrite, FileShare.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
Sheet sheet = hssfworkbook.GetSheet("sheet1");
NPOI.SS.UserModel.CellStyle normalStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center" });
NPOI.SS.UserModel.CellStyle dateStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center", "DateTime" });
Cell cell = sheet.GetRow(8).GetCell(2);
cell.SetCellValue(tb.BillAddress.Party.Name);
cell = sheet.GetRow(8).GetCell(8);
cell.SetCellValue(tb.BillNo);
int i = 10;
decimal cnt = 0;
foreach (TransportationBillDetail tbd in detailList)
{
Row row = sheet.CreateRow(i);
TransportationOrder tord = TheTransportationOrderMgr.LoadTransportationOrder(tbd.ActBill.OrderNo);
row.CreateCell(0).SetCellValue(tord.CreateDate); //运输日期
row.CreateCell(1).SetCellValue(tord.TransportationRoute != null ? tord.TransportationRoute.Description : ""); //运输路线
row.CreateCell(2).SetCellValue(tbd.ActBill.PricingMethod != null ? tbd.ActBill.PricingMethod : ""); //运输形式
row.CreateCell(3).SetCellValue(tord.OrderNo); //运单号码
row.CreateCell(4).SetCellValue(tbd.ActBill.EffectiveDate); //生效日期
row.CreateCell(5).SetCellValue(tbd.ActBill.UnitPrice.ToString("F2")); //单价
row.CreateCell(6).SetCellValue(tbd.ActBill.Currency.Name); //币种
row.CreateCell(7).SetCellValue(tbd.ActBill.BillQty.ToString("F0")); //开票数
row.CreateCell(8).SetCellValue(tbd.ActBill.BillAmount.ToString("F2")); //金额
cnt = Convert.ToInt32(tbd.ActBill.BillAmount) + cnt;
for (int y = 0; y < 9; y++)
{
row.GetCell(y).CellStyle = normalStyle;
}
row.GetCell(0).CellStyle = dateStyle;
row.GetCell(4).CellStyle = dateStyle;
i++;
}
if (i <= 20)
{
for (int j = i; j < 21; j++)
{
Row row = sheet.CreateRow(j);
for (int y = 0; y < 9; y++)
{
row.CreateCell(y).CellStyle = normalStyle;
}
}
i = 20;
}
Row _row = sheet.CreateRow(i + 1);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i + 1, i + 1, 6, 7));
_row.CreateCell(6).SetCellValue("合计发票金额：");
_row.GetCell(6).CellStyle.Alignment = HorizontalAlignment.RIGHT;
_row.CreateCell(8).SetCellValue(cnt.ToString("F2"));
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
if (!isExportExcel)
{
FileStream f = new FileStream(_wpath, FileMode.Open, FileAccess.Write);
byte[] data = ms.ToArray();
f.Write(data, 0, data.Length);
f.Close();
f.Dispose();
hssfworkbook = null;
ms.Close();
ms.Dispose();
return("http://" + Request.Url.Authority + filename);
}
else
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=TBillResult.xls"));
Response.BinaryWrite(ms.ToArray());
hssfworkbook = null;
ms.Close();
ms.Dispose();
return(null);
}
}
else
{
return(null);
}
}
private static IEnumerable <EntryChange> ImportSingleSheet([NotNull] ResourceManager resourceManager, [NotNull, ItemNotNull] Sheet firstSheet, [NotNull] WorkbookPart workbookPart, [ItemNotNull][CanBeNull] IList <SharedStringItem> sharedStrings)
{
Contract.Requires(resourceManager != null);
Contract.Requires(firstSheet != null);
Contract.Requires(workbookPart != null);
Contract.Ensures(Contract.Result <IEnumerable <EntryChange> >() != null);
var data = firstSheet.GetRows(workbookPart).Select(row => row.GetCellValues(sharedStrings)).ToArray();
var firstRow = data.FirstOrDefault();
Contract.Assume(firstRow != null);
var headerRow = (IList <string>)firstRow.Skip(2).ToArray();
var rowsByProject = data.Skip(1).GroupBy(row => row.FirstOrDefault() ?? string.Empty, row => (IList <string>)row.Skip(1).ToArray());
foreach (var projectRows in rowsByProject)
{
Contract.Assume(projectRows != null);
var projectName = projectRows.Key;
if (string.IsNullOrEmpty(projectName))
{
continue;
}
var rowsByFile = projectRows.GroupBy(row => row.FirstOrDefault() ?? string.Empty, row => (IList <string>)row.Skip(1).ToArray());
foreach (var fileRows in rowsByFile)
{
Contract.Assume(fileRows != null);
var uniqueName = fileRows.Key;
if (string.IsNullOrEmpty(uniqueName))
{
continue;
}
var projectEntities = resourceManager.ResourceEntities
.Where(item => projectName.Equals(item.ProjectName, StringComparison.OrdinalIgnoreCase))
.ToArray();
var entity = projectEntities.FirstOrDefault(item => uniqueName.Equals(item.UniqueName, StringComparison.OrdinalIgnoreCase))
// fallback for backward compatibility:
?? projectEntities.FirstOrDefault(item => uniqueName.Equals(item.BaseName, StringComparison.OrdinalIgnoreCase));
if (entity == null)
{
continue;
}
var tableData = new[] { headerRow }.Concat(fileRows).ToArray();
foreach (var change in entity.ImportTable(_fixedColumnHeaders, tableData))
{
yield return(change);
}
}
}
}
private static ResourceEntity FindResourceEntity([NotNull][ItemNotNull] this IEnumerable <MultipleSheetEntity> entities, [NotNull][ItemNotNull] Sheet sheet)
{
Contract.Requires(entities != null);
Contract.Requires(sheet != null);
Contract.Ensures(Contract.Result <ResourceEntity>() != null);
var name = GetName(sheet);
var entity = entities.Where(item => item.SheetName.Equals(name, StringComparison.OrdinalIgnoreCase))
.Select(item => item.ResourceEntity)
.FirstOrDefault();
if (entity == null)
{
throw new ImportException(string.Format(CultureInfo.CurrentCulture, Resources.ImportMapSheetError, name));
}
return(entity);
}
static void Main(string[] args)
{
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
var fileName = Path.Combine(path, @"test.xlsx");
if (File.Exists(fileName))
{
File.Delete(fileName);
}
var headerList = new string[] { "Header 1", "Header 2", "Header 3", "Header 4" };
//sheet1
var boolList = new bool[] { true, false, true, false };
var intList = new int[] { 1, 2, 3, -4 };
var dateList = new DateTime[] { DateTime.Now, DateTime.Today, DateTime.Parse("1/1/2014"), DateTime.Parse("2/2/2014") };
var sharedStringList = new string[] { "shared string", "shared string", "cell 3", "cell 4" };
var inlineStringList = new string[] { "inline string", "inline string", "3>", "<4" };
var stopWatch = new Stopwatch();
using (var spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create the workbook
var workbookPart = spreadSheet.AddWorkbookPart();
var openXmlExportHelper = new OpenXmlWriterHelper();
openXmlExportHelper.SaveCustomStylesheet(workbookPart);
var workbook = workbookPart.Workbook = new Workbook();
var sheets = workbook.AppendChild <Sheets>(new Sheets());
// create worksheet 1
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
using (var writer = OpenXmlWriter.Create(worksheetPart))
{
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
//Create header row
writer.WriteStartElement(new Row());
for (int i = 0; i < headerList.Length; i++)
{
//header formatting attribute. This will create a <c> element with s=2 as its attribute
//s stands for styleindex
var attributes = new OpenXmlAttribute[] { new OpenXmlAttribute("s", null, "2") }.ToList();
openXmlExportHelper.WriteCellValueSax(writer, headerList[i], CellValues.SharedString, attributes);
}
writer.WriteEndElement(); //end of Row tag
// bool List
writer.WriteStartElement(new Row());
for (int i = 0; i < boolList.Length; i++)
{
openXmlExportHelper.WriteCellValueSax(writer, boolList[i].ToString(), CellValues.Boolean);
}
writer.WriteEndElement(); //end of Row
//int List
writer.WriteStartElement(new Row());
for (int i = 0; i < intList.Length; i++)
{
openXmlExportHelper.WriteCellValueSax(writer, intList[i].ToString(), CellValues.Number);
}
writer.WriteEndElement(); // end of Row
// datetime List
writer.WriteStartElement(new Row());
for (int i = 0; i < dateList.Length; i++)
{
//date format. Excel internally represent the datetime value as number, the date is only a formatting
//applied to the number. It will look something like 40000.2833 without formatting
var attributes = new OpenXmlAttribute[] { new OpenXmlAttribute("s", null, "1") }.ToList();
//the helper internally translate the CellValues.Date into CellValues.Number before writing
openXmlExportHelper.WriteCellValueSax(writer, (dateList[i]).ToOADate().ToString(CultureInfo.InvariantCulture), CellValues.Date, attributes);
}
writer.WriteEndElement(); //end of Row
// shared string List
writer.WriteStartElement(new Row());
for (int i = 0; i < sharedStringList.Length; i++)
{
openXmlExportHelper.WriteCellValueSax(writer, sharedStringList[i], CellValues.SharedString);
}
writer.WriteEndElement(); //end of Row
// inline string List
writer.WriteStartElement(new Row());
for (int i = 0; i < inlineStringList.Length; i++)
{
openXmlExportHelper.WriteCellValueSax(writer, inlineStringList[i], CellValues.InlineString);
}
writer.WriteEndElement(); //end of Row
writer.WriteEndElement(); //end of SheetData
writer.WriteEndElement(); //end of worksheet
writer.Close();
}
//sheet2
Console.WriteLine("Starting to generate 1 million random string");
//1 million rows
var a = new string[1000000, 4];
var chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var random = new Random();
for (int i = 0; i < 1000000; i++)
{
for (int j = 0; j < 4; j++)
{
a[i, j] = new string(
Enumerable.Repeat(chars, 5)
.Select(s => s[random.Next(s.Length)])
.ToArray());
}
}
Console.WriteLine("Starting to generate 1 million excel rows...");
stopWatch.Start();
// create worksheet 2
worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 2, Name = "Sheet2"
};
sheets.Append(sheet);
using (var writer = OpenXmlWriter.Create(worksheetPart))
{
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
//Create header row
for (int i = 0; i < 1000000; i++)
{
writer.WriteStartElement(new Row());
for (int j = 0; j < 4; j++)
{
openXmlExportHelper.WriteCellValueSax(writer, a[i, j], CellValues.InlineString);
}
writer.WriteEndElement(); //end of Row tag
}
writer.WriteEndElement(); //end of SheetData
writer.WriteEndElement(); //end of worksheet
writer.Close();
}
//create the share string part using sax like approach too
openXmlExportHelper.CreateShareStringPart(workbookPart);
}
stopWatch.Stop();
Console.WriteLine(string.Format("Time elapsed for writing 1 million rows: {0}", stopWatch.Elapsed));
Console.ReadLine();
}
/// <summary>
/// Load all sheets in the folder into our hashset
/// </summary>
public static void Load_AllSheets()
{
// If there is no save folder (which means there is no sheet available)
if (!Directory.Exists(sheet_lib_directory))
{
Directory.CreateDirectory(sheet_lib_directory);
}
else
{
// Get all the folders the global music sheet save path
var flist = Directory.GetDirectories(sheet_lib_directory);
// Each folder represents a single music sheet
foreach (var folder in flist)
{
Debug.Log(folder);
var files = Directory.GetFiles(folder);
var path = new FilePath();
foreach (var f in files)
{
Debug.Log(f);
// We get the file based on the file extension
var extension = Path.GetExtension(f);
if (extension == ".st")
{
path.sheetpath = f;
}
else if (extension == ".sha")
{
path.checksumpath = f;
}
else if (extension == ".mp3" || extension == ".ogg" || extension == ".wav")
{
path.musicpath = f;
path.musictype = extension;
}
else if (extension == ".jpg" || extension == ".jpeg" || extension == ".bmp" || extension == ".png")
{
path.coverpath = f;
}
}
Debug.Log(path.ToString());
if (path.sheetpath == "" || path.checksumpath == "" || path.musicpath == "")
{
continue;
}
try {
// We perform a checksum on our data
FileStream fstream = File.OpenRead(path.sheetpath);
var hashstring = Encoding.ASCII.GetString(G.sha256.ComputeHash(fstream));
var checksumhash = Encoding.ASCII.GetString(File.ReadAllBytes(path.checksumpath));
if (hashstring == checksumhash)
{
FileStream nfstream = File.OpenRead(path.sheetpath);
Sheet tempsheet = new Sheet();
tempsheet = (Sheet)formatter.Deserialize(nfstream);
musicsheet_lib.Add(path, tempsheet.GetSummary());
Debug.Log("File check passed");
nfstream.Close();
}
fstream.Close();
Debug.Log("Sheet added to the list");
} catch (SerializationException e) {
Debug.Log(e.Message);
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("test.xlsx", SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new 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 = "mySheet"
};
sheets.Append(sheet);
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open("test.xlsx", true))
{
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadSheet.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(text, shareStringPart);
// Insert a new worksheet.
worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
// Insert cell A1 into the new worksheet.
Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);
// Set the value of cell A1.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
// Save the new worksheet.
worksheetPart.Worksheet.Save();
}
// Save the new worksheet.
worksheetPart.Worksheet.Save();
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
private void SetStaticFields(Patient pat, Family fam = null, StaticTextData staticTextData = null)
{
_sheetWidget = SheetUtil.CreateSheet(_sheetDefWidget, pat?.PatNum ?? 0);
SheetFiller.FillFields(_sheetWidget, pat: pat, fam: fam, staticTextData: staticTextData);
}
public static void CreateExcel(string destination, IEnumerable <DataTable> dataTables,
string[] sheetNames = null)
{
using (var workbook = SpreadsheetDocument.Create(destination, SpreadsheetDocumentType.Workbook))
{
workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook
{
Sheets = new Sheets()
};
uint sheetId = 1;
var isAddStyle = false;
foreach (var table in dataTables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet();
if (!isAddStyle)
{
var stylesPart = workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
var styles = new Stylesheet();
styles.Save(stylesPart);
isAddStyle = true;
}
var headColumns = CrateColunms(table);
sheetPart.Worksheet.Append(headColumns);
var sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
var relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements <Sheet>().Any())
{
sheetId =
sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
var sheetName = string.Empty;
if (sheetNames != null)
{
if (sheetNames.Length >= sheetId)
{
sheetName = sheetNames[sheetId - 1];
}
}
else
{
sheetName = table.TableName ?? sheetId.ToString();
}
var sheet = new Sheet {
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
var headerRow = new Row();
var columns = new List <string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
var cell = new Cell
{
DataType = CellValues.String,
StyleIndex = 11,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
var newRow = new Row();
foreach (var col in columns)
{
var cell = new Cell
{
DataType = CellValues.String,
StyleIndex = 10,
CellValue = new CellValue(dsrow[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Append(sheetData);
}
workbook.Close();
}
}
public ActionResult LoadFile(LoadDonorPartnersViewModel model, HttpPostedFileBase imageFile)
{
JSonResult objResult = new JSonResult();
string fileName = string.Empty;
string Ext = string.Empty;
byte[] imgData = null;//; new byte[0];
string path = string.Empty;
string basePath;
// basePath = "E:\\TFS_Fuentes\\UnitLite\\Fuentes CMS Net\\CMSWeb\\File";
basePath = Server.MapPath("~/File");
DataTable dt;
var include = new[] { "A", "B", "D" };
try
{
if (imageFile != null)
{
fileName = imageFile.FileName;
Ext = Path.GetExtension(imageFile.FileName);
// imgData = Extension.FileToByteArray(imageFile);
path = string.Format("{0}\\{1}", basePath, imageFile.FileName);
}
if (!Directory.Exists(basePath))
{
Directory.CreateDirectory(basePath);
}
imageFile.SaveAs(path);
try
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
{
//Read the first Sheet from Excel file.
Sheet sheet = doc.WorkbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == "UNCDF");
//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>();
dt = new DataTable();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
//Use the first row to add columns to DataTable.
if (row.RowIndex.Value == 2)
{
foreach (Cell cell in row.Descendants <Cell>())
{
string cel = cell.CellReference;
cel = cel.Substring(0, 1);
if (include.Any(x => cel.Contains(x)))
{//Continue adding the row to the table
dt.Columns.Add(OpenXMLUtil.GetValue(doc, cell));
}
}
}
else if (row.RowIndex.Value > 2)
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
string cel2 = cell.CellReference;
cel2 = cel2.Substring(0, 1);
if (include.Any(x => cel2.Contains(x)))
{
dt.Rows[dt.Rows.Count - 1][i] = OpenXMLUtil.GetValue(doc, cell);
i++;
}
}
}
}
}
}
catch (Exception ex)
{
objResult.isError = true;
objResult.data = null;
objResult.message = string.Format("Error: Please check the template for this upload ", "Funds");
return(Json(objResult));
}
if (dt.Rows.Count <= 0)
{
objResult.isError = true;
objResult.data = null;
objResult.message = string.Format("The uploaded file has no rows", "Funds");
return(Json(objResult));
}
try
{
var dtResultado = dt.Rows.Cast <DataRow>().Where(row => !Array.TrueForAll(row.ItemArray, value => { return(value.ToString().Length == 0); }));
dt = dtResultado.CopyToDataTable();
List <ModelDonorPartnerResult> entlist = new List <ModelDonorPartnerResult>();
for (int i = 0; i < dt.Rows.Count; i++)
{
ModelDonorPartnerResult ent = new ModelDonorPartnerResult();
ent.DonorCode = Extension.ToEmpty(dt.Rows[i][0].ToString());//Convert.ToInt32(dt.Rows[i]["StudentId"]);
ent.DonorName = Extension.ToEmpty(dt.Rows[i][1].ToString());
ent.FundingPartner = Extension.ToEmpty(dt.Rows[i][2].ToString());
ent.AlertMessage = string.Empty;
ent.WithAlert = "N";
if (ent.DonorCode.Length > 10)
{
ent.AlertMessage += "<tr><td> - the Donor Code column must not must not exceed 10 characters </td></tr> ";
}
if (ent.DonorCode.Length == 0)
{
ent.AlertMessage += "<tr><td> - the Donor Code column is required </td></tr> ";
}
if (ent.DonorName.Length > 255)
{
ent.AlertMessage += "<tr><td> - the Donor Name column must not must not exceed 255 characters </td></tr> ";
}
if (ent.DonorName.Length == 0)
{
ent.AlertMessage += "<tr><td> - the Donor Name column is required </td></tr> ";
}
if (ent.FundingPartner.Length > 100)
{
ent.AlertMessage += "<tr><td> - the Funding Partner column must not must not exceed 100 characters </td></tr> ";
}
if (ent.AlertMessage.Length > 0)
{
ent.AlertMessage = "<table>" + ent.AlertMessage + "</table>";
ent.WithAlert = "S";
}
entlist.Add(ent);
}
Session["ListDonors"] = entlist;
objResult.data = entlist;
}
catch (Exception ex)
{
objResult.isError = true;
objResult.data = null;
objResult.message = "Donors :" + "Format error, check records";
return(Json(objResult));
}
objResult.isError = false;
objResult.message = null; // string.Format(MessageResource.SaveSuccess, "Load File save");
}
catch (Exception ex)
{
objResult.isError = true;
objResult.data = null;
objResult.message = "Error loading Donors";
}
return(Json(objResult));
}
private static void ExportList(List <ReportData> dueDates, string destination)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(destination, 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 = "Due Dates"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Create Header Row
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
headerRow.Append(
new Cell()
{
CellValue = new CellValue("FormMasterId"),
DataType = new EnumValue <CellValues>(CellValues.Number)
},
new Cell()
{
CellValue = new CellValue("Country"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Region"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("TaxFormCode"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Description"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Domains"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("DueDate"),
DataType = new EnumValue <CellValues>(CellValues.Date)
}
);
sheetData.AppendChild(headerRow);
// Add Data Rows to SheetData
foreach (ReportData rd in dueDates)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
newRow.Append(
new Cell()
{
CellValue = new CellValue(rd.FormMasterId.ToString()),
DataType = new EnumValue <CellValues>(CellValues.Number)
},
new Cell()
{
CellValue = new CellValue(rd.Country),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Region),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.TaxFormCode),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Description),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Domains),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.DueDate),
DataType = new EnumValue <CellValues>(CellValues.Date)
}
);
sheetData.AppendChild(newRow);
}
worksheetPart.Worksheet.Save();
}
}
public void SystemTest()
{
using (var driver = new ChromeDriver())
{
/* Opening Configuration File and Loading Init Data */
if (File.Exists("configurationfile.xml") == false)
{
throw new Exception("Configuration file do not exists in the program's directory.");
}
XmlDocument configurationFile = new XmlDocument();
configurationFile.Load("configurationfile.xml");
string projectName = configurationFile.SelectSingleNode("//project").InnerText;
string settingInProgess = configurationFile.SelectSingleNode("//setting_inprogress").InnerText;
string settingCompleted = configurationFile.SelectSingleNode("//setting_completed").InnerText;
if (projectName == String.Empty || settingInProgess == String.Empty || settingCompleted == String.Empty)
{
throw new Exception(String.Format("At least one of the configuration arguments is empty. ProjectName: {0}, SettingInProgressName: {1}, SettingCompletedName: {2}", projectName, settingInProgess, settingCompleted));
}
/* Initializing the Driver and Navigating to TMS Home Page */
var wait = new WebDriverWait(driver, TimeSpan.FromSeconds(60));
driver.Manage().Window.Maximize();
driver.Navigate().GoToUrl("https://tms.lionbridge.com/");
wait.Until(ExpectedConditions.UrlMatches("https://tms.lionbridge.com/"));
/**/
TMSProjectsPage tmsProjectsPage = new TMSProjectsPage(driver);
tmsProjectsPage.ClickChosenProject(projectName);
TMSProjectHomePage tmsProjectHomePage = new TMSProjectHomePage(driver);
tmsProjectHomePage.ChangeItemsPerPageToMinimum(driver);
tmsProjectHomePage.StatusClick();
TMSStatusPage tmsStatusPage = new TMSStatusPage(driver);
tmsStatusPage.AssingeeClick();
TMSAssigneesSubpage tmsAssigneesSubpage = new TMSAssigneesSubpage(driver);
tmsAssigneesSubpage.InitializeFiltersPanel(driver);
if (tmsAssigneesSubpage.AssigneeCount == 1)
{
throw new Exception("Activities drop down list is empty. Program now will shut down. ");
}
tmsAssigneesSubpage.ChoseActivityOption(driver, settingInProgess);
tmsAssigneesSubpage = new TMSAssigneesSubpage(driver);
/*PageNavBar pageNavBar = new PageNavBar(driver);
*
* if (pageNavBar.ItemsPerPage != null)
* {
* pageNavBar.ItemsPerPage.ChoseDropDownOption(driver, "1000");
* }*/
tmsAssigneesSubpage = new TMSAssigneesSubpage(driver);
AssigneeList assigneeList = new AssigneeList(driver);
List <AssigneeData> listAssigneeData = new List <AssigneeData>();
foreach (AssigneeItem assigneeItem in assigneeList.AssigneeItemsList)
{
listAssigneeData.Add(new AssigneeData(assigneeItem));
}
assigneeList.AssigneeItemsList[0].AssigneeItemElements[0].TagSingleJob(driver);
tmsAssigneesSubpage = new TMSAssigneesSubpage(driver);
tmsAssigneesSubpage.LeftMenu.JobsView.ButtonClick();
wait.Until(ExpectedConditions.ElementIsVisible(By.ClassName("r_L")));
JobList jobList = new JobList(driver);
jobList.JobShowHistory(driver, 0);
HistoryPopUp historyPopUp = new HistoryPopUp(driver);
historyPopUp.InitializeFiltersPanel(driver);
historyPopUp.ChoseSourceLanguageOption(driver, listAssigneeData[0].assigneeDataElements[0].sourceLanguage);
historyPopUp.ChoseTargetLanguageOption(driver, listAssigneeData[0].assigneeDataElements[0].targetLanguage);
historyPopUp.ChoseActivityOption(driver, settingCompleted);
HistoryList historyList = new HistoryList(driver);
//historyList.HistoryItemsList[0].HistoryItemElements[0].StepCompletedByClick(driver);
listAssigneeData[0].assigneeDataElements[0].translatorName = historyList.HistoryItemsList[0].HistoryItemElements[0].StepCompletedBy;
string path = Path.Combine(Directory.GetCurrentDirectory(), "TestFile.xlsx");
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new 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 = "mySheet"
};
sheets.Append(sheet);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
UInt32 rowIndex = 0;
foreach (var info in listAssigneeData)
{
var row = new Row()
{
RowIndex = rowIndex
};
var firstNameCell = new Cell()
{
CellReference = "A" + (rowIndex + 1)
};
firstNameCell.CellValue = new CellValue(info.assigneeDataElements[0].jobName);
firstNameCell.DataType = CellValues.String;
row.AppendChild(firstNameCell);
Cell secondNameCell = new Cell()
{
CellReference = "B" + (rowIndex + 1)
};
secondNameCell.CellValue = new CellValue(info.assigneeDataElements[0].sourceLanguage);
secondNameCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(secondNameCell);
Cell thirdNameCell = new Cell()
{
CellReference = "C" + (rowIndex + 1)
};
thirdNameCell.CellValue = new CellValue(info.assigneeDataElements[0].targetLanguage);
thirdNameCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(thirdNameCell);
Cell fourthNameCell = new Cell()
{
CellReference = "D" + (rowIndex + 1)
};
fourthNameCell.CellValue = new CellValue(info.assigneeDataElements[0].reviewerName);
fourthNameCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(fourthNameCell);
Cell fifthNameCell = new Cell()
{
CellReference = "E" + (rowIndex + 1)
};
fifthNameCell.CellValue = new CellValue(info.assigneeDataElements[0].translatorName);
fifthNameCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(fifthNameCell);
Cell sixthNameCell = new Cell()
{
CellReference = "F" + (rowIndex + 1)
};
sixthNameCell.CellValue = new CellValue(info.assigneeDataElements[0].effort);
sixthNameCell.DataType = CellValues.String;
row.AppendChild(sixthNameCell);
Cell seventhNameCell = new Cell()
{
CellReference = "G" + (rowIndex + 1)
};
seventhNameCell.CellValue = new CellValue(info.assigneeDataElements[0].wordcount);
seventhNameCell.DataType = CellValues.String;
row.AppendChild(seventhNameCell);
sheetData.AppendChild(row);
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
}
/// <summary>
/// </summary>
private Sheet CreateSheet(long PatNum, WebSheets.SheetAndSheetField sAnds)
{
Sheet newSheet = null;
try{
SheetDef sheetDef = new SheetDef((SheetTypeEnum)sAnds.web_sheet.SheetType);
newSheet = SheetUtil.CreateSheet(sheetDef, PatNum);
SheetParameter.SetParameter(newSheet, "PatNum", PatNum);
newSheet.DateTimeSheet = sAnds.web_sheet.DateTimeSheet;
newSheet.Description = sAnds.web_sheet.Description;
newSheet.Height = sAnds.web_sheet.Height;
newSheet.Width = sAnds.web_sheet.Width;
newSheet.FontName = sAnds.web_sheet.FontName;
newSheet.FontSize = sAnds.web_sheet.FontSize;
newSheet.SheetType = (SheetTypeEnum)sAnds.web_sheet.SheetType;
newSheet.IsLandscape = sAnds.web_sheet.IsLandscape == (sbyte)1?true:false;
newSheet.InternalNote = "";
newSheet.IsWebForm = true;
//loop through each variable in a single sheetfield
for (int i = 0; i < sAnds.web_sheetfieldlist.Count(); i++)
{
SheetField sheetfield = new SheetField();
sheetfield.FieldName = sAnds.web_sheetfieldlist[i].FieldName;
sheetfield.FieldType = (SheetFieldType)sAnds.web_sheetfieldlist[i].FieldType;
//sheetfield.FontIsBold=sAnds.web_sheetfieldlist[i].FontIsBold==(sbyte)1?true:false;
if (sAnds.web_sheetfieldlist[i].FontIsBold == (sbyte)1)
{
sheetfield.FontIsBold = true;
}
else
{
sheetfield.FontIsBold = false;
}
sheetfield.FontIsBold = sAnds.web_sheetfieldlist[i].FontIsBold == (sbyte)1?true:false;
sheetfield.FontName = sAnds.web_sheetfieldlist[i].FontName;
sheetfield.FontSize = sAnds.web_sheetfieldlist[i].FontSize;
sheetfield.Height = sAnds.web_sheetfieldlist[i].Height;
sheetfield.Width = sAnds.web_sheetfieldlist[i].Width;
sheetfield.XPos = sAnds.web_sheetfieldlist[i].XPos;
sheetfield.YPos = sAnds.web_sheetfieldlist[i].YPos;
//sheetfield.IsRequired=sAnds.web_sheetfieldlist[i].IsRequired==(sbyte)1?true:false;
if (sAnds.web_sheetfieldlist[i].IsRequired == (sbyte)1)
{
sheetfield.IsRequired = true;
}
else
{
sheetfield.IsRequired = false;
}
sheetfield.TabOrder = sAnds.web_sheetfieldlist[i].TabOrder;
sheetfield.ReportableName = sAnds.web_sheetfieldlist[i].ReportableName;
sheetfield.RadioButtonGroup = sAnds.web_sheetfieldlist[i].RadioButtonGroup;
sheetfield.RadioButtonValue = sAnds.web_sheetfieldlist[i].RadioButtonValue;
sheetfield.GrowthBehavior = (GrowthBehaviorEnum)sAnds.web_sheetfieldlist[i].GrowthBehavior;
sheetfield.FieldValue = sAnds.web_sheetfieldlist[i].FieldValue;
sheetfield.TextAlign = (HorizontalAlignment)sAnds.web_sheetfieldlist[i].TextAlign;
sheetfield.ItemColor = Color.FromArgb(sAnds.web_sheetfieldlist[i].ItemColor);
newSheet.SheetFields.Add(sheetfield);
} // end of j loop
Sheets.SaveNewSheet(newSheet);
return(newSheet);
}
catch (Exception e) {
gridMain.EndUpdate();
MessageBox.Show(e.Message);
}
return(newSheet);
}
public static CutLineAndSheet Create(ICutLine line, Sheet sheet, ISheet[] outputSheets)
{
return(new CutLineAndSheet(line, sheet, outputSheets));
}
private void RetrieveAndSaveData()
{
try {
#if DEBUG
//IgnoreCertificateErrors();// used with faulty certificates only while debugging.
#endif
WebSheets.Sheets wh = new WebSheets.Sheets();
wh.Timeout = 300000; //5 minutes. Default is 100000 (1.66667 minutes).
wh.Url = PrefC.GetString(PrefName.WebHostSynchServerURL);
string RegistrationKey = PrefC.GetString(PrefName.RegistrationKey);
if (wh.GetDentalOfficeID(RegistrationKey) == 0)
{
MsgBox.Show(this, "Registration key provided by the dental office is incorrect");
return;
}
List <WebSheets.SheetAndSheetField> listSheets;
List <long> listSkippedSheets = new List <long>();
int iterations = 0;
do //Because WebSheets are only downloaded 20 at a time, we need to get the sheets within a loop to download them all.
{
listSheets = wh.GetSheets(RegistrationKey) //Only gets the first 20 sheets.
//if we are not in HQ, filter out non-HQ sheets that don't match our current clinic
.Where(x => x.web_sheet.ClinicNum == 0 || Clinics.ClinicNum == 0 || x.web_sheet.ClinicNum == Clinics.ClinicNum).ToList();
iterations++;
List <long> SheetsForDeletion = new List <long>();
listSheets.RemoveAll(x => listSkippedSheets.Contains(x.web_sheet.SheetID)); //Remove all sheets that the user has already skipped.
if (listSheets.Count == 0)
{
if (iterations == 1)
{
MsgBox.Show(this, "No Patient forms retrieved from server");
}
else
{
MsgBox.Show(this, "All Patient forms retrieved from server");
}
return;
}
//loop through all incoming sheets
for (int i = 0; i < listSheets.Count; i++)
{
try { //this try catch is put so that a defective downloaded sheet does not stop other sheets from being downloaded.
long patNum = 0;
string lName = "";
string fName = "";
List <string> listPhoneNumbers = new List <string>();
string email = "";
DateTime bDate = DateTime.MinValue;
//loop through each field in this sheet to find First name, last name, and DOB
for (int j = 0; j < listSheets[i].web_sheetfieldlist.Count(); j++)
{
if (listSheets[i].web_sheetfieldlist[j].FieldName.ToLower().Contains("lname") ||
listSheets[i].web_sheetfieldlist[j].FieldName.ToLower().Contains("lastname"))
{
lName = listSheets[i].web_sheetfieldlist[j].FieldValue;
}
if (listSheets[i].web_sheetfieldlist[j].FieldName.ToLower().Contains("fname") ||
listSheets[i].web_sheetfieldlist[j].FieldName.ToLower().Contains("firstname"))
{
fName = listSheets[i].web_sheetfieldlist[j].FieldValue;
}
if (listSheets[i].web_sheetfieldlist[j].FieldName.ToLower().Contains("bdate") ||
listSheets[i].web_sheetfieldlist[j].FieldName.ToLower().Contains("birthdate"))
{
bDate = PIn.Date(listSheets[i].web_sheetfieldlist[j].FieldValue);
}
if (listSheets[i].web_sheetfieldlist[j].FieldName.ToLower().In("hmphone", "wkphone", "wirelessphone") &&
listSheets[i].web_sheetfieldlist[j].FieldValue != "")
{
listPhoneNumbers.Add(listSheets[i].web_sheetfieldlist[j].FieldValue);
}
if (listSheets[i].web_sheetfieldlist[j].FieldName.ToLower() == "email")
{
email = listSheets[i].web_sheetfieldlist[j].FieldValue;
}
}
if (bDate.Year < 1880)
{
//log invalid birth date format. Shouldn't happen, though.
}
List <long> listMatchingPats = Patients.GetPatNumsByNameBirthdayEmailAndPhone(lName, fName, bDate, email, listPhoneNumbers);
FormPatientPickWebForm FormPpw = new FormPatientPickWebForm(listSheets[i]);
FormPpw.LnameEntered = lName;
FormPpw.FnameEntered = fName;
FormPpw.BdateEntered = bDate;
if (listMatchingPats.Count == 0)
{
FormPpw.HasMoreThanOneMatch = false;
FormPpw.ShowDialog();
if (FormPpw.DialogResult == DialogResult.Cancel)
{
//user wants to stop importing altogether
//we will pick up where we left off here next time
wh.DeleteSheetData(RegistrationKey, SheetsForDeletion.ToArray());
return;
}
else if (FormPpw.DialogResult == DialogResult.Ignore)
{
//user wants to skip this patient import only
listSkippedSheets.Add(listSheets[i].web_sheet.SheetID);
continue;
//future feature suggestion... 4th state = discard
//mark this patient's import sheet for delete go to the next patient
//SheetsForDeletion.Add(listSheets[i].web_sheet.SheetID);
//continue
}
patNum = FormPpw.SelectedPatNum; //might be zero to indicate new patient
}
else if (listMatchingPats.Count > 1)
{
FormPpw.HasMoreThanOneMatch = true;
FormPpw.ShowDialog();
if (FormPpw.DialogResult == DialogResult.Cancel)
{
//user wants to stop importing altogether
//we will pick up where we left off here next time
wh.DeleteSheetData(RegistrationKey, SheetsForDeletion.ToArray());
return;
}
else if (FormPpw.DialogResult == DialogResult.Ignore)
{
//user wants to skip this patient import only
listSkippedSheets.Add(listSheets[i].web_sheet.SheetID);
continue;
}
patNum = FormPpw.SelectedPatNum; //might be zero to indicate new patient
}
else //Exactly one match was found so make a log entry what the match was.
{
patNum = listMatchingPats[0];
Patient pat = Patients.GetPat(patNum);
//Security log for OD automatically importing a sheet into a patient.
SecurityLogs.MakeLogEntry(Permissions.SheetEdit, patNum, Lan.g(this, "Web form import from:")
+ " " + lName + ", " + fName + " " + bDate.ToShortDateString() + "\r\n"
+ Lan.g(this, "Auto imported into:") + " " + pat.LName + ", " + pat.FName + " " + pat.Birthdate.ToShortDateString());
}
if (patNum == 0)
{
Patient newPat = CreatePatient(lName, fName, bDate, listSheets[i]);
patNum = newPat.PatNum;
//Security log for user creating a new patient.
SecurityLogs.MakeLogEntry(Permissions.SheetEdit, patNum, Lan.g(this, "Web form import from:")
+ " " + lName + ", " + fName + " " + bDate.ToShortDateString() + "\r\n"
+ Lan.g(this, "User created new pat:") + " " + newPat.LName + ", " + newPat.FName + " " + newPat.Birthdate.ToShortDateString());
}
//We should probably make a security log entry for a manually selected patient.
Sheet newSheet = SheetUtil.CreateSheetFromWebSheet(patNum, listSheets[i]);
Sheets.SaveNewSheet(newSheet);
if (DataExistsInDb(newSheet))
{
SheetsForDeletion.Add(listSheets[i].web_sheet.SheetID);
}
}
catch (Exception e) {
MessageBox.Show(e.Message);
}
} // end of for loop
wh.DeleteSheetData(RegistrationKey, SheetsForDeletion.ToArray());
} while(listSheets.Count > 0);
}
catch (Exception e) {
MessageBox.Show(e.Message);
return;
}
}
public Sheet SampleSheet(OrderRelatedElement[] orderRelatedElements)
{
return(Sheet.CreateNewSheet(orderRelatedElements, default, new Size(1000, 1500)));
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 = "F1"
});
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 = "C",
RowIndex = 2,
Text = "Цена",
StyleIndex = 0U
});
uint i = 1;
foreach (var service in info.Services)
{
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 = service.ServiceName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = i + 2,
Text = service.Price.ToString(),
StyleIndex = 0U
});
i++;
}
workbookpart.Workbook.Save();
}
}
public override async Task GenerateContent()
{
await Task.Factory.StartNew(() =>
{
Dictionary <Player, PlayerStats> data = new Dictionary <Player, PlayerStats>();
foreach (Demo demo in Demos)
{
foreach (Player player in demo.Players)
{
if (!data.ContainsKey(player))
{
data.Add(player, new PlayerStats());
}
data[player].MatchCount++;
data[player].RankMax = data[player].RankMax < player.RankNumberNew ? player.RankNumberNew : data[player].RankMax;
data[player].KillCount += player.KillCount;
data[player].AssistCount += player.AssistCount;
data[player].DeathCount += player.DeathCount;
data[player].FiveKillCount += player.FiveKillCount;
data[player].FourKillCount += player.FourKillCount;
data[player].ThreeKillCount += player.ThreeKillCount;
data[player].TwoKillCount += player.TwoKillCount;
data[player].OneKillCount += player.OneKillCount;
data[player].HeadshotCount += player.HeadshotCount;
data[player].TeamKillCount += player.TeamKillCount;
data[player].JumpKillCount += player.JumpKillCount;
data[player].CrouchKillCount += player.CrouchKillCount;
data[player].ClutchCount += player.ClutchCount;
data[player].ClutchWonCount += player.ClutchWonCount;
data[player].ClutchLostCount += player.ClutchLostCount;
data[player].Clutch1V1Count += player.Clutch1V1Count;
data[player].Clutch1V2Count += player.Clutch1V2Count;
data[player].Clutch1V3Count += player.Clutch1V3Count;
data[player].Clutch1V4Count += player.Clutch1V4Count;
data[player].Clutch1V5Count += player.Clutch1V5Count;
data[player].Clutch1V1WonCount += player.Clutch1V1WonCount;
data[player].Clutch1V2WonCount += player.Clutch1V2WonCount;
data[player].Clutch1V3WonCount += player.Clutch1V3WonCount;
data[player].Clutch1V4WonCount += player.Clutch1V4WonCount;
data[player].Clutch1V5WonCount += player.Clutch1V5WonCount;
data[player].Clutch1V1LossCount += player.Clutch1V1LossCount;
data[player].Clutch1V2LossCount += player.Clutch1V2LossCount;
data[player].Clutch1V3LossCount += player.Clutch1V3LossCount;
data[player].Clutch1V4LossCount += player.Clutch1V4LossCount;
data[player].Clutch1V5LossCount += player.Clutch1V5LossCount;
data[player].BombPlantedCount += player.BombPlantedCount;
data[player].BombDefusedCount += player.BombDefusedCount;
data[player].BombExplodedCount += player.BombExplodedCount;
data[player].MvpCount += player.RoundMvpCount;
data[player].ScoreCount += player.Score;
data[player].Rating += player.RatingHltv;
data[player].EseaRws += player.EseaRws;
data[player].RoundCount += player.RoundPlayedCount;
data[player].IsVacBanned = data[player].IsVacBanned || player.IsVacBanned;
data[player].IsOverwatchBanned = data[player].IsOverwatchBanned || player.IsOverwatchBanned;
data[player].FlashbangThrownCount += player.FlashbangThrownCount;
data[player].SmokeThrownCount += player.SmokeThrownCount;
data[player].HeGrenadeThrownCount += player.HeGrenadeThrownCount;
data[player].DecoyThrownCount += player.DecoyThrownCount;
data[player].MolotovThrownCount += player.MolotovThrownCount;
data[player].IncendiaryThrownCount += player.IncendiaryThrownCount;
data[player].DamageHealthCount += player.TotalDamageHealthCount;
data[player].DamageArmorCount += player.TotalDamageArmorCount;
data[player].EntryKillCount += player.EntryKills.Count;
data[player].EntryKillWinCount += player.EntryKillWonCount;
data[player].EntryKillLossCount += player.EntryKillLossCount;
data[player].EntryHoldKillCount += player.EntryHoldKills.Count;
data[player].EntryHoldKillWonCount += player.EntryHoldKillWonCount;
data[player].EntryHoldKillLossCount += player.EntryHoldKillLossCount;
data[player].TradeKillCount += player.TradeKillCount;
data[player].TradeDeathCount += player.TradeDeathCount;
data[player].AverageTimeDeathSeconds += player.AverageTimeDeath;
}
}
int rowCount = 1;
foreach (KeyValuePair <Player, PlayerStats> keyValuePair in data)
{
IRow row = Sheet.CreateRow(rowCount++);
int columnNumber = 0;
SetCellValue(row, columnNumber++, CellType.String, keyValuePair.Key.Name);
SetCellValue(row, columnNumber++, CellType.String, keyValuePair.Key.SteamId);
SetCellValue(row, columnNumber++, CellType.String, keyValuePair.Key.TeamName);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.MatchCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.KillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.AssistCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.DeathCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.KillPerDeath);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.HeadshotCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.HeadshotPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.RoundCount);
SetCellValue(row, columnNumber++, CellType.Numeric, Math.Round(keyValuePair.Value.EseaRws / keyValuePair.Value.MatchCount, 2));
SetCellValue(row, columnNumber++, CellType.Numeric, Math.Round(keyValuePair.Value.Rating / keyValuePair.Value.MatchCount, 2));
SetCellValue(row, columnNumber++, CellType.Numeric, Math.Round(keyValuePair.Value.AverageTimeDeathSeconds / keyValuePair.Value.MatchCount, 2));
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.FiveKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.FourKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.ThreeKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.TwoKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.OneKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.TradeKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.TradeDeathCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.TeamKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.JumpKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.CrouchKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.BombPlantedCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.BombDefusedCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.BombExplodedCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.MvpCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.ScoreCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.ClutchCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.ClutchWonCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.ClutchLostCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.ClutchWonPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V1Count);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V1WonCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V1LossCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V1WonPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V2Count);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V2WonCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V2LossCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V2WonPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V3Count);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V3WonCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V3LossCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V3WonPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V4Count);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V4WonCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V4LossCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V4WonPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V5Count);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V5WonCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V5LossCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.Clutch1V5WonPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.EntryKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.EntryKillWinCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.EntryKillLossCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.EntryKillWinPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.EntryHoldKillCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.EntryHoldKillWonCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.EntryHoldKillLossCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.EntryHoldKillWinPercent);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.KillPerRound);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.AssistPerRound);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.DeathPerRound);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.AverageDamagePerRound);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.DamageHealthCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.DamageArmorCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.FlashbangThrownCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.SmokeThrownCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.HeGrenadeThrownCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.DecoyThrownCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.MolotovThrownCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.IncendiaryThrownCount);
SetCellValue(row, columnNumber++, CellType.Numeric, keyValuePair.Value.RankMax);
SetCellValue(row, columnNumber++, CellType.Boolean, keyValuePair.Value.IsVacBanned);
SetCellValue(row, columnNumber, CellType.Boolean, keyValuePair.Value.IsOverwatchBanned);
}
});
}
public void Read_GroupingsReverseOrder()
{
var sheetConfig = new SheetConfig()
{
HeaderIdentifier = new Identifier()
{
Column = "A",
Value = "Policy Number"
},
Fields = new List <Field>()
{
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "B"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "C"
},
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "GRP_CT;D",
DefaultCommissionTypeCode = "unknown",
Types = new List <CommissionType>()
{
new CommissionType()
{
CommissionTypeCode = "type_1", Value = "comType3;code_1"
},
new CommissionType()
{
CommissionTypeCode = "type_2", Value = "comType3;code_3"
},
new CommissionType()
{
CommissionTypeCode = "type_3", Value = "comType1;code_2"
},
new CommissionType()
{
CommissionTypeCode = "type_4", Value = "comType1;code_3"
},
new CommissionType()
{
CommissionTypeCode = "type_5", Value = "comType2;code_2"
},
new CommissionType()
{
CommissionTypeCode = "type_6", Value = "comType2;code_1"
},
}
},
Groups = new List <Group>()
{
new Group()
{
FieldName = "BrokerFullName",
Column = "A",
Formatter = ".+?(?= : Broker)", //Take everything before ': Broker'
ReverseOrder = false,
Identifiers = new List <Identifier>()
{
new Identifier()
{
Column = "A", Value = ": Broker"
}, //Contains ': Broker'
new Identifier()
{
Column = "B", Value = "^(?![\\s\\S])"
}, //Empy text
}
},
new Group()
{
FieldName = "CommissionType",
Column = "A",
ReverseOrder = true,
Identifiers = new List <Identifier>()
{
new Identifier()
{
Column = "A", Value = "\\b"
}, //Any word
new Identifier()
{
Column = "B", Value = "\\d"
}, //Any number
new Identifier()
{
Column = "C", Value = "^(?![\\s\\S])"
}, //Empy text
new Identifier()
{
Column = "D", Value = "^(?![\\s\\S])"
}, //Empy text
}
},
},
};
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var config = new Config();
config.Sheets = new List <Sheet>()
{
sheet
};
var bytes = System.Convert.FromBase64String(GroupingsReverseOrder_Base64.STRING);
var stream = new MemoryStream(bytes);
var reader = new UniqueCommissionTypesReader(sheet);
var commissionTypeValues = reader.Read(stream).ToList();
Assert.Equal(6, commissionTypeValues.Count);
Assert.Equal("comType2;code_1", commissionTypeValues[0]);
Assert.Equal("comType2;code_2", commissionTypeValues[1]);
Assert.Equal("comType1;code_3", commissionTypeValues[2]);
Assert.Equal("comType1;code_2", commissionTypeValues[3]);
Assert.Equal("comType3;code_3", commissionTypeValues[4]);
Assert.Equal("comType3;code_1", commissionTypeValues[5]);
}
public void UploadUser()
{
GridView GridView1 = new GridView();
int r = Request.Files.Count;
HttpPostedFileBase file = Request.Files[0]; //Uploaded file
//Use the following properties to get file's name, size and MIMEType
int fileSize = file.ContentLength;
string fileName = file.FileName;
string[] values = fileName.Split('.');
string SheetName = values[0];
string filetype = file.GetType().ToString();
//OleDbConnection objConn = null;
//System.Data.DataTable dt1 = null;
//fileName = Server.MapPath(Path.Combine("~/Content/",Path.ChangeExtension(fileName, ".xlsx")));
//fileName = Server.MapPath(Path.Combine("~/Content/MyExcelFile_files/", Path.ChangeExtension(fileName, ".xlsx")));
//file.SaveAs(fileName);
fileName = Server.MapPath(Path.Combine("~/Content/", fileName));
file.SaveAs(Path.ChangeExtension(fileName, ".xlsx"));
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
//Read the first Sheet from Excel file.
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>();
//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.
DataTable dt = new DataTable();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
//Use the first row to add columns to DataTable.
if (row.RowIndex.Value == 1)
{
foreach (Cell cell in row.Descendants <Cell>())
{
dt.Columns.Add(GetValue(doc, cell));
}
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
i++;
}
}
}
//GridView1.DataSource = dt;
//GridView1.DataBind();
int AlreadyExist = 0; string PhoneNumber = "", EmailAddress = "", PhoneNumberSaved = "", EmailAddressSaved = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][1].ToString() != string.Empty)
{
var user = new ApplicationUser {
UserName = admin.GenerateUserName(), Email = dt.Rows[i][2].ToString(), PhoneNumber = dt.Rows[i][1].ToString()
};
int count = admin.GetUserDetails(generic.GetSubscriberId(User.Identity.GetUserId())).Where(c => c.PhoneNumber == dt.Rows[i][1].ToString() || c.Email == dt.Rows[i][2].ToString()).Count();
if (count == 0)
{
rm.InsertBulkUser(user.Id, generic.GetSubscriberId(User.Identity.GetUserId()), dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString(), dt.Rows[i][3].ToString(), dt.Rows[i][0].ToString(), dt.Rows[i][4].ToString());
PhoneNumberSaved = PhoneNumberSaved + dt.Rows[i][1].ToString() + ",";
EmailAddressSaved = EmailAddressSaved + dt.Rows[i][2].ToString() + ",";
}
else
{
AlreadyExist = AlreadyExist + 1;
PhoneNumber = PhoneNumber + dt.Rows[i][1].ToString() + ",";
EmailAddress = EmailAddress + dt.Rows[i][2].ToString() + ",";
}
}
}
doc.Close();
if (PhoneNumberSaved != "" && EmailAddressSaved != "")
{
ViewBag.BulkUserUpdated = true;
ViewBag.ExistedPhoneNumberSaved = PhoneNumberSaved.TrimEnd(',');
ViewBag.ExistedEmailSaved = PhoneNumberSaved.TrimEnd(',');
}
else if (PhoneNumber != "" && EmailAddress != "")
{
ViewBag.AlreadyExist = true;
ViewBag.ExistedPhoneNumber = PhoneNumber.TrimEnd(',');
ViewBag.ExistedEmail = EmailAddress.TrimEnd(',');
}
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
if (System.IO.File.Exists(fileName))
{
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
}
}
TempData["BulkUserUpdated"] = true;
}
protected override void Execute(NativeActivityContext context)
{
object misValue = Missing.Value;
string[] rang = null;
string R1 = null;
string R2 = null;
FileParth = @FilePath.Get(context);
SheetName = Sheet.Get(context);
string range = Range.Get(context);
try
{
ExcelHelper.Shared.Close_OpenedFile(FileParth);
//comment by ashu
//if (CommonLibrary.CustomException.CheckFileExists(FileParth) == true) //custom Exception
//{
char[] ch = { ':' };
if (range.Contains(":"))
{
//Logger.Log.Logger.LogData("Exception in " + DisplayName + ": invalid cell number", LogLevel.Error);
throw new Exception("Exception in " + DisplayName + ": invalid cell number");
}
else
{
R1 = range;
}
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlWorkBook = xlApp.Workbooks.Open(FileParth, misValue, false, misValue, misValue, misValue, true, misValue, misValue, misValue, misValue, misValue, false, misValue, misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[SheetName];
xlWorkSheet.Activate();
if (R1 != null)
{
xlRange = xlWorkSheet.get_Range(R1);
}
//if (R1 != null && R2 == null)
//{
// xlRange = xlWorkSheet.get_Range(R1);
//}
colorNumber = System.Convert.ToInt32((xlRange.Cells).Interior.Color);
color = System.Drawing.ColorTranslator.FromOle(colorNumber);
// Logger.Log.Logger.LogData("Color is " + color.ToString(), LogLevel.Info);
Result.Set(context, color);
// }
//if (File.Exists(FileParth))
//{
// throw (new CustomException.FileNotFoundException("FileNotFoundException Generated in GetCellColor Activity" + FileParth + " Does not Exist,"));
//}
}
catch (NullReferenceException e)
{
Logger.Log.Logger.LogData("NullReferenceException " + e.Message, LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
catch (Exception e)
{
Logger.Log.Logger.LogData("Exception " + e.Message, LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
xlWorkBook.Save();
xlWorkBook.Close(true, FileParth, Missing.Value);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
}
public HealthComponent(string componentName, int _MaxValue, bool _AllowAggravated, DamageCombinationType _CombinationType, ColumnId _column, Sheet parentSheet) : base(componentName, _column)
{
MaxValue.CurrentValue = _MaxValue;
AllowAggravated = _AllowAggravated;
CombinationType = _CombinationType;
Init(parentSheet);
}
public static void Reporte_exel(DataGridView DTG, String Nombre_Reporte)
{
DTG.SelectAll();
DataObject DTobj = DTG.GetClipboardContent();
int cellfin;
cellfin = DTG.ColumnCount;
datos(DTobj);
Microsoft.Office.Interop.Excel.Application excell;
Microsoft.Office.Interop.Excel.Workbook workbook;
Microsoft.Office.Interop.Excel.Worksheet Sheet;
object miobj = System.Reflection.Missing.Value;
excell = new Excel.Application();
excell.Visible = true;
int incre;
int Columnas, col;
col = DTG.ColumnCount / 26;
string Letracol = "ABCDEFEHIJKLMNOPQRSTUVWXYZ";
string Complementocol;
//Determinando la letra que se usara despues de la columna 26
if (col > 0)
{
Columnas = DTG.ColumnCount - (26 * col);
Complementocol = Letracol.ToString().Substring(col - 1, 1);
}
else
{
Columnas = DTG.ColumnCount;
Complementocol = "";
}
string ColumnaFinal;
incre = Encoding.ASCII.GetBytes("A")[0];
ColumnaFinal = Complementocol.ToString() + Convert.ToChar(incre + Columnas - 1).ToString();
workbook = excell.Workbooks.Add(miobj);
Sheet = workbook.Worksheets.get_Item(1);
Excel.Range rg = Sheet.Cells[4, 1];
Excel.Range Enc;
Excel.Range RN;
Excel.Range Report;
rg.Select();
for (int c = 0; c < DTG.ColumnCount; c++)
{
//Sheet.Cells[3, c + 1] = String.Format("{0}", DT.Columns[c].Caption);
Sheet.Cells[3, c + 1] = String.Format("{0}", DTG.Columns[c].HeaderText);
}
Sheet.PasteSpecial(rg, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
try
{
// nombre de la empresa
RN = Sheet.get_Range("A1", ColumnaFinal + "1");
RN.Font.Name = "Times New Roman";
//rango.Font.Color = Color.Blue;
RN.Font.Size = 14;
Sheet.Cells[1, 1] = "DISTRIBUIDORA MORAZAN SA DE CV";
RN.Merge();
RN.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//Nombre del Reporte
Report = Sheet.get_Range("A2", ColumnaFinal + "2");
Report.Font.Name = "Times New Roman";
Report.Font.Size = 12;
//"DETALLE " + " DEL " + FechaIni.ToString("dd-MM-yyyy") + " AL " + FechaFin.ToString("dd-MM-yyyy") + " ";
if (Nombre_Reporte != "" || Nombre_Reporte != null)
{
Sheet.Cells[2, 1] = "" + Nombre_Reporte + "" + " EMISION " + DateTime.Now.ToString();
}
else
{
Sheet.Cells[2, 1] = "REPORTE" + " EMISION " + DateTime.Now.ToString();
}
Report.Select();
Report.Merge();
Report.Font.Bold = true;
Report.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//ENCABEZDO DE COLUMNAS
Enc = Sheet.get_Range("A3", ColumnaFinal + 3);
Enc.Font.Name = "Times New Roman";
Enc.Font.Size = 9;
Enc.Borders.LineStyle = Excel.XlLineStyle.xlDouble;
Enc.Font.Bold = true;
}
catch (SystemException exec)
{
MessageBoxButtons bt1 = MessageBoxButtons.OK;
DialogResult result = MessageBox.Show(exec.ToString(), "!!!!ERROR!!!!", bt1, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
}
}
/* Export the list to excel file at the location provide by DependencyService */
public async System.Threading.Tasks.Task ExportDataToExcelAsync()
{
// Granted storage permission
var storageStatus = await CrossPermissions.Current.CheckPermissionStatusAsync(Permission.Storage);
if (storageStatus != PermissionStatus.Granted)
{
var results = await CrossPermissions.Current.RequestPermissionsAsync(new[] { Permission.Storage });
storageStatus = results[Permission.Storage];
}
if (Developers.Count() > 0)
{
try
{
string date = DateTime.Now.ToShortDateString();
date = date.Replace("/", "_");
var path = DependencyService.Get <IExportFilesToLocation>().GetFolderLocation() + "XFDevelopers" + date + ".xlsx";
FilePath = path;
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, 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 = "Xamarin Forms developers list"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("No", CellValues.String),
ConstructCell("FullName", CellValues.String),
ConstructCell("Phone", CellValues.String)
);
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Add each product
foreach (var d in Developers)
{
row = new Row();
row.Append(
ConstructCell(d.ID.ToString(), CellValues.String),
ConstructCell(d.FullName, CellValues.String),
ConstructCell(d.Phone, CellValues.String));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
MessagingCenter.Send(this, "DataExportedSuccessfully");
}
}
catch (Exception e)
{
Debug.WriteLine("ERROR: " + e.Message);
}
}
else
{
MessagingCenter.Send(this, "NoDataToExport");
}
}
public override Task WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
{
var query = context.Object is IQueryable ? (IQueryable)context.Object : ((IEnumerable)context.Object).AsQueryable();
var queryString = context.HttpContext.Request.QueryString;
var columns = queryString.Value.Contains("$select") ?
OutputFormatter.GetPropertiesFromSelect(queryString.Value, query.ElementType) :
OutputFormatter.GetProperties(query.ElementType);
var stream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
GenerateWorkbookStylesPartContent(workbookStylesPart);
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
var headerRow = new Row();
foreach (var column in columns)
{
headerRow.Append(new Cell()
{
CellValue = new CellValue(column.Key),
DataType = new EnumValue <CellValues>(CellValues.String)
});
}
sheetData.AppendChild(headerRow);
foreach (var item in query)
{
var row = new Row();
foreach (var column in columns)
{
var value = OutputFormatter.GetValue(item, column.Key);
var stringValue = $"{value}".Trim();
var cell = new Cell();
var underlyingType = column.Value.IsGenericType &&
column.Value.GetGenericTypeDefinition() == typeof(Nullable <>) ?
Nullable.GetUnderlyingType(column.Value) : column.Value;
var typeCode = Type.GetTypeCode(underlyingType);
if (typeCode == TypeCode.DateTime)
{
if (stringValue != string.Empty)
{
cell.CellValue = new CellValue()
{
Text = DateTime.Parse(stringValue).ToOADate().ToString()
};
cell.StyleIndex = (UInt32Value)1U;
}
}
else if (typeCode == TypeCode.Boolean)
{
cell.CellValue = new CellValue(stringValue.ToLower());
cell.DataType = new EnumValue <CellValues>(CellValues.Boolean);
}
else if (IsNumeric(typeCode))
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
row.Append(cell);
}
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
}
if (stream?.Length > 0)
{
stream.Seek(0, SeekOrigin.Begin);
}
return(stream.CopyToAsync(context.HttpContext.Response.Body));
}
