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);
}
}
}
public static void Execute(DownloadServiceOptions options)
{
using (var workbook = new XLWorkbook(options.File))
using (var worksheet = workbook.Worksheet(1))
{
var fullRange = worksheet.RangeUsed();
var rangeUsed = worksheet.Range(2, 1, fullRange.RangeAddress.LastAddress.RowNumber, fullRange.RangeAddress.LastAddress.ColumnNumber);
const int movieNameCell = 1;
const int yearCell = 2;
const int processedCell = 3;
const int downloadedCell = 4;
var fullCount = 0;
foreach (var row in rangeUsed.RowsUsed())
{
if (fullCount > 100) break;
// must have 4 cells, and the processed cell must not contain an x
if (row.CellCount() == 4 && !row.Cell(processedCell).GetValue<string>().Equals("x"))
{
var urls = CreateUrl(options.BaseUrl, row.Cell(movieNameCell).GetValue<string>(), row.Cell(yearCell).GetValue<string>());
var downloaded = false;
foreach (var url in urls)
{
Thread.Sleep(1000 * 30); // wait 30 seconds
var hash = GetTorrentMagnetHash(PullSource(url));
if (string.IsNullOrEmpty(hash)) continue;
var torrent = string.Format("http://torcache.net/torrent/{0}.torrent", hash);
var result = DownloadTorrent(torrent, options.DownloadPath);
if (!result) continue;
downloaded = true;
break;
}
row.Cell(processedCell).SetValue("x");
if (downloaded)
row.Cell(downloadedCell).SetValue("x");
fullCount++;
}
}
workbook.Save();
}
}
//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);
}
//public static void Create()
//{
// ////create new xls file
// //string file = "C:\\newdoc.xls";
// ////Workbook workbook = new Workbook();
// ////Worksheet worksheet = new Worksheet("First Sheet");
// ////worksheet.Cells[0, 1] = new Cell((short)1);
// ////worksheet.Cells[2, 0] = new Cell(9999999);
// ////worksheet.Cells[3, 3] = new Cell((decimal)3.45);
// ////worksheet.Cells[2, 2] = new Cell("Text string");
// ////worksheet.Cells[2, 4] = new Cell("Second string");
// ////worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
// ////worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
// ////worksheet.Cells.ColumnWidth[0, 1] = 3000;
// ////workbook.Worksheets.Add(worksheet);
// ////workbook.Save(file);
// ////// open xls file
// ////Workbook book = Workbook.Load(file);
// ////Worksheet sheet = book.Worksheets[0];
// //var wb = new XLWorkbook();
// //var ws = wb.Worksheets.Add("Contacts");
// //// Title
// //ws.Cell("B2").Value = "Contacts";
// //// First Names
// //ws.Cell("B3").Value = "FName";
// //ws.Cell("B4").Value = "John";
// //ws.Cell("B5").Value = "Hank";
// //ws.Cell("B6").Value = "Dagny";
// //// Last Names
// //ws.Cell("C3").Value = "LName";
// //ws.Cell("C4").Value = "Galt";
// //ws.Cell("C5").Value = "Rearden";
// //ws.Cell("C6").Value = "Taggart";
// //// Boolean
// //ws.Cell("D3").Value = "Outcast";
// //ws.Cell("D4").Value = true;
// //ws.Cell("D5").Value = false;
// //ws.Cell("D6").Value = false;
// //// DateTime
// //ws.Cell("E3").Value = "DOB";
// //ws.Cell("E4").Value = new DateTime(1919, 1, 21);
// //ws.Cell("E5").Value = new DateTime(1907, 3, 4);
// //ws.Cell("E6").Value = new DateTime(1921, 12, 15);
// //// Numeric
// //ws.Cell("F3").Value = "Income";
// //ws.Cell("F4").Value = 2000;
// //ws.Cell("F5").Value = 40000;
// //ws.Cell("F6").Value = 10000;
// //// From worksheet
// //var rngTable = ws.Range("B2:F6");
// //// From another range
// //var rngDates = rngTable.Range("D3:D5"); // The address is relative to rngTable (NOT the worksheet)
// //var rngNumbers = rngTable.Range("E3:E5"); // The address is relative to rngTable (NOT the worksheet)
// //// Using OpenXML's predefined formats
// //rngDates.Style.NumberFormat.NumberFormatId = 15;
// //// Using a custom format
// //rngNumbers.Style.NumberFormat.Format = "$ #,##0";
// //var rngHeaders = rngTable.Range("A2:E2"); // The address is relative to rngTable (NOT the worksheet)
// //rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// //rngHeaders.Style.Font.Bold = true;
// //rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua;
// //rngTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
// //rngTable.Cell(1, 1).Style.Font.Bold = true;
// //rngTable.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
// //rngTable.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// //rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge()
// ////Add a thick outside border
// //rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
// //// You can also specify the border for each side with:
// //// rngTable.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick;
// //// rngTable.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick;
// //// rngTable.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick;
// //// rngTable.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick;
// //ws.Columns(2, 6).AdjustToContents();
// //wb.SaveAs("BasicTable.xlsx");
// //CreateResult();
//}
public static void CreateResult(ExcelModel model, bool needToSave = true)
{
model.SetMeltsOxides();
Model = model;
var disk = AppDomain.CurrentDomain.BaseDirectory.ToCharArray().First();
string fileName = string.Format(@"{1}:\result{0}.xlsx", DateTime.Now.ToString("dd-MM-yy_hhmm"), disk);
string template = "template.xlsx";
var wb = new XLWorkbook(template);
var ws = wb.Worksheet("result");
CreateHeader(ws);
SetRows(ws);
SetData(ws);
wb.SaveAs(fileName);
if (needToSave)
{
Sqlite.SaveProject(Model);
}
Model = null;
}
public void Test()
{
XLWorkbook wb = new XLWorkbook("D:\\aaa.xlsx");
wb.Worksheet("Sheet1").Cell(1, 5).Value = 3;
wb.Worksheet("Sheet1").Cell(2, 5).Value = "BBB";
wb.Worksheet("Sheet1").Cell(3, 5).SetValue("ASDASDASDASD");
//wb.AddWorksheet("CCC");
wb.SaveAs("D:\\bbb.xlsx");
//wb.Save();
}
public void InsertingSheets3()
{
var wb = new XLWorkbook();
wb.Worksheets.Add("Sheet3");
wb.Worksheets.Add("Sheet2", 1);
wb.Worksheets.Add("Sheet1", 1);
Assert.AreEqual("Sheet1", wb.Worksheet(1).Name);
Assert.AreEqual("Sheet2", wb.Worksheet(2).Name);
Assert.AreEqual("Sheet3", wb.Worksheet(3).Name);
}
public void DeletingSheets1()
{
var wb = new XLWorkbook();
wb.Worksheets.Add("Sheet3");
wb.Worksheets.Add("Sheet2");
wb.Worksheets.Add("Sheet1", 1);
wb.Worksheet("Sheet3").Delete();
Assert.AreEqual("Sheet1", wb.Worksheet(1).Name);
Assert.AreEqual("Sheet2", wb.Worksheet(2).Name);
Assert.AreEqual(2, wb.Worksheets.Count);
}
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);
}
}
}
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 GivenCreateCalled_AndDateDataRows_WhenFillData_ThenDatePrecisionToSecondsPreserved()
{
var nowWithSecondPrecision = DateTime.Now.Truncate(TimeSpan.FromSeconds(1));
var data = new List<List<object>>
{
new List<object> { nowWithSecondPrecision },
new List<object> { nowWithSecondPrecision.AddDays(2) },
new List<object> { nowWithSecondPrecision.TimeOfDay }
};
string outputPath = Path.ChangeExtension(Path.Combine("TestData", MethodBase.GetCurrentMethod().Name), ".xlsx");
byte[] templateData = File.ReadAllBytes(@"TestData\StudentProfileExportTemplate.xltx");
using (MemoryStream stream = new MemoryStream())
{
stream.Write(templateData, 0, (int)templateData.Length);
using (var target = new StudentProfileExportFile())
{
target.Create(stream);
target.FillData(data);
}
File.WriteAllBytes(outputPath, stream.ToArray());
}
using (XLWorkbook workbook = new XLWorkbook(outputPath))
{
IXLWorksheet worksheet = workbook.Worksheet(1);
var date1 = worksheet.Cell("A3").GetDateTime();
var date2 = worksheet.Cell("A4").GetDateTime();
var timeSpan = worksheet.Cell("A5").GetTimeSpan();
Assert.AreEqual(data[0][0], date1);
Assert.AreEqual(data[1][0], date2);
Assert.AreEqual(data[2][0], timeSpan);
}
}
//---------------------------------------------------------//
// 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
}
private void findProvderName()
{
Point providerLocation = new Point(0, 0);
workbook = workbooks[0];
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);
for (int metricNumber = 0; metricNumber < 8; metricNumber++)
{
setMetricDataLocations(providerLocation, metricNumber);
}
break;
}
}
}
}
private void findProvderName()
{
Point providerLocation = new Point(0, 0);
//int fileNumber = 0;
for (int fileNumber = 0; fileNumber < workbooks.Count; fileNumber++)
{
workbook = workbooks[fileNumber];
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, fileNumber);
break;
}
}
}
}
//metrics.Insert(0, now.Month +"-" + now.Year);
}
public TranslationData Read(string srcPath)
{
// ワークブックの作成
using (var workbook = new XLWorkbook(srcPath))
{
// ワークブック指定の名前のシートを取得
var worksheet = workbook.Worksheet(ExcelConverter.SheetName);
var dataRange = worksheet.Range(LanguageTitleRow,
DataColumn,
worksheet.LastRowUsed().RowNumber(),
worksheet.LastColumnUsed().ColumnNumber());
var dataTable = dataRange.AsTable();
var langList = dataTable.DataRange.Columns()
.Select(o => new
{
Locale = o.Cell(0).GetString(),
Values = o.Cells().Select(i => new LocalizationItem(worksheet.Cell(i.Address.RowNumber, OffsetColumn).GetString(),
worksheet.Cell(i.Address.RowNumber, OffsetColumn + 1).GetString(),
i.GetString()))
});
var result = langList.Select(o => new LanguageData(o.Locale, o.Values));
return new TranslationData(result);
}
}
//----------------------------------------------------------------------
// 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 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);
}
}
}
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);
}
public void Create(string filePath)
{
var wb = new XLWorkbook();
foreach (var wsNum in Enumerable.Range(1, 5))
{
wb.Worksheets.Add("Original Pos. is " + wsNum.ToString());
}
// Move first worksheet to the last position
wb.Worksheet(1).Position = wb.Worksheets.Count() + 1;
// Delete worksheet on position 4 (in this case it's where original position = 5)
wb.Worksheet(4).Delete();
// Swap sheets in positions 1 and 2
wb.Worksheet(2).Position = 1;
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);
}
}
static void Main(string[] args)
{
XLWorkbook workbook = new XLWorkbook("table_prefecturecode.xlsx");
IXLWorksheet worksheet = workbook.Worksheet(1);
int lastRow = worksheet.LastRowUsed().RowNumber();
for (int i = 1; i <= lastRow; i++)
{
IXLCell cell = worksheet.Cell(i, 2);
Console.WriteLine(cell.Value);
}
}
static void Main(string[] args)
{
var fileSpese = args[0];
var fileAnalisi = args[1];
var template = args[2];
new GeneraDaTemplate().Run(fileSpese, fileAnalisi, template);
return;
var numeroDiAnniFinoAdOggi = DateTime.Today.Year - 2013;
var anni = Enumerable.Range(2013, numeroDiAnniFinoAdOggi + 1);
using (var analisiWb = new XLWorkbook())
{
var dati = analisiWb.Worksheets.Add("Dati");
int riga = 1;
dati.Cell(riga, "A").Value = "Data";
dati.Cell(riga, "B").Value = "Categoria";
dati.Cell(riga, "C").Value = "Descrizione";
dati.Cell(riga, "D").Value = "Spesa";
riga++;
using (var spese = new XLWorkbook(fileSpese))
{
foreach (var anno in anni)
{
var wsAnno = spese.Worksheet(anno + "");
var lastRowNumber = wsAnno.LastRowUsed().FirstCell().Address.RowNumber;
var laura = wsAnno.Range("B1", "E"+ lastRowNumber).Rows().Select(Movimento.TryParse2017);
var valerio = wsAnno.Range("G1", "J" + lastRowNumber).Rows().Select(Movimento.TryParse2017);
var comune = wsAnno.Range("L1", "O" + lastRowNumber).Rows().Select(Movimento.TryParse2017);
var movimenti = laura.Concat(valerio).Concat(comune).Where(r => r != null).ToArray();
foreach (var movimento in movimenti)
{
dati.Cell(riga, "A").Value = movimento.Data;
dati.Cell(riga, "B").Value = movimento.Categoria;
dati.Cell(riga, "C").Value = movimento.Descrizione;
dati.Cell(riga, "D").Value = movimento.Spesa;
riga++;
}
}
var dataAsTable = dati.RangeUsed().AsTable();
dati.Tables.Add(dataAsTable);
}
analisiWb.SaveAs(fileAnalisi);
}
}
// 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 Create(string filePath)
{
string 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 firstDataCell = ws.Cell("B4");
var lastDataCell = ws.LastCellUsed();
var rngData = ws.Range(firstDataCell.Address, lastDataCell.Address);
// Delete all rows where Outcast = false (the 3rd column)
rngData.Rows() // From all rows
.Where(r => !r.Cell(3).GetBoolean()) // where the 3rd cell of each row is false
.ForEach(r => r.Delete()); // delete the row and shift the cells up (the default for rows in a range)
//// Put a light gray background to all text cells
//rngData.Cells() // From all cells
// .Where(c => c.DataType == XLCellValues.Text) // where the data type is Text
// .ForEach(c => c.Style.Fill.BackgroundColor = XLColor.LightGray); // Fill with a light gray
var cells = rngData.Cells();
var filtered = cells.Where(c => c.DataType == XLCellValues.Text);
var list = filtered.ToList();
foreach (var c in list)
{
c.Style.Fill.BackgroundColor = XLColor.LightGray;
}
// Put a thick border to the bottom of the table (we may have deleted the bottom cells with the border)
rngData.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick;
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
static void Main(string[] args)
{
var workbook = new XLWorkbook("Input.xlsx");
var ws = workbook.Worksheet("Posts");
var cell = ws.Column(3).FirstCellUsed().CellBelow();
while (!cell.IsEmpty())
{
int value = cell.GetValue<int>();
Console.WriteLine("Value: " + value);
cell.CellRight().Style.Fill.BackgroundColor = (value < 0 ? XLColor.Red : XLColor.Green);
cell = cell.CellBelow();
}
workbook.SaveAs("Output.xlsx");
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
}
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);
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new MergeCells().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
ws.Range("B1:F1").InsertRowsBelow(1);
ws.Range("A3:A9").InsertColumnsAfter(1);
ws.Row(1).Delete();
ws.Column(1).Delete();
ws.Range("E8:E9").InsertColumnsAfter(1);
ws.Range("F2:F8").Merge();
ws.Range("E3:E4").InsertColumnsAfter(1);
ws.Range("F2:F8").Merge();
ws.Range("E1:E2").InsertColumnsAfter(1);
ws.Range("G2:G8").Merge();
ws.Range("E1:E2").Delete(XLShiftDeletedCells.ShiftCellsLeft);
ws.Range("D3:E3").InsertRowsBelow(1);
ws.Range("A1:B1").InsertRowsBelow(1);
ws.Range("B3:D3").Merge();
ws.Range("A1:B1").Delete(XLShiftDeletedCells.ShiftCellsUp);
ws.Range("B8:D8").Merge();
ws.Range("D8:D9").Clear();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public List<String> GetMetricsNames(string dashboardFile, int sheetNum)
{
List<String> metricsNames = new List<string>();
try
{
_dashboard = new XLWorkbook(dashboardFile);
//iterate thorugh all the dashboard sheets, and try to pull the metrics name row
int numSheets = _dashboard.Worksheets.Count;
var sheet = _dashboard.Worksheet(sheetNum);
metricsNames = ReturnMetricsRow(sheet);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return metricsNames;
}
public static List<IXLCells> ReadFromExcelFile(string fileName, string workSheetName, string leadCell)
{
var rowCellList = new List<IXLCells>();
var wb = new XLWorkbook(fileName);
var ws = wb.Worksheet(workSheetName);
// Look for the first row used
var firstRowUsed = ws.FirstRowUsed();
int rowCount = 0;
// Narrow down the row so that it only includes the used part
var categoryRow = firstRowUsed.RowUsed();
while (!categoryRow.Cell(1).IsEmpty())
{
string categoryName = categoryRow.Cell(1).GetString();
Console.WriteLine(categoryName + "\n" + categoryRow.Cells().Count());
rowCellList.Add(categoryRow.Cells());
categoryRow = categoryRow.RowBelow();
rowCount++;
}
return rowCellList;
}
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);
// Change the background color of the headers
var rngHeaders = ws.Range("B3:F3");
rngHeaders.Style.Fill.BackgroundColor = XLColor.LightSalmon;
// Change the date formats
var rngDates = ws.Range("E4:E6");
rngDates.Style.DateFormat.Format = "MM/dd/yyyy";
// Change the income values to text
var rngNumbers = ws.Range("F4:F6");
foreach (var cell in rngNumbers.Cells())
{
string formattedString = cell.GetFormattedString();
cell.DataType = XLCellValues.Text;
cell.Value = formattedString + " Dollars";
}
ws.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
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.Row(rngTable.RowCount() - 1).Delete(XLShiftDeletedCells.ShiftCellsUp);
// Place some markers
var cellNextRow = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber + 1, rngTable.RangeAddress.LastAddress.ColumnNumber);
cellNextRow.Value = "ColumnRight Row";
var cellNextColumn = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber, rngTable.RangeAddress.LastAddress.ColumnNumber + 1);
cellNextColumn.Value = "ColumnRight Column";
rngTable.Transpose(XLTransposeOptions.MoveCells);
rngTable.Transpose(XLTransposeOptions.MoveCells);
rngTable.Transpose(XLTransposeOptions.ReplaceCells);
rngTable.Transpose(XLTransposeOptions.ReplaceCells);
ws.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public void GivenCreateCalled_AndHeaderTextProvided_WhenSetupColumnHeaders_ThenSavedFileContainsAddedHeaderCellWithFormatting()
{
string outputPath = Path.ChangeExtension(Path.Combine("TestData", MethodBase.GetCurrentMethod().Name), ".xlsx");
byte[] templateData = File.ReadAllBytes(@"TestData\StudentProfileExportTemplate.xltx");
using (MemoryStream stream = new MemoryStream())
{
stream.Write(templateData, 0, (int)templateData.Length);
using (var target = new StudentProfileExportFile())
{
target.Create(stream);
target.SetupColumnHeaders(new[] { "whatever", "to be formatted" });
}
File.WriteAllBytes(outputPath, stream.ToArray());
}
using (XLWorkbook workbook = new XLWorkbook(outputPath))
{
IXLWorksheet worksheet = workbook.Worksheet(1);
IXLCell preexistingHeaderCell = worksheet.Cell(2, "A");
IXLCell actualAddedHeaderCell = worksheet.Cell(2, "B");
Assert.AreEqual(preexistingHeaderCell.Style, actualAddedHeaderCell.Style);
}
}
public void Run(string fileSpese, string fileAnalisi, string template)
{
var numeroDiAnniFinoAdOggi = DateTime.Today.Year - 2013;
var anni = Enumerable.Range(2013, numeroDiAnniFinoAdOggi + 1);
File.Copy(template, fileAnalisi, true);
using (var analisiWb = new XLWorkbook(fileAnalisi))
{
var dati = analisiWb.Worksheet("Dati");
int riga = 2;
using (var spese = new XLWorkbook(fileSpese))
{
foreach (var anno in anni)
{
var wsAnno = spese.Worksheet(anno + "");
var lastRowNumber = wsAnno.LastRowUsed().FirstCell().Address.RowNumber;
if (anno < 2017)
{
riga = Estrai_Template_2013(wsAnno, lastRowNumber, dati, riga);
}
if (anno >= 2017)
{
riga = Estrai_Template_2017(wsAnno, lastRowNumber, dati, riga);
}
}
var dataAsTable = dati.RangeUsed().AsTable();
dati.Tables.Add(dataAsTable);
}
analisiWb.SaveAs(fileAnalisi);
}
}
public StampaPagamento()
{
_wb = new ClosedXML.Excel.XLWorkbook(Path.Combine(Application.StartupPath
, @"TemplateExcel\ProtoPagamento.xlsx"));
_ws = _wb.Worksheet(1);
}
private void Export_Novy()
{
if (!InvokeRequired)
{
form.timer_ClearInfo.Stop();
}
else
{
this.BeginInvoke(new Action(() => form.timer_ClearInfo.Stop()));
}
form.infoBox.Text = form.jazyk.Message_Exportuji;
form.Update();
DateTime start;
DateTime konec;
over = false;
List <ExportRow> exportRadky = new List <ExportRow>();
int odec = 0;
if (DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).DayOfWeek == DayOfWeek.Sunday && (int)DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).DayOfWeek == 0)
{
odec = 1;
}
if (radioButton3.Checked)
{
start = dateTimePicker1.Value;
konec = dateTimePicker2.Value;
}
else if (radioButton1.Checked)
{
konec = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
//tohle zrevidovat, jestli by nestačilo jen to Add day v catch bez try
try
{
start = new DateTime(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Year, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Month, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Day + odec);
}
catch
{
start = new DateTime(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Year, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Month, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Day);
start = start.AddDays(odec);
}
}
else
{
start = DateTime.Now.AddDays(-7);
start = start.AddDays(-(int)(start.DayOfWeek - 1));
start = new DateTime(start.Year, start.Month, start.Day);
konec = new DateTime(start.Year, start.Month, start.Day).AddDays(6);
}
//Projít tickety splňující podmínku a zařadit je správně do exportu
for (DateTime d = start; d <= konec; d = d.AddDays(1))
{
if (form.poDnech.ContainsKey(d))
{
foreach (string s in form.poDnech[d].Keys)
{
foreach (Ticket t in form.poDnech[d][s])
{
DateTime pauzaDohromady = new DateTime();
DateTime cas = new DateTime();
DateTime hrubyCas = new DateTime();
for (int i = 0; i < t.PauzyDo.Count; i++)
{
if (t.PauzyDo[i].ToString("H:mm") != "0:00")
{
pauzaDohromady = pauzaDohromady.AddHours(t.PauzyDo[i].Hour - t.PauzyOd[i].Hour).AddMinutes(t.PauzyDo[i].Minute - t.PauzyOd[i].Minute);
}
}
try
{
cas = cas.AddHours((t.Do.Hour - t.Od.Hour) - pauzaDohromady.Hour).AddMinutes((t.Do.Minute - t.Od.Minute) - pauzaDohromady.Minute);
hrubyCas = form.RoundUp(cas, TimeSpan.FromMinutes(30));
//dny[d][t.TerpT].Add(t, hrubyCas);
string den = "";
switch (d.DayOfWeek)
{
case DayOfWeek.Monday: den = "Pondělí";
break;
case DayOfWeek.Tuesday: den = "Úterý";
break;
case DayOfWeek.Wednesday: den = "Středa";
break;
case DayOfWeek.Thursday: den = "Čtvrtek";
break;
case DayOfWeek.Friday: den = "Pátek";
break;
case DayOfWeek.Saturday: den = "Sobota";
break;
case DayOfWeek.Sunday: den = "Neděle";
break;
}
ExportTyp et;
if (t.TypPrace == 0 || t.TypPrace == 2 || t.TypPrace == 8 || t.TypPrace == 12 || t.TypPrace == 16 || t.TypPrace == 20 || t.TypPrace == 24)
{
et = ExportTyp.Normal;
}
else if (t.TypPrace == 1 || t.TypPrace == 6 || t.TypPrace == 10 || t.TypPrace == 15 || t.TypPrace == 18 || t.TypPrace == 23 || t.TypPrace == 27)
{
et = ExportTyp.Holiday;
}
else if (t.TypPrace == 3 || t.TypPrace == 5 || t.TypPrace == 9 || t.TypPrace == 13 || t.TypPrace == 17 || t.TypPrace == 21 || t.TypPrace == 25)
{
et = ExportTyp.Prescas;
}
else if (t.TypPrace == 4 || t.TypPrace == 7 || t.TypPrace == 11 || t.TypPrace == 14 || t.TypPrace == 19 || t.TypPrace == 22 || t.TypPrace == 26)
{
et = ExportTyp.Compens;
}
else
{
et = ExportTyp.Normal;
}
if (exportRadky.Count == 0)
{
exportRadky.Add(new ExportRow());
}
List <int> toSkip = new List <int>();
if (t.CustomTerp == "")
{
t.CustomTerp = Zakaznici.GetTerp(t.Zakaznik);
}
//když není task, tak defaultně incident 1.2.1 - nová verze by neměla umět uložit bez tasku
if (t.CustomTask == "")
{
t.CustomTask = Zakaznici.Terpy.Get <NbtCompound>("Task").Get <NbtString>("Incident").Value;
}
//přiřazení ticketu ke správnému řádku a dni
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Terp == null)
{
exportRadky[i].Terp = t.CustomTerp;
exportRadky[i].Task = t.CustomTask;
exportRadky[i].Typ = et;
exportRadky[i].Radek[den].Koment = t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n";
decimal tCas = hrubyCas.Hour;
if (hrubyCas.Minute == 30)
{
tCas += 0.5m;
}
exportRadky[i].Radek[den].Cas = tCas;
break;
}
else if (exportRadky[i].Terp == t.CustomTerp && exportRadky[i].Task == t.CustomTask && exportRadky[i].Typ == et)
{
if ((exportRadky[i].Radek[den].Koment.Length + (t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n").Length < 240))
{
exportRadky[i].Radek[den].Koment += t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n";
decimal tCas = hrubyCas.Hour;
if (hrubyCas.Minute == 30)
{
tCas += 0.5m;
}
exportRadky[i].Radek[den].Cas += tCas;
break;
}
else
{
exportRadky.Add(new ExportRow());
continue;
}
}
else if (i < exportRadky.Count - 1)
{
continue;
}
exportRadky.Add(new ExportRow());
}
}
catch
{
MessageBox.Show(form.jazyk.Windows_Export_Ticket + " " + t.ID + " - " + t.Zakaznik + ", " + form.jazyk.Windows_Export_NaKteremJsiPracoval + " " + t.Datum.ToString("d.MM.yyyy") + ", " + form.jazyk.Windows_Export_Neukoncen);
}
}
}
}
}
//přepočet času na 8h
//celkový čas normálních ticketů (statní se neupravují)
Dictionary <string, decimal> casy = new Dictionary <string, decimal> {
{ "Pondělí", 0 }, { "Úterý", 0 }, { "Středa", 0 }, { "Čtvrtek", 0 }, { "Pátek", 0 }, { "Sobota", 0 }, { "Neděle", 0 }
};
foreach (ExportRow s in exportRadky)
{
if (s.Typ == ExportTyp.Normal)
{
casy["Pondělí"] += s.Radek["Pondělí"].Cas;
casy["Úterý"] += s.Radek["Úterý"].Cas;
casy["Středa"] += s.Radek["Středa"].Cas;
casy["Čtvrtek"] += s.Radek["Čtvrtek"].Cas;
casy["Pátek"] += s.Radek["Pátek"].Cas;
casy["Sobota"] += s.Radek["Sobota"].Cas;
casy["Neděle"] += s.Radek["Neděle"].Cas;
}
}
//výběr řádků, co se upraví čas
foreach (string cs in casy.Keys)
{
if (casy[cs] == 8 || casy[cs] == 0)
{
continue;
}
else if (casy[cs] < 8)
{
Dictionary <int, decimal> pridat = new Dictionary <int, decimal>();
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Typ == ExportTyp.Normal && exportRadky[i].Radek[cs].Cas > 0)
{
pridat.Add(i, exportRadky[i].Radek[cs].Cas);
}
}
pridat = pridat.OrderBy(x => x.Value).ToDictionary(x => x.Key, x => x.Value);
decimal zbyva = 8 - casy[cs];
decimal prumerNaRadek = Math.Ceiling((zbyva / pridat.Count) / 0.5m) * 0.5m;
//úprava času
int index = 0;
for (decimal d = zbyva; d > 0; d -= prumerNaRadek)
{
if (d - prumerNaRadek < 0)
{
prumerNaRadek = d;
}
pridat[pridat.Keys.ElementAt(index)] += prumerNaRadek;
index++;
zbyva -= prumerNaRadek;
}
foreach (int newI in pridat.Keys)
{
exportRadky[newI].Radek[cs].Cas = pridat[newI];
}
}
else if (casy[cs] > 8)
{
Dictionary <int, decimal> ubrat = new Dictionary <int, decimal>();
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Typ == ExportTyp.Normal && exportRadky[i].Radek[cs].Cas > 0.5m)
{
ubrat.Add(i, exportRadky[i].Radek[cs].Cas);
}
}
ubrat = ubrat.OrderByDescending(x => x.Value).ToDictionary(x => x.Key, x => x.Value);
decimal zbyva = casy[cs] - 8;
decimal prumerNaRadek = Math.Ceiling((zbyva / ubrat.Count) / 0.5m) * 0.5m;
//úprava času
int index = 0;
for (decimal d = zbyva; d > 0; d -= prumerNaRadek)
{
if (d - prumerNaRadek < 0)
{
prumerNaRadek = d;
}
ubrat[ubrat.Keys.ElementAt(index)] -= prumerNaRadek;
index++;
zbyva -= prumerNaRadek;
}
foreach (int newI in ubrat.Keys)
{
exportRadky[newI].Radek[cs].Cas = ubrat[newI];
}
}
}
//export do souboru
//pro excel
File.WriteAllBytes(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Ticketnik\\tmp_export.xlsx", Properties.Resources.mytime_template);
Excel.XLWorkbook export = new Excel.XLWorkbook(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Ticketnik\\tmp_export.xlsx");
Excel.IXLWorksheet exportSheet = export.Worksheet(1);
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
int row = 2;
foreach (ExportRow s in exportRadky)
{
if (s.Terp != null)
{
//project
exportSheet.Cell(row, 1).Value = s.Terp;
//project name
exportSheet.Cell(row, 2).Value = "najdiSiSam";
//task
exportSheet.Cell(row, 3).SetValue(s.Task);
//task name
exportSheet.Cell(row, 4).Value = "TyVisCo";
//type
exportSheet.Cell(row, 5).Value = s.GetTyp();
//pondělí (čas, comment)
exportSheet.Cell(row, 6).Value = s.Radek["Pondělí"].Cas.ToString() == "0" ? "" : s.Radek["Pondělí"].Cas.ToString(nfi);
exportSheet.Cell(row, 7).Value = s.Radek["Pondělí"].Koment.Replace("\t", " ").Replace("\"", "");
//úterý
exportSheet.Cell(row, 10).Value = s.Radek["Úterý"].Cas.ToString() == "0" ? "" : s.Radek["Úterý"].Cas.ToString(nfi);
exportSheet.Cell(row, 11).Value = s.Radek["Úterý"].Koment.Replace("\t", " ").Replace("\"", "");
//středa
exportSheet.Cell(row, 14).Value = s.Radek["Středa"].Cas.ToString() == "0" ? "" : s.Radek["Středa"].Cas.ToString(nfi);
exportSheet.Cell(row, 15).Value = s.Radek["Středa"].Koment.Replace("\t", " ").Replace("\"", "");
//čtvrtek
exportSheet.Cell(row, 18).Value = s.Radek["Čtvrtek"].Cas.ToString() == "0" ? "" : s.Radek["Čtvrtek"].Cas.ToString(nfi);
exportSheet.Cell(row, 19).Value = s.Radek["Čtvrtek"].Koment.Replace("\t", " ").Replace("\"", "");
//pátek
exportSheet.Cell(row, 22).Value = s.Radek["Pátek"].Cas.ToString() == "0" ? "" : s.Radek["Pátek"].Cas.ToString(nfi);
exportSheet.Cell(row, 23).Value = s.Radek["Pátek"].Koment.Replace("\t", " ").Replace("\"", "");
//sobota
exportSheet.Cell(row, 26).Value = s.Radek["Sobota"].Cas.ToString() == "0" ? "" : s.Radek["Sobota"].Cas.ToString(nfi);
exportSheet.Cell(row, 27).Value = s.Radek["Sobota"].Koment.Replace("\t", " ").Replace("\"", "");
//neděle
exportSheet.Cell(row, 30).Value = s.Radek["Neděle"].Cas.ToString() == "0" ? "" : s.Radek["Neděle"].Cas.ToString(nfi);
exportSheet.Cell(row, 31).Value = s.Radek["Neděle"].Koment.Replace("\t", " ").Replace("\"", "");
row++;
}
}
export.Save();
export.Dispose();
form.infoBox.Text = "";
saveFileDialog1.AddExtension = true;
saveFileDialog1.DefaultExt = "xlsx";
saveFileDialog1.Filter = "Excel|*.xlsx";
saveFileDialog1.FileName = "MyTime Info.xlsx";
saveFileDialog1.ShowDialog();
}
// Wakamatsu
//----------------------------------------------------------------------
// SubRoutine
//----------------------------------------------------------------------
private void editExcelSheet(string sheetName, PublishData pd, DataGridView dgv, DataGridView dgv1, DataGridView dgv2)
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
using (oWBook = new XLWorkbook(fileName))
{
oWSheet = oWBook.Worksheet(sheetName);
switch (sheetName)
{
case "EstimateTop":
editEstimateTop(pd);
oWSheet = oWBook.Worksheet("EstimateCont");
editEstimateCont(dgv);
break;
case "EstimateCopy":
editEstimateCopy(pd);
oWSheet = oWBook.Worksheet("EstimateCont");
editEstimateCont(dgv);
break;
case "Planning":
editPlanning(pd);
break;
case "PlanningCont":
editPlanningCont(pd, dgv);
break;
case "OsOrder":
editOutsourceOrder(pd);
editOutsourceConfirm(pd);
break;
case "OsConfirm":
editOutsourceConfirm(pd);
break;
case "OsContent":
editOutsourceContent(pd, dgv);
break;
case "OsARegular":
editAccountsRegular(pd, dgv, dgv1, dgv2);
break;
case "OsAContract":
editAccountsContract(pd, dgv);
break;
case "Invoice":
editAccountsInvoice(pd, dgv);
break;
case "VolumeInvoice":
editAccountsVolumeInvoice(pd, dgv);
break;
// Wakamatsu 20170302
case "TaskSummary":
editTaskSummary(pd, dgv);
break;
// Wakamatsu 20170302
default:
break;
}
oWBook.SaveAs(tempFile); // Excel保存
}
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
}
//---------------------------------------------------------/
// Method
//---------------------------------------------------------/
public void ExcelFile()
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
// Wakamatsu 20170315
try
{
using (oWBook = new XLWorkbook(fileName))
{
if (tla == null || tla.Length == 0)
{
DMessage.DataNotExistence("中断します！");
return;
}
// 編集
oWSheet = oWBook.Worksheet(1); // シートを開く
// Wakamatsu 20170315
//readyExcelRows(tla.Length, 5);
readyExcelRows(tla.Length, 4);
int sNo = 5;
for (int i = 0; i < tla.Length; i++)
{
// Wakamatsu 20170315
using (IXLRange SetRange = oWSheet.Range("A5:I5"))
// テンプレートデータ行コピー/ペースト
SetRange.CopyTo(oWSheet.Cell(sNo + i, 1));
// Wakamatsu 20170315
if (i == 0)
{
officeName = tla[i].OfficeName;
departName = tla[i].DepartName;
// Wakamatsu 20170315
//oWSheet.Cell(1, 7).Value = DateTime.Today;
oWSheet.Cell(2, 2).Value = DateTime.Today;
oWSheet.Cell(3, 2).Value = officeName + " " + departName;
// Wakamatsu 20170315
}
oWSheet.Cell(sNo + i, 1).Value = tla[i].TaskCode;
oWSheet.Cell(sNo + i, 2).Value = tla[i].TaskName;
oWSheet.Cell(sNo + i, 3).Value = tla[i].PartnerName;
oWSheet.Cell(sNo + i, 4).Value = tla[i].Contract;
oWSheet.Cell(sNo + i, 5).Value = tla[i].StartDate;
oWSheet.Cell(sNo + i, 6).Value = tla[i].EndDate;
oWSheet.Cell(sNo + i, 7).Value = tla[i].SalesM;
oWSheet.Cell(sNo + i, 8).Value = tla[i].LeaderM;
oWSheet.Cell(sNo + i, 9).Value = tla[i].IssueDate;
// Wakamatsu 20170315
if (i != 0)
{
oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.TopBorder = XLBorderStyleValues.Hair;
}
if (i == tla.Length - 1)
{
oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
}
else
{
oWSheet.Range(sNo + i, 1, sNo + i, 9).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
}
// Wakamatsu 20170315
}
}
// 保存
oWBook.SaveAs(tempFile); // Excel保存
}
// Wakamatsu 20170315
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
return;
}
// Wakamatsu 20170315
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
//System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
// pdf file 出力
DateTime now = DateTime.Now;
outputFile = System.IO.Path.GetDirectoryName(tempFile) + @"\業務一覧表_" + officeName + "_" + departName + "_" + now.ToString("yyMMddHHmmss");
PublishExcelToPdf etp = new PublishExcelToPdf();
etp.ExcelToPDF(tempFile, outputFile);
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
private void button_Click(object sender, EventArgs e)
{
if (initProc)
{
return;
}
Button btn = (Button)sender;
switch (btn.Name)
{
case "buttonOpen":
// Wakamatsu 20170301
//fileName = Files.Open("M_Office.xlsx", Folder.MyDocuments(), "xlsx");
fileName = Files.Open(BookName, Folder.MyDocuments(), "xlsx");
// Wakamatsu 20170301
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();
int[] procArray = new int[] { 0, 0 };
switch (System.IO.Path.GetExtension(fileName))
{
case ".xlsx":
// Wakamatsu 20170227
try
{
oWBook = new XLWorkbook(fileName);
procArray = mmo.MaintOfficeByExcelData(oWBook.Worksheet(1));
// Wakamatsu 20170227
if (procArray[0] < 0)
{
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:
procArray[0] = -1;
textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n");
break;
}
if (procArray[0] < 0)
{
return;
}
textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n");
textBoxMsg.AppendText(procArray[0] + "件のデータを登録しました。\r\n");
textBoxMsg.AppendText(procArray[1] + "件のデータを更新しました。\r\n");
break;
case "buttonEnd":
this.Close();
break;
case "buttonExport":
textBoxMsg.AppendText("☆ 処理を開始しました。\r\n");
string SetSQL = "";
SetSQL += "OfficeCode, OfficeName, MemberCode, MemberName, ";
SetSQL += "Title, PostCode, Address, TelNo, FaxNo, OrderSeqNo, ";
SetSQL += "OrderLastNo, PurchaseSeqNo ";
SetSQL += "FROM M_Office ";
SetSQL += "ORDER BY OfficeID";
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出力クラス
// Wakamatsu 20170301
//PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate("M_Office.xlsx"));
PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName));
// Wakamatsu 20170301
// Excelファイル出力
// Wakamatsu 20170301
//textBoxMsg.AppendText(publ.ExcelFile("M_Office", dt, FormatSet));
textBoxMsg.AppendText(publ.ExcelFile(masterName, SheetName, dt, FormatSet));
// Wakamatsu 20170301
break;
default:
break;
}
}
private void button_Click(object sender, EventArgs e)
{
if (initProc)
{
return;
}
Button btn = ( Button )sender;
switch (btn.Name)
{
case "buttonOpen":
fileName = Files.Open(bookName, Folder.MyDocuments(), "xlsx");
if (fileName == null)
{
textBoxMsg.AppendText("× " + fileName + "は不適切なファイルです。処理続行不可能です。\r\n");
}
else
{
textBoxMsg.AppendText("☆ " + fileName + "の内容を作業項目マスタに書き込みます。\r\n");
}
break;
case "buttonCancel":
textBoxMsg.Text = "";
break;
case "buttonStart":
if (fileName == null)
{
return;
}
MasterMaintOp mmo = new MasterMaintOp();
if (!mmo.MWorkItems_Delete(hp.MemberCode))
{
textBoxMsg.AppendText("× 旧データの削除に失敗しました処理を中断します。\r\n");
return;
}
mmo = new MasterMaintOp();
int procCount = 0;
switch (System.IO.Path.GetExtension(fileName))
{
case ".csv":
procCount = mmo.MaintWorkItemsByCSVData(fileName, hp.MemberCode);
break;
case ".xlsx":
oWBook = new XLWorkbook(fileName);
procCount = mmo.MaintWorkItemsByExcelData(oWBook.Worksheet(1), hp.MemberCode);
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;
case "buttonExport":
textBoxMsg.AppendText("☆ 処理を開始しました。\r\n");
//string SetSQL = "ItemCode, UItem, Item, ItemDetail, Unit, StdCost, MemberCode, UpdateDate"
// + " FROM M_WorkItems WHERE MemberCode = '" + hp.MemberCode + "'";
string SetSQL = "ItemCode, UItem, Item, ItemDetail, Unit, StdCost, MemberCode, UpdateDate"
+ " FROM M_WorkItems WHERE MemberCode = ";
SqlHandling sqlh = new SqlHandling(); // SQL実行クラス
// レコードを取得する
DataTable dt = sqlh.SelectFullDescription(SetSQL + "'" + hp.MemberCode + "'");
if (dt == null)
{
dt = sqlh.SelectFullDescription(SetSQL + "'000'");
if (dt == null)
{
textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n");
return;
}
else
{
textBoxMsg.AppendText("△ " + hp.MemberName + "様の作業項目マスタが未登録のため、共用の作業項目マスタをExcel出力します。\r\n");
}
}
// フォーマット設定用構造体
PrintOut.Publish.FormatSet[] FormatSet = new PrintOut.Publish.FormatSet[dt.Columns.Count];
// フォーマット設定
FormatSetting(ref FormatSet);
// Excel出力クラス
PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(bookName));
// Excelファイル出力
textBoxMsg.AppendText(publ.ExcelFile(masterName, sheetName, dt, FormatSet));
break;
case "buttonEnd":
this.Close();
break;
default:
break;
}
}
private void button_Click(object sender, EventArgs e)
{
if (initProc)
{
return;
}
Button btn = (Button)sender;
switch (btn.Name)
{
case "buttonOpen":
// Wakamatsu 20170301
//fileName = Files.Open("M_Members.xlsx",Folder.MyDocuments(),"xlsx");
fileName = Files.Open(BookName, Folder.MyDocuments(), "xlsx");
// Wakamatsu 20170301
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();
int[] procArray = new int[] { 0, 0 };
switch (System.IO.Path.GetExtension(fileName))
{
case ".xlsx":
// Wakamatsu 20170227
try
{
oWBook = new XLWorkbook(fileName);
procArray = mmo.MaintMembersByExcelData(oWBook.Worksheet(1));
// Wakamatsu 20170227
if (procArray[0] < 0)
{
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:
procArray[0] = -1;
textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n");
break;
}
if (procArray[0] < 0)
{
return;
}
textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n");
textBoxMsg.AppendText(procArray[0] + "件のデータを登録しました。\r\n");
textBoxMsg.AppendText(procArray[1] + "件のデータを更新しました。\r\n");
break;
case "buttonExport":
textBoxMsg.AppendText("☆ 処理を開始しました。\r\n");
string SetSQL = "";
SetSQL += "MemberCode, Name, Phonetic, OfficeCode + Department, ";
SetSQL += "BirthDate, PostCode, Address, PostCode2, Address2, TelNo, ";
SetSQL += "CellularNo, CellularNo2, EMail, MobileEMail, BloodType, ";
SetSQL += "JoinDate, FinalEducation, GradDate, BasicPNo, HealthInsNo, ";
SetSQL += "EmploymentInsNo, GainQDate, BankName, BBranchName, AccountType, ";
SetSQL += "AccountNo, EContact, RadiationMedical, MedicalCheckup, ";
SetSQL += "FormWage, MemberType, AccessLevel, Enrollment, Note ";
SetSQL += "FROM M_Members ";
SetSQL += "ORDER BY RIGHT('0000' + MemberCode,4)";
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出力クラス
// Wakamatsu 20170301
//PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate("M_Members.xlsx"));
PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName));
// Wakamatsu 20170301
// Excelファイル出力
// Wakamatsu 20170301
//textBoxMsg.AppendText(publ.ExcelFile("M_Members",dt,FormatSet));
textBoxMsg.AppendText(publ.ExcelFile(masterName, SheetName, dt, FormatSet));
// Wakamatsu 20170301
break;
case "buttonEnd":
this.Close();
break;
default:
break;
}
}
private void button_Click(object sender, EventArgs e)
{
if (initProc)
{
return;
}
Button btn = (Button)sender;
switch (btn.Name)
{
case "buttonOpen":
// Wakamatsu 20170301
//fileName = Files.Open("M_Cost.xlsx", Folder.MyDocuments(), "xlsx");
fileName = Files.Open(BookName, Folder.MyDocuments(), "xlsx");
// Wakamatsu 20170301
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();
/*
* if (!mmo.AllMCost_Delete())
* {
* labelMsg.Text += "旧データの削除に失敗しました処理を中断します。\r\n";
* return;
* }
* mmo = new MasterMaintOp();
*/
int[] procCount = new int[] { 0, 0 };
switch (System.IO.Path.GetExtension(fileName))
{
//case ".csv":
//procCount = mmo.MaintCostByCSVData(fileName);
//break;
case ".xlsx":
// Wakamatsu 20170227
try
{
oWBook = new XLWorkbook(fileName);
procCount = mmo.MaintCostByExcelData(oWBook.Worksheet(1));
// Wakamatsu 20170227
if (procCount[0] < 0)
{
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:
textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n");
break;
}
if (procCount[0] < 0)
{
return;
}
textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n");
textBoxMsg.AppendText(procCount[0] + "件のデータを登録しました。\r\n");
textBoxMsg.AppendText(procCount[1] + "件のデータを更新しました。\r\n");
break;
// Wakamatsu
case "buttonExport":
textBoxMsg.AppendText("☆ 処理を開始しました。\r\n");
string SetSQL = "";
// Wakamatsu 20170303
SqlHandling sqlh = new SqlHandling(); // SQL実行クラス
SetSQL += "OfficeCode, OfficeName ";
SetSQL += "FROM M_Office ";
SetSQL += "ORDER BY OfficeID";
DataTable dt = sqlh.SelectFullDescription(SetSQL);
if (dt == null)
{
textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n");
return;
}
PrintOut.Publish.FormatSet[] FormatSet = null; // フォーマット設定用構造体
PrintOut.Publish publ = null; // Excel出力クラス
for (int i = 0; i < dt.Rows.Count; i++)
{
SetSQL = "";
SetSQL += "CostCode, Item, ItemDetail, Unit, Cost, ";
SetSQL += "OfficeCode, MemberCode ";
SetSQL += "FROM M_Cost ";
SetSQL += "WHERE OfficeCode = '" + Convert.ToString(dt.Rows[i][0]) + "' ";
SetSQL += "ORDER BY CostCode";
DataTable dt1 = sqlh.SelectFullDescription(SetSQL);
if (dt1 != null)
{
FormatSet = new PrintOut.Publish.FormatSet[dt1.Columns.Count];
// フォーマット設定
FormatSetting(ref FormatSet);
publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName));
// Excelファイル出力
textBoxMsg.AppendText(publ.ExcelFile(masterName + "(" + Convert.ToString(dt.Rows[i][1]) + ")", SheetName, dt1, FormatSet));
}
}
//SetSQL += "CST.CostCode, CST.Item, CST.ItemDetail, CST.Unit, CST.Cost, ";
//SetSQL += "CST.OfficeCode, CST.MemberCode ";
//SetSQL += "FROM M_Cost AS CST ";
//SetSQL += "LEFT JOIN M_Office AS OFC ";
//SetSQL += "ON CST.OfficeCode = OFC.OfficeCode ";
//SetSQL += "ORDER BY OFC.OfficeID, CST.CostCode";
//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出力クラス
//// Wakamatsu 20170301
////PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate("M_Cost.xlsx"));
//PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName));
//// Wakamatsu 20170301
//// Excelファイル出力
//// Wakamatsu 20170301
////textBoxMsg.AppendText(publ.ExcelFile("M_Cost", dt, FormatSet));
//textBoxMsg.AppendText(publ.ExcelFile(masterName, SheetName, dt, FormatSet));
//// Wakamatsu 20170301
// Wakamatsu 20170303
break;
// Wakamatsu
case "buttonEnd":
this.Close();
break;
default:
break;
}
}
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 Run(string fileSpese, string fileAnalisi)
{
var numeroDiAnniFinoAdOggi = DateTime.Today.Year - 2011;
var anni = Enumerable.Range(2011, numeroDiAnniFinoAdOggi + 1);
using (var analisiWb = new XLWorkbook())
{
var dati = analisiWb.Worksheets.Add("Dati");
int riga = 1;
dati.Cell(riga, "A").Value = "Data";
dati.Cell(riga, "B").Value = "Categoria";
dati.Cell(riga, "C").Value = "Descrizione";
dati.Cell(riga, "D").Value = "Spesa";
riga++;
using (var spese = new XLWorkbook(fileSpese))
{
foreach (var anno in anni)
{
var wsAnno = spese.Worksheet(anno + "");
var movimenti = wsAnno.Rows().Select(Movimento.TryParse).Where(r => r != null).ToArray();
foreach (var movimento in movimenti)
{
dati.Cell(riga, "A").Value = movimento.Data;
dati.Cell(riga, "B").Value = movimento.Categoria;
dati.Cell(riga, "C").Value = movimento.Descrizione;
dati.Cell(riga, "D").Value = movimento.Spesa;
riga++;
}
}
var dataAsTable = dati.RangeUsed().AsTable();
dati.Tables.Add(dataAsTable);
var range = dati.Range("B1", "B30");
//range = worksheet.RangeUsed();
var pivotSh = analisiWb.Worksheets.Add("Pivot");
}
analisiWb.SaveAs(fileAnalisi);
}
}
public ActionResult MyOrders(string cname, string cid)
{
cname = HttpUtility.UrlDecode(cname);
cid = HttpUtility.UrlDecode(cid);
var wb = new XLWorkbook(Server.MapPath("~/App_Data/进度表.xlsm"));
var ws = wb.Worksheet("进度单");
var firstRowUsed = ws.FirstRowUsed();
var categoryRow = firstRowUsed.RowUsed();
//categoryRow = categoryRow.RowBelow();
var firstPossibleAddress = ws.Row(categoryRow.RowNumber()).FirstCell().Address;
var lastPossibleAddress = ws.LastCellUsed().Address;
var companyRange = ws.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed();
var companyTable = companyRange.AsTable();
var retValue = companyTable.DataRange.Rows()
.Where(companyRow => companyRow.Field(0).GetString() == cname && companyRow.Field(1).GetString() == cid)
.Select(companyRow => new Order() {
customerName = companyRow.Field(0).GetString(),
customerID = companyRow.Field(1).GetString(),
orderNO = companyRow.Field(2).GetString(),
dateBlanking = ReadCellValueByDateTime(companyRow, 3),
dateAccessary = ReadCellValueByDateTime(companyRow, 4),
dateCombination = ReadCellValueByDateTime(companyRow, 5),
datePressureMachine = ReadCellValueByDateTime(companyRow, 6),
dateCarving = ReadCellValueByDateTime(companyRow, 7),
dateCutOut = ReadCellValueByDateTime(companyRow, 8),
dateModeling = ReadCellValueByDateTime(companyRow, 9),
dateAttachedWorkers = ReadCellValueByDateTime(companyRow, 10),
datePolished = ReadCellValueByDateTime(companyRow, 11),
dateStockTaking = ReadCellValueByDateTime(companyRow, 12),
datePutty = ReadCellValueByDateTime(companyRow, 13),
dateFinishes = ReadCellValueByDateTime(companyRow, 14),
datePrimer = ReadCellValueByDateTime(companyRow, 15),
dateSecondaryGrinding = ReadCellValueByDateTime(companyRow, 16),
dateFacePaint = ReadCellValueByDateTime(companyRow, 17),
datePacking = ReadCellValueByDateTime(companyRow, 18)
})
.OrderByDescending(o=>o.orderNO)
.ToList();
return new JsonNetActionResult(retValue);
}
/// <summary>
/// ConvertXLToCSV
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
private static byte[] ConvertXLToCSV(byte[] data)
{
var workbook = new XLWorkbook(new System.IO.MemoryStream(data));
var worksheet = workbook.Worksheet(1);
StringBuilder csvStringBuilder = new StringBuilder();
// Look for the first row used
var firstRowUsed = worksheet.FirstRowUsed();
// Narrow down the row so that it only includes the used part
var exportedRow = firstRowUsed.RowUsed();
int iCount = exportedRow.CellCount();
//Verify null value for current column
while (!IsBlankRow(exportedRow, iCount))
{
RenderRow(exportedRow, ref csvStringBuilder, iCount);
csvStringBuilder.Append("\r\n");
exportedRow = exportedRow.RowBelow();
}
return Encoding.UTF8.GetBytes(csvStringBuilder.ToString());
}
