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
}
//var wb = new XLWorkbook(northwinddataXlsx);
public ExcelHelper(string filePath)
{
this.filePath = filePath;
workbook = new XLWorkbook(filePath);
if(workbook.Worksheets.Count>0)
worksheet = workbook.Worksheet(1);
}
/// <summary>
/// Creates a new parser using the given <see cref="IXLWorksheet"/> and <see cref="CsvConfiguration"/>.
/// </summary>
/// <param name="worksheet">The <see cref="IXLWorksheet"/> with the data.</param>
/// <param name="configuration">The configuration.</param>
public ExcelParser(IXLWorksheet worksheet, CsvConfiguration configuration)
{
workbook = worksheet.Workbook;
this.worksheet = worksheet;
this.configuration = configuration;
FieldCount = worksheet.RowsUsed().CellsUsed().Max(cell => cell.Address.ColumnNumber);
}
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 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);
}
public void IsBlank_false()
{
var ws = new XLWorkbook().AddWorksheet("Sheet");
ws.Cell("A1").Value = " ";
var actual = ws.Evaluate("=IsBlank(A1)");
Assert.AreEqual(false, actual);
}
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; // マウスカーソルを戻す
}
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");
}
private void WriteCategoriesByMonth(XLWorkbook workbook)
{
var ws = workbook.AddWorksheet("Categorization (by month)");
var all_posts = profile.AggregatedPosts().OrderBy(p => p.Date);
var first_date = all_posts.First().Date;
var last_date = all_posts.Last().Date;
var all_categories = Category.Flatten(profile.RootCategory);
var posts_by_category = all_categories.ToDictionary(category => category, category => category.AggregatePosts().Distinct().ToList());
ws.Row(1).Cell(1).SetValue("Date");
for (int i = 0; i < all_categories.Count; i++)
{
ws.Row(1).Cell(i + 2).SetValue(all_categories[i].Name);
}
var current_date = new DateTime(first_date.Year, first_date.Month, 1);
last_date = new DateTime(last_date.Year, last_date.Month, 1).AddMonths(1);
var current_row = 2;
while (current_date != last_date)
{
ws.Row(current_row).Cell(1).SetValue(current_date.ToShortDateString());
for (int j = 0; j < all_categories.Count; j++)
{
var total = posts_by_category[all_categories[j]].Where(p => p.Date.Year == current_date.Year && p.Date.Month == current_date.Month).Sum(p => p.Value);
ws.Row(current_row).Cell(j + 2).SetValue(total);
}
current_row++;
current_date = current_date.AddMonths(1);
}
}
public void Then_feature_with_background_is_added_successfully()
{
var excelFeatureFormatter = Container.Resolve<ExcelFeatureFormatter>();
var feature = new Feature
{
Name = "Test Feature",
Description =
"In order to test this feature,\nAs a developer\nI want to test this feature",
};
var background = new Scenario
{
Name = "Test Background Scenario",
Description =
"In order to test this background,\nAs a developer\nI want to test this background"
};
var given = new Step { NativeKeyword = "Given", Name = "a precondition" };
background.Steps = new List<Step>(new[] { given });
feature.AddBackground(background);
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
excelFeatureFormatter.Format(worksheet, feature);
Check.That(worksheet.Cell("B4").Value).IsEqualTo(background.Name);
Check.That(worksheet.Cell("C5").Value).IsEqualTo(background.Description);
Check.That(worksheet.Cell("D6").Value).IsEqualTo(given.Name);
}
}
public void InsertingColumnsPreservesFormatting()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLColumn column1 = ws.Column(1);
column1.Style.Fill.SetBackgroundColor(XLColor.FrenchLilac);
column1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fulvous);
IXLColumn column2 = ws.Column(2);
column2.Style.Fill.SetBackgroundColor(XLColor.Xanadu);
column2.Cell(2).Style.Fill.SetBackgroundColor(XLColor.MacaroniAndCheese);
column1.InsertColumnsAfter(1);
column1.InsertColumnsBefore(1);
column2.InsertColumnsBefore(1);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Column(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Column(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Column(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Column(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Xanadu, ws.Column(5).Style.Fill.BackgroundColor);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Cell(2, 1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(2, 5).Style.Fill.BackgroundColor);
}
// 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 void InsertingRowsPreservesFormatting()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
row1.Style.Fill.SetBackgroundColor(XLColor.FrenchLilac);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fulvous);
IXLRow row2 = ws.Row(2);
row2.Style.Fill.SetBackgroundColor(XLColor.Xanadu);
row2.Cell(2).Style.Fill.SetBackgroundColor(XLColor.MacaroniAndCheese);
row1.InsertRowsBelow(1);
row1.InsertRowsAbove(1);
row2.InsertRowsAbove(1);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Row(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Row(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Row(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Row(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Xanadu, ws.Row(5).Style.Fill.BackgroundColor);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Cell(1, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(3, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(4, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(5, 2).Style.Fill.BackgroundColor);
}
public void Build(GeneralTree<INode> 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())
{
var actionVisitor = new ActionVisitor<INode>(node =>
{
var featureDirectoryTreeNode =
node as FeatureNode;
if (featureDirectoryTreeNode != null)
{
IXLWorksheet worksheet =
workbook.AddWorksheet(
this.excelSheetNameGenerator.GenerateSheetName(
workbook,
featureDirectoryTreeNode
.Feature));
this.excelFeatureFormatter.Format(
worksheet,
featureDirectoryTreeNode.Feature);
}
});
features.AcceptVisitor(actionVisitor);
this.excelTableOfContentsFormatter.Format(workbook, features);
workbook.SaveAs(spreadsheetPath);
}
}
public ActionResult Import(int?IDNamHoc, HttpPostedFileBase excel, string LHDT, int sheet = 1)
{
if (Path.GetExtension(excel.FileName) != ".xlsx")
{
ViewBag.IDNamHocs = db.NamHocs.Where(q => q.Active != false).CreateSelectList(q => q.IDNamHoc, q => q.mNamHoc, IDNamHoc);
ModelState.AddModelError("", "Vui lòng chọn tệp tin định dạng .xlsx");
return(View());
}
var workbook = new ClosedXML.Excel.XLWorkbook(excel.InputStream);
var ws = workbook.Worksheet(sheet);
var lst = (from row in ws.Rows(2, ws.RowsUsed().Count())
select GetLopHP(IDNamHoc, row)).ToList();
lst.ForEach(q =>
{
if (q != null)
{
q.LHDT = LHDT;
_fillHeSo(q);
db.LopHPs.Add(q);
}
});
db.SaveChanges();
return(RedirectToAction("Index", new { IDNamHoc }));
}
//---------------------------------------------------------//
// 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
}
//----------------------------------------------------------------------
// 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);
}
public void ThenTableAddedSuccessfully()
{
var excelTableFormatter = Container.Resolve<ExcelTableFormatter>();
var table = new Table();
table.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4");
table.DataRows =
new List<TableRow>(new[] {new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8")});
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
int row = 6;
excelTableFormatter.Format(worksheet, table, ref row);
Check.That(worksheet.Cell("D6").Value).IsEqualTo("Var1");
Check.That(worksheet.Cell("E6").Value).IsEqualTo("Var2");
Check.That(worksheet.Cell("F6").Value).IsEqualTo("Var3");
Check.That(worksheet.Cell("G6").Value).IsEqualTo("Var4");
Check.That(worksheet.Cell("D7").Value).IsEqualTo(1.0);
Check.That(worksheet.Cell("E7").Value).IsEqualTo(2.0);
Check.That(worksheet.Cell("F7").Value).IsEqualTo(3.0);
Check.That(worksheet.Cell("G7").Value).IsEqualTo(4.0);
Check.That(worksheet.Cell("D8").Value).IsEqualTo(5.0);
Check.That(worksheet.Cell("E8").Value).IsEqualTo(6.0);
Check.That(worksheet.Cell("F8").Value).IsEqualTo(7.0);
Check.That(worksheet.Cell("G8").Value).IsEqualTo(8.0);
Check.That(row).IsEqualTo(9);
}
}
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");
}
public void Dispose()
{
if (_wb != null)
{
_wb.Dispose();
}
_wb = null;
}
static void Main(string[] args)
{
//Console.WriteLine("Hello World!");
var s = GetExcelPath();
var bytes = GetExcel(s);
var wb = new ClosedXML.Excel.XLWorkbook(new MemoryStream(bytes));
byte[] file = System.IO.File.ReadAllBytes([ExcelPath])
internal static void VerifyExcel(MemoryStream stream,
[CallerMemberName] string?caller = null)
{
stream.Seek(0, SeekOrigin.Begin);
//File.WriteAllBytes(caller + ".xlsx", stream.ToArray());
var book = new ClosedXML.Excel.XLWorkbook(stream);
VerifyExcel(book.Worksheet(1), caller);
}
public static XLS.XLWorkbook getClosedXMLWorkbook()
{
XLS.XLWorkbook workbook = new XLS.XLWorkbook(@"C:\Users\fleet\Documents\cruises.xlsx");
var worksheet = workbook.Worksheet(1);
var usedRange = worksheet.RangeUsed();
Console.WriteLine("USED: {0}", usedRange.RowCount());
return(workbook);
}
//作業割り当て表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;
}
}
public static void ExcelWorkingReader(string strPath)
{
Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);
foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
{
if (Isheet.Name == "T1科技")
{
ReadT1Item(Isheet);
}
}
}
public static void ExcelP4Break(string strPath)
{
Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);
foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
{
if (Isheet.Name == "P4碎铁")
{
ReadP4Break(Isheet);
}
}
}
public static void ExcelMoonChart(string strPath)
{
Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);
foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
{
if (Isheet.Name == "卫星矿")
{
ReadIceChart(Isheet);
}
}
}
public static void ExcelOreRecycle(string strPath)
{
Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);
foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
{
if (Isheet.Name == "基础矿石")
{
ReadOreRecycle(Isheet);
}
}
}
public static void ExcelT2High(string strPath)
{
Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);
foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
{
if (Isheet.Name == "T2高级反应")
{
ReadT2High(Isheet);
}
}
}
public void Export()
{
using (this.workbook = new ClosedXML.Excel.XLWorkbook())
{
foreach (var item in sheetTemplate)
{
var sheet = this.workbook.AddWorksheet(item.Name);
sheet = item.GetTemplate(sheet);
}
this.Response();
}
}
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
}
//}
}
public void Export()
{
using (this.workbook = new ClosedXML.Excel.XLWorkbook())
{
foreach (var item in sheets)
{
var sheet = this.workbook.AddWorksheet(item.SheetName);
WorksheetManager sh = new WorksheetManager(sheet);
sh.Setting = item;
sheet = sh.RenderSheet();
}
this.Response();
}
}
public static void ExcelReader(string strPath)
{
Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);
foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
{
if (Isheet.Name == "物品列表")
{
ReadItem(Isheet);
}
//if (Isheet.Name == "星系列表")
//{
// ReadSolarSystem(Isheet);
//}
}
}
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);
}
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 void btnLoadExcel_Click(object sender, EventArgs e)
{
var s = GetExcelPath();
var bytes = GetExcel(s);
var wb = new ClosedXML.Excel.XLWorkbook(new MemoryStream(bytes));
var l = wb.ReadTable(1,
new ReadOptions()
{
TitlesInFirstRow = true
});
dataGridView1.DataSource = l;
}
static void Main(string[] args)
{
//Console.WriteLine("Hello World!");
var s = GetExcelPath();
var bytes = GetExcel(s);
var wb = new ClosedXML.Excel.XLWorkbook(new MemoryStream(bytes));
IEnumerable <SimpleTable> data = wb.ReadTable <SimpleTable>(1);
var l = wb.ReadTable <Models.TableWithHeaders>(1,
new ReadOptions()
{
TitlesInFirstRow = true
});
Console.WriteLine(l.ToList().Count);
//Example with trasnformations
Expression <Func <string, DateTime> > fConvertData = _ => DateTime.Now.AddDays(10);
Expression <Func <string, bool> > fSelectToBool = c => c == "x";
var ls = wb.ReadTable <Models.TableWithHeadersAndBoleanConversion>(1,
new ReadOptions()
{
TitlesInFirstRow = true,
Converters = new Dictionary <string, LambdaExpression>()
{
{ nameof(TableWithHeadersAndBoleanConversion.Birthday), fConvertData },
{ nameof(TableWithHeadersAndBoleanConversion.Selected), fSelectToBool },
}
});
wb.Dispose();
Console.WriteLine(ls.ToList().Count);
Console.ReadLine();
}
public static IList <CruiseData> getCruisedataFromExcel()
{
IList <CruiseData> cruisedataList = new List <CruiseData>();
XLS.XLWorkbook workbook = new XLS.XLWorkbook(@"C:\Users\fleet\Documents\cruises.xlsx");
var worksheet = workbook.Worksheet(1);
var usedRange = worksheet.RangeUsed();
for (int i = 1; i <= usedRange.RowCount(); i++)
{
CruiseData cs = new CruiseData();
cs.cruise = worksheet.Row(i).Cell(1).GetValue <String>();
cs.ship = worksheet.Row(i).Cell(2).GetValue <String>();
cs.price = worksheet.Row(i).Cell(3).GetValue <String>();
cruisedataList.Add(cs);
}
workbook.Dispose();
return(cruisedataList);
}
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);
}
//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;
}
}
}
private void button_Click(object sender, EventArgs e)
{
if (initProc)
{
return;
}
Button btn = (Button)sender;
switch (btn.Name)
{
case "buttonOpen":
//fileName = Files.Open( "M_Calendar.xlsx", Folder.MyDocuments(), "xlsx" );
fileName = Files.Open(BookName, Folder.MyDocuments(), "xlsx");
if (fileName == null)
{
textBoxMsg.AppendText("× " + fileName + "は不適切なファイルです。処理続行不可能です。\r\n");
}
else
{
textBoxMsg.AppendText("☆ " + fileName + "の内容でカレンダマスタを再作成します。\r\n");
}
break;
case "buttonCancel":
// Wakamatsu 20170323
fileName = null;
textBoxMsg.Text = "";
break;
case "buttonStart":
if (fileName == null)
{
// Wakamatsu 20170323
textBoxMsg.AppendText("× 取り込むファイルを指定してください。\r\n");
return;
}
MasterMaintOp mmo = new MasterMaintOp();
// Wakamatsu 20170227
//if (!mmo.MCalendar_Delete())
//{
// textBoxMsg.AppendText("× " + "旧データの削除に失敗しました処理を中断します。\r\n");
// return;
//}
//mmo = new MasterMaintOp();
// Wakamatsu 20170227
int procCount = 0;
switch (System.IO.Path.GetExtension(fileName))
{
case ".xlsx":
// Wakamatsu 20170227
try
{
oWBook = new XLWorkbook(fileName);
procCount = mmo.MaintCalendarByExcelData(oWBook.Worksheet(1));
// Wakamatsu 20170227
if (procCount < 0)
{
if (procCount == -2)
{
textBoxMsg.AppendText("× 旧データの削除に失敗しました処理を中断します。\r\n");
}
textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n");
return;
}
// Wakamatsu 20170227
}
// Wakamatsu 20170227
catch (Exception ex)
{
textBoxMsg.AppendText(ex.Message + "\r\n");
textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n");
return;
}
// Wakamatsu 20170227
break;
default:
procCount = -1;
textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n");
break;
}
if (procCount < 0)
{
return;
}
textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n");
textBoxMsg.AppendText(procCount + "件のデータを登録しました。\r\n");
break;
// Wakamatsu
case "buttonExport":
textBoxMsg.AppendText("☆ 処理を開始しました。\r\n");
string SetSQL = "";
SetSQL += "MDate, DType ";
SetSQL += "FROM M_Calendar ";
SetSQL += "ORDER BY MDate";
SqlHandling sqlh = new SqlHandling(); // SQL実行クラス
// レコードを取得する
DataTable dt = sqlh.SelectFullDescription(SetSQL);
if (dt == null)
{
textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n");
return;
}
// フォーマット設定用構造体
PrintOut.Publish.FormatSet[] FormatSet = new PrintOut.Publish.FormatSet[dt.Columns.Count];
// フォーマット設定
FormatSetting(ref FormatSet);
// Excel出力クラス
//PrintOut.Publish publ = new PrintOut.Publish( Folder.DefaultExcelTemplate( "M_Calendar.xlsx" ) );
PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName));
// Excelファイル出力
//textBoxMsg.AppendText( publ.ExcelFile( "M_Calendar", dt, FormatSet ) );
// Wakamatsu 20170301
//textBoxMsg.AppendText(publ.ExcelFile(SheetName, dt, FormatSet));
textBoxMsg.AppendText(publ.ExcelFile(masterName, SheetName, dt, FormatSet));
// Wakamatsu 20170301
break;
// Wakamatsu
case "buttonEnd":
this.Close();
break;
default:
break;
}
}
//---------------------------------------------------------/
// 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 void ThenSingleScenarioWithStepsAddedSuccessfully()
{
var excelScenarioFormatter = Container.Resolve<ExcelScenarioFormatter>();
var scenario = new Scenario
{
Name = "Test Feature",
Description =
"In order to test this feature,\nAs a developer\nI want to test this feature"
};
var given = new Step {NativeKeyword = "Given", Name = "a precondition"};
var when = new Step {NativeKeyword = "When", Name = "an event occurs"};
var then = new Step {NativeKeyword = "Then", Name = "a postcondition"};
scenario.Steps = new List<Step>(new[] {given, when, then});
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
int row = 3;
excelScenarioFormatter.Format(worksheet, scenario, ref row);
worksheet.Cell("B3").Value.ShouldEqual(scenario.Name);
worksheet.Cell("C4").Value.ShouldEqual(scenario.Description);
row.ShouldEqual(8);
}
}
// 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);
}
// 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 ExcelWriter(string newFilePath)
{
if (!string.IsNullOrEmpty(newFilePath))
filePath = newFilePath;
workbook = new XLWorkbook();
}
private void findProvderName()
{
Point providerLocation = new Point(0, 0);
for (int metricNumber = 0; metricNumber < 16; metricNumber++)//total numebr of metrics = 15
{
if (metricNumber < 10)
workbook = workbooks[0];
else if (metricNumber == 10)
workbook = workbooks[1];
else if (metricNumber > 10 && metricNumber < 14)
workbook = workbooks[2];
else if (metricNumber == 14)
workbook = workbooks[3];
else if (metricNumber == 15)
workbook = workbooks[4];
var sheet = workbook.Worksheet(1);
var colRange = sheet.Range("A:A");
foreach (var cell in colRange.CellsUsed())
{
if (cell.Value != null)
{
String value = (String)cell.Value;
int cellRow = cell.Address.RowNumber;
if (value.Contains(provider))
{
providerLocation = new Point(1, cellRow);
setMetricDataLocations(providerLocation, metricNumber);
break;
}
}
}
}
}
public void ThenTableAddedSuccessfully()
{
var excelTableFormatter = Kernel.Get<ExcelTableFormatter>();
var table = new Table();
table.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4");
table.DataRows =
new List<TableRow>(new[] {new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8")});
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
int row = 6;
excelTableFormatter.Format(worksheet, table, ref row);
worksheet.Cell("D6").Value.ShouldEqual("Var1");
worksheet.Cell("E6").Value.ShouldEqual("Var2");
worksheet.Cell("F6").Value.ShouldEqual("Var3");
worksheet.Cell("G6").Value.ShouldEqual("Var4");
worksheet.Cell("D7").Value.ShouldEqual(1.0);
worksheet.Cell("E7").Value.ShouldEqual(2.0);
worksheet.Cell("F7").Value.ShouldEqual(3.0);
worksheet.Cell("G7").Value.ShouldEqual(4.0);
worksheet.Cell("D8").Value.ShouldEqual(5.0);
worksheet.Cell("E8").Value.ShouldEqual(6.0);
worksheet.Cell("F8").Value.ShouldEqual(7.0);
worksheet.Cell("G8").Value.ShouldEqual(8.0);
row.ShouldEqual(9);
}
}
public ActionResult CreateStandardReport(AutoModel model)
{
string filename = "testje.xlsx";
XLWorkbook workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("Scheet");
worksheet.ActiveCell = worksheet.Cell(string.Format("A1"));
worksheet.ActiveCell.Value = model.Naam;
worksheet.ActiveCell = worksheet.Cell(string.Format("B1"));
worksheet.ActiveCell.Value = model.AantalWielen;
//worksheet.Columns().AdjustToContents();
worksheet.ActiveCell = worksheet.Cell("A1");
try
{
DownloadExcel(workbook, filename);
model.Message = "Succes!";
return View(model);
}
catch (Exception ex)
{
model.Message = ex.Message;
return View("About", model);
}
}
public void TestRowCopyContents()
{
var workbook = new XLWorkbook();
IXLWorksheet originalSheet = workbook.Worksheets.Add("original");
IXLWorksheet copyRowSheet = workbook.Worksheets.Add("copy row");
IXLWorksheet copyRowAsRangeSheet = workbook.Worksheets.Add("copy row as range");
IXLWorksheet copyRangeSheet = workbook.Worksheets.Add("copy range");
originalSheet.Cell("A2").SetValue("test value");
originalSheet.Range("A2:E2").Merge();
{
IXLRange originalRange = originalSheet.Range("A2:E2");
IXLRange destinationRange = copyRangeSheet.Range("A2:E2");
originalRange.CopyTo(destinationRange);
}
CopyRowAsRange(originalSheet, 2, copyRowAsRangeSheet, 3);
{
IXLRow originalRow = originalSheet.Row(2);
IXLRow destinationRow = copyRowSheet.Row(2);
copyRowSheet.Cell("G2").Value = "must be removed after copy";
originalRow.CopyTo(destinationRow);
}
TestHelper.SaveWorkbook(workbook, @"Misc\CopyRowContents.xlsx");
}
public void Format(XLWorkbook workbook, GeneralTree<IDirectoryTreeNode> features)
{
IXLWorksheet tocWorksheet = workbook.AddWorksheet("TOC", 0);
int startRow = 1;
BuildTableOfContents(workbook, tocWorksheet, ref startRow, 1, features);
}
public void CopyingRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
ws.Cell(2, 1).Value = row1;
ws.Cell(3, 1).Value = row1.Row(1, 7);
IXLRow row2 = ws.Row(2);
Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);
IXLRow row3 = ws.Row(3);
Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
}
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 void _openExcel(String dashboardFile, String metricsFolder)
{
//grab all the files in the metrics folder
metrics_files = Directory.GetFiles(metricsFolder);
if (File.Exists("Copy.xlsx"))
{
File.Delete("Copy.xlsx");
}
File.Copy(dashboardFile, "Copy.xlsx");
//try openeing the excel sheets listed in metricsFile
try
{
_dashboard = new XLWorkbook(dashboardFile);
//iterate thorugh all the dashboard sheets, prep them for new daata by adding new rows, then extrract data from
//metrics reports and insert into those sheets
int numSheets = _dashboard.Worksheets.Count;
for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
{
var sheet = _dashboard.Worksheet(sheetNum);
prepDashboardSheets(sheet);
}
_dashboard.Save();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public void CanGetNamedFromAnother()
{
var wb = new XLWorkbook();
var ws1 = wb.Worksheets.Add("Sheet1");
ws1.Cell("A1").SetValue(1).AddToNamed("value1");
Assert.AreEqual(1, wb.Cell("value1").GetValue<int>());
Assert.AreEqual(1, wb.Range("value1").FirstCell().GetValue<int>());
Assert.AreEqual(1, ws1.Cell("value1").GetValue<int>());
Assert.AreEqual(1, ws1.Range("value1").FirstCell().GetValue<int>());
var ws2 = wb.Worksheets.Add("Sheet2");
ws2.Cell("A1").SetFormulaA1("=value1").AddToNamed("value2");
Assert.AreEqual(1, wb.Cell("value2").GetValue<int>());
Assert.AreEqual(1, wb.Range("value2").FirstCell().GetValue<int>());
Assert.AreEqual(1, ws2.Cell("value1").GetValue<int>());
Assert.AreEqual(1, ws2.Range("value1").FirstCell().GetValue<int>());
Assert.AreEqual(1, ws2.Cell("value2").GetValue<int>());
Assert.AreEqual(1, ws2.Range("value2").FirstCell().GetValue<int>());
}
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");
}