public byte[] Map(IEnumerable<Game> games)
{
var result = new MemoryStream();
var pack = new ExcelPackage();
ExcelWorksheet ws = pack.Workbook.Worksheets.Add("games");
CreateHeader(ws);
int row = 2;
foreach (Game game in games)
{
ws.Cells[row, 1].Value = game.Id.ToString();
ws.Cells[row, 2].Value = game.Slot.StartDateTime.ToString("MM/dd/yyyy");
ws.Cells[row, 3].Value = game.Slot.StartDateTime.ToString("hh:mm tt");
ws.Cells[row, 4].Value = string.Format("U{0}", game.Age);
ws.Cells[row, 5].Value = game.Level.ToString();
ws.Cells[row, 6].Value = game.Gender;
ws.Cells[row, 7].Value = game.Slot.Field.Description;
ws.Cells[row, 8].Value = game.Team1 != null ? game.Team1.FullName : string.Empty;
ws.Cells[row, 9].Value = game.Team2 != null ? game.Team2.FullName : string.Empty;
ws.Cells[row, 10].Value = game.Activity;
ws.Cells[row, 11].Value = "3";
ws.Cells[row, 12].Value = string.Format("Refs needed: {0}", game.AreRefereesNeeded ? "Yes" : "No");
ws.Cells[row, 13].Value = game.Notes;
row++;
}
pack.SaveAs(result);
return result.ToArray();
}
public void ReadStreamWithTemplateWorkSheet()
{
FileStream instream = new FileStream(@"Test\Worksheet.xlsx", FileMode.Open, FileAccess.Read);
MemoryStream stream = new MemoryStream();
using (ExcelPackage pck = new ExcelPackage(stream, instream))
{
var ws = pck.Workbook.Worksheets["Perf"];
Assert.AreEqual(ws.Cells["H6"].Formula, "B5+B6");
var wsHF=pck.Workbook.Worksheets["Header footer"];
Assert.AreEqual(wsHF.HeaderFooter.firstFooter.LeftAlignedText, "First Left");
Assert.AreEqual(wsHF.HeaderFooter.firstFooter.RightAlignedText, "First Right");
Assert.AreEqual(wsHF.HeaderFooter.evenFooter.CenteredText, "even Centered");
Assert.AreEqual(wsHF.HeaderFooter.oddFooter.LeftAlignedText, "odd Left");
Assert.AreEqual(wsHF.HeaderFooter.oddFooter.CenteredText,"odd Centered");
Assert.AreEqual(wsHF.HeaderFooter.oddFooter.RightAlignedText, "odd Right");
foreach (var cell in pck.Workbook.Names["Data"])
{
Assert.IsNotNull(cell);
}
pck.SaveAs(new FileInfo(@"Test\Worksheet2.xlsx"));
}
instream.Close();
}
public static string RunSample11(DirectoryInfo outputDir)
{
//Create a Sample10 directory...
if (!Directory.Exists(outputDir.FullName + @"\Sample11"))
{
outputDir.CreateSubdirectory("Sample11");
}
outputDir = new DirectoryInfo(outputDir + @"\Sample11");
//create FileInfo object...
FileInfo output = new FileInfo(outputDir.FullName + @"\Output.xlsx");
if (output.Exists)
{
output.Delete();
output = new FileInfo(outputDir.FullName + @"\Output.xlsx");
}
using (var package = new ExcelPackage(output))
{
AddIntegerValidation(package);
AddListValidationFormula(package);
AddListValidationValues(package);
AddTimeValidation(package);
AddDateTimeValidation(package);
ReadExistingValidationsFromPackage(package);
package.SaveAs(output);
}
return output.FullName;
}
protected void ImgExportToExcell_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
long RecordCount;
var kids = KidsUser_DataProvider.GetGeustUser(out RecordCount,
Name: txtSearchName.Text,
Family: txtSearchFamily.Text,
MelliCode: txtSearchMelliCode.Text,
EmailAddress: txtSearchEmailAddress.Text,
MobileNumber: txtSearchMobileNumber.Text,
SelectDistinct: true,
PageSize: 100000
);
string templatefilePath = Server.MapPath("~/AdminCP/Files/GeustUser/GeustUserReport.xlsx");
FileInfo templateFileInfo = new FileInfo(templatefilePath);
string NewfilePath = string.Format("~/AdminCP/Files/GeustUser/Temp/GeustUserReport_{0}.xlsx", PersianDateTime.Now.ToLongDateTimeString().Replace("/", "-").Replace(":", "-"));
FileInfo NewfileInfo = new FileInfo(Server.MapPath(NewfilePath));
ExcelPackage xlPackage = new ExcelPackage(templateFileInfo, true);
ExcelWorksheet workSheetGeustUser = xlPackage.Workbook.Worksheets["GeustUser"];
int i = 2;
foreach (var user in kids)
{
FillExcellRow(user, workSheetGeustUser, i);
i++;
}
xlPackage.SaveAs(NewfileInfo);
xlPackage.Dispose();
ClientRedirect(NewfilePath, 2000);
}
private void OpenAndExportWFTDAStatsReport()
{
try
{
FileInfo newFile = new FileInfo(openFileName);
ExcelPackage p = new ExcelPackage(newFile);
//Add the Content sheet
if (GameViewModel.Instance != null)
{
PopulateWftdaReport pop = new PopulateWftdaReport(p);
pop.PopulateReport();
}
FileInfo fi = new FileInfo(this.fileName);
p.SaveAs(fi);
System.Diagnostics.Process.Start(this.fileName);
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
}
public static void OutputExcel(HttpResponseBase response, string filename, ExcelPackage pck)
{
pck.SaveAs(response.OutputStream);
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("content-disposition", "attachment; filename=" + filename + ".xlsx");
response.AddHeader("Content-Length", pck.Stream.Length.ToString());
}
public void WriteVBA()
{
var package = new ExcelPackage();
package.Workbook.Worksheets.Add("Sheet1");
package.Workbook.CreateVBAProject();
package.Workbook.VbaProject.Modules["Sheet1"].Code += "\r\nPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)\r\nMsgBox(\"Test of the VBA Feature!\")\r\nEnd Sub\r\n";
package.Workbook.VbaProject.Modules["Sheet1"].Name = "Blad1";
package.Workbook.CodeModule.Name = "DenHärArbetsboken";
package.Workbook.Worksheets[1].Name = "FirstSheet";
package.Workbook.CodeModule.Code += "\r\nPrivate Sub Workbook_Open()\r\nBlad1.Cells(1,1).Value = \"VBA test\"\r\nMsgBox \"VBA is running!\"\r\nEnd Sub";
//X509Store store = new X509Store(StoreLocation.CurrentUser);
//store.Open(OpenFlags.ReadOnly);
//package.Workbook.VbaProject.Signature.Certificate = store.Certificates[11];
var m = package.Workbook.VbaProject.Modules.AddModule("Module1");
m.Code += "Public Sub Test(param1 as string)\r\n\r\nEnd sub\r\nPublic Function functest() As String\r\n\r\nEnd Function\r\n";
var c = package.Workbook.VbaProject.Modules.AddClass("Class1", false);
c.Code += "Private Sub Class_Initialize()\r\n\r\nEnd Sub\r\nPrivate Sub Class_Terminate()\r\n\r\nEnd Sub";
var c2 = package.Workbook.VbaProject.Modules.AddClass("Class2", true);
c2.Code += "Private Sub Class_Initialize()\r\n\r\nEnd Sub\r\nPrivate Sub Class_Terminate()\r\n\r\nEnd Sub";
package.Workbook.VbaProject.Protection.SetPassword("EPPlus");
package.SaveAs(new FileInfo(@"c:\temp\vbaWrite.xlsm"));
}
public void ReadWriteEncrypt()
{
using (ExcelPackage pck = new ExcelPackage(new FileInfo(@"Test\Drawing.xlsx"), true))
{
pck.Encryption.Password = "******";
pck.Encryption.Algorithm = EncryptionAlgorithm.AES192;
pck.Workbook.Protection.SetPassword("test");
pck.Workbook.Protection.LockStructure = true;
pck.Workbook.Protection.LockWindows = true;
pck.SaveAs(new FileInfo(@"Test\DrawingEncr.xlsx"));
}
using (ExcelPackage pck = new ExcelPackage(new FileInfo(_worksheetPath + @"\DrawingEncr.xlsx"), true, "EPPlus"))
{
pck.Encryption.IsEncrypted = false;
pck.SaveAs(new FileInfo(_worksheetPath + @"\DrawingNotEncr.xlsx"));
}
FileStream fs = new FileStream(_worksheetPath + @"\DrawingEncr.xlsx", FileMode.Open, FileAccess.ReadWrite);
using (ExcelPackage pck = new ExcelPackage(fs, "EPPlus"))
{
pck.Encryption.IsEncrypted = false;
pck.SaveAs(new FileInfo(_worksheetPath + @"DrawingNotEncr.xlsx"));
}
}
public ActionResult Excel()
{
var tbl = CriaDataTableProdutos();
IncluirNoDataTable("Arroz Tipo 1", "PCT", 12, 2, tbl);
IncluirNoDataTable("Feijão São João", "PCT", 7, 3, tbl);
IncluirNoDataTable("Macarrão Ferrari", "PCT", 8, (decimal) 2.3, tbl);
using (var pacote = new ExcelPackage())
{
//Cria uma pasta de Trabalho, no arquivo do Excel
var ws = pacote.Workbook.Worksheets.Add("Produtos");
//Carrega os dados para a planilha, inicia na celula A1, a primeira linha é o descricao dos campos usado na tabela
ws.Cells["A1"].LoadFromDataTable(tbl, true);
//Formata a coluna para numerico, valor unitario (4)
using (var col = ws.Cells[2, 4, 2 + tbl.Rows.Count, 4])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Formata o cabeçalho, 4 celulas (a1:d1)
using (var rng = ws.Cells["A1:F1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
rng.Style.Font.Color.SetColor(Color.White);
}
//Seta um texto para o Valor Total
ws.Cells["F1"].Value = "Valor Total";
//Formata a coluna do valor total, para numeric, e coloca uma formula
for (var i = 2; i <= tbl.Rows.Count + 1; i++)
{
ws.Cells["F" + i].Style.Numberformat.Format = "#,##0.00";
ws.Cells["F" + i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells["F" + i].Formula = string.Format(" {0}*{1}", "D" + i, "E" + i);
}
//Formata o valor total
var vTotal = tbl.Rows.Count + 2;
ws.Cells["F" + vTotal].Style.Font.Bold = true;
ws.Cells["F" + vTotal].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["F" + vTotal].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
ws.Cells["F" + vTotal].Style.Font.Color.SetColor(Color.White);
ws.Cells["F" + vTotal].Style.Numberformat.Format = "#,##0.00";
ws.Cells["F" + vTotal].Formula = string.Format("SUM({0},{1})", "F2", "F" + (vTotal - 1));
//gera o arquivo para download
var stream = new MemoryStream();
pacote.SaveAs(stream);
const string nomeDoArquivo = "RelatorioDeProdutos.xlsx";
const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
stream.Position = 0;
return File(stream, contentType, nomeDoArquivo);
}
}
public Task<HttpResponseMessage> ExecuteAsync(CancellationToken cancellationToken)
{
var stream = new MemoryStream();
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Report");
worksheet.Cells["A1"].LoadFromCollection(_data, true);
const string dateFormat = "dd/mm/yyyy";
worksheet.Cells[1, 2, _data.Length + 1, 2].Style.Numberformat.Format = dateFormat;
worksheet.Cells[1, 6, _data.Length + 1, 6].Style.Numberformat.Format = dateFormat;
worksheet.Cells.AutoFitColumns();
package.SaveAs(stream);
}
var responseMessage = new HttpResponseMessage(HttpStatusCode.OK)
{Content = new ByteArrayContent(stream.GetBuffer())};
responseMessage.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = _filename
};
responseMessage.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
return Task.FromResult(responseMessage);
}
public void ItCreatesAnExcelFile()
{
ExcelWorksheet worksheet;
var playedGames = new List<PlayedGameExportModel>
{
new PlayedGameExportModel
{
DateCreated = DateTime.UtcNow,
DatePlayed = DateTime.UtcNow.AddDays(-2),
GameDefinitionId = 1,
GamingGroupId = 12,
Id = 123,
Notes = "some notes",
NumberOfPlayers = 3,
BoardGameGeekGameDefinitionId = 1234,
GameDefinitionName = "the game name",
WinningPlayerIds = "12345|123456",
WinningPlayerNames = "Jim|Jane"
}
};
using (MemoryStream memoryStream = new MemoryStream())
{
using (var package = new ExcelPackage(memoryStream))
{
worksheet = package.Workbook.Worksheets.Add("NemeStats Played Games");
worksheet.Cells[1, 1].Value = playedGames[0].GameDefinitionName;
FileInfo file = new FileInfo(@"C:\temp\nemestats_export.xlsx");
package.SaveAs(file);
}
}
}
public ActionResult Generate(int CountryID, string Languaje_country, int? HospitalID, int? Year, int? WeekFrom, int? WeekTo)
{
try {
var ms = new MemoryStream();
var Languaje_country_ = db.Countries.Find(CountryID);
using (var fs = System.IO.File.OpenRead(ConfigurationManager.AppSettings["FluIDTemplate"].Replace("{countryId}", CountryID.ToString()))) {
using (var excelPackage = new ExcelPackage(fs)) {
var excelWorkBook = excelPackage.Workbook;
AppendDataToExcel(CountryID, Languaje_country_.Language.ToString(), Year, HospitalID, WeekFrom, WeekTo, excelWorkBook, "FLUID_NATIONAL_VIRUSES", 6, 1,false);
AppendDataToExcel(CountryID, Languaje_country_.Language.ToString(), Year, HospitalID, WeekFrom, WeekTo, excelWorkBook, "FLUID_IRAG", 9, 4, false);
//AppendDataToExcel(CountryID, Languaje_country_.Language.ToString(), Year, HospitalID, WeekFrom, WeekTo, excelWorkBook, "FluidIragExport", 33, "Fallecidos IRAG");
excelPackage.SaveAs(ms);
}
}
ms.Position = 0;
return new FileStreamResult(ms, "application/xlsx") {
FileDownloadName = "FluID.xlsx"
};
//ViewBag.Message = $"Report generated successfully!";
} catch (Exception e) {
ViewBag.Message = "Reporte no pudo generarse, por favor ponganse en contacto con el administrador";
}
return View();
}
public void ReadBug12()
{
var package = new ExcelPackage(new FileInfo(@"c:\temp\bug.xlsx"));
var ws = package.Workbook.Worksheets[1];
ws.Cells["A1"].Value = 1;
package.SaveAs(new FileInfo(@"c:\temp\bug2.xlsx"));
}
public static byte[] SaveWorkbookToBytes(ExcelPackage package)
{
using (var stream = new MemoryStream()) {
package.SaveAs(stream);
return stream.ToArray();
}
}
protected void ExportToExcel_Click(object sender, EventArgs e)
{
var products = GetProducts();
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Products");
var totalCols = products.Columns.Count;
var totalRows = products.Rows.Count;
for (var col = 1; col <= totalCols; col++)
{
workSheet.Cells[1, col].Value = products.Columns[col - 1].ColumnName;
}
for (var row = 1; row <= totalRows; row++)
{
for (var col = 0; col < totalCols; col++)
{
workSheet.Cells[row + 1, col + 1].Value = products.Rows[row - 1][col];
}
}
using (var memoryStream = new MemoryStream())
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename= BuyerDetails.xlsx");
excel.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
private void ExportWFTDAStatsReport()
{
using (ExcelPackage p = new ExcelPackage())
{
try
{
p.Workbook.Properties.Author = "RDNation.com v" + ScoreboardConfig.SCOREBOARD_VERSION_NUMBER;
p.Workbook.Properties.Title = "WFTDA Report For " + GameViewModel.Instance.GameName;
Color tabPink = Color.FromArgb(255, 138, 255);
ExportIBRF(p);
ExportScores(p);
ExportPenalties(p);
ExportLineups(p);
ExportBoutSummary(p);
ExportPenaltySummary(p);
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
FileInfo fi = new FileInfo(this.fileName);
p.SaveAs(fi);
}
}
/// <summary>
/// Exports the scan result.
/// </summary>
/// <param name="tempFilename">The temporary filename.</param>
/// <param name="dt">The data in DataTable.</param>
public static void ExportScanResult(String tempFilename, DataTable dt)
{
MemoryStream ms = new MemoryStream(Properties.Resources.XportScanResults);
ms.Seek(0, SeekOrigin.Begin);
ExcelPackage pck = new ExcelPackage(ms);
GenerateScanReport(pck, dt);
MemoryStream msOut = new MemoryStream();
pck.SaveAs(msOut);
ms.Close();
ms.Dispose();
msOut.Seek(0, SeekOrigin.Begin);
byte[] resutls = msOut.ToArray();
msOut.Close();
msOut.Dispose();
using (BinaryWriter b = new BinaryWriter(File.Open(tempFilename, FileMode.Create)))
{
b.Write(resutls);
}
}
public void ReadBug11()
{
var package = new ExcelPackage(new FileInfo(@"c:\temp\sample.xlsx"));
var ws = package.Workbook.Worksheets[1];
var pck2 = new ExcelPackage();
pck2.Workbook.Worksheets.Add("Test", ws);
pck2.SaveAs(new FileInfo(@"c:\temp\SampleNew.xlsx"));
}
public void DecrypTestBug()
{
var p = new ExcelPackage(new FileInfo(@"c:\temp\bug\TestExcel_2040.xlsx"), "");
var n = p.Workbook.Worksheets[1].Name;
p.Encryption.Password = null;
p.SaveAs(new FileInfo(@"c:\temp\encrNew.xlsx"));
}
public void Resign()
{
var package = new ExcelPackage(new FileInfo(@"c:\temp\vbaWrite.xlsm"));
X509Store store = new X509Store(StoreLocation.CurrentUser);
store.Open(OpenFlags.ReadOnly);
package.Workbook.VbaProject.Signature.Certificate = store.Certificates[11];
package.SaveAs(new FileInfo(@"c:\temp\vbaWrite2.xlsm"));
}
public void Save(ExcelPackage excelPackage, ExcelPackageModel excelPackageModel)
{
_log.Debug(string.Format("Saving ExcelPackage to {0}", excelPackageModel.SaveFilePath));
var fileInfo = File.Create(excelPackageModel.SaveFilePath);
excelPackage.SaveAs(fileInfo);
}
private static void OpenAndSave(string updatedExcelFile, string excelFile)
{
File.Delete(updatedExcelFile);
File.Copy(excelFile, updatedExcelFile);
using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFile)))
{
package.SaveAs(new FileInfo(updatedExcelFile));
}
}
public void ReadBug()
{
var file = new FileInfo(@"c:\temp\Adenoviridae Protocol.xlsx");
using (ExcelPackage pck = new ExcelPackage(file))
{
pck.Workbook.Worksheets[1].Cells["G4"].Value=12;
pck.SaveAs(new FileInfo(@"c:\temp\Adenoviridae Protocol2.xlsx"));
}
}
public bool Export(Dictionary<string, List<ReportingEventDataEntity>> eventsPerName, string fileName)
{
try
{
using(var package = new ExcelPackage())
{
foreach(var kvp in eventsPerName)
{
var worksheet = package.Workbook.Worksheets.Add(kvp.Key);
var allAvailableProperties =
kvp.Value.SelectMany(evt => evt.Properties.Keys.ToList()).Distinct().ToList();
var column = 1;
var row = 1;
worksheet.Cells[row, column].Value = "EventName";
worksheet.Cells[row, column++].Style.Font.Bold = true;
worksheet.Cells[row, column].Value = "UserName";
worksheet.Cells[row, column++].Style.Font.Bold = true;
worksheet.Cells[row, column].Value = "EventTime";
worksheet.Cells[row, column++].Style.Font.Bold = true;
foreach(var property in allAvailableProperties)
{
worksheet.Cells[row, column].Value = property;
worksheet.Cells[row, column++].Style.Font.Bold = true;
}
foreach(var evt in kvp.Value)
{
row++;
column = 1;
worksheet.Cells[row, column++].Value = evt.Name;
worksheet.Cells[row, column++].Value = evt.UserName;
worksheet.Cells[row, column++].Value = evt.EventTime;
foreach(var property in allAvailableProperties)
worksheet.Cells[row, column++].Value = evt.Properties[property];
}
}
package.SaveAs(new FileInfo(fileName));
}
}
catch(Exception e)
{
File.AppendAllText("errors.txt", e.Message);
return false;
}
return true;
}
public void ReadBlankStream()
{
MemoryStream stream = new MemoryStream();
using (ExcelPackage pck = new ExcelPackage(stream))
{
var ws = pck.Workbook.Worksheets["Perf"];
pck.SaveAs(stream);
}
stream.Close();
}
public static Boolean ExportDataSet(DataSet ds, string filePath)
{
ExcelPackage package = new ExcelPackage();
for (int i = 0; i < ds.Tables.Count; i++)
{
package = writeToSheet(package, ds.Tables[i]);
}
package.SaveAs(new FileInfo(filePath));
return true;
}
public void AllDrawingsInsideMarkupCompatibility()
{
string workbooksDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\workbooks");
// This is codeplex issue 15028: Making an unrelated change to an Excel file that contains drawings that ALL exist
// inside MarkupCompatibility/Choice nodes causes the drawings.xml file to be incorrectly garbage collected
// when an unrelated change is made.
string path = Path.Combine(workbooksDir, "AllDrawingsInsideMarkupCompatibility.xlsm");
// Load example document.
_pck = new ExcelPackage(new FileInfo(path));
// Verify the drawing part exists:
Uri partUri = new Uri("/xl/drawings/drawing1.xml", UriKind.Relative);
Assert.IsTrue(_pck.Package.PartExists(partUri));
// The Excel Drawings NamespaceManager from ExcelDrawing.CreateNSM:
NameTable nt = new NameTable();
var xmlNsm = new XmlNamespaceManager(nt);
xmlNsm.AddNamespace("a", ExcelPackage.schemaDrawings);
xmlNsm.AddNamespace("xdr", ExcelPackage.schemaSheetDrawings);
xmlNsm.AddNamespace("c", ExcelPackage.schemaChart);
xmlNsm.AddNamespace("r", ExcelPackage.schemaRelationships);
xmlNsm.AddNamespace("mc", ExcelPackage.schemaMarkupCompatibility);
XmlDocument drawingsXml = new XmlDocument();
drawingsXml.PreserveWhitespace = false;
XmlHelper.LoadXmlSafe(drawingsXml, _pck.Package.GetPart(partUri).GetStream());
// Verify that there are the correct # of drawings:
Assert.AreEqual(drawingsXml.SelectNodes("//*[self::xdr:twoCellAnchor or self::xdr:oneCellAnchor or self::xdr:absoluteAnchor]", xmlNsm).Count, 5);
// Make unrelated change. (in this case a useless additional worksheet)
_pck.Workbook.Worksheets.Add("NewWorksheet");
// Save it out.
string savedPath = Path.Combine(workbooksDir, "AllDrawingsInsideMarkupCompatibility2.xlsm");
_pck.SaveAs(new FileInfo(savedPath));
_pck.Dispose();
// Reload the new saved file.
_pck = new ExcelPackage(new FileInfo(savedPath));
// Verify the drawing part still exists.
Assert.IsTrue(_pck.Package.PartExists(new Uri("/xl/drawings/drawing1.xml", UriKind.Relative)));
drawingsXml = new XmlDocument();
drawingsXml.PreserveWhitespace = false;
XmlHelper.LoadXmlSafe(drawingsXml, _pck.Package.GetPart(partUri).GetStream());
// Verify that there are the correct # of drawings:
Assert.AreEqual(drawingsXml.SelectNodes("//*[self::xdr:twoCellAnchor or self::xdr:oneCellAnchor or self::xdr:absoluteAnchor]", xmlNsm).Count, 5);
// Verify that the new worksheet exists:
Assert.IsNotNull(_pck.Workbook.Worksheets["NewWorksheet"]);
// Cleanup:
File.Delete(savedPath);
}
public ActionResult DownloadExcel(DateTime startDate, DateTime endDate, string portal)
{
int portalId = 0;
int.TryParse(portal, out portalId);
SubscriptionHistoryRepository subscriptionHistoryRepository =
new SubscriptionHistoryRepository();
//get data from database
var subscriptionHistory = subscriptionHistoryRepository.GetSubscriptionHistory
(portalId,
startDate,
endDate);
FileInfo template = new FileInfo(
Server.MapPath("~/ExcelTemplate/8eSieugameClubreport.xlsx"));
using (ExcelPackage pck = new ExcelPackage(template, true))
{
ExcelWorksheet wsDaily = pck.Workbook.Worksheets["8e Sieugame-Daily"];
int startRow = 3;
foreach (SubscriptionHistory subHistory in subscriptionHistory)
{
int columnIndex = 1;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory
.DateLogged
.DayOfWeek
.ToString();
wsDaily.Cells[startRow, columnIndex++].Value = subHistory
.DateLogged
.ToString("MM/dd/yyyy");
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Visit;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Active;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Billed;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.New;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Pending;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Renewals;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Suspended;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Total;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Canceled;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Unsubscribed;
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Downloads;
columnIndex++;//blank column
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.PaidDownloadRevenue;
columnIndex++;//blank column
wsDaily.Cells[startRow, columnIndex++].Value = subHistory.TurnoverAmount;
startRow++;
}
Response.Clear();
pck.SaveAs(Response.OutputStream);
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition",
"attachment; filename=8eSieugameClubreport.xlsx");
}
return new EmptyResult();
}
public void TestMethod1()
{
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet worksheet = pck.Workbook.Worksheets.Add("Detalle IGS");
//worksheet.Cells[2, 2].Value = "RESULTADOS " + GlosaMeses[informe.Fecha.Month-1].ToUpper() + " IGS ZONALES";
using (ExcelRange rng = worksheet.Cells[2, 2, 4, 8])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#99CCFF"));
rng.Style.Font.Color.SetColor(Color.Black);
}
const int filaBase = 5;
int filaActual = filaBase;
using (ExcelRange rng = worksheet.Cells[filaActual, 2, filaActual, 8])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#969696"));
rng.Style.Font.Color.SetColor(Color.White);
}
worksheet.Cells[filaActual, 2].Value = "Jefe Zonal";
worksheet.Cells[filaActual, 3].Value = "N° Local";
worksheet.Cells[filaActual, 4].Value = "Cuenta de IGS";
worksheet.Cells[filaActual, 5].Value = "Promedio de IGS2";
worksheet.Cells[filaActual, 5].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#FFFF00"));
worksheet.Cells[filaActual, 5].Style.Font.Color.SetColor(Color.Black);
worksheet.Cells[filaActual, 6].Value = "Promedio de I4P";
worksheet.Cells[filaActual, 6].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#000080"));
worksheet.Cells[filaActual, 7].Value = "Promedio de IA";
worksheet.Cells[filaActual, 7].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#008080"));
worksheet.Cells[filaActual, 8].Value = "Promedio de IP";
worksheet.Cells[filaActual, 8].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#FF0000"));
using (ExcelRange rng = worksheet.Cells[filaBase, 5, filaActual, 8])
{
rng.Style.Numberformat.Format = "#.0";
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
using (ExcelRange rng = worksheet.Cells[filaActual, 2, filaActual, 8])
{
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#FFFF99"));
}
pck.SaveAs(new FileInfo("c:\\temp\\bug1.xlsx"));
}
}
private static void DuplicateExcelSheetByClosedXML(string baseDir, string bookPath)
{
var outPath = Path.Combine(baseDir, "Book1 - EPPlus.xlsx");
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(bookPath)))
using (var book = package.Workbook)
{
book.Worksheets.Add("Sheet2", book.Worksheets["Sheet1"]);
package.SaveAs(new FileInfo(outPath));
}
}
private void btnExport_Click(object sender, EventArgs e)
{
var dia = new System.Windows.Forms.SaveFileDialog();
dia.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
dia.Filter = "Excel Worksheets (*.xlsx)|*.xlsx|xls file (*.xls)|*.xls|All files (*.*)|*.*";
if (dia.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
{
var excel = new OfficeOpenXml.ExcelPackage();
var ws = excel.Workbook.Worksheets.Add("worksheet-name");
ws.Cells["A1"].LoadFromDataTable(this._result, true, OfficeOpenXml.Table.TableStyles.Light1);
ws.Cells[ws.Dimension.Address.ToString()].AutoFitColumns();
using (var file = File.Create(dia.FileName))
excel.SaveAs(file);
}
}
public void Convert(string filePath)
{
using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage())
{
foreach (JProperty jProperty in _jObjectToConvert.Properties())
{
JArray jContentArray = (JArray)jProperty.Value;
var worksheet = _template.Worksheets.Where(e => e.SheetName == jProperty.Name).FirstOrDefault();
var excelWorksheet = excelPackage.Workbook.Worksheets.Add(jProperty.Name);
CreateHeader(excelWorksheet, worksheet);
int rowIndex = 2;
int colIndex = 1;
foreach (JObject jObject in jContentArray)
{
colIndex = 1;
string statusDetails = "";
foreach (var errorItem in (JArray)jObject["StatusDetails"])
{
if (statusDetails.Length > 0)
{
statusDetails += "\r\n";
}
statusDetails = errorItem.ToString();
}
excelWorksheet.Cells[rowIndex, colIndex++].Value = jObject["Status"].ToString();
excelWorksheet.Cells[rowIndex, colIndex++].Value = statusDetails;
foreach (var column in worksheet.Columns)
{
object cellValue = jObject[column.Name].ToString();
int iCellvalue = 0;
if (Int32.TryParse(cellValue.ToString(), out iCellvalue))
{
cellValue = iCellvalue;
}
excelWorksheet.Cells[rowIndex, colIndex++].Value = cellValue;
}
rowIndex++;
}
}
excelPackage.SaveAs(new FileInfo(filePath));
}
}
public static void SaveToExcel <T>(List <VProject> list, string filePath)
{
ExcelPackage ep = new OfficeOpenXml.ExcelPackage();
ExcelWorkbook wb = ep.Workbook;
ExcelWorksheet ws = wb.Worksheets.Add(typeof(T).Name);
//配置文件属性
//wb.Properties.Category = "类别";
//wb.Properties.Author = "作者";
//wb.Properties.Comments = "备注";
//wb.Properties.Company = "公司";
//wb.Properties.Keywords = "关键字";
//wb.Properties.Manager = "管理者";
//wb.Properties.Status = "内容状态";
//wb.Properties.Subject = "主题";
//wb.Properties.Title = "标题";
//wb.Properties.LastModifiedBy = "最后一次保存者";
FileInfo file = new FileInfo(filePath);
//ws.Cells["A1"].LoadFromCollection(data, true, TableStyles.Medium10);
ws.Cells["A1"].Value = "Project Type";
ws.Cells["B1"].Value = "USCode";
ws.Cells["C1"].Value = "Store Name";
ws.Cells["D1"].Value = "Asset Actor";
ws.Cells["E1"].Value = "City";
ws.Cells["F1"].Value = "Create Time";
ws.Cells["G1"].Value = "Status";
ws.Cells["H1"].Value = "Reimage 1st Rd Filter";
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
ws.Cells[i + 2, 1].Value = item.FlowCode;
ws.Cells[i + 2, 2].Value = item.USCode;
ws.Cells[i + 2, 3].Value = item.StoreNameZHCN;
ws.Cells[i + 2, 4].Value = item.AssetActorNameENUS;
ws.Cells[i + 2, 5].Value = item.CityZHCN;
ws.Cells[i + 2, 6].Value = item.CreateTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
ws.Cells[i + 2, 7].Value = item.Status == ProjectStatus.UnFinish ? "Active" : item.Status.ToString();
ws.Cells[i + 2, 8].Value = item.HoldingProjectStatus.HasValue ? item.HoldingProjectStatus.ToString() : "";
}
ep.SaveAs(file);
}
public ActionResult ReporteExcel(string stado, string id, string etiqueta, string tarea, string label, string FechaIncio, string FechaFin, int numPag, int allReg, int Cant)
{
try
{
//Ruta de la plantilla
FileInfo fTemplateFile = new FileInfo(Server.MapPath("/") + "Reporte/Venta/excel/" + "Tarea.xlsx");
var Usuario = Authentication.UserLogued.Usuario;
//Ruta del nuevo documento
FileInfo fNewFile = new FileInfo(Server.MapPath("/") + "Reporte/Venta/excel/" + "Tarea" + "_" + Usuario + ".xlsx");
List <ETareas> ListaCompra;
ListaCompra = General.ListaTarea(stado, id, etiqueta, tarea, label, FechaIncio, FechaFin, numPag, allReg, Cant);
ExcelPackage pck = new ExcelPackage(fNewFile, fTemplateFile);
var ws = pck.Workbook.Worksheets[1];
////** aqui
DateTime today = DateTime.Now;
ws.Cells[6, 2].Value = today;
ws.Cells[7, 2].Value = Usuario;
int iFilaDetIni = 10;
int starRow = 1;
foreach (ETareas Detalle in ListaCompra)
{
ws.Cells[iFilaDetIni + starRow, 1].Value = Detalle.item;
ws.Cells[iFilaDetIni + starRow, 2].Value = Detalle.stado;
ws.Cells[iFilaDetIni + starRow, 3].Value = Detalle.etiqueta;
ws.Cells[iFilaDetIni + starRow, 4].Value = Detalle.tarea;
ws.Cells[iFilaDetIni + starRow, 5].Value = Detalle.label + "-" + Detalle.descripcion;
ws.Cells[iFilaDetIni + starRow, 6].Value = Detalle.id;
ws.Cells[iFilaDetIni + starRow, 7].Value = Detalle.direcion;
ws.Cells[iFilaDetIni + starRow, 8].Value = Detalle.inicio;
ws.Cells[iFilaDetIni + starRow, 9].Value = Detalle.fin;
ws.Cells[iFilaDetIni + starRow, 10].Value = Detalle.llegada;
ws.Cells[iFilaDetIni + starRow, 11].Value = Detalle.duracion;
starRow++;
}
int Count = starRow + 1;
string modelRange = "A" + System.Convert.ToString(iFilaDetIni) + ":K" + (iFilaDetIni + Count - 1).ToString();
var modelTable = ws.Cells[modelRange];
modelTable.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
//Guardando Archivo...
pck.Save();
// Liberando...
pck.Dispose();
OfficeOpenXml.ExcelPackage pcks = new OfficeOpenXml.ExcelPackage(fNewFile, false);
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + "ReporteTarea" + "_" + Usuario + ".xlsx");
MemoryStream memoryStream = new MemoryStream();
pcks.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.End();
pcks.Dispose();
System.IO.File.Delete(fNewFile.FullName);
return(View());
}
catch (Exception e)
{
throw e;
}
}
protected void btnDownloadSummary_Click(object sender, EventArgs e)
{
String ConnString = ConfigurationManager.ConnectionStrings["ConcordConnectionString_Report"].ConnectionString;
using (SqlConnection conn = new SqlConnection(ConnString))
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
using (SqlCommand command = new SqlCommand("DownloadSummaryReport", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@region", ddlRegion.SelectedValue);
if (!string.IsNullOrEmpty(ddlMarket.SelectedValue) && ddlMarket.SelectedValue != "-- Select --")
{
command.Parameters.AddWithValue("@market", ddlMarket.SelectedValue);
}
adapter.SelectCommand = command;
conn.Open();
adapter.Fill(dt);
}
}
conn.Close();
using (MemoryStream xlFileStream = new MemoryStream())
{
using (OfficeOpenXml.ExcelPackage xlPkg = new OfficeOpenXml.ExcelPackage())
{
int xlRowCnt = 1;
int xlColCnt = 1;
string sheetName = "Download Intake Summary";
OfficeOpenXml.ExcelWorksheet xlWrkSht1 = xlPkg.Workbook.Worksheets.Add(sheetName);
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Region";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Market";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Requestor ";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Site_ID ";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Status";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Desktop_Analysis_Request_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Los_Upload_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Prelim_Design_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Final_Design_Requested_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Final_Design_Completed_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "PCN_Filed_date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "PCN_Cleared_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "BOM_Sent_to_Market_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "FCC_601_Filed_Date";
xlWrkSht1.Select("A1:N1");
xlWrkSht1.SelectedRange.Style.Font.Bold = true;
xlWrkSht1.SelectedRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
xlWrkSht1.SelectedRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#B4C6E7"));
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
xlRowCnt++;
xlWrkSht1.Cells[xlRowCnt, 1].Value = Convert.ToString(dt.Rows[i]["Region"]);
xlWrkSht1.Cells[xlRowCnt, 2].Value = Convert.ToString(dt.Rows[i]["Market"]);
xlWrkSht1.Cells[xlRowCnt, 3].Value = Convert.ToString(dt.Rows[i]["Requester"]);
xlWrkSht1.Cells[xlRowCnt, 4].Value = Convert.ToString(dt.Rows[i]["Site_ID"]);
xlWrkSht1.Cells[xlRowCnt, 5].Value = Convert.ToString(dt.Rows[i]["Status"]);
xlWrkSht1.Cells[xlRowCnt, 6].Value = ((Convert.ToString(dt.Rows[i]["Desktop_Analysis_Request_Date"]) != null && Convert.ToString(dt.Rows[i]["Desktop_Analysis_Request_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Desktop_Analysis_Request_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 7].Value = ((Convert.ToString(dt.Rows[i]["Los_Upload_Date"]) != null && Convert.ToString(dt.Rows[i]["Los_Upload_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Los_Upload_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 8].Value = ((Convert.ToString(dt.Rows[i]["Prelim_Design_Date"]) != null && Convert.ToString(dt.Rows[i]["Prelim_Design_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Prelim_Design_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 9].Value = ((Convert.ToString(dt.Rows[i]["Final_Design_Requested_Date"]) != null && Convert.ToString(dt.Rows[i]["Final_Design_Requested_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Final_Design_Requested_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 10].Value = ((Convert.ToString(dt.Rows[i]["Final_Design_Completed_Date"]) != null && Convert.ToString(dt.Rows[i]["Final_Design_Completed_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Final_Design_Completed_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 11].Value = ((Convert.ToString(dt.Rows[i]["PCN_Filed_date"]) != null && Convert.ToString(dt.Rows[i]["PCN_Filed_date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["PCN_Filed_date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 12].Value = ((Convert.ToString(dt.Rows[i]["PCN_Cleared_Date"]) != null && Convert.ToString(dt.Rows[i]["PCN_Cleared_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["PCN_Cleared_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 13].Value = ((Convert.ToString(dt.Rows[i]["BOM_Sent_to_Market_Date"]) != null && Convert.ToString(dt.Rows[i]["BOM_Sent_to_Market_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["BOM_Sent_to_Market_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 14].Value = ((Convert.ToString(dt.Rows[i]["FCC_601_Filed_Date"]) != null && Convert.ToString(dt.Rows[i]["FCC_601_Filed_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["FCC_601_Filed_Date"]).ToString("MM/dd/yyyy") : string.Empty);
}
}
for (int colCnt = 1; colCnt <= xlColCnt; colCnt++)
{
xlWrkSht1.Column(colCnt).AutoFit();
}
xlWrkSht1.Column(xlColCnt).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
xlWrkSht1.Select("A1:N" + xlRowCnt);
xlWrkSht1.SelectedRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
xlWrkSht1.SelectedRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
xlWrkSht1.SelectedRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
xlWrkSht1.SelectedRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
xlPkg.SaveAs(xlFileStream);
xlWrkSht1.Dispose();
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
xlFileStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
static void Main(string[] args)
{
List <string[, ]> _tmp = new List <string[, ]>();
Console.Write("Введите название папки в которой находятся файлы: ");
string folder = Console.ReadLine();
pathFolder += folder;
DirectoryInfo di = new DirectoryInfo(pathFolder);
//foreach (FileInfo file in di.GetFiles())
//{
// Console.WriteLine(file.Name);
// ExcelList.Add(new Excel(file));
// _tmp.Add(ExcelList.Last().ExcelTable);
//}
FileInfo[] fi = di.GetFiles();
fi = fi.OrderBy(x => int.Parse(x.Name.Remove(x.Name.LastIndexOf(")")).Remove(0, x.Name.IndexOf("(") + 1))).ToArray();
for (int i = 0; i < di.GetFiles().Length; i++)
{
//FileInfo file = new FileInfo($"{pathFolder}\\amocrm__contacts ({i}).xlsx");
Console.WriteLine(fi[i].Name);
ExcelList.Add(new Excel(fi[i]));
_tmp.Add(ExcelList.Last().ExcelTable);
}
if (_tmp.Count == 0)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Папка пуста");
Console.ResetColor();
}
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("Важно отметить, что начиная со 2го файла, 1я строка в документе пропускается.\r\nСчитается что там описание полей.");
Console.ResetColor();
Console.WriteLine("Порядок объединения соблюден?\r\nЕсли нет - просто закройте программу\r\nЕсли да - нажмите на любую клавишу");
Console.ReadKey();
int cursorRowIndex = 0;
int offsetRow = 0;
Console.Write("Введите длинну пропуска между файлами(Обычно 1 достаточно):");
int offsetRowBeetwen = int.Parse(Console.ReadLine());
using (OfficeOpenXml.ExcelPackage eP = new OfficeOpenXml.ExcelPackage())
{
eP.Workbook.Worksheets.Add("Worksheet1");
using (OfficeOpenXml.ExcelWorksheet eWs = eP.Workbook.Worksheets[1])
{
for (int i = 0; i < ExcelList.Count; i++)
{
for (int row = 1; row < ExcelList[i].ExcelTable.GetLength(0) + 1; row++)
{
if (i != 0 && row == 1)
{
row++;
}
for (int column = 1; column < ExcelList[i].ExcelTable.GetLength(1) + 1; column++)
{
eWs.Cells[row + offsetRow, column].Value = ExcelList[i].ExcelTable[row - 1, column - 1];
}
}
offsetRow += ExcelList[i].ExcelTable.GetLength(0) - offsetRowBeetwen;//-2 чтоб пропустить 2 пустые строки, чтоб инфо шло друг-за-другом
}
string nameUnionExcel = "UnionExcel.xlsx";
//
string filePath = AppDomain.CurrentDomain.BaseDirectory + nameUnionExcel;
//
eP.SaveAs(new FileInfo(filePath)); //eP.SaveAs(new FileInfo($"{reportDir.FullName}\\{nameReport}"));
//WwasLogMsg(null, $"Лог: Отчет сохранен в папке {filePath}"); //WwasLogMsg(null, $"Лог: Отчет сохранен в папке с программой \"{nameReport}\"");
Console.WriteLine($"Фаил сохранент: {nameUnionExcel}");
}
//OfficeOpenXml.ExcelPackage eP = new OfficeOpenXml.ExcelPackage(new FileInfo(oFD.FileName))
}
Console.WriteLine();
}
static void Save(this ExcelPackage excel, string path)
{
excel.SaveAs(path);
}
/// <summary>Save the workbook to the specified stream.</summary>
/// <param name="stream">The stream that will receive the file contents.</param>
public override void Save(System.IO.Stream stream)
{
_package.SaveAs(stream);
}
/// <summary>
/// Creating Customized Excel Sheet.
/// </summary>
/// <param name="filePath">The File path</param>
public static void CreateComosExcel(string filePath)
{
var cosmoSheetRowsCollection = ReadSpreadsheetCosmo(filePath);
// Creating excel package
ExcelPackage _ExcelPackage = new ExcelPackage();
// Adding worksheet name
ExcelWorksheet _ExcelWorksheet = _ExcelPackage.Workbook.Worksheets.Add("0");
_ExcelWorksheet.Cells.Style.Font.Size = 11; //Default font size for whole sheet
_ExcelWorksheet.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
_ExcelWorksheet.View.FreezePanes(2, 8);
int rowIndex = 1;
// Creating Header Row into Excel Sheet
CreateHeader(_ExcelWorksheet, ref rowIndex);
CreateData(_ExcelWorksheet, ref rowIndex, cosmoSheetRowsCollection);
//Generate A File with Random name
string file = "Cosmetologist" + "_" + Guid.NewGuid().ToString() + ".xlsx";
using (MemoryStream ms = new MemoryStream())
{
_ExcelPackage.SaveAs(ms);
File.WriteAllBytes(file, ms.ToArray());
}
}