/// <summary>
/// Sortieren der Daten
/// Überschriften auf BOLD setzen
/// Autofilter Aktivieren
/// Spaltenbreite an Inhalt anpassen
/// </summary>
/// <param name="worksheet"></param>
private void SortAndFormatXlsSheet(IXLWorksheet worksheet)
{
////Sortieren der Daten
var lastCellUsed = worksheet.LastCellUsed();
var lastCellUsedAddress = $"A2:{lastCellUsed.Address}";
var DataRange = worksheet.Range(lastCellUsedAddress);
DataRange.Sort("F, C, D, E");
//Autofilter und Spaltenbreite an Inhalt anpassen
worksheet.RangeUsed().SetAutoFilter();
lastCellUsed = worksheet.LastCellUsed();
//Spaltenbreite an Inhalt anpassen
worksheet.Columns().AdjustToContents(1, lastCellUsed.Address.RowNumber);
worksheet.Row(1).Style.Font.SetBold();
//Spalten als Zahl formartieren
worksheet.Columns("F,J").AdjustToContents(1, lastCellUsed.Address.RowNumber).Style.NumberFormat.NumberFormatId = 2;
worksheet.Range($"I2:I{lastCellUsed.Address.RowNumber}").SetDataType(XLDataType.Number);
//Formatieren
//Druckbereich
//Druckeigenschaften
}
public object ExcelToJson(string excel_path, string isHeader, string importStartRow)
{
List <Dictionary <string, object> > resultJson = new List <Dictionary <string, object> >();
List <string> header = new List <string>();
XLWorkbook workbook = new XLWorkbook(excel_path);
IXLWorksheet worksheet = workbook.Worksheets.Worksheet(1);
var firstCell = worksheet.FirstCellUsed();
var lastCell = worksheet.LastCellUsed();
var firstRow = worksheet.FirstRow();
int hearderColumnsCount = lastCell.Address.ColumnNumber;
if (isHeader == "checked")
{
for (int col = 1; col <= hearderColumnsCount; col++)
{
header.Add(worksheet.Cell(1, col).Value.ToString());
}
}
else
{
Dictionary <string, object> jo = new Dictionary <string, object>();
for (int col = 1; col <= hearderColumnsCount; col++)
{
string id = "col_" + col.ToString();
header.Add(id);
jo.Add(id, id);
}
resultJson.Add(jo);
}
int row = 1;
foreach (IXLRow dr in worksheet.Rows())
{
if (row != 1)
{
if (int.Parse(importStartRow) > row)
{
row += 1;
continue;
}
}
Dictionary <string, object> jo = new Dictionary <string, object>();
jo.Add("sort_order", row);
var i = 1;
foreach (var head in header)
{
jo.Add(head, dr.Cell(i).Value.ToString());
i += 1;
}
resultJson.Add(jo);
row += 1;
}
return(resultJson);
}
public IXLRange CopyTo(IXLRange range)
{
var tempRng = _sheet.Range(_sheet.Cell(1, 1), _sheet.LastCellUsed()); //_sheet.Cell(_prevrow, _prevclmn));
range.InsertRowsBelow(tempRng.RowCount() - range.RowCount(), true);
range.InsertColumnsAfter(tempRng.ColumnCount() - range.ColumnCount(), true);
tempRng.CopyTo(range.FirstCell());
var tgtSheet = range.Worksheet;
var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber;
using (var srcRows = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber))
foreach (var row in srcRows)
{
var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1);
xlRow.OutlineLevel = row.OutlineLevel;
if (row.IsHidden)
{
xlRow.Collapse();
}
else
{
xlRow.Expand();
}
}
return(range);
}
private void button2_Click(object sender, EventArgs e)
{
IXLWorkbook wb = new XLWorkbook(); //Create workbook used closedXML
IXLWorksheet ws = wb.Worksheets.Add("Sample Sheet"); //In workbook create worksheet and give name
//To delete worksheet ---> wb.Worksheet("Sample Sheet").Delete();
//Give headers using textbox inputs
ws.Cell(1, 1).Value = box_0101.Text;
ws.Cell(1, 2).Value = box_0102.Text;
ws.Cell(1, 3).Value = box_0103.Text;
ws.Cell(1, 4).Value = box_0104.Text;
ws.Cell(1, 5).Value = box_0105.Text;
ws.Cell(1, 6).Value = box_0106.Text;
ws.Cell(1, 7).Value = box_0107.Text;
ws.Cell(1, 8).Value = box_0108.Text;
//select range in cells
//IXLRange rng = ws.Range("A1:H1");
var firstCell = ws.FirstCellUsed();
var lastCell = ws.LastCellUsed();
var range = ws.Range(firstCell.Address, lastCell.Address);
range.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
ws.Row(1).Sort();
wb.SaveAs(@"Y:\Liverpool projects\Windows form app\Ruth\test.xlsx");
}
/// <summary>
/// Infoシートに情報を書き込む
/// </summary>
/// <param name="info">Infoシート</param>
private void setInfo(IXLWorksheet info)
{
setInfoHeader(info);
// 画像情報
info.Cell(3, 2).SetValue(FileName);
info.Cell(4, 2).SetValue(string.Format("{0} x {1}", Image.Width, Image.Height));
info.Cell(5, 2).SetValue(PaletteName);
// ビーズ情報
int i = 0;
Palette.Where(x => x.Count > 0).ForEach(bead =>
{
int row = 8 + i++;
info.Cell(row, 1).SetValue(bead.No)
.Style.Fill.SetBackgroundColor(bead.Color)
.Font.SetFontColor(bead.FontColor);
info.Cell(row, 2).SetValue(bead.Name);
info.Cell(row, 3).Value = bead.Count;
bead.Total += bead.Count;
bead.Count = 0;
});
info.Range(info.Cell(7, 1), info.LastCellUsed())
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center)
.Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
.Border.SetInsideBorder(XLBorderStyleValues.Dashed);
}
// ワークシートをCSVとして保存
public static void SaveAsCsv(IXLWorksheet worksheet, string filename)
{
var lines = new List <string>();
var lastCellUsed = worksheet.LastCellUsed();
if (lastCellUsed != null)
{
var rows = worksheet.Rows(1, lastCellUsed.Address.RowNumber);
foreach (var row in rows)
{
var values = new List <string>();
var cells = row.Cells(1, lastCellUsed.Address.ColumnNumber);
foreach (var cell in cells)
{
if (cell.HasFormula) // エラーになるので対応
{
values.Add(cell.ValueCached.ToString());
}
else
{
values.Add(cell.Value.ToString());
}
}
string line = string.Join(";", values);
lines.Add(line);
}
}
File.WriteAllLines(filename, lines);
}
private DataTable TransferExcelToDataTable(IXLWorksheet xlWorksheet)
{
var datatable = new DataTable();
var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed());
int col = range.ColumnCount();
int row = range.RowCount();
// add columns hedars
datatable.Clear();
for (int i = 1; i <= col; i++)
{
IXLCell column = xlWorksheet.Cell(1, i);
datatable.Columns.Add(column.Value.ToString());
}
// add rows data
int firstHeadRow = 0;
foreach (var item in range.Rows())
{
if (firstHeadRow != 0)
{
var array = new object[col];
for (int y = 1; y <= col; y++)
{
array[y - 1] = item.Cell(y).Value;
}
datatable.Rows.Add(array);
}
firstHeadRow++;
}
return(datatable);
}
private int GetRowCount(IXLWorksheet worksheet)
{
var mainFirstTableCell = worksheet.FirstCellUsed();
var mainLastTableCell = worksheet.LastCellUsed();
return(mainFirstTableCell == null || mainLastTableCell == null
? 1
: worksheet.Range(mainFirstTableCell.Address, mainLastTableCell.Address).RowCount() + 1);
}
public static void ReadFromXml(string filePath)
{
XLWorkbook wb = new XLWorkbook(filePath);
IXLWorksheet ws = wb.Worksheets.First();
IXLTable table = ws.Range(ws.FirstCellUsed(), ws.LastCellUsed()).AsTable();
/*string s = "";
* table.DataRange.CellsUsed().Count();
* table.DataRange.Rows().ForEach(row =>
* {
* s += "\n";
* row.Cells().ToList().ForEach(cell =>
* {
* s += cell.GetString()+"\t|||\t";
* });
* });
* if (true)
* {
*
* }*/
TimeTable orar = new TimeTable();
IXLCell grupaCell = null;
#region grupa
//cauta coloana care contine grupa
IXLRangeColumn grupaColoana = table.DataRange.FindColumn(column =>
{
grupaCell = column.Cells()?.FirstOrDefault(cell => cell.GetString().Equals("Grupa"));
return(grupaCell != null);
//return column.Cells().Any(cell => cell.GetString().Equals("Grupa"));
});
int id = 1;
//insereaza grupele care apar in excel in orar
grupaColoana.CellsUsed().Where(cell => !cell.GetString().Equals("Grupa")).ForEach(cell =>
{
orar.Groups.Add(new GroupDto(id++, cell.GetString()));
});
#endregion
table.Range(grupaCell.Address.RowNumber + 1, grupaCell.Address.ColumnNumber + 2,
table.LastCellUsed().Address.RowNumber, table.LastCellUsed().Address.ColumnNumber)
.CellsUsed().Where(cell => !table.Cell(cell.Address.RowNumber, 3).IsEmpty()).ForEach(cell =>
{
string[] split = cell.GetString().Split(",".ToCharArray());
//Programare prog = new Programare();
string materie = split[0];
string tipMaterie = split[1];
string sala = split[2];
string profesor = split[3];
});
}
public void SetPrevCellToLastUsed()
{
var lastUsed = _sheet.LastCellUsed();
var clmn = _clmn < lastUsed.Address.ColumnNumber
? lastUsed.Address.ColumnNumber + 1
: _clmn;
ChangeAddress(lastUsed.Address.RowNumber, clmn);
NewRow();
}
private IXLTable CreateTable <T>(IEnumerable <T> listOfEntities, IXLRow emptyRow)
{
var r = emptyRow.RowBelow().RangeAddress;
var first = r.FirstAddress;
ws.Cell(first).InsertData(listOfEntities);
var range = ws.Range(emptyRow.FirstCell(), ws.LastCellUsed());
return(range.CreateTable());
}
/// <summary>
/// Calcula la siguiente posición libre que habrá
/// en la tabla excel
/// </summary>
/// <param name="worksheet"></param>
/// <param name="posicionInicial"></param>
/// <returns></returns>
private PosicionTabla SiguientePosicion(IXLWorksheet worksheet, PosicionTabla posicionInicial)
{
var celda = worksheet.LastCellUsed();
if (celda == null)
{
return(posicionInicial);
}
return(new PosicionTabla(posicionInicial.Columna, celda.Address.RowNumber + 1));
}
public void LastCellFromMerge()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
ws.Range("B2:D4").Merge();
string first = ws.FirstCellUsed(true).Address.ToStringRelative();
string last = ws.LastCellUsed(true).Address.ToStringRelative();
Assert.AreEqual("B2", first);
Assert.AreEqual("D4", last);
}
public static void Delete(IXLWorksheet ws)
{
var firstDataCell = ws.Cell("B4");
var lastDataCell = ws.LastCellUsed();
var rng = ws.Range(firstDataCell.Address, lastDataCell.Address);
using (var range = rng.Rows(r => !string.IsNullOrWhiteSpace(r.Cell(3).GetString()) && !r.Cell(3).GetBoolean()))
{
foreach (var item in range)
{
item.Delete();
}
}
}
private static void CreateTable <TData>(IXLWorksheet worksheet, IList <ExcelColumnInfo <TData> > excelColumnInfos)
{
var firstCell = worksheet.FirstCellUsed();
var lastCell = worksheet.LastCellUsed();
var range = worksheet.Range(firstCell.Address, lastCell.Address);
foreach (var excelColumnInfo in excelColumnInfos.Where(x => x.DataFormat != null))
{
var index = excelColumnInfos.IndexOf(excelColumnInfo) + 1;
range.Column(index).Style.NumberFormat.Format = excelColumnInfo.DataFormat;
}
range.CreateTable();
worksheet.Columns().AdjustToContents();
}
public static void AreWorksheetsContentEquals(IXLWorksheet expected, IXLWorksheet actual)
{
if (expected == actual)
{
return;
}
Assert.AreEqual(expected.CellsUsed(XLCellsUsedOptions.All).Count(), actual.CellsUsed(XLCellsUsedOptions.All).Count(), "Cells used count failed");
IXLCell expectedFirstCellUsed = expected.FirstCellUsed(XLCellsUsedOptions.All);
IXLCell actualFirstCellUsed = actual.FirstCellUsed(XLCellsUsedOptions.All);
Assert.AreEqual(expectedFirstCellUsed.Address, actualFirstCellUsed.Address, "First cell used failed");
IXLCell expectedLastCellUsed = expected.LastCellUsed(XLCellsUsedOptions.All);
IXLCell actualLastCellUsed = actual.LastCellUsed(XLCellsUsedOptions.All);
Assert.AreEqual(expectedLastCellUsed.Address, actualLastCellUsed.Address, "Last cell used failed");
IXLRange range = expected.Range(expectedFirstCellUsed, expectedLastCellUsed);
foreach (IXLCell expectedCell in range.Cells())
{
IXLCell actualCell = actual.Cell(expectedCell.Address);
if (expectedCell.HasFormula)
{
Assert.AreEqual(expectedCell.FormulaA1, actualCell.FormulaA1, $"Cell {expectedCell.Address} FormulaA1 failed.");
//// For some reason sometimes the formulas "FormulaR1C1" are different although the formulas "FormulaA1" are match
//Assert.AreEqual(expectedCell.FormulaR1C1, actualCell.FormulaR1C1, $"Cell {expectedCell.Address} FormulaR1C1 failed.");
Assert.AreEqual(expectedCell.FormulaReference, actualCell.FormulaReference, $"Cell {expectedCell.Address} FormulaReference failed.");
}
else
{
Assert.AreEqual(expectedCell.Value, actualCell.Value, $"Cell {expectedCell.Address} Value failed.");
}
Assert.AreEqual(expectedCell.DataType, actualCell.DataType, $"Cell {expectedCell.Address} DataType failed.");
Assert.AreEqual(expectedCell.Active, actualCell.Active, $"Cell {expectedCell.Address} Active failed.");
AreColumnsEquals(expectedCell.WorksheetColumn(), actualCell.WorksheetColumn(), $"Column {actualCell.WorksheetColumn().RangeAddress} {{0}} failed.");
AreRowEquals(expectedCell.WorksheetRow(), actualCell.WorksheetRow(), $"Row {actualCell.WorksheetRow().RangeAddress} {{0}} failed.");
AreCellsStyleEquals(expectedCell.Style, actualCell.Style, $"Cell {expectedCell.Address} Style {{0}} failed.");
AreCellsCommentEquals(expectedCell.Comment, actualCell.Comment, $"Cell {expectedCell.Address} Comment {{0}} failed.");
}
AreMergedRangesEquals(expected.MergedRanges, actual.MergedRanges);
AreNamedRangesEquals(expected.NamedRanges, actual.NamedRanges);
ArePageSetupEquals(expected.PageSetup, actual.PageSetup, "PageSetup {0} failed.");
}
/// <summary>
/// Resultシートに情報を書き込む
/// </summary>
/// <param name="result">Resultシート</param>
public void setResult(IXLWorksheet result)
{
// タイトル
result.Cell(1, 1).SetValue(Text)
.Style.Font.SetFontSize(20.0)
.Font.SetBold();
// パレット情報
result.Cell(3, 1).SetValue("パレット")
.Style.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
.Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
.Border.SetInsideBorder(XLBorderStyleValues.Dashed)
.Fill.SetBackgroundColor(XLColor.LightGray);
result.Cell(3, 2).SetValue(PaletteName);
// ビーズ情報
result.Cell(5, 1).SetValue("No");
result.Cell(5, 2).SetValue("名前");
result.Cell(5, 3).SetValue("個数");
result.Range(5, 1, 5, 3).Style.Font.SetBold()
.Fill.SetBackgroundColor(XLColor.LightGray);
int i = 0;
Palette.Where((x) => x.Total > 0).ForEach((bead) =>
{
int row = 6 + i++;
result.Cell(row, 1).SetValue(bead.No)
.Style.Fill.SetBackgroundColor(bead.Color)
.Font.SetFontColor(bead.FontColor);
result.Cell(row, 2).SetValue(bead.Name);
result.Cell(row, 3).Value = bead.Total;
});
result.Range(result.Cell(5, 1), result.LastCellUsed())
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center)
.Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
.Border.SetInsideBorder(XLBorderStyleValues.Dashed);
// 行列幅
result.Columns(1, 1).Width = 16.43;
result.Columns(2, 2).Width = 23.57;
result.Rows(1, 1).Height = 26.25;
}
private bool LoadFromWorksheet(IXLWorksheet worksheet, out IXLRange range)
{
var numberRow = _fileMerge[_indexFile].HeaderLength + (_fileMerge[_indexFile].HeaderLength == 0 ? 1 : 0);
var finalCell = worksheet.LastCellUsed().Address;
var cellAdressInitial = string.Empty;
switch (_headerAction)
{
case HeaderAction.ConsiderFirstFile:
if (_copiedOnlyFirstHeader)
{
cellAdressInitial = $"A{numberRow + 1}";
}
else
{
if (_indexFile == 0 && _indexSheet == 0)
{
cellAdressInitial = $"A{numberRow}";
_copiedOnlyFirstHeader = true;
}
}
break;
case HeaderAction.IgnoreAll:
cellAdressInitial = $"A{numberRow + 1}";
break;
case HeaderAction.None:
default:
cellAdressInitial = "A1";
break;
}
range = worksheet.Range(worksheet.Cell(cellAdressInitial).Address, finalCell);
return(worksheet.RangeUsed().RowCount() >= numberRow);
}
/// <summary>
/// To the data table.
/// </summary>
/// <param name="xlWorksheet">The xl worksheet.</param>
/// <returns>DataTable.</returns>
public static DataTable ToDataTable(this IXLWorksheet xlWorksheet)
{
var datatable = new DataTable();
var errorFound = false;
try
{
var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed()).RangeUsed();
var columnCount = range.ColumnCount();
var rowCount = range.RowCount();
datatable.Clear();
//
// Create our columns bases on the first row which contains the itemName and category names
//
try
{
if (rowCount < 1)
{
return(new DataTable());
}
var goodColumnCount = 0;
for (var i = 1; i <= columnCount; i++)
{
if (xlWorksheet.Cell(1, i).Value.ToString().GetClsString().IsNullOrEmpty())
{
break;
}
goodColumnCount++;
datatable.Columns.Add(new DataColumn
{
DataType = Type.GetType("System.String"),
ColumnName = xlWorksheet.Cell(1, i).Value.ToString().GetClsString(),
Caption = xlWorksheet.Cell(1, i).Value.ToString()
}
);
}
columnCount = goodColumnCount;
}
catch (Exception ex)
{
NLogger.Warn("Unable to create the data table columns. {0}", ex.Message);
return(new DataTable());
}
//
// Copy cell data into our datatable.
//
if (rowCount < 2)
{
return(datatable);
}
for (var i = 2; i <= rowCount; i++)
{
if (range.Row(i).Cell(1).Value.ToString().IsNullOrEmpty())
{
continue;
}
var array = new object[columnCount];
for (var y = 1; y <= columnCount; y++)
{
try
{
if (range.Row(i).Cell(y).HasFormula)
{
NLogger.Warn("Unable to process cells which contain a forumlas (to remove formulas: copy -> paste special values). Check on Sheet {0} row {1} column {2}", xlWorksheet.Name, i, y);
errorFound = true;
continue;
}
array[y - 1] = range.Row(i).Cell(y).Value.ToString();
if (y == 1)
{
//if (array[y-1].ToString().IsEqualTo("Spurlock - SP03 Bed Ash Silo Chute", true)) System.Diagnostics.Debugger.Break();
NLogger.Trace(array[y - 1]);
}
}
catch (Exception ex)
{
errorFound = true;
NLogger.Warn("Error on Sheet {0} row {1} column {2}", xlWorksheet.Name, i, y);
NLogger.Trace(ex.Message);
}
}
if (ArrayHasValue(array))
{
datatable.Rows.Add(array);
}
}
if (errorFound)
{
throw new Exception("There are value errors in the spreadsheet.");
}
}
catch (Exception ex)
{
NLogger.Warn(ex.Message);
datatable = new DataTable();
}
return(datatable);
}
///----------------------------------------------------
/// <summary>
/// 稼働表作成 : closedXML版 2018/02/22</summary>
///----------------------------------------------------
public void worksOutputXML()
{
DateTime stDate;
DateTime edDate;
//IXLWorksheet tmpSheet = null;
try
{
using (var book = new XLWorkbook(Properties.Settings.Default.xlsKadouPath, XLEventTracking.Disabled))
{
// 稼働予定開始年月日
stDate = DateTime.Parse(DateTime.Today.Year.ToString() + "/" + DateTime.Today.Month.ToString() + "/01");
// 稼働予定終了年月日
edDate = stDate.AddMonths(6).AddDays(-1);
int ew = 0;
while (ew < sCnt)
{
// 言語別シートを作成
for (int i = 1; i <= gengo.GetLength(0); i++)
{
int sNum = i + (10 * ew);
// シートを追加する 2018/02/22
if (ew == cEAST)
{
book.Worksheet("東").CopyTo(book, sheetName[ew] + "・" + gengo[i - 1, 1], sNum);
}
else if (ew == cWEST)
{
book.Worksheet("西").CopyTo(book, sheetName[ew] + "・" + gengo[i - 1, 1], sNum);
}
// カレントシート
IXLWorksheet tmpSheet = book.Worksheet(sNum);
int xCol = 0; // 日列初期値
// 稼働予定期間のカレンダーをセット
for (int mon = 0; mon < 6; mon++)
{
// 該当月
DateTime wDt = stDate.AddMonths(mon);
xCol = 31 * mon + ew + 9;
//tmpSheet.Cell(1, xCol).Value = wDt.ToShortDateString(); // 9,40,71,102,・・・ 2018/02/23
tmpSheet.Cell(1, xCol).SetValue(wDt.Year + "年" + wDt.Month + "月"); // 9,40,71,102,・・・ 2018/02/28
// 年月と開始列の配列にセット
sheetYYMM[mon, 0] = wDt.Year.ToString() + wDt.Month.ToString().PadLeft(2, '0');
sheetYYMM[mon, 1] = xCol.ToString();
DateTime dDay;
// 該当月の暦
int dy = 0;
while (dy < 31)
{
if (DateTime.TryParse(wDt.Year.ToString() + "/" + wDt.Month.ToString() + "/" + (dy + 1).ToString(), out dDay))
{
// 2018/02/23
tmpSheet.Cell(2, xCol + dy).SetValue((dy + 1).ToString()); // 日
tmpSheet.Cell(3, xCol + dy).SetValue(dDay.ToString("ddd")); // 曜日
}
else
{
// 2018/02/23
tmpSheet.Cell(2, xCol + dy).SetValue(string.Empty);
tmpSheet.Cell(3, xCol + dy).SetValue(string.Empty);
}
dy++;
}
}
// 組合員予定申告データを取得
string cardNum = string.Empty;
string gCode = gengo[i - 1, 0];
int sRow = sheetStRow;
jfgDataClassDataContext db = new jfgDataClassDataContext();
// 東・LINQ
var linqEast = db.会員情報.Where(a => (a.言語1 == int.Parse(gCode) || a.言語2 == int.Parse(gCode) ||
a.言語3 == int.Parse(gCode) || a.言語4 == int.Parse(gCode) ||
a.言語5 == int.Parse(gCode)) && a.東西 == 1)
.OrderBy(a => a.会員稼働予定.フリガナ).ThenBy(a => a.会員稼働予定.カード番号).ThenBy(a => a.会員稼働予定.年).ThenBy(a => a.会員稼働予定.月)
.Select(a => new
{
cardno = a.カード番号,
氏名 = a.氏名,
携帯電話番号 = a.携帯電話番号,
JFG加入年 = a.JFG加入年,
a.会員稼働予定
});
// 西・LINQ
var linqWest = db.会員情報.Where(a => (a.言語1 == int.Parse(gCode) || a.言語2 == int.Parse(gCode) ||
a.言語3 == int.Parse(gCode) || a.言語4 == int.Parse(gCode) ||
a.言語5 == int.Parse(gCode)) && a.東西 == 2)
.OrderBy(a => a.地域コード).ThenBy(a => a.会員稼働予定.フリガナ).ThenBy(a => a.カード番号).ThenBy(a => a.会員稼働予定.年).ThenBy(a => a.会員稼働予定.月)
.Select(a => new
{
地域コード = a.地域コード,
地域名 = a.地域名,
cardno = a.カード番号,
氏名 = a.氏名,
携帯電話番号 = a.携帯電話番号,
JFG加入年 = a.JFG加入年,
a.会員稼働予定
});
if (ew == cEAST) // 東
{
// 組合員予定申告データクラスのインスタンス生成
clsWorksTbl w = new clsWorksTbl();
w.cardNumBox = string.Empty;
w.sRow = sheetStRow;
w.ew = ew;
foreach (var t in linqEast)
{
w.cardNo = t.cardno;
w.氏名 = t.氏名;
w.携帯電話番号 = t.携帯電話番号;
w.JFG加入年 = (short)t.JFG加入年;
w.会員稼働予定 = t.会員稼働予定;
// エクセル稼働表作成 2018/02/26
if (!xlsCellsSetXML(w, tmpSheet))
{
continue;
}
}
}
else if (ew == cWEST) // 西
{
// 組合員予定申告データクラスのインスタンス生成
clsWorksTbl w = new clsWorksTbl();
w.cardNumBox = string.Empty;
w.sRow = sheetStRow;
w.ew = ew;
foreach (var t in linqWest)
{
w.地域コード = (int)t.地域コード;
w.地域名 = t.地域名;
w.cardNo = t.cardno;
w.氏名 = t.氏名;
w.携帯電話番号 = t.携帯電話番号;
w.JFG加入年 = (short)t.JFG加入年;
w.会員稼働予定 = t.会員稼働予定;
// エクセル稼働表作成 2018/02/26
if (!xlsCellsSetXML(w, tmpSheet))
{
continue;
}
}
}
// カレンダーにない日の列削除
bool colDelStatus = true;
// 2018/02/26
while (colDelStatus)
{
for (int cl = (ew + 9); cl <= tmpSheet.RangeUsed().RangeAddress.LastAddress.ColumnNumber; cl++)
{
if (!Utility.NumericCheck(Utility.nulltoString(tmpSheet.Cell(2, cl).Value).Trim()))
{
tmpSheet.Column(cl).Delete(); // 2018/02/26
colDelStatus = true;
break;
}
else
{
colDelStatus = false;
}
}
}
// 年月を表すセルを結合する 2018/02/28
int stCell = 0;
int edCell = 0;
tmpSheet.Range(tmpSheet.Cell(1, ew + 9).Address,
tmpSheet.Cell(1, tmpSheet.LastCellUsed().Address.ColumnNumber).Address).Style
.Border.BottomBorder = XLBorderStyleValues.Thin;
for (int cl = (ew + 9); cl <= tmpSheet.LastCellUsed().Address.ColumnNumber; cl++)
{
if (Utility.nulltoString(tmpSheet.Cell(2, cl).Value).Trim() == "1")
{
if (stCell == 0)
{
stCell = cl;
}
else
{
// セル結合
tmpSheet.Range(tmpSheet.Cell(1, stCell).Address, tmpSheet.Cell(1, edCell).Address).Merge(false);
// IsMerge()パフォ劣化回避のためのStyle変更
for (int cc = stCell; cc <= edCell; cc++)
{
tmpSheet.Cell(1, cc).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
stCell = cl;
}
}
else
{
edCell = cl;
}
}
if (stCell != 0)
{
// セル結合
tmpSheet.Range(tmpSheet.Cell(1, stCell).Address, tmpSheet.Cell(1, edCell).Address).Merge(false);
// IsMerge()パフォ劣化回避のためのStyle変更
for (int cc = stCell; cc <= edCell; cc++)
{
tmpSheet.Cell(1, cc).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
}
// 表の外枠罫線を引く 2018/02/26
var range = tmpSheet.Range(tmpSheet.Cell("A1").Address, tmpSheet.LastCellUsed().Address);
range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// 年月セル下部に罫線を引く 2018/02/28
tmpSheet.Range(tmpSheet.Cell(1, ew + 9).Address,
tmpSheet.Cell(1, tmpSheet.LastCellUsed().Address.ColumnNumber).Address).Style
.Border.BottomBorder = XLBorderStyleValues.Thin;
tmpSheet.Range(tmpSheet.Cell(2, ew + 9).Address,
tmpSheet.Cell(2, tmpSheet.LastCellUsed().Address.ColumnNumber).Address).Style
.Border.BottomBorder = XLBorderStyleValues.Dotted;
// 明細最上部に罫線を引く 2018/02/27
tmpSheet.Range(tmpSheet.Cell("A4").Address,
tmpSheet.Cell(4, tmpSheet.LastCellUsed().Address.ColumnNumber).Address).Style
.Border.TopBorder = XLBorderStyleValues.Thin;
// 表の外枠左罫線を引く 2018/02/27
tmpSheet.Range(tmpSheet.Cell("A1").Address, tmpSheet.LastCellUsed().Address).Style
.Border.LeftBorder = XLBorderStyleValues.Thin;
// 見出しの背景色 2018/02/28
tmpSheet.Range(tmpSheet.Cell("A1").Address, tmpSheet.Cell(3, tmpSheet.LastCellUsed().Address.ColumnNumber).Address)
.Style.Fill.BackgroundColor = XLColor.WhiteSmoke;
// 日曜日の背景色
range = tmpSheet.Range(tmpSheet.Cell(3, ew + 9).Address, tmpSheet.Cell(3, tmpSheet.LastCellUsed().Address.ColumnNumber).Address);
range.AddConditionalFormat()
.WhenEquals("日")
.Fill.SetBackgroundColor(XLColor.MistyRose);
var range2 = tmpSheet.Range(tmpSheet.Cell(2, ew + 9).Address, tmpSheet.Cell(2, tmpSheet.LastCellUsed().Address.ColumnNumber).Address);
if (ew == cEAST)
{
// 日曜日の日付の背景色
range2.AddConditionalFormat()
.WhenIsTrue("=I3=" + @"""日""")
.Fill.BackgroundColor = XLColor.MistyRose;
// ウィンドウ枠の固定
tmpSheet.SheetView.Freeze(3, 2);
// 見出し
tmpSheet.Cell("A2").SetValue("氏名").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
tmpSheet.Cell("B2").SetValue("フリガナ").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
tmpSheet.Cell("C2").SetValue("入会年度").Style.Font.SetBold(true);
tmpSheet.Cell("D2").SetValue("携帯電話").Style.Font.SetBold(true);
tmpSheet.Cell("E2").SetValue("稼働日数").Style.Font.SetBold(true);
tmpSheet.Cell("F2").SetValue("自己申告").Style.Font.SetBold(true);
tmpSheet.Cell("F3").SetValue("日数").Style.Font.SetBold(true);
tmpSheet.Cell("G2").SetValue("備考").Style.Font.SetBold(true);
tmpSheet.Cell("H2").SetValue("更新日").Style.Font.SetBold(true);
// 見出しはBold 2018/02/28
tmpSheet.Range(tmpSheet.Cell("I1").Address, tmpSheet.Cell(3, tmpSheet.LastCellUsed().Address.ColumnNumber).Address)
.Style.Font.SetBold(true);
}
else if (ew == cWEST)
{
// 日曜日の日付の背景色
range2.AddConditionalFormat()
.WhenIsTrue("=J3=" + @"""日""")
.Fill.BackgroundColor = XLColor.MistyRose;
// ウィンドウ枠の固定
tmpSheet.SheetView.Freeze(3, 3);
// 見出し
tmpSheet.Cell("A2").SetValue("地域").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
tmpSheet.Cell("B2").SetValue("氏名").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
tmpSheet.Cell("C2").SetValue("フリガナ").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
tmpSheet.Cell("D2").SetValue("入会年度").Style.Font.SetBold(true);
tmpSheet.Cell("E2").SetValue("携帯電話").Style.Font.SetBold(true);
tmpSheet.Cell("F2").SetValue("稼働日数").Style.Font.SetBold(true);
tmpSheet.Cell("G2").SetValue("自己申告").Style.Font.SetBold(true);
tmpSheet.Cell("G3").SetValue("日数").Style.Font.SetBold(true);
tmpSheet.Cell("H2").SetValue("備考").Style.Font.SetBold(true);
tmpSheet.Cell("I2").SetValue("更新日").Style.Font.SetBold(true);
// 見出しはBold 2018/02/28
tmpSheet.Range(tmpSheet.Cell("J1").Address, tmpSheet.Cell(3, tmpSheet.LastCellUsed().Address.ColumnNumber).Address)
.Style.Font.SetBold(true);
}
}
ew++;
}
// テンプレートシートは削除する 2018/02/26
book.Worksheet("東").Delete();
book.Worksheet("西").Delete();
// カレントシート 2018/02/26
//tmpSheet = book.Worksheet(1);
//保存処理 2018/02/26
book.SaveAs(Properties.Settings.Default.xlsWorksPath);
}
}
catch (Exception ex)
{
Console.Write(ex.ToString());
}
finally
{
}
}
private DataTable ConvertWorksheetToDataTable(IXLWorksheet worksheet)
{
var dataTable = new DataTable();
List <int> formattedColumnNumbers = new List <int>();
IXLRange range = worksheet.Range(worksheet.FirstCellUsed(), worksheet.LastCellUsed());
int columnCount = range.ColumnCount();
dataTable.Clear();
for (int i = 1; i <= columnCount; i++)
{
IXLCell cell = worksheet.Cell(1, i);
Type dataTableColumnType = GetDataType(range, cell.Address.ColumnNumber);
dataTable.Columns.Add(cell.Value.ToString(), dataTableColumnType);
}
int firstHeadRow = 0;
foreach (var row in range.Rows())
{
if (firstHeadRow != 0)
{
var array = new object[columnCount];
for (int y = 1; y <= columnCount; y++)
{
if (!row.Cell(y).HasFormula)
{
if (row.Cell(y).Value == null || string.IsNullOrEmpty(row.Cell(y).Value.ToString()))
{
array[y - 1] = null;
}
else if (formattedColumnNumbers.Contains(row.Cell(y).Address.ColumnNumber))
{
array[y - 1] = row.Cell(y).GetFormattedString();
}
else if (row.Cell(y).DataType == XLDataType.DateTime)
{
DateTime dateTime = row.Cell(y).GetDateTime();
array[y - 1] = dateTime.TimeOfDay.Ticks == 0
? dateTime.ToString("MM/dd/yyyy")
: dateTime.ToString();
}
else
{
row.Cell(y).SetDataType(XLDataType.Text);
array[y - 1] = row.Cell(y).Value;
}
}
}
dataTable.Rows.Add(array);
}
firstHeadRow++;
}
return(dataTable);
}
private void AddPerformanceFormatting(IXLWorksheet performanceSheet)
{
int lastRowUsed = performanceSheet.LastRowUsed().RowNumber();
//freeze panels
performanceSheet.SheetView.Freeze(1, 2);
//performance global styles
performanceSheet.Range(1, 1, performanceSheet.LastCellUsed().Address.RowNumber, performanceSheet.LastCellUsed().Address.ColumnNumber)
.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
//column specific formatting
//date columns
performanceSheet.Range(string.Format("A2:A{0}", lastRowUsed)).Style.DateFormat.Format = "dd/MM/yyyy";
//percentage formatting (0.00%)
performanceSheet.Ranges(string.Format("E2:E{0},H2:I{1},Y2:Y{2},AC2:AC{3},AE2:AF{4},AH2:AI{5},AK2:AL{6},AN2:AO{7}",
lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.NumberFormatId = 10;
//no decimal points
performanceSheet.Range(string.Format("R2:R{0}", lastRowUsed)).Style.NumberFormat.Format = "0";
//decimal format (0.00)
performanceSheet.Ranges(string.Format("J2:J{0},L2:N{1}",
lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "0.00";
//three decimal points (0.000)
performanceSheet.Range(string.Format("U2:U{0}",
lastRowUsed
)).Style.NumberFormat.Format = "0.000";
//money with two decimals ($ 0.00)
performanceSheet.Ranges(string.Format("T2:T{0},W2:W{1},Z2:Z{2}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.00";
//money with three decimals ($ 0.000)
performanceSheet.Range(string.Format("V2:V{0}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.000";
// adjust to content
performanceSheet.Columns().AdjustToContents();
}
//TODO:
//Throw Exception
public DataTable InsertTblRegisteredStudents(String FilePath)
{
DataTable dt = null;
using (XLWorkbook workBook = new XLWorkbook(FilePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
//Create a new DataTable.
dt = new DataTable();
//Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
if (!row.Cell(1).IsEmpty())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(workSheet.FirstCellUsed().Address.ColumnNumber, workSheet.LastCellUsed().Address.ColumnNumber))
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
}
}
return(dt);
}
private void prepDashboardSheets(IXLWorksheet sheet)
{
/*.
* can use htis for both types of sheets.
* Regular: List.Count -1 (end of list: should be Month) List.Count -2 (last value: Should be provider name)
* Diabetes: List.Count -1 (end of list: Month) List.Count -3 (blank space between provider name an dmonth on this one)
*/
_worksheet = sheet;
//use this one to keep track of provider name and row number. then send it to metrics to dashboard and do accordignly
Dictionary<String, IXLRangeRow> providerRows = new Dictionary<String, IXLRangeRow>();
if (_worksheet != null)
{
var firstCell = _worksheet.FirstCellUsed();
var lastCell = _worksheet.LastCellUsed();
_worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);
if (_worksheet_range != null)
{
int nRows = _worksheet_range.RowCount();
int nCols = _worksheet_range.ColumnCount();
for (int i = 1; i < nRows + 1; i++)
{
var row = _worksheet_range.Row(i);
var newRow = _worksheet_range.Row(i + 1);
string value = row.Cell(1).Value as string;
if (value != null)
{
foreach (string provider in providers)
{
if (value.Contains(provider))
{
if (_worksheet == _dashboard.Worksheet(2))//add a new row for the depression sheets
{
newRow = _worksheet_range.Row(i + 3);
newRow.InsertRowsBelow(1);
var blankRow = _worksheet_range.Row(i + 4);
blankRow.Style.NumberFormat.NumberFormatId = 0;
providerRows.Add(value, blankRow);
}
else //add a new row for every other sheet in the dashboard: Asthma, Diabetes, Cardiovascular, Preventive
{
newRow = _worksheet_range.Row(i + 2);//this gets us int he right area and then insert the row above
newRow.InsertRowsBelow(1); //try to insert rows after we have metrics and tehn insert metrics into cells then insert row
var blankRow = _worksheet_range.Row(i + 3);
blankRow.Style.NumberFormat.NumberFormatId = 0;
providerRows.Add(value, blankRow);
}
break; //break out of the foreach provider loop, we already found one, we wont find another match, time to go to the next row instead
}
}
}
}
MetricsToDashboard(providerRows, _worksheet);//figure out what we need to send to this method , worksheet, provider / row dict etc.
}
}
}
internal static void SetAsTable(this IXLWorksheet sheet, int startRow, int startCol)
{
var range = sheet.Range(sheet.Cell(startRow, startCol), sheet.LastCellUsed());
range.CreateTable("EarningsTable");
}
public ViewResult saveToDatabase(HttpPostedFileBase excelFile)
{
try
{
//Save the uploaded Excel file.
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
excelFile.SaveAs(path + Path.GetFileName(excelFile.FileName));
using (XLWorkbook workBook = new XLWorkbook(path + Path.GetFileName(excelFile.FileName)))
{
String columnsNamesQuery = String.Format("CREATE TABLE {0} (", excelFile.FileName.Split('.')[0]);
String insertQuery = String.Format("INSERT INTO {0} (", excelFile.FileName.Split('.')[0]);
IXLWorksheet workSheet = workBook.Worksheet(1);
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
columnsNamesQuery += "[" + cell.Value.ToString() + "] VARCHAR(MAX),";
insertQuery += "[" + cell.Value.ToString() + "],";
}
insertQuery = insertQuery.Remove(insertQuery.Length - 1, 1);
insertQuery += ") VALUES (";
columnsNamesQuery = columnsNamesQuery.Remove(columnsNamesQuery.Length - 1, 1);
columnsNamesQuery += ");";
//Creating data table
Command.Connection = Connection;
Connection.Open();
Command.CommandText = columnsNamesQuery;
Command.ExecuteNonQuery();
Connection.Close();
firstRow = false;
}
else
{
String rowsQuery = insertQuery;
foreach (IXLCell cell in row.Cells(workSheet.FirstCellUsed().Address.ColumnNumber, workSheet.LastCellUsed().Address.ColumnNumber))
{
if (cell.IsEmpty())
{
rowsQuery += "NULL,";
}
else
{
rowsQuery += "'" + cell.Value.ToString() + "' ,";
}
}
rowsQuery = rowsQuery.Remove(rowsQuery.Length - 1, 1);
rowsQuery += ");";
//Insert data into database
Command.Connection = Connection;
Connection.Open();
Command.CommandText = rowsQuery;
Command.ExecuteNonQuery();
Connection.Close();
}
}
}
return(View("Success"));
}
catch (Exception e)
{
ViewBag.error = "ERROR: " + e.Message + "<br />";
return(View("Index"));
}
}
//カレントSheetのデータ範囲最終行を取得
private int getEndRow()
{
var last = _currentWs.LastCellUsed();
return(last.WorksheetRow().RowNumber());
}
//Read Excel data to generic list - overloaded version 2.
public static IList <T> GetDataToList <T>(string filePath, string sheetName, Func <IList <string>, T> addRecord)
{
List <T> resultList = new List <T>();
using (XLWorkbook wB = new XLWorkbook(filePath))
{
IXLWorksheet wS = wB.Worksheet(1);
//DataTable dt = new DataTable();
//bool firstRow = true;
//Used for sheet row data to be added through delegation.
var firstRow = true;
int lastRow = wS.LastRowUsed().RowNumber();
var rows = wS.Rows(1, lastRow);
foreach (IXLRow row in rows)
{
if (firstRow)
{
////foreach (IXLCell cell in row.Cells())
////{
//// dt.Columns.Add(cell.Value.ToString());
////}
firstRow = false;
continue;
}
if (row.IsEmpty())
{
continue;
}
var rowData = new List <string>();
//else
//{
//dt.Rows.Add();
//int i = 0;
//rowData.Clear();
foreach (IXLCell cell in row.Cells(wS.FirstCellUsed().Address.ColumnNumber, wS.LastCellUsed().Address.ColumnNumber))
{
rowData.Add(cell.Value.ToString());
}
//foreach (IXLCell cell in row.Cells())
//{
// rowData.Add(cell.Value.ToString());
//}
resultList.Add(addRecord(rowData));
}
// }
return(resultList);
}
}
private IXLRange GetRootRange(IXLWorksheet ws, IList <IXLNamedRange> worksheetPanels)
{
return(ws.Range(ws.FirstCell(), GetRootRangeLastCell(ws.LastCellUsed(), worksheetPanels ?? new List <IXLNamedRange>())));
}
public IXLRange CopyTo(IXLRange range)
{
var firstCell = _sheet.Cell(1, 1);
var lastCell = _sheet.LastCellUsed(XLCellsUsedOptions.All) ?? firstCell;
var tempRng = _sheet.Range(firstCell, lastCell);
var rowDiff = tempRng.RowCount() - range.RowCount();
if (rowDiff > 0)
{
range.LastRow().RowAbove().InsertRowsBelow(rowDiff, true);
}
else if (rowDiff < 0)
{
range.Worksheet.Range(
range.LastRow().RowNumber() + rowDiff + 1,
range.FirstColumn().ColumnNumber(),
range.LastRow().RowNumber(),
range.LastColumn().ColumnNumber())
.Delete(XLShiftDeletedCells.ShiftCellsUp);
}
range.Worksheet.ConditionalFormats.Remove(c => c.Range.Intersects(range));
var columnDiff = tempRng.ColumnCount() - range.ColumnCount();
if (columnDiff > 0)
{
range.InsertColumnsAfter(columnDiff, true);
}
else if (columnDiff < 0)
{
range.Worksheet.Range(
range.FirstRow().RowNumber(),
range.LastColumn().ColumnNumber() + columnDiff + 1,
range.LastRow().RowNumber(),
range.LastColumn().ColumnNumber())
.Delete(XLShiftDeletedCells.ShiftCellsLeft);
}
tempRng.CopyTo(range.FirstCell());
var tgtSheet = range.Worksheet;
var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber;
var srcRows = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber);
foreach (var row in srcRows)
{
var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1);
xlRow.OutlineLevel = row.OutlineLevel;
if (row.IsHidden)
{
xlRow.Collapse();
}
else
{
xlRow.Expand();
}
}
return(range);
}
public static Dictionary <string, string> ReadExcel(string path, int fCell, int fRow)
{
try
{
using (XLWorkbook workBook = new XLWorkbook(path))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
var range = workSheet.Range(workSheet.FirstCellUsed(), workSheet.LastCellUsed());
var dict = new Dictionary <string, string>();
var i = 0;
foreach (var item in range.Rows())
{
if (i >= fRow)
{
dict.TryAdd(item.Cell(fCell).Value.ToString(), item.Cell(fCell + 2).Value.ToString());
}
i++;
}
return(dict);
}
}
catch (Exception ex)
{
throw ex;
}
}
private List<String> ReturnMetricsRow(IXLWorksheet sheet)
{
List<String> metricNames = new List<string>();
_worksheet = sheet;
if (_worksheet != null)
{
var firstCell = _worksheet.FirstCellUsed();
var lastCell = _worksheet.LastCellUsed();
_worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);
if (_worksheet_range != null)
{
int nRows = _worksheet_range.RowCount();
int nCols = _worksheet_range.ColumnCount();
for (int i = 1; i < nRows + 1; i++)
{
var row = _worksheet_range.Row(i);
var newRow = _worksheet_range.Row(i + 1);
string value = row.Cell(1).Value as string;
if (value == "Month")
{
var metricRow = _worksheet_range.Row(i);
for (int x = 1; x <= metricRow.CellCount(); x++)
{
metricNames.Add(metricRow.Cell(x).Value.ToString());
}
break;
}
}
}
}
return metricNames;
}