public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
// Get a range object
var rngHeaders = ws.Range("B3:F3");
// Insert some rows/columns before the range
ws.Row(1).InsertRowsAbove(2);
ws.Column(1).InsertColumnsBefore(2);
// Change the background color of the headers
rngHeaders.Style.Fill.BackgroundColor = XLColor.LightSalmon;
ws.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
/// Unpacks and writes out the FullNetwork file, returns the filepath
/// </summary>
/// <param name="Network"></param>
/// <param name="Path"></param>
public string WriteOutExcelDocument(FullNetwork Network, string FileName)
{
// Build data table from the Network
DataTable Table = new DataTable();
Table.Columns.Add("Network ID");
Table.Columns.Add("First Usable");
Table.Columns.Add("Last Usable");
Table.Columns.Add("Broadcast Address");
foreach (var subnet in Network.Subnets)
{
// Add the data to each collumn
DataRow Row = Table.NewRow();
Row[0] = subnet.NetworkId;
Row[1] = subnet.FirstUsable;
Row[2] = subnet.LastUsable;
Row[3] = subnet.BroadcastAddress;
Table.Rows.Add(Row);
}
string FilePath = FileName + ".xlsx";
XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add(Table, "Subnet Data");
wb.SaveAs(FilePath);
return FilePath;
}
//---------------------------------------------------------//
// SubRoutine
//---------------------------------------------------------//
private void editExcelSheet(string sheetName, PublishData pd, DataGridView dgv)
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
try
{
using (oWBook = new XLWorkbook(fileName))
{
oWSheet = oWBook.Worksheet(sheetName);
switch (sheetName)
{
case "ContractWorks":
editContractWorks(pd, dgv);
break;
case "ContractSummary":
editContractSummary(pd, dgv);
break;
default:
break;
}
oWBook.SaveAs(tempFile); // Excel保存
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
}
private static void ExportToExcelFile(DataGridView dGV, string filename, string tabName)
{
//Creating DataTable
DataTable dt = new DataTable();
//Adding the Columns
foreach (DataGridViewColumn column in dGV.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
//Adding the Rows
foreach (DataGridViewRow row in dGV.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in row.Cells)
{
dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
}
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, tabName);
wb.SaveAs(filename);
}
}
// Public
public void Create(String filePath)
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Clearing Ranges");
foreach (var ro in Enumerable.Range(1, 10))
{
foreach (var co in Enumerable.Range(1, 10))
{
var cell = ws.Cell(ro, co);
cell.Value = cell.Address.ToString();
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
cell.Style.Fill.BackgroundColor = XLColor.Turquoise;
cell.Style.Font.Bold = true;
}
}
// Clearing a range
ws.Range("B1:C2").Clear();
// Clearing a row in a range
ws.Range("B4:C5").Row(1).Clear();
// Clearing a column in a range
ws.Range("E1:F4").Column(2).Clear();
// Clear an entire row
ws.Row(7).Clear();
// Clear an entire column
ws.Column("H").Clear();
workbook.SaveAs(filePath);
}
public static void ExportExcels(DataTable data)
{
try
{
DataTable _myDataTable = data;
_myDataTable.Columns[0].ColumnName = "#";
_myDataTable.Columns[1].ColumnName = "შექმნის თარიღი";
_myDataTable.Columns[2].ColumnName = "მოვლენის თარიღი";
_myDataTable.Columns[3].ColumnName = "წერილის თარიღი";
_myDataTable.Columns[4].ColumnName = "პროვაიდერი";
_myDataTable.Columns[5].ColumnName = "ტიპი";
_myDataTable.Columns[6].ColumnName = "საიდან";
_myDataTable.Columns[7].ColumnName = "სად";
_myDataTable.Columns[8].ColumnName = "ათვისებული თანხა";
_myDataTable.Columns[9].ColumnName = "დასაბრუნებელი თანხა";
_myDataTable.Columns[10].ColumnName = "მომხმარებელი";
_myDataTable.Columns[11].ColumnName = "ინციდენტი";
var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add(_myDataTable, "Letters");
var table = sheet.Table(0);
table.Theme = XLTableTheme.TableStyleMedium2;
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel Files (*.xlsx)|*.xlsx";
dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
dialog.FileName = "letters";
if (dialog.ShowDialog() != DialogResult.OK) return;
workbook.SaveAs(dialog.FileName);
System.Diagnostics.Process.Start(dialog.FileName);
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
public static void ExportExcel(Letter[] data)
{
try
{
var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Letters");
var row = sheet.FirstRow();
SetHeader(row);
row = row.RowBelow();
foreach (var letter in data)
{
SetData(row, letter);
row = row.RowBelow();
}
sheet.Columns().AdjustToContents();
var table = sheet.RangeUsed().CreateTable();
table.Theme = XLTableTheme.TableStyleMedium15;
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel Files (*.xlsx)|*.xlsx";
dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
dialog.FileName = "Letters";
if (dialog.ShowDialog() != DialogResult.OK) return;
workbook.SaveAs(dialog.FileName);
System.Diagnostics.Process.Start(dialog.FileName);
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
// Public
public void Create(String filePath)
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Merge Cells");
// Merge a row
ws.Cell("B2").Value = "Merged Row(1) of Range (B2:D3)";
ws.Range("B2:D3").Row(1).Merge();
// Merge a column
ws.Cell("F2").Value = "Merged Column(1) of Range (F2:G8)";
ws.Cell("F2").Style.Alignment.WrapText = true;
ws.Range("F2:G8").Column(1).Merge();
// Merge a range
ws.Cell("B4").Value = "Merged Range (B4:D6)";
ws.Cell("B4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell("B4").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Range("B4:D6").Merge();
// Unmerging a range...
ws.Cell("B8").Value = "Unmerged";
ws.Range("B8:D8").Merge();
ws.Range("B8:D8").Unmerge();
workbook.SaveAs(filePath);
}
// Public
public void Create(String filePath)
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Inserting Rows");
// Color the entire spreadsheet using rows
ws.Rows().Style.Fill.BackgroundColor = XLColor.LightCyan;
// Put a value in a few cells
foreach (var r in Enumerable.Range(1, 5))
foreach (var c in Enumerable.Range(1, 5))
ws.Cell(r, c).Value = "X";
var blueRow = ws.Row(2);
var redRow = ws.Row(5);
blueRow.Style.Fill.BackgroundColor = XLColor.Blue;
blueRow.InsertRowsBelow(2);
redRow.Style.Fill.BackgroundColor = XLColor.Red;
redRow.InsertRowsAbove(2);
ws.Columns(3, 4).Style.Fill.BackgroundColor = XLColor.Orange;
ws.Range("A2:A4").InsertRowsBelow(2);
ws.Range("B2:B4").InsertRowsAbove(2);
ws.Range("C2:C4").InsertRowsBelow(2);
ws.Range("D2:D4").InsertRowsAbove(2);
workbook.SaveAs(filePath);
}
public void DataTableExport(DataTable dataTable, string filePath, string title)
{
if (string.IsNullOrWhiteSpace(filePath))
throw new FileNotFoundException("Incorrect file path.");
if ((dataTable == null) || (dataTable.Rows.Count == 0))
throw new ArgumentException("Empty data table.");
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add(title);
if (IncludeTitle)
{
worksheet.Cell(1, 1).Value = title;
worksheet.Range(1, 1, 1, dataTable.Columns.Count).Merge().AddToNamed("Titles");
var titlesStyle = workbook.Style;
titlesStyle.Font.Bold = true;
titlesStyle.Font.FontSize = 16;
titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
titlesStyle.Fill.BackgroundColor = XLColor.Cyan;
workbook.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle;
}
worksheet.Cell(2, 1).InsertTable(dataTable.AsEnumerable());
worksheet.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
public static void ExportToExcel(DataSet ds, string sheetName, System.Web.UI.Page page)
{
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count <= 0)
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
}
}
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
wbook.Worksheets.Add(ds.Tables[0], sheetName);
// Prepare the response
HttpResponse httpResponse = page.Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Provide you file name here
httpResponse.AddHeader("content-disposition", "attachment;filename='" + sheetName + ".xlsx'");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wbook.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
}
public void Create(String filePath)
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
workbook.SaveAs(filePath);
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
//Note: Prepare
{
var workbook = new XLWorkbook();
workbook.Worksheets.Add("1");
workbook.Worksheets.Add("2");
workbook.Worksheets.Add("3");
workbook.Worksheets.Add("4");
workbook.SaveAs(tempFile);
}
//Note: Delate few worksheet
{
var workbook = new XLWorkbook(tempFile);
workbook.Worksheets.Delete("1");
workbook.Worksheets.Delete("2");
workbook.SaveAs(filePath);
}
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
protected void btExcel_Click(object sender, EventArgs e)
{
string sql = "";
sql += @"select * from tb_DetailB ORDER BY NgayDangKy DESC";
DataTable table = Connect.GetTable(sql);
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
wbook.Worksheets.Add(table, "tab1");
// Prepare the response
HttpResponse httpResponse = Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Provide you file name here
httpResponse.AddHeader("content-disposition", "attachment;filename=\"DanhSachDangKyKhaiSinh(" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ").xlsx\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wbook.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
}
public void Create(string filePath)
{
var tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
// Define a range with the data
var firstTableCell = ws.FirstCellUsed();
var lastTableCell = ws.LastCellUsed();
var rngData = ws.Range(firstTableCell.Address, lastTableCell.Address);
// Copy the table to another worksheet
var wsCopy = workbook.Worksheets.Add("Contacts Copy");
wsCopy.Cell(1, 1).Value = rngData;
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
protected void exportToExcel_Click(object sender, EventArgs e)
{
LinkButton btnOption = (LinkButton)sender;
var value = btnOption.CommandArgument;
var Opt = btnOption.CommandName;
cmpObj.campaignId = value.ToString();
cmpObj.option = Opt.ToString();
DataTable dtTemp = cmpObj.getCampaignReportDetailsByCampaignId(cmpObj);
if (dtTemp.Rows.Count > 0)
{
dtTemp.Columns["VoterName"].SetOrdinal(0);
dtTemp.Columns["Issues"].SetOrdinal(1);
dtTemp.Columns["Complaint"].SetOrdinal(2);
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
wbook.Worksheets.Add(dtTemp, "tab1");
// Prepare the response
HttpResponse httpResponse = Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Provide you file name here
httpResponse.AddHeader("content-disposition", "attachment;filename=\"CampaignReportIssues.xlsx\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wbook.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
}
}
public void GenerateSalesPerCategoryResultFile(CategorySalesReport reportData, string fileName)
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Sales per Category");
//Columns
ws.Cell("B2").Value = "Category";
ws.Cell("C2").Value = "Quantity";
ws.Cell("D2").Value = "Amount Sold";
var rowCount = 3;
foreach (var row in reportData.Report)
{
var categoryCell = "B" + rowCount;
var quantityCell = "C" + rowCount;
var totalAmountCell = "D" + rowCount;
ws.Cell(categoryCell).Value = row.Category;
ws.Cell(quantityCell).Value = row.Quantity;
ws.Cell(totalAmountCell).Value = row.TotalAmountSold;
rowCount++;
}
var tableRange = ws.Range("B2", "D" + (rowCount - 1));
var finBalanceTable = tableRange.CreateTable();
finBalanceTable.Theme = XLTableTheme.TableStyleMedium16;
ws.Columns().AdjustToContents();
wb.SaveAs(ExcelSettings.Default.ExcelReportsDestinationFolder + fileName);
}
public static void WriteBeaches()
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Плажове");
for(int i = 0; i < Beach.beaches.Count; i++)
{
Beach current = Beach.beaches[i];
int rowIdx = i + 2;
worksheet.Cell(f("A", rowIdx)).Value = current.name;
worksheet.Cell(f("B", rowIdx)).Value = current.location;
worksheet.Cell(f("C", rowIdx)).Value = current.nCar;
worksheet.Cell(f("D", rowIdx)).Value = current.nCity;
worksheet.Cell(f("E", rowIdx)).Value = current.nFoot;
worksheet.Cell(f("F", rowIdx)).Value = current.facilties[0];
worksheet.Cell(f("G", rowIdx)).Value = current.facilties[1];
worksheet.Cell(f("H", rowIdx)).Value = current.facilties[2];
worksheet.Cell(f("I", rowIdx)).Value = current.facilties[3];
worksheet.Cell(f("J", rowIdx)).Value = current.Density;
worksheet.Cell(f("K", rowIdx)).Value = current.Polution;
worksheet.Cell(f("L", rowIdx)).Value = current.Grade;
worksheet.Cell(f("M", rowIdx)).Value = current.People;
}
workbook.SaveAs("ало.xlsx");
}
public void Create(string filePath)
{
string tempFile1 = ExampleHelper.GetTempFilePath(filePath);
string tempFile2 = ExampleHelper.GetTempFilePath(filePath);
try
{
new UsingTables().Create(tempFile1);
var wb = new XLWorkbook(tempFile1);
var wsSource = wb.Worksheet(1);
// Copy the worksheet to a new sheet in this workbook
wsSource.CopyTo("Copy");
// We're going to open another workbook to show that you can
// copy a sheet from one workbook to another:
new BasicTable().Create(tempFile2);
var wbSource = new XLWorkbook(tempFile2);
wbSource.Worksheet(1).CopyTo(wb, "Copy From Other");
// Save the workbook with the 2 copies
wb.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile1))
{
File.Delete(tempFile1);
}
if (File.Exists(tempFile2))
{
File.Delete(tempFile2);
}
}
}
public void Create(String filePath)
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Style Worksheet");
ws.Style.Font.Bold = true;
ws.Style.Font.FontColor = XLColor.Red;
ws.Style.Fill.BackgroundColor = XLColor.Cyan;
// The following cells will be bold and red
// because we've specified those attributes to the entire worksheet
ws.Cell(1, 1).Value = "Test";
ws.Cell(1, 2).Value = "Case";
// Here we'll change the style of a single cell
ws.Cell(2, 1).Value = "Default";
ws.Cell(2, 1).Style = XLWorkbook.DefaultStyle;
// Let's play with some rows
ws.Row(4).Style = XLWorkbook.DefaultStyle;
ws.Row(4).Height = 20;
ws.Rows(5, 6).Style = XLWorkbook.DefaultStyle;
ws.Rows(5, 6).Height = 20;
// Let's play with some columns
ws.Column(4).Style = XLWorkbook.DefaultStyle;
ws.Column(4).Width = 5;
ws.Columns(5, 6).Style = XLWorkbook.DefaultStyle;
ws.Columns(5, 6).Width = 5;
workbook.SaveAs(filePath);
}
private static void CreateExcelFile(ChromeDriver driver)
{
var upcomingEvents = driver.FindElementsByCssSelector(".event.active");
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Games");
ws.Cell("A1").Value = "Games";
ws.Cell("B1").Value = "Home";
ws.Cell("C1").Value = "Draw";
ws.Cell("D1").Value = "Away";
for (int i = 0; i < upcomingEvents.Count; i++)
{
string eventName = upcomingEvents[i].FindElement(By.ClassName("eventName")).Text;
string homeOdd = upcomingEvents[i].FindElement(By.ClassName("home")).Text;
string drawOdd = upcomingEvents[i].FindElement(By.ClassName("draw")).Text;
string awayOdd = upcomingEvents[i].FindElement(By.ClassName("away")).Text;
ws.Cell("A" + (i + 2)).Value = eventName;
ws.Cell("B" + (i + 2)).Value = homeOdd;
ws.Cell("C" + (i + 2)).Value = drawOdd;
ws.Cell("D" + (i + 2)).Value = awayOdd;
}
// Beautify
ws.Range("A1:D1").Style.Font.Bold = true;
ws.Columns().AdjustToContents();
wb.SaveAs("../../../../Events.xlsx");
}
public void Build(Tree features)
{
if (Log.IsInfoEnabled)
{
Log.Info("Writing Excel workbook to {0}", this.configuration.OutputFolder.FullName);
}
string spreadsheetPath = this.fileSystem.Path.Combine(this.configuration.OutputFolder.FullName, "features.xlsx");
using (var workbook = new XLWorkbook())
{
foreach (var node in features)
{
var featureDirectoryTreeNode =
node as FeatureNode;
if (featureDirectoryTreeNode != null)
{
IXLWorksheet worksheet =
workbook.AddWorksheet(
this.excelSheetNameGenerator.GenerateSheetName(
workbook,
featureDirectoryTreeNode
.Feature));
this.excelFeatureFormatter.Format(
worksheet,
featureDirectoryTreeNode.Feature);
}
}
this.excelTableOfContentsFormatter.Format(workbook, features);
workbook.SaveAs(spreadsheetPath);
}
}
public void CreateExcelForPdf(string sheetName, PublishData pd, DataGridView dgv)
{
//string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
string tempFile = "";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
try
{
using (oWBook = new XLWorkbook(fileName))
{
oWSheet = oWBook.Worksheet(sheetName);
switch (sheetName)
{
case "Volume":
tempFile = Folder.DefaultLocation() + @"\" + pd.vTaskCode + ".xlsx";
editContractWorks(pd, dgv);
break;
default:
break;
}
oWBook.SaveAs(tempFile); // Excel保存
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
}
public static void Basic()
{
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Worksheet Name");
ws.Cell("A1").Value = Data;
wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx");
}
//----------------------------------------------------------------------
// SubRoutine
//----------------------------------------------------------------------
private void editExcelSheet(string sheetName, TaskData td, TaskNoteData tnd, TaskIndData[] tid, PartnersData pd, TaskOp tod)
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
using (oWBook = new XLWorkbook(FileName))
{
// シートを開く
//oWSheet = oWBook.Worksheet(sheetName);
oWSheet = oWBook.Worksheet(1);
// 編集
//MessageBox.Show("Excel書込み開始");
editTaskDataPart(td);
editTaskNoteDataPart(tnd);
editTaskIndDataPart(tid);
editPartnersDataPart(pd);
editPersonsDataPart(tod);
// 保存
oWBook.SaveAs(tempFile); // Excel保存
}
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
//if (File.Exists(tempFile)) File.Delete(tempFile);
}
private byte[] GenerateReport(List<RsvpReportRow> users)
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("RSVP Users");
worksheet.Cell("A1").Value = "First Name";
worksheet.Cell("B1").Value = "Last Name";
worksheet.Cell("C1").Value = "Email";
worksheet.Cell("D1").Value = "Date RSVPed";
int rowCount = 1;
foreach (var row in users)
{
rowCount++;
worksheet.Cell("A" + rowCount).Value = row.FirstName;
worksheet.Cell("B" + rowCount).Value = row.LastName;
worksheet.Cell("C" + rowCount).Value = row.EmailAddress;
worksheet.Cell("D" + rowCount).Value = row.RsvpDate.ToLongTimeString();
}
byte[] results;
using( MemoryStream memory = new MemoryStream())
{
workbook.SaveAs(memory);
results = memory.ToArray();
};
return results;
}
protected void BtnExcel_Click(object sender, EventArgs e)
{
Db.DAOrdenes OBJ = new Db.DAOrdenes();
//GcExportar.DataSource = OBJ.Reporte(txtOrden.Value.Trim(), ddlTipoResultado.SelectedValue.ToString(),
//datavalue.Value.ToString(), datavalue2.Value.ToString(), ddlMotorizado.SelectedValue.ToString(), ddlSucursal.SelectedValue.ToString());
//GcExportar.DataBind();
DataTable dt_report = new DataTable();
dt_report = OBJ.Reporte(txtOrden.Value.Trim(), ddlTipoResultado.SelectedValue.ToString(),
datavalue.Value.ToString(), datavalue2.Value.ToString(), ddlMotorizado.SelectedValue.ToString(), ddlSucursal.SelectedValue.ToString());
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
wbook.Worksheets.Add(dt_report, "reporte");
// Preparar la respuesta
HttpResponse httpResponse = Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// Proporcione su nombre de archivo aquí
httpResponse.AddHeader("content-disposition", "attachment;filename=\"Reporte_Pedido " + DateTime.Now + ".xlsx\"");
//Vacíe el libro de trabajo al Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wbook.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
}
public void Export(string filePath)
{
using (var workbook = new XLWorkbook(XLEventTracking.Disabled))
{
IEnumerable<CultureInfo> cultures = null;
if (Cultures != null)
cultures = Cultures.Select(vc => vc.Culture);
else
cultures = Solution.Cultures.Keys;
IEnumerable<VSProject> projects = Projects;
if (Projects == null)
projects = (IEnumerable<VSProject>)Solution.Projects.Values;
foreach (var project in projects)
{
var data = GetData(project, cultures);
if (IncludeProjectsWithoutTranslations || data.Count() > 0)
AddProject(project, workbook, cultures, data);
}
workbook.SaveAs(filePath);
}
}
// Public
public void Create(String filePath)
{
#region Create case
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Delete red rows");
// Put a value in a few cells
foreach (var r in Enumerable.Range(1, 5))
foreach (var c in Enumerable.Range(1, 5))
ws.Cell(r, c).Value = string.Format("R{0}C{1}", r, c);
var blueRow = ws.Rows(1, 2);
var redRow = ws.Row(5);
blueRow.Style.Fill.BackgroundColor = XLColor.Blue;
redRow.Style.Fill.BackgroundColor = XLColor.Red;
workbook.SaveAs(filePath);
}
#endregion
#region Remove rows
{
var workbook = new XLWorkbook(filePath);
var ws = workbook.Worksheets.Worksheet("Delete red rows");
ws.Rows(1, 2).Delete();
workbook.Save();
}
#endregion
}
// Public
public void Create(String filePath)
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Inserting Columns");
// Color the entire spreadsheet using columns
ws.Columns().Style.Fill.BackgroundColor = XLColor.LightCyan;
// Put a value in a few cells
foreach (var r in Enumerable.Range(1, 5))
foreach (var c in Enumerable.Range(1, 5))
ws.Cell(r, c).Value = "X";
var blueColumn = ws.Column(2);
var redColumn = ws.Column(5);
blueColumn.Style.Fill.BackgroundColor = XLColor.Blue;
blueColumn.InsertColumnsAfter(2);
redColumn.Style.Fill.BackgroundColor = XLColor.Red;
redColumn.InsertColumnsBefore(2);
ws.Rows(3, 4).Style.Fill.BackgroundColor = XLColor.Orange;
ws.Range("B1:D1").InsertColumnsAfter(2);
ws.Range("B2:D2").InsertColumnsBefore(2);
ws.Range("B3:D3").InsertColumnsAfter(2);
ws.Range("B4:D4").InsertColumnsBefore(2);
workbook.SaveAs(filePath);
}
//---------------------------------------------------------//
// Method
//---------------------------------------------------------//
public void ExcelFile()
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
try
{
using (oWBook = new XLWorkbook(fileName))
{
// 編集
oWSheet = oWBook.Worksheet(1); // シートを開く
editReviewData(pubDat);
// 保存
oWBook.SaveAs(tempFile); // Excel保存
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
var rngTable = ws.Range("B2:F6");
rngTable.Transpose(XLTransposeOptions.MoveCells);
ws.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public static void Closeit()
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Rippit");
worksheet.Cell("B5").Value = "Snart that!";
//worksheet.Column("A")
workbook.SaveAs("Yosup.xlsx");
}
// Public
public void Create(String filePath)
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Sheet1");
ws.Cell(1, 1).Value = "X";
ws.Cell(1, 1).Clear();
wb.SaveAs(filePath);
}
private static void DuplicateExcelSheetByEPPlus(string baseDir, string bookPath)
{
var outPath = Path.Combine(baseDir, "Book1 - ClosedXML.xlsx");
using (var book = new ClosedXML.Excel.XLWorkbook(bookPath))
{
book.Worksheet("Sheet1").CopyTo("Sheet2");
book.SaveAs(outPath);
}
}
//作業割り当て表Excelファイル出力
public void save_asignlist_xlsx_as(List <List <string> > data, List <string> categories, string filename)
{
_write_log __write_log = write_log;
try
{
using (var wb = new ClosedXML.Excel.XLWorkbook())
{
var ws = wb.Worksheets.Add("Sheet1");
//行のループ
for (int i = 0; i < data.Count; i++)
{
List <string> row = (List <string>)data[i];
//列のループ
for (int j = 0; j < row.Count; j++)
{
string col = (string)row[j];
ws.Cell(i + 1, j + 1).SetValue <string>(fetch_overflow_characters(col));
ws.Cell(i + 1, j + 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, j + 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, j + 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, j + 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, j + 1).Style.Font.FontName = "ＭＳ Ｐゴシック";
ws.Cell(i + 1, j + 1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
}
if (i == 0)
{
int maxcol = row.Count;
for (int z = 0; z < categories.Count; z++)
{
ws.Cell(i + 1, maxcol + (z + 1)).SetValue <string>((string)categories[z]);
ws.Cell(i + 1, maxcol + (z + 1)).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, maxcol + (z + 1)).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, maxcol + (z + 1)).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, maxcol + (z + 1)).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, maxcol + (z + 1)).Style.Font.FontName = "ＭＳ Ｐゴシック";
ws.Cell(i + 1, maxcol + (z + 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Cell(i + 1, maxcol + (z + 1)).Style.Alignment.TopToBottom = true;
}
}
}
wb.SaveAs(filename);
main_form.Invoke(__write_log, "保存に成功しました。（" + filename + "）");
}
}
catch (Exception ex)
{
main_form.Invoke(__write_log, "【エラー】" + ex.Message);
return;
}
}
static void Main(string[] args)
{
DataTable table = new DataTable("test");
table.Columns.Add();
table.Columns.Add();
table.Rows.Add("1", "2");
table.Rows.Add("3", "4");
ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook();
workbook.Worksheets.Add(table);
workbook.SaveAs("D:\\test.xlsx");
}
public void ExportExcel(DataSet ds, string destination)
{
var workbook = new ClosedXML.Excel.XLWorkbook();
foreach (DataTable dt in ds.Tables)
{
var worksheet = workbook.Worksheets.Add(dt.TableName);
worksheet.Cell(1, 1).InsertTable(dt);
worksheet.Columns().AdjustToContents();
}
workbook.SaveAs(destination);
workbook.Dispose();
}
// Wakamatsu
// Wakamatsu 20170301
//private string editExcelSheet(string sheetName, DataTable dt, FormatSet[] FormatSet)
private string editExcelSheet(string FileName, string sheetName, DataTable dt, FormatSet[] FormatSet)
{
// Wakamatsu 20170301
//string tempFile = Folder.MyDocuments() + @"\" + sheetName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
string tempFile = Folder.MyDocuments() + @"\" + FileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
// Wakamatsu 20170301
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
if (System.IO.File.Exists(fileName) == false)
{
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return("× テンプレートファイルが存在しません。\r\n");
}
// Wakamatsu 20170322
//using(oWBook = new XLWorkbook(fileName))
//{
try
{
// Wakamatsu 20170322
using (oWBook = new XLWorkbook(fileName))
{
oWSheet = oWBook.Worksheet(sheetName);
// Excelファイル出力
if (MasterExport(dt, FormatSet) == true)
{
oWBook.SaveAs(tempFile); // Excel保存
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return("○ Excel出力が正常に終了しました。\r\n" +
" " + dt.Rows.Count + "件のデータを出力しました。\r\n");
}
else
{
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return("× Excel出力ができませんでした。\r\n");
}
}
}
catch (Exception ex)
{
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
// Wakamatsu 20170322
//MessageBox.Show(ex.Message);
//return "× Excel出力ができませんでした。\r\n";
return(ex.Message + "\r\n× Excel出力ができませんでした。\r\n");
// Wakamatsu 20170322
}
//}
}
private void btnFullMetal_Click(object sender, EventArgs e)
{
string strKeyWord = "毒蜥级";
string strPath = Application.StartupPath + "\\EXCEL\\" + strKeyWord + "制造方案";
Excel.XLWorkbook xFile = new Excel.XLWorkbook();
Excel.IXLWorksheet xLSheet = xFile.AddWorksheet();
int nRow = 1;
int nCol = 1;
OutputBluePrintResult(xLSheet, strKeyWord, ref nRow, ref nCol);
xFile.SaveAs(strPath + ".xlsx");
MessageBox.Show("计算完成,路径:" + strPath);
}
private static void getReport()
{
DAL objDAL = new DAL();
DataTable dt = new DataTable();
DataTable dtConfigReport = getConfig("Report");
string filePath = ConfigurationManager.AppSettings["folderPath"].ToString() + "WeeklyReport.xlsx";
string Query = "usp_get_WeeklyReportData";
objDAL.CommandText = Query;
dt = objDAL.ExecuteDataSet().Tables[0];
XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
wb.Worksheets.Add(dt);
wb.SaveAs(filePath);
sendEmail("", dtConfigReport, "", filePath);
}
protected virtual void Response()
{
HttpResponse _response = HttpContext.Current.Response;
_response.ClearContent();
_response.Buffer = true;
_response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", this._filename ?? (new Guid()).ToString()));
_response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
_response.Charset = "";
using (MemoryStream MyMemoryStream = new MemoryStream())
{
workbook.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(_response.OutputStream);
_response.Flush();
_response.End();
}
}
private static async Task <XLWorkbook> BuildWorkbook(List <CommonLookup> headerfields, SqlDataReader reader, SqlConnection connection, string filename)
{
Console.WriteLine("BuildWorkbook");
//now let's build a spreadsheet
XLWorkbook workbook = null;
var t = Task.Run(() =>
{
workbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sheet1");
int row = 1;
int idx = 1;
foreach (CommonLookup cl in headerfields)
{
worksheet.Row(row).Cell(idx).SetDataType(XLCellValues.Text);
worksheet.Row(row).Cell(idx).SetValue(cl.Code);
idx++;
}
row = row + 1;
idx = 1;
while (reader.Read() && row < 100000)
{
foreach (CommonLookup cl in headerfields)
{
worksheet.Row(row).Cell(idx).SetDataType(XLCellValues.Text);
worksheet.Row(row).Cell(idx).SetValue(reader[cl.CodeDesc].ToString());
idx++;
}
row++;
idx = 1;
}
workbook.SaveAs(filename);
});
t.Wait();
Console.WriteLine("Finished - BuildingWorkbook");
return(workbook);
}
public void Stampa(Pagamento pagamentoSel)
{
using (var uof = new UnitOfWork())
{
var pagamento = uof.PagamentoRepository.Find(a => a.ID == pagamentoSel.ID)
.First();
var righePag = uof.PagamentoRepository.Find(a => a.IDPagamentoMaster == pagamentoSel.IDPagamentoMaster &&
a.ID != pagamentoSel.ID).OrderBy(a => a.DataRata).ToList();
ImpostaCampiTestata(pagamento);
ImpostaDettaglio(righePag, pagamento);
}
var newfile = Path.Combine(System.IO.Path.GetTempPath(),
DateTime.Now.Ticks.ToString() + "_Pag.xlsx");
_wb.SaveAs(newfile);
Process.Start(newfile);
}
//Excelファイルに出力
public void save_xlsx_as(List <List <string> > data, string filename)
{
d_messenger message = new d_messenger(w_messenger);
try
{
using (var wb = new ClosedXML.Excel.XLWorkbook())
{
var ws = wb.Worksheets.Add("Sheet1");
//行のループ
for (int i = 0; i < data.Count; i++)
{
List <string> row = (List <string>)data[i];
//列のループ
for (int j = 0; j < row.Count; j++)
{
string col = (string)row[j];
ws.Cell(i + 1, j + 1).SetValue <string>(fetch_overflow_characters(col));
ws.Cell(i + 1, j + 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, j + 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, j + 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, j + 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 1, j + 1).Style.Font.FontName = "ＭＳ Ｐゴシック";
ws.Cell(i + 1, j + 1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
}
}
wb.SaveAs(filename);
main_form.Invoke(message, "保存に成功しました。（" + filename + "）");
}
}
catch (Exception ex)
{
main_form.Invoke(message, "【エラー】" + ex.Message);
return;
}
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
if (gridVista.SelectedRows.Count != 0)
{
try
{
string carpeta = System.Configuration.ConfigurationManager.AppSettings[companySelected() + "_directorio"].ToString();
DcemVwContabilidad item = (DcemVwContabilidad)gridVista.SelectedRows[0].DataBoundItem;
if (item != null)
{
var iTipoDoc = lParametros.Where(x => x.Tipo == item.tipodoc);
string nombreArchivo = iTipoDoc.First().Archivo;
nombreArchivo = Path.GetFileNameWithoutExtension(nombreArchivo) + "_" + DateTime.Now.ToString("yyyyMMdd HHmmss");
string archivo = Path.Combine(carpeta, nombreArchivo + ".xlsx");
object items = mostrarContenido(item.year1, item.periodid, item.tipodoc);
DataTable dtItems = ContabilidadElectronicaPresentacion.ConvierteLinqQueryADataTable((IEnumerable <dynamic>)items);
var wb = new ClosedXML.Excel.XLWorkbook();
dtItems.TableName = "test";
wb.Worksheets.Add(dtItems);
//wb.Worksheet(1).Cell("B1").Value = "0";
//wb.Worksheet(1).Column(2).CellsUsed().SetDataType(XLDataType.Number);
//wb.Worksheet(1).Cell("B1").Value = "Saldo Inicial";
wb.SaveAs(archivo);
UtilitarioArchivos.AbrirArchivo(archivo);
lblProcesos.Text = "Archivo guardado en: " + archivo;
}
}
catch (Exception exl)
{
grid.DataSource = null;
lblError.Text = exl.Message;
}
}
}
//---------------------------------------------------------/
// Method
//---------------------------------------------------------/
public void ExcelFile(string proc)
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
// Wakamatsu 20170313
try
{
using (oWBook = new XLWorkbook(fileName))
{
// 編集
switch (procList.IndexOf(proc))
{
case 0:
case 1:
if (pod == null || pod.Length == 0)
{
DMessage.DataNotExistence("中断します！");
return;
}
//MessageBox.Show("Excel書込み開始");
if (proc == payoff)
{
oWSheet = oWBook.Worksheet(1); // シートを開く
editOsPayOff(pod); // 起案書発行
}
else
{
// Wakamatsu 20170313
//decimal sum = 0M;
//int page = pod.Length / posRow;
//if (pod.Length % posRow > 0) page++;
//for (int i = 0; i < page; i++)
//{
// oWSheet = oWBook.Worksheet(i + 1); // シートを開く
// oWSheet.Cell(3, 5).Value = "'" + (i + 1).ToString() + "/" + page.ToString();
// sum = editOsPayOffS(pod, posRow * i, sum); // 起案書発行
//}
oWSheet = oWBook.Worksheet(1); // シートを開く
oWSheet.Cell(3, 5).Value = "'1/1";
editOsPayOffS(pod, posRow, 0); // 起案書発行
// Wakamatsu 20170313
}
break;
case 2:
if (pmd == null || pmd.Length == 0)
{
DMessage.DataNotExistence("中断します！");
return;
}
//MessageBox.Show("Excel書込み開始");
oWSheet = oWBook.Worksheet(1); // シートを開く
editOsPayment(pmd); // 調書発行
break;
default:
break;
}
// 保存
oWBook.SaveAs(tempFile); // Excel保存
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
// Wakamatsu 20170313
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
//System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
// pdf file 出力
DateTime now = DateTime.Now;
outputFile = System.IO.Path.GetDirectoryName(tempFile) + @"\" + pNameList[procList.IndexOf(proc)] + "_" + itemCode + "_" + now.ToString("yyMMddHHmmss");
PublishExcelToPdf etp = new PublishExcelToPdf();
// Wakamatsu 20170313
//etp.ExcelToPDF(tempFile, outputFile);
if (etp.ExcelToPDF(tempFile, outputFile) == true)
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
private void btnCreateReport_Click(object sender, EventArgs e)
{
var wb = new cXML.XLWorkbook();
var ws = wb.Worksheets.Add("Members");
string title = "Citadel Report";
if (cmbReportState.Text != "All")
{
title += " - " + cmbReportState.Text;
}
//Title
ws.Cell("B2").Value = title;
// Headers
ws.Cell("B3").Value = "Mem #";
ws.Cell("C3").Value = "FName";
ws.Cell("D3").Value = "LName";
ws.Cell("E3").Value = "Joined";
ws.Cell("F3").Value = "Grade";
ws.Cell("G3").Value = "Due";
int skipped = 0;
int total = 0;
int owing = 0;
int active = 0;
Double fees = 0;
Double _temp;
// Populate the spreadsheet.
for (int i = 4; i - 4 < students.GetLength(0); i++)
{
if (students[i - 4, 0] == null)
{
break;
}
if ((students[i - 4, 11] == cmbReportState.Text || cmbReportState.Text == "All") && rbAll.Checked || (rbOwing.Checked && students[i - 4, 3] != "$0.00") || (rbPaid.Checked && students[i - 4, 3] == "$0.00"))
{
ws.Cell("B" + (i - skipped).ToString()).Value = students[i - 4, 0]; // Member Numbers
ws.Cell("C" + (i - skipped).ToString()).Value = students[i - 4, 1]; // First Names
ws.Cell("D" + (i - skipped).ToString()).Value = students[i - 4, 2]; // Last Names
ws.Cell("E" + (i - skipped).ToString()).Value = students[i - 4, 4]; // Year Joined
ws.Cell("F" + (i - skipped).ToString()).Value = students[i - 4, 7]; // Grade
ws.Cell("G" + (i - skipped).ToString()).Value = students[i - 4, 3]; // Amount Due
if (students[i - 4, 3] != "$0.00")
{
owing++;
}
if (students[i - 4, 5] == "1")
{
active++;
}
Double.TryParse(students[i - 4, 3].Trim('$'), out _temp);
fees += _temp;
total++;
}
else
{
skipped++;
}
}
// Members Owing
ws.Cell("C" + (studentLength + 4 - skipped).ToString()).Value = "Owing:";
ws.Cell("C" + (studentLength + 4 - skipped).ToString()).Style.Font.SetBold();
ws.Cell("D" + (studentLength + 4 - skipped).ToString()).Value = owing.ToString() + " of " + total.ToString();
ws.Cell("D" + (studentLength + 4 - skipped).ToString()).Style.Alignment.Horizontal = cXML.XLAlignmentHorizontalValues.Center;
// Active Members
ws.Cell("C" + (studentLength + 5 - skipped).ToString()).Value = "Active:";
ws.Cell("C" + (studentLength + 5 - skipped).ToString()).Style.Font.SetBold();
ws.Cell("D" + (studentLength + 5 - skipped).ToString()).Value = active.ToString() + " of " + total.ToString();
ws.Cell("D" + (studentLength + 5 - skipped).ToString()).Style.Alignment.Horizontal = cXML.XLAlignmentHorizontalValues.Center;
// Total Fees due
ws.Cell("F" + (studentLength + 4 - skipped).ToString()).Value = "Total:";
ws.Cell("F" + (studentLength + 4 - skipped).ToString()).Style.Font.SetBold();
ws.Cell("G" + (studentLength + 4 - skipped).ToString()).Value = "$" + fees.ToString();
ws.Cell("G" + (studentLength + 4 - skipped).ToString()).Style.Alignment.Horizontal = cXML.XLAlignmentHorizontalValues.Center;
//From worksheet
var rngTable = ws.Range("B2:G" + (studentLength + 5 - skipped).ToString());
rngTable.FirstCell().Style
.Font.SetBold()
.Fill.SetBackgroundColor(cXML.XLColor.CornflowerBlue)
.Alignment.SetHorizontal(cXML.XLAlignmentHorizontalValues.Center);
rngTable.FirstRow().Merge();
var num = rngTable.Range("A1:A" + (studentLength + 2 - skipped).ToString());
num.Style.Alignment.Horizontal = cXML.XLAlignmentHorizontalValues.Center;
var due = rngTable.Range("F1:F" + (studentLength + 2 - skipped).ToString());
due.Style.Alignment.Horizontal = cXML.XLAlignmentHorizontalValues.Center;
var rngHeaders = rngTable.Range("A2:F2"); // The address is relative to rngTable (NOT the worksheet)
rngHeaders.Style.Alignment.Horizontal = cXML.XLAlignmentHorizontalValues.Center;
rngHeaders.Style.Font.Bold = true;
rngHeaders.Style.Font.FontColor = cXML.XLColor.DarkBlue;
rngHeaders.Style.Fill.BackgroundColor = cXML.XLColor.Aqua;
var rngData = ws.Range("B3:G" + (studentLength + 5 - skipped).ToString());
var excelTable = rngData.CreateTable();
excelTable.ShowTotalsRow = true;
//Add thick borders to the contents of the spreadsheet
ws.RangeUsed().Style.Border.OutsideBorder = cXML.XLBorderStyleValues.Thick;
ws.Columns().AdjustToContents();
wb.SaveAs(Path.Combine(Application.StartupPath, "Report" + reportSuffix() + ".xlsx"));
msgbox msg = new msgbox("Successfully generated report in program's local directory.", "Success", 1);
msg.Show();
}
private void btnCreateSenior_Click(object sender, EventArgs e)
{
var wb = new cXML.XLWorkbook();
var ws = wb.Worksheets.Add("Members");
string title = "Senior Report";
//Title
ws.Cell("B2").Value = title;
// Headers
ws.Cell("B3").Value = "State";
ws.Cell("C3").Value = "FName";
ws.Cell("D3").Value = "LName";
ws.Cell("E3").Value = "Email";
int skipped = 0;
int total = 0;
// Populate the spreadsheet.
foreach (string item in cmbReportState.Items)
{
skipped = 0;
for (int i = 4; i - 4 < students.GetLength(0); i++)
{
if (students[i - 4, 0] == null)
{
break;
}
if (students[i - 4, 7] == "12" && students[i - 4, 11] == item)
{
ws.Cell("B" + (i + total - skipped).ToString()).Value = students[i - 4, 11]; // State
ws.Cell("C" + (i + total - skipped).ToString()).Value = students[i - 4, 1]; // First Names
ws.Cell("D" + (i + total - skipped).ToString()).Value = students[i - 4, 2]; // Last Names
ws.Cell("E" + (i + total - skipped).ToString()).Value = students[i - 4, 9]; // Emails
total++;
}
else
{
skipped++;
}
}
}
skipped = studentLength - total;
//From worksheet
var rngTable = ws.Range("B2:E" + (studentLength + 5 - skipped).ToString());
rngTable.FirstCell().Style
.Font.SetBold()
.Fill.SetBackgroundColor(cXML.XLColor.CornflowerBlue)
.Alignment.SetHorizontal(cXML.XLAlignmentHorizontalValues.Center);
rngTable.FirstRow().Merge();
var rngHeaders = rngTable.Range("A2:D2"); // The address is relative to rngTable (NOT the worksheet)
rngHeaders.Style.Alignment.Horizontal = cXML.XLAlignmentHorizontalValues.Center;
rngHeaders.Style.Font.Bold = true;
rngHeaders.Style.Font.FontColor = cXML.XLColor.DarkBlue;
rngHeaders.Style.Fill.BackgroundColor = cXML.XLColor.Aqua;
var rngData = ws.Range("B3:E" + (studentLength + 5 - skipped).ToString());
var excelTable = rngData.CreateTable();
excelTable.ShowTotalsRow = true;
//Add thick borders to the contents of the spreadsheet
ws.RangeUsed().Style.Border.OutsideBorder = cXML.XLBorderStyleValues.Thick;
ws.Columns().AdjustToContents();
wb.SaveAs(Path.Combine(Application.StartupPath, "SeniorReport.xlsx"));
msgbox msg = new msgbox("Successfully generated senior report in program's local directory.", "Success", 1);
msg.Show();
}
//作業割り当て表（検査項目詳細版）Excelファイル出力
public void save_asignlist_by_details_xlsx(List <List <string> > pages, List <string> categories, List <List <object> > data, string filename)
{
_write_log __write_log = write_log;
try
{
using (var wb = new ClosedXML.Excel.XLWorkbook())
{
var ws = wb.Worksheets.Add("Sheet1");
//ヘッダーセルの処理
ws.Rows(1, 1).Height = 111.5;
ws.Cell(1, 1).SetValue <string>("PID");
//セル結合
ws.Range(ws.Cell(1, 1), ws.Cell(2, 1)).Merge(false);
ws.Range(ws.Cell(1, 1), ws.Cell(2, 1)).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Range(ws.Cell(1, 1), ws.Cell(2, 1)).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Range(ws.Cell(1, 1), ws.Cell(2, 1)).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Range(ws.Cell(1, 1), ws.Cell(2, 1)).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Range(ws.Cell(1, 1), ws.Cell(2, 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Range(ws.Cell(1, 1), ws.Cell(2, 1)).Style.Fill.BackgroundColor = XLColor.FromArgb(0xFFFFCC);
ws.Range(ws.Cell(1, 1), ws.Cell(2, 1)).Style.Font.FontName = "ＭＳ Ｐゴシック";
ws.Cell(1, 2).SetValue <string>("URL");
ws.Cell(1, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(1, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(1, 2).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
//セル結合
ws.Range(ws.Cell(1, 2), ws.Cell(2, 2)).Merge(false);
ws.Range(ws.Cell(1, 2), ws.Cell(2, 2)).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Range(ws.Cell(1, 2), ws.Cell(2, 2)).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Range(ws.Cell(1, 2), ws.Cell(2, 2)).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Range(ws.Cell(1, 2), ws.Cell(2, 2)).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Range(ws.Cell(1, 2), ws.Cell(2, 2)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Range(ws.Cell(1, 2), ws.Cell(2, 2)).Style.Fill.BackgroundColor = XLColor.FromArgb(0xFFFFCC);
ws.Range(ws.Cell(1, 2), ws.Cell(2, 2)).Style.Font.FontName = "ＭＳ Ｐゴシック";
//列カウンタ
int total_cnt = 3;
//偶数奇数カウンタ
int mod_cnt = 1;
//カテゴリのループ
for (int hx = 0; hx < categories.Count; hx++)
{
//結合範囲セル（カテゴリ名のセル）のRangeリスト
List <IXLCell> merge_range = new List <IXLCell>();
//検査項目のループ
for (int cx = 0; cx < data.Count; cx++)
{
//最初はカテゴリ名を入力
if (cx == 0)
{
//セル結合開始セルRangeを追加
merge_range.Add(ws.Column(total_cnt).Cell(1));
ws.Cell(1, total_cnt).SetValue <string>(categories[hx]);
ws.Cell(1, total_cnt).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Cell(1, total_cnt).Style.Font.FontName = "ＭＳ Ｐゴシック";
ws.Cell(1, total_cnt).Style.Alignment.TopToBottom = true;
//カテゴリの順番偶数・奇数で色分け
if ((mod_cnt % 2) == 0)
{
ws.Cell(1, total_cnt).Style.Fill.BackgroundColor = XLColor.FromArgb(0xCCFF66);
}
else
{
ws.Cell(1, total_cnt).Style.Fill.BackgroundColor = XLColor.FromArgb(0xCCECFF);
}
}
//カテゴリ名の入力セルの下の行に項番とコメント入力
var row = data[cx];
string v0 = (string)row[0];
double v1 = double.Parse((string)row[1]);
string v2 = (string)row[2];
string v3 = "";
int inx = 0;
List <string> gs = (List <string>)row[3];
foreach (string tx in gs)
{
v3 += tx;
if (inx != (gs.Count - 1))
{
v3 += "\r\n";
}
inx++;
}
//カレントカテゴリと一致する時だけ入力
if (v0 == categories[hx])
{
//数値はValueメソッドで入力
ws.Cell(2, total_cnt).Value = v1;
ws.Cell(2, total_cnt).Comment.AddText(v2 + v3);
ws.Cell(2, total_cnt).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(2, total_cnt).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(2, total_cnt).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(2, total_cnt).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(2, total_cnt).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell(2, total_cnt).Style.Font.FontName = "ＭＳ Ｐゴシック";
ws.Column(total_cnt).Width = 4;
//カテゴリの順番偶数・奇数で色分け
if ((mod_cnt % 2) == 0)
{
ws.Cell(2, total_cnt).Style.Fill.BackgroundColor = XLColor.FromArgb(0xCCFF66);
}
else
{
ws.Cell(2, total_cnt).Style.Fill.BackgroundColor = XLColor.FromArgb(0xCCECFF);
}
total_cnt++;
}
}
//セル結合終了セルRangeを追加
merge_range.Add(ws.Column(total_cnt - 1).Cell(1));
//セル結合
ws.Range(merge_range.First <IXLCell>(), merge_range.Last <IXLCell>()).Merge(false);
ws.Range(merge_range.First <IXLCell>(), merge_range.Last <IXLCell>()).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Range(merge_range.First <IXLCell>(), merge_range.Last <IXLCell>()).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Range(merge_range.First <IXLCell>(), merge_range.Last <IXLCell>()).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Range(merge_range.First <IXLCell>(), merge_range.Last <IXLCell>()).Style.Border.RightBorder = XLBorderStyleValues.Thin;
mod_cnt++;
}
//データセルの処理
//行のループ
for (int i = 0; i < pages.Count; i++)
{
List <string> row = (List <string>)pages[i];
//列のループ
for (int j = 0; j < row.Count; j++)
{
string col = (string)row[j];
ws.Cell(i + 3, j + 1).SetValue <string>(col);
ws.Cell(i + 3, j + 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 3, j + 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 3, j + 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 3, j + 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 3, j + 1).Style.Font.FontName = "ＭＳ Ｐゴシック";
ws.Cell(i + 3, j + 1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
}
}
//残りのセルの罫線処理
for (int i = 0; i < pages.Count; i++)
{
for (int z = 3; z < total_cnt; z++)
{
ws.Cell(i + 3, z).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 3, z).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 3, z).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 3, z).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 3, z).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell(i + 3, z).Style.Font.FontName = "ＭＳ Ｐゴシック";
}
}
wb.SaveAs(filename);
main_form.Invoke(__write_log, "保存に成功しました。（" + filename + "）");
}
}
catch (Exception ex)
{
main_form.Invoke(__write_log, "【エラー】" + ex.Message);
return;
}
}
/// <summary>
/// Scrive un datatable su Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="nomeStat"></param>
/// <param name="titolo"></param>
/// <param name="sheetName"></param>
/// <param name="args"></param>
/// <returns></returns>
public static ExcelRender EseguiRenderDataTableExcel(DataTable dt, string nomeStat, string titolo, string sheetName, Dictionary <string, string> args)
{
var oEsitoRender = new ExcelRender();
oEsitoRender.NomeFile = String.Format("Report_{0}_{1:yyyyMMdd_HHmmss}.xlsx", nomeStat, DateTime.Now);
var workbook = new ClosedXML.Excel.XLWorkbook();
//Se fornito imposta un nome sheet
if (string.IsNullOrEmpty(sheetName))
{
sheetName = nomeStat;
}
//Crea sheet dati
var worksheet = workbook.Worksheets.Add(dt, sheetName);
worksheet.Rows().AdjustToContents();
worksheet.Columns().AdjustToContents();
for (int i = 0; i < dt.Columns.Count - 1; i++)
{
var colIdx = i + 1;
if (dt.Columns[i].DataType == typeof(decimal))
{
worksheet.Column(colIdx).Cells().Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
worksheet.Column(colIdx).CellsUsed().Style.NumberFormat.Format = "0.00 €";
}
else
{
worksheet.Column(colIdx).Cells().Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
}
}
//Titolo
if (!String.IsNullOrEmpty(titolo))
{
//Add titolo
worksheet.FirstRowUsed().InsertRowsAbove(1);
//Ultima colonna
var cellLast = worksheet.Cell(1, dt.Columns.Count);
//Crea range e merge
var oRange = worksheet.Range("A1:" + cellLast.Address.ColumnLetter + "1").Merge();
oRange.Value = titolo;
oRange.Style.Font.Bold = true;
oRange.Style.Font.FontColor = ClosedXML.Excel.XLColor.CornflowerBlue;
oRange.Style.Font.FontSize = 16d;
oRange.Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.Yellow;
oRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
//Parametri
if (args != null && args.Count > 0)
{
//Crea sheet parametri
var worksheetp = workbook.Worksheets.Add("Parametri");
worksheetp.Column(1).Cell(1).Value = "Nome";
worksheetp.Column(2).Cell(2).Value = "Valore";
var iRow = 1;
foreach (var oParam in args)
{
worksheetp.Column(1).Cell(iRow).Value = oParam.Key;
worksheetp.Column(2).Cell(iRow).Value = oParam.Value;
iRow += 1;
}
worksheetp.Rows().AdjustToContents();
worksheetp.Columns().AdjustToContents();
worksheetp.Rows(1, 1).Style.Font.Bold = true;
}
//Scrive
var memoryStream = new MemoryStream();
workbook.SaveAs(memoryStream);
oEsitoRender.DatiMemory = memoryStream.ToArray();
return(oEsitoRender);
}
public JsonResult ExportExcel(
DateTime?dateStart, DateTime?dateFinish,
string start = "0", string finish = "z",
string productKind = "",
string orderby = "TotalQty", string sort = "desc", int page = 1)
{
ResultModel result = new ResultModel();
//建立Excel
string template =
System.Web.Hosting.HostingEnvironment.MapPath("~/Templates/產品銷售排行統計表.xlsx");
var exportFileName = $"{ Guid.NewGuid().ToString()}.xlsx";
var fullPath = Path.Combine(Server.MapPath("~/Temp"), exportFileName);
XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook(template);
int rowIndex = 7;
int rowIndexForCopy = 7;
int columnIndex = 1;
int rank = 0;
try
{
var infos = GeQuery(dateStart, dateFinish, start, finish, productKind, orderby, sort);
var sheet = workbook.Worksheets.First();
sheet.Cell(2, 2).Value = GetOrderByText(orderby); //排列方式
sheet.Cell(3, 2).Value = GetDateRange(dateStart, dateFinish); //銷售日期
sheet.Cell(4, 2).Value = GetProductKind(productKind); //產品類別
sheet.Cell(5, 2).Value = GetProductRange(start, finish); //產品編號
foreach (var item in infos)
{
sheet.Cell(rowIndex, columnIndex++).Value = ++rank;
sheet.Cell(rowIndex, columnIndex++).Value = $"'{ item.ProductID}";
sheet.Cell(rowIndex, columnIndex++).Value = item.ProductName;
sheet.Cell(rowIndex, columnIndex++).Value = item.TotalQty;
sheet.Cell(rowIndex, columnIndex++).Value = item.TotalAmount;
sheet.Cell(rowIndex, columnIndex++).Value = item.TotalProfit;
sheet.Cell(rowIndex, columnIndex++).Value = item.GrossProfitMargin;
rowIndex++;
columnIndex = 1;
sheet.Row(rowIndexForCopy).CopyTo(sheet.Row(rowIndex));
}
sheet.Row(rowIndex).Delete();
workbook.SaveAs(fullPath);
result.Status = true;
result.CloseWindow = false;
result.Url = exportFileName;
result.Message = "MessageComplete".ToLocalized();
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
return(Json(result));
}
private string ExportToExcel()
{
string outputPath = Path.Combine(Context.Config.GetDirectory("Upload"), "MemberReport");
string excelFile = Path.Combine(outputPath, "VipDailyJoinList.xlsx");
FileInfo xlsFile = new FileInfo(excelFile);
if (xlsFile.Exists)
{
xlsFile.Delete();
xlsFile = new FileInfo(excelFile);
}
var wb = new ClosedXML.Excel.XLWorkbook();
var worksheet = wb.Worksheets.Add("Sheet1");
// Set width of columns
worksheet.Column(1).Width = 13d;
worksheet.Column(2).Width = 15d;
worksheet.Column(3).Width = 18d;
worksheet.Column(4).Width = 18d;
worksheet.Column(5).Width = 12d;
worksheet.Column(6).Width = 12d;
worksheet.Column(7).Width = 12d;
worksheet.Column(8).Width = 12d;
worksheet.Column(9).Width = 12d;
// Setting first row
// Title
worksheet.Cell(1, 1).Value = "VIP2800 - VIP Daily Join In Report (Excel)";
// Report Criteria
worksheet.Cell(2, 1).Value = "DATE RANGE:";
worksheet.Cell(2, 2).Value = dtpFromRegDate.Value.ToString(DateTimeHelper.GetDateFormat()) + " TO " + dtpToRegDate.Value.ToString(DateTimeHelper.GetDateFormat());
worksheet.Cell(3, 1).Value = "PRINTED ON:";
worksheet.Cell(3, 2).Value = DateTime.Now.ToString(DateTimeHelper.GetDateFormat());
// Header
worksheet.Cell(5, 1).Value = "REGISTRATION DATE";
worksheet.Cell(5, 2).Value = "VIP#";
worksheet.Cell(5, 3).Value = "NAME";
worksheet.Cell(5, 4).Value = "EMAIL";
worksheet.Cell(5, 5).Value = "TEL. WORK";
worksheet.Cell(5, 6).Value = "TEL. HOME";
worksheet.Cell(5, 7).Value = "PAGER";
worksheet.Cell(5, 8).Value = "FAX";
worksheet.Cell(5, 9).Value = "OTHER";
// writing data
DataTable oTable = DataSource();
var rangeWithData = worksheet.Cell(7, 1).InsertData(oTable.AsEnumerable());
// add some document properties to the spreadsheet
// set some core property values
wb.Properties.Title = "VIP2800 - VIP Daily Join In Report (Excel)";
wb.Properties.Author = "RT2020";
wb.Properties.Subject = "VIP2800 - VIP Daily Join In Report (Excel)";
wb.Properties.Keywords = "RT2020";
wb.Properties.Category = "VIP2800 - VIP Daily Join In Report (Excel)";
wb.Properties.Comments = "VIP2800 - VIP Daily Join In Report (Excel)";
// set some extended property values
wb.Properties.Company = "Synergy";
// save our new workbook and we are done!
wb.SaveAs(excelFile);
return(excelFile);
}
//カテゴリ別検査項目一覧表Excelファイル作成
public void save_category_by_details_xlsx(List <List <object> > data, string filename)
{
_write_log __write_log = write_log;
try
{
using (var wb = new ClosedXML.Excel.XLWorkbook())
{
var ws = wb.Worksheets.Add("検査項目一覧");
//ヘッダー行
ws.Cell(1, 1).SetValue <string>("カテゴリ");
ws.Cell(1, 2).SetValue <string>("項番");
ws.Cell(1, 3).SetValue <string>("検査内容");
ws.Cell(1, 4).SetValue <string>("レベル/達成基準/実装番号");
double[] col_wd = { 15, 5, 91.9, 68 };
for (int cx = 1; cx <= 4; cx++)
{
ws.Cell(1, cx).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(1, cx).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(1, cx).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(1, cx).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(1, cx).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Cell(1, cx).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell(1, cx).Style.Font.Bold = true;
ws.Column(cx).Width = col_wd[cx - 1];
}
for (int i = 0; i < data.Count; i++)
{
var row = data[i];
string v0 = (string)row[0];
string v1 = (string)row[1];
string v2 = (string)row[2];
string v3 = "";
List <string> gs = (List <string>)row[3];
int nx = 0;
ws.Cell(i + 2, 1).SetValue <string>(v0);
ws.Cell(i + 2, 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Cell(i + 2, 2).SetValue <string>(v1);
ws.Cell(i + 2, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 2).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Cell(i + 2, 3).SetValue <string>(v2);
ws.Cell(i + 2, 3).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 3).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 3).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 3).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 3).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Cell(i + 2, 3).Style.Alignment.WrapText = true;
foreach (string tx in gs)
{
v3 += tx;
if (nx != (gs.Count - 1))
{
v3 += "\r\n";
}
nx++;
}
ws.Cell(i + 2, 4).SetValue <string>(v3);
ws.Cell(i + 2, 4).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 4).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 4).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 4).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(i + 2, 4).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Cell(i + 2, 4).Style.Alignment.WrapText = true;
}
wb.SaveAs(filename);
main_form.Invoke(__write_log, "保存に成功しました。（" + filename + "）");
}
}
catch (Exception ex)
{
main_form.Invoke(__write_log, "【エラー】" + ex.Message);
return;
}
}
public ActionResult ReporteServicios(string fechaInicio, string fechaFin, string submitButton)
{
try
{
DateTime fechaI = new DateTime();
DateTime fechaF = new DateTime();
if (fechaInicio == "")
{
fechaI = DateTime.MinValue;
}
else
{
fechaI = DateTime.Parse(fechaInicio);
}
if (fechaFin == "")
{
fechaF = DateTime.MaxValue;
}
else
{
fechaF = DateTime.Parse(fechaFin);
}
List <Incidencia> incidencias = fachada.GetIncidenciasReporte(EstadoIncidencia.Finalizada, fechaI, fechaF);
List <Servicio> listaServicios = new List <Servicio>();
foreach (Incidencia i in incidencias)
{
List <Servicio> servicios = fachada.GetServiciosIncidencia(i.Id);
if (servicios.Count() != 0)
{
if (servicios.Count() != 0)
{
listaServicios.AddRange(servicios);
}
}
}
if (submitButton == "Filtrar")
{
ViewBag.FechaInicio = fechaInicio;
ViewBag.FechaFin = fechaFin;
return(View(listaServicios));
}
else if (submitButton == "Exportar")
{
if (listaServicios.Count > 0)
{
DataTable table = new DataTable();
table.Columns.Add("Tipo", typeof(string));
table.Columns.Add("Vehículo", typeof(string));
table.Columns.Add("Taller", typeof(string));
table.Columns.Add("Fecha Entrada", typeof(string));
table.Columns.Add("Fecha Salida", typeof(string));
table.Columns.Add("Estado", typeof(string));
table.Columns.Add("Descripción", typeof(string));
table.Columns.Add("Número de orden", typeof(string));
// Add Three rows with those columns filled in the DataTable.
foreach (Servicio s in listaServicios)
{
table.Rows.Add(s.Tipo, s.Vehiculo.Matricula, s.Taller.Nombre, s.FechaEntrada, s.FechaSalida, s.Estado, s.Descripcion, s.NumeroOrden);
}
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
wbook.Worksheets.Add(table, "tab1");
// Prepare the response
HttpResponseBase httpResponse = Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Provide you file name here
httpResponse.AddHeader("content-disposition", "attachment;filename=\"Reporte Servicios.xlsx\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wbook.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
return(View(listaServicios));
}
else
{
throw new MyException("No hay servicios para exportar en el período");
}
}
return(View(listaServicios));
}
catch (Exception ex)
{
throw ex;
}
}
//---------------------------------------------------------/
// Method
//---------------------------------------------------------/
public void ExcelFile()
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
try
{
using (oWBook = new XLWorkbook(fileName))
{
if (tncA == null || tncA.Length == 0)
{
DMessage.DataNotExistence("中断します！");
return;
}
// 編集
oWSheet = oWBook.Worksheet(1); // シートを開く
readyExcelRows(tncA.Length, 4);
int sNo = 5;
for (int i = 0; i < tncA.Length; i++)
{
using (IXLRange SetRange = oWSheet.Range("A5:M5"))
// テンプレートデータ行コピー/ペースト
SetRange.CopyTo(oWSheet.Cell(sNo + i, 1));
if (i == 0)
{
officeName = tncA[i].OfficeName;
oWSheet.Cell(2, 3).Value = DateTime.Today;
oWSheet.Cell(3, 3).Value = officeName;
}
oWSheet.Cell(sNo + i, 1).Value = i + 1;
oWSheet.Cell(sNo + i, 2).Value = tncA[i].TaskCode;
oWSheet.Cell(sNo + i, 3).Value = tncA[i].TaskName;
oWSheet.Cell(sNo + i, 4).Value = tncA[i].VersionNo;
oWSheet.Cell(sNo + i, 5).Value = tncA[i].IssueDate;
oWSheet.Cell(sNo + i, 6).Value = tncA[i].SalesMName;
oWSheet.Cell(sNo + i, 7).Value = tncA[i].SalesMInputDate;
oWSheet.Cell(sNo + i, 8).Value = tncA[i].Approval;
oWSheet.Cell(sNo + i, 9).Value = tncA[i].ApprovalDate;
oWSheet.Cell(sNo + i, 10).Value = tncA[i].MakeOrder;
oWSheet.Cell(sNo + i, 11).Value = tncA[i].MakeOrderDate;
oWSheet.Cell(sNo + i, 12).Value = tncA[i].ConfirmAdm;
oWSheet.Cell(sNo + i, 13).Value = tncA[i].ConfirmDate;
if (i != 0)
{
oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.TopBorder = XLBorderStyleValues.Hair;
}
if (i == tncA.Length - 1)
{
oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
}
else
{
oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
}
}
}
// 保存
oWBook.SaveAs(tempFile); // Excel保存
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
// pdf出力にする場合は、上記 System.DiafnosticsのLineをコメントアウトし、下記DateTime以下のコメントを外す。
// pdf file 出力
//DateTime now = DateTime.Now;
//outputFile = System.IO.Path.GetDirectoryName( tempFile ) + @"\業務引継書承認未完了一覧表_" + officeName + "_" + "_" + now.ToString( "yyMMddHHmmss" );
//PublishExcelToPdf etp = new PublishExcelToPdf();
//etp.ExcelToPDF( tempFile, outputFile );
//if( File.Exists( tempFile ) ) File.Delete( tempFile );
}
// Public
public void Create(String filePath)
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Outline");
ws.Outline.SummaryHLocation = XLOutlineSummaryHLocation.Right;
ws.Columns(2, 6).Group(); // Create an outline (level 1) for columns 2-6
ws.Columns(2, 4).Group(); // Create an outline (level 2) for columns 2-4
ws.Column(2).Ungroup(true); // Remove column 2 from all outlines
ws.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Bottom;
ws.Rows(1, 5).Group(); // Create an outline (level 1) for rows 1-5
ws.Rows(1, 4).Group(); // Create an outline (level 2) for rows 1-4
ws.Rows(1, 4).Collapse(); // Collapse rows 1-4
ws.Rows(1, 2).Group(); // Create an outline (level 3) for rows 1-2
ws.Rows(1, 2).Ungroup(); // Ungroup rows 1-2 from their last outline
// You can also Collapse/Expand specific outline levels
//
// ws.CollapseRows(Int32 outlineLevel)
// ws.CollapseColumns(Int32 outlineLevel)
//
// ws.ExpandRows(Int32 outlineLevel)
// ws.ExpandColumns(Int32 outlineLevel)
// And you can also Collapse/Expand ALL outline levels in one shot
//
// ws.CollapseRows()
// ws.CollapseColumns()
//
// ws.ExpandRows()
// ws.ExpandColumns()
wb.SaveAs(filePath);
}
public static void exportToExcel(DataTable table, string tableName, string workSheetName, string fileName)
{
// Create the excel file and add worksheet
XLWorkbook workBook = new XLWorkbook();
IXLWorksheet workSheet = workBook.Worksheets.Add(workSheetName);
// Hardcode title and contents locations
IXLCell titleCell = workSheet.Cell(2, 2);
IXLCell contentsCell = workSheet.Cell(3, 2);
//Pretty-up the title
titleCell.Value = tableName;
titleCell.Style.Font.Bold = true;
titleCell.Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// Merge cells for title
workSheet.Range(titleCell, workSheet.Cell(2, table.Columns.Count + 1)).Merge();
// Insert table contents, and adjust for content width
contentsCell.InsertTable(table);
workSheet.Columns().AdjustToContents(1, 75);
// Create a new response and flush it to a memory stream
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();
response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx;");
using (MemoryStream stream = new MemoryStream())
{
workBook.SaveAs(stream);
stream.WriteTo(response.OutputStream);
stream.Close();
}
response.End();
}
// Public
public void Create(String filePath)
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Defining a Range");
// With a string
var range1 = ws.Range("A1:B1");
range1.Cell(1, 1).Value = "ws.Range(\"A1:B1\").Merge()";
range1.Merge();
// With two XLAddresses
var range2 = ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2).Address);
range2.Cell(1, 1).Value = "ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2).Address).Merge()";
range2.Merge();
// With two strings
var range4 = ws.Range("A3", "B3");
range4.Cell(1, 1).Value = "ws.Range(\"A3\", \"B3\").Merge()";
range4.Merge();
// With 4 points
var range5 = ws.Range(4, 1, 4, 2);
range5.Cell(1, 1).Value = "ws.Range(4, 1, 4, 2).Merge()";
range5.Merge();
ws.Column("A").AdjustToContents();
workbook.SaveAs(filePath);
}
private static void CreateExcelFile(ChromeDriver driver)
{
var homeTeams = driver.FindElements(By.ClassName("team-home"));
var awayTeams = driver.FindElements(By.ClassName("team-away"));
var scores = driver.FindElements(By.ClassName("score"));
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Scores");
ws.Cell("A1").Value = "Home Team";
ws.Cell("B1").Value = "Score";
ws.Cell("C1").Value = "Away Team";
for (int i = 0; i < homeTeams.Count; i++)
{
string homeTeam = homeTeams[i].Text;
string score = scores[i].Text;
string awayTeam = awayTeams[i].Text;
ws.Cell("A" + (i + 2)).Value = homeTeam;
ws.Cell("B" + (i + 2)).Value = score;
ws.Cell("C" + (i + 2)).Value = awayTeam;
}
// Beautify
ws.Range("A1:C1").Style.Font.Bold = true;
ws.Columns().AdjustToContents();
wb.SaveAs("../../../../FlashScore.xlsx");
}
