public static DataTable SheetToDataTable(string path, string sheetname)
{
XLWorkbook book = new XLWorkbook(path);
IXLWorksheet sheet = book.Worksheet(sheetname);
Console.WriteLine(sheet.RangeUsed().RowCount());
DataTable dt = new DataTable();
for (int c = 1; c <= sheet.RangeUsed().ColumnCount(); c++)
{
dt.Columns.Add(sheet.Row(1).Cell(c).GetString());
}
for (int r = 2; r <= sheet.RangeUsed().RowCount(); r++)
{
DataRow row = dt.NewRow();
for (int c = 1; c <= sheet.RangeUsed().ColumnCount(); c++)
{
row[c - 1] = sheet.Row(r).Cell(c).GetString();
}
dt.Rows.Add(row);
}
book.Dispose();
return(dt);
}
public static DataTable SheetToDataTable(string path, string sheetname)
{
DataTable dt = new DataTable();
using (XLWorkbook book = new XLWorkbook(path))
{
IXLWorksheet sheet = book.Worksheet(sheetname);
int rowCount = sheet.RangeUsed().RowCount();
int colCount = sheet.RangeUsed().ColumnCount();
//fill coulmn
for (int c = 1; c <= colCount; c++)
{
//Console.WriteLine(sheet.Row(1).Cell(c).GetString());
dt.Columns.Add(sheet.Row(1).Cell(c).GetString());
}
//fill row
for (int r = 2; r <= rowCount; r++)
{
DataRow row = dt.NewRow();
for (int c = 1; c <= colCount; c++)
{
// Console.WriteLine(sheet.Row(r).Cell(c).GetString());
row[c - 1] = sheet.Row(r).Cell(c).GetString();
}
dt.Rows.Add(row);
}
}
return(dt);
}
/// <summary>
/// Загрузка фильтров в таблицу DataTable
/// </summary>
/// <param name="FileName">Имя файла, из которого загружаются фильтры</param>
/// <returns></returns>
private DataTable LoadFilters(string FileName)
{
// Загрузка книги Excel
XLWorkbook FilterTable = new XLWorkbook(FileName);
IXLWorksheet FilterSheet = FilterTable.Worksheets.ToList()[0];
// Подготовка таблицы
DataTable Filter = new DataTable();
Filter.TableName = "Фильтры";
Filter.Columns.Add("Number", typeof(string));
// Подготовка счётчиков для статусной строки
string StatusStr = "Загрузка фильтров";
int i = 0;
int m = FilterSheet.RangeUsed().RowsUsed().Skip(0).Count();
SetStatus(StatusStr, i, m);
// Загрузка фильтров из книги Excel в DataTable
foreach (var row in FilterSheet.RangeUsed().RowsUsed().Skip(0))
{
Filter.Rows.Add(row.Cell(1).Value);
SetStatus(StatusStr, i++, m);
Wait();
}
return(Filter);
}
public bool readLOIExcelFile(DataTable dtLOI, string path)
{
bool result = true;
int totalRow = 0;
int totalColumn = 0;
DataRow dr = null;
try
{
using (XLWorkbook workBook = new XLWorkbook(path))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
totalRow = workSheet.RangeUsed().RowCount();
totalColumn = workSheet.RangeUsed().ColumnCount();
for (int row = 3; row <= totalRow; row++)
{
dr = dtLOI.NewRow();
string workpackageid = workSheet.Row(row).Cell(2).Value.ToString();
string Customer_PO = workSheet.Row(row).Cell(3).Value.ToString();
string strCustomer_PO_Date = workSheet.Row(row).Cell(4).Value.ToString();
string PO_Description = workSheet.Row(row).Cell(5).Value.ToString();
string Region = workSheet.Row(row).Cell(6).Value.ToString();
string Site_ID = workSheet.Row(row).Cell(7).Value.ToString();
string ScopeOfWork = workSheet.Row(row).Cell(8).Value.ToString();
string Subcone_Name = workSheet.Row(row).Cell(9).Value.ToString();
string Site_Model = workSheet.Row(row).Cell(10).Value.ToString();
DateTime Customer_PO_Date;
DateTime.TryParse(strCustomer_PO_Date, out Customer_PO_Date);
dr[0] = workpackageid;
dr[1] = Customer_PO;
dr[2] = Customer_PO_Date;
dr[3] = PO_Description;
dr[4] = Region;
dr[5] = Site_ID;
dr[6] = ScopeOfWork;
dr[7] = Subcone_Name;
dr[8] = Site_Model;
dtLOI.Rows.Add(dr);
}
}
}
catch (Exception e)
{
result = false;
}
finally
{
}
return(result);
}
/// <summary>
/// Загрузка исходных данных в таблицу DataTable
/// </summary>
/// <param name="FileName">Имя файла, из которого загружаются данные</param>
/// <returns></returns>
private DataTable LoadIn(string FileName)
{
// Загрузка книги Excel
string PureName = Path.GetFileNameWithoutExtension(FileName);
XLWorkbook FromTable = new XLWorkbook(FileName);
IXLWorksheet FromSheet = FromTable.Worksheets.ToList()[0];
// Подготовка таблицы
DataTable In = new DataTable();
In.TableName = PureName;
int k = 1;
foreach (object X in FromSheet.Columns())
{
In.Columns.Add(FromSheet.RangeUsed().RowsUsed().ToArray()[0].Cell(k).Value.ToString(),
FromSheet.RangeUsed().RowsUsed().ToArray()[1].Cell(k++).Value.GetType());
}
// Подготовка счётчиков для статусной строки
string StatusStr = $"{PureName}: Загрузка данных";
int i = 0;
int m = FromSheet.RangeUsed().RowsUsed().Skip(Head).Count();
SetStatus(StatusStr, i, m);
Wait();
// Загрузка данных из книги Excel в DataTable
foreach (var row in FromSheet.RangeUsed().RowsUsed().Skip(Head))
{
object[] NewRow = new object[In.Columns.Count];
for (int j = 0; j < In.Columns.Count; j++)
{
NewRow[j] = row.Cell(j + 1).Value;
}
try
{
In.Rows.Add(NewRow);
}
catch (Exception e)
{
LogWindow.Add($"{PureName}: строка {row.RowNumber()}: ошибка загрузки данных (неправильный формат): {e.Message}");
}
SetStatus(StatusStr, i++, m);
Wait();
}
return(In);
}
private void BtnBrowse_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = "Excel Dosyası |*.xlsx";
DialogResult dialogResult = openFileDialog1.ShowDialog();
if (dialogResult == DialogResult.OK)
{
string filePath = openFileDialog1.FileName;
IXLWorkbook workbook = new XLWorkbook(filePath);
IXLWorksheet worksheet = workbook.Worksheet("Veriler");
IXLRange range = worksheet.RangeUsed();
List <Person> people = new List <Person>();
int lastRow = range.RowCount();
foreach (IXLRangeRow item in range.Rows(2, lastRow))
{
Person person = new Person(
Convert.ToInt32(item.Cell("A").Value),
item.Cell("B").Value.ToString(),
(DateTime)item.Cell("C").Value
);
people.Add(person);
}
DtgPersonList.DataSource = people;
TxtPath.Text = filePath;
}
}
/// <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 static async Task <List <Kategoria> > Import(StorageFile _fajl)
{
XLWorkbook wb = null;
using (IRandomAccessStream s = await _fajl.OpenReadAsync())
wb = new XLWorkbook(s.AsStreamForRead());
IXLWorksheet ws = wb.Worksheet("Categories");
List <Kategoria> kategoriak = new List <Kategoria>();
foreach (var sor in ws.RangeUsed().RowsUsed())
{
if (sor.RowNumber() == 1)
{
continue;
}
Kategoria kategoria = new Kategoria()
{
ID = sor.Cell(1).GetValue <int>(),
ParentID = sor.Cell(2).GetValue <int>(),
Megnevezes = sor.Cell(4).GetString()
};
kategoriak.Add(kategoria);
}
return(kategoriak);
}
/// <summary>
/// Метод по получению списка объектов справочника "ОбъектыСтроительства".
/// </summary>
/// <returns>Список объектов справочника "ОбъектыСтроительства".</returns>
public static Dictionary <int, DataVersion> GetDataOfDataVersionDirectory()
{
// Прочитаем Excel файл с данными:
Dictionary <int, DataVersion> dataVersions = new Dictionary <int, DataVersion>();
// Открываем файл
XLWorkbook workBook = new XLWorkbook("DataSources\\DataVersions.xlsx");
IXLWorksheet workSheet = workBook.Worksheet(1);
int indexOfLastRow = workSheet.RangeUsed().LastRowUsed().RangeAddress.FirstAddress.RowNumber;
// Читаем со второй строки, так как первая это шапка-заголовок
var wshRows = workSheet.Rows(2, indexOfLastRow);
foreach (IXLRow row in wshRows)
{
IXLCells wshCells = row.Cells("1:3");
int id = Convert.ToInt32(row.Cell(1).Value);
// Создаём и добавляем элементы.
dataVersions.Add(
id,
new DataVersion
{
ID = id,
Name = row.Cell(2).Value.ToString(),
VersionType = row.Cell(3).Value.ToString(),
});
}
return(dataVersions);
}
/// <summary>
/// Метод по получению списка объектов справочника "ОбъектыСтроительства".
/// </summary>
/// <returns>Список объектов справочника "ОбъектыСтроительства".</returns>
public static Dictionary <int, ConstructionObject> GetDataOfConstructionObjectsDirectory()
{
// Прочитаем Excel файл с данными:
Dictionary <int, ConstructionObject> objectsOfBuilding = new Dictionary <int, ConstructionObject>();
// Открываем файл
XLWorkbook workBook = new XLWorkbook("DataSources\\ConstructionObjects.xlsx");
IXLWorksheet workSheet = workBook.Worksheet(1);
int indexOfLastRow = workSheet.RangeUsed().LastRowUsed().RangeAddress.FirstAddress.RowNumber;
// Читаем со второй строки, так как первая это шапка-заголовок
var wshRows = workSheet.Rows(2, indexOfLastRow);
foreach (IXLRow row in wshRows)
{
IXLCells wshCells = row.Cells("1:4");
int id = Convert.ToInt32(row.Cell(1).Value);
// Создаём и добавляем элементы в List.
objectsOfBuilding.Add(
id,
new ConstructionObject
{
ID = id,
Name = row.Cell(2).Value.ToString(),
Code = row.Cell(3).Value.ToString(),
Budget = (double)row.Cell(4).Value,
});
}
return(objectsOfBuilding);
}
public static object[] GetSheetIntoObjectUsingClosedXML(string fileDetails, string sheetName)
{
object[] main = null;
using (XLWorkbook book = new XLWorkbook(fileDetails))
{
IXLWorksheet xlSheet = book.Worksheet(sheetName);
IXLRange xlRange = xlSheet.RangeUsed();
int rowCount = xlRange.RowCount();
int ColCount = xlRange.ColumnCount();
Console.WriteLine(rowCount);
Console.WriteLine(ColCount);
main = new object[rowCount - 1]; //number of rows (Test case)
for (int i = 2; i <= rowCount; i++)
{
object[] temp = new object[ColCount]; //number of parameter
for (int j = 1; j <= ColCount; j++)
{
string value = xlRange.Cell(i, j).GetString();
Console.WriteLine(value);
temp[j - 1] = value;
}
main[i - 2] = temp;
}
book.Dispose();
}
return(main);
}
public void FileOpen(string path)
{
using (XLWorkbook workbook = new XLWorkbook(path))
{
for (int i = 2; i <= workbook.Worksheets.Count(); i++) // Удаление лишних листов, но я не увидел улучшение производительности :(
{
workbook.Worksheet(i).Delete();
}
IXLWorksheet ws1 = workbook.Worksheet(1);
foreach (var xlRow in ws1.RangeUsed().Rows())
{
Rows.Add(new List <string>());
foreach (var xlCell in xlRow.Cells())
{
var formula = xlCell.FormulaA1;
var value = xlCell.Value.ToString();
string targetCellValue = (formula.Length == 0) ? value : "=" + formula;
Rows[Rows.Count - 1].Add(targetCellValue);
}
}
workbook.Worksheet(1).Delete();
}
}
/// <summary>
/// 使用FormattedText 返回模拟译文以及宽度
/// </summary>
/// <param name="sheet"></param>
/// <param name="beginRow"></param>
/// <param name="sourceClumn1"></param>
/// <param name="sourceClumn2"></param>
/// <param name="translation"></param>
/// <returns></returns>
public static Dictionary <string, Translation> GetTranlstionFromExcel(IXLWorksheet sheet, int beginRow, int sourceClumn1, int sourceClumn2, int translation)
{
int rows = sheet.RangeUsed().RowCount();
Dictionary <string, Translation> dicCheckTranlation = new Dictionary <string, Translation>();
Random random = new Random(6);
//遍历
for (int i = beginRow; i <= rows; i++)
{
//获取模拟译文
string strTranlation = sheet.Cell(i, random.Next(3, 5)).Value + GetRandString(10);
//获取模拟译文长度
double width = GetStringActualWidthByMethod(strTranlation,
new FontFamily("Microsoft YaHei UI"),
FontStyles.Normal,
FontWeights.Normal,
FontStretches.Normal,
12);
//键
string strKey = sheet.Cell(i, 2).Value.ToString();
//保存
dicCheckTranlation.Add(strKey, new Translation {
TranslationValue = strTranlation, TranslationWidth = width
});
}
return(dicCheckTranlation);
}
private static IEnumerable <T> Entries <T>(IXLWorksheet worksheet)
where T : KeyStringDictionary, new()
{
var range = worksheet.RangeUsed();
if (null == range)
{
yield break;
}
var columns = new List <string>();
for (var i = 1; i < range.ColumnCount() + 1; i++)
{
columns.Add(Cell(range, 1, i));
}
for (var i = 2; i < range.RowCount() + 1; i++)
{
var entry = Activator.CreateInstance <T>();
for (var j = 0; j < columns.Count; j++)
{
entry.Add(columns[j], Cell(range, i, j + 1));
}
yield return(entry);
}
}
public void AutoFilterExpandsWithTable()
{
using (var wb = new XLWorkbook())
{
using (IXLWorksheet ws = wb.Worksheets.Add("Sheet1"))
{
ws.FirstCell().SetValue("Categories")
.CellBelow().SetValue("1")
.CellBelow().SetValue("2");
IXLTable table = ws.RangeUsed().CreateTable();
var listOfArr = new List <Int32>();
listOfArr.Add(3);
listOfArr.Add(4);
listOfArr.Add(5);
listOfArr.Add(6);
table.DataRange.InsertRowsBelow(listOfArr.Count - table.DataRange.RowCount());
table.DataRange.FirstCell().InsertData(listOfArr);
Assert.AreEqual("A1:A5", table.AutoFilter.Range.RangeAddress.ToStringRelative());
}
}
}
public void ReadAll()
{
if (Rows == null)
{
Rows = workSheet.RangeUsed().RowsUsed();
}
}
public void SetBorderRangeUsed(IXLWorksheet worksheet)
{
var rangeUsed = worksheet.RangeUsed();
rangeUsed.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rangeUsed.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
}
internal void CreateWorksheet(IXLWorkbook workbook, string sheetName, IElement tableNode)
{
IXLWorksheet worksheet = workbook.Worksheets.Add(sheetName);
int row = 1;
int col = 1;
foreach (IElement rowNode in tableNode.QuerySelectorAll("tr"))
{
List <IElement> cells = rowNode.QuerySelectorAll("th").ToList();
cells.AddRange(rowNode.QuerySelectorAll("td"));
foreach (IElement cellNode in cells)
{
RenderCell(worksheet, cellNode, row, ref col);
}
col = 1;
row++;
}
if (!hasMergedCells)
{
var table = worksheet.RangeUsed().CreateTable("mainTable" + worksheet.Name);
table.Theme = XLTableTheme.TableStyleLight1;
table.ShowRowStripes = Settings.ShowRowStripes;
table.ShowAutoFilter = Settings.ShowFilter;
}
if (Settings.AutofitColumns)
{
worksheet.ColumnsUsed().AdjustToContents();
}
}
static void ProcessWorkSheet(IXLWorksheet sheet, bool Belfan)
{
var data = sheet.RangeUsed();
int lastcol = data.LastColumn().ColumnNumber();
for (int numRow = data.LastRow().RowNumber(); numRow >= 2; numRow--)
{
var row = sheet.Row(numRow);
ProcessedCall call = new ProcessedCall();
var curCell = row.Cell(1);
call.Client = curCell.GetString();
if (curCell.HasHyperlink && curCell.GetHyperlink().IsExternal)
{
call.Link = curCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
curCell = row.Cell(lastcol - 4);
call.Manager = curCell.GetString();
curCell = curCell.CellRight();
call.Comment = curCell.GetString();
curCell = curCell.CellRight();
call.NoticeCRM = curCell.GetString();
curCell = curCell.CellRight();
call.ClientState = curCell.GetString();
curCell = curCell.CellRight();
if (curCell.GetString() != "" && curCell.DataType == XLDataType.DateTime)
{
call.StartDateAnalyze = curCell.GetDateTime();
}
else
{
if (!DateTime.TryParse(curCell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out call.StartDateAnalyze))
{
DateTime.TryParse(curCell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out call.StartDateAnalyze);
}
}
if ((!Belfan && !calls.Exists(c => (c.Client == call.Client && call.Link == "") || (c.Link == call.Link && call.Link != null))) || (Belfan && !calls.Exists(c => (c.Client == call.Client))))
{
calls.Add(call);
}
else
{
ProcessedCall exCall;
if (!Belfan)
{
exCall = calls.Where(c => (c.Client == call.Client && call.Link == "") || (c.Link == call.Link && call.Link != null)).First();
}
else
{
exCall = calls.Where(c => (c.Client == call.Client)).First();
}
if (exCall.StartDateAnalyze < call.StartDateAnalyze)
{
calls.Remove(exCall);
calls.Add(call);
}
}
}
}
private PlanCalendarModel GenerationListActivitysInCurrentMoth(IXLWorksheet worksheet)
{
if (new Regex(@"\d").Matches(worksheet.Name).Count != 4)
{
return(null);
}
var rows = worksheet.RangeUsed().RowsUsed(); // Skip header row
var dataBaseParserModel = new PlanCalendarModel();
dataBaseParserModel.Year = GetYear(worksheet.Name);
dataBaseParserModel.Month = GetMoth(worksheet.Name);
for (int i = 0; i < rows.Count(); i++)
{
if (CheckStartingFiled(rows.ElementAt(i).Cell(1).Value))
{
var countMergeColumn = rows.ElementAt(i).Cell(1).MergedRange().RowCount();
var date = GenerationDayDictionary(rows, i);
for (int currentIndexActivity = i + countMergeColumn + 1, k = 2, programIndex = -1; currentIndexActivity < rows.Count(); currentIndexActivity++)
{
var currentActivity = rows.ElementAt(currentIndexActivity).Cell(k);
if (!String.IsNullOrWhiteSpace(currentActivity.Value.ToString()))
{
foreach (var element in GenerationMarksInCurrentActivity(currentIndexActivity, date, rows))
{
EventCalendarModel parserActivityModel = new EventCalendarModel();
parserActivityModel.Day = element.IsExist ? element.day.ToString() : element.Text;
parserActivityModel.Name = currentActivity.Value.ToString();
parserActivityModel.NameAllStav = GenerationData(currentActivity.Value.ToString(), currentIndexActivity, date.Last().Key + 1, rows);
parserActivityModel.Leader = GenerationData(parserActivityModel.NameAllStav, currentIndexActivity, date.Last().Key + 2, rows);
parserActivityModel.Location = GenerationData(parserActivityModel.Leader, currentIndexActivity, date.Last().Key + 3, rows);
parserActivityModel.Time = GenerationData(parserActivityModel.Location, currentIndexActivity, date.Last().Key + 4, rows);
parserActivityModel.Result = GenerationData(parserActivityModel.Time, currentIndexActivity, date.Last().Key + 5, rows);
parserActivityModel.NameProgram = rows.ElementAt(programIndex).Cell(1).Value.ToString();
dataBaseParserModel.Events.Add(parserActivityModel);
}
}
else
{
if (String.IsNullOrWhiteSpace(rows.ElementAt(currentIndexActivity).Cell(1).Value.ToString()))
{
break;
}
programIndex = currentIndexActivity;
}
}
return(dataBaseParserModel);
}
//break;
}
return(dataBaseParserModel);
}
/// <summary>
/// Загрузка исходных данных в таблицу DataTable
/// </summary>
/// <param name="FileName">Имя файла, из которого загружаются данные</param>
/// <returns></returns>
private DataTable LoadIn(string FileName)
{
// Загрузка книги Excel
XLWorkbook FromTable = new XLWorkbook(FileName);
IXLWorksheet FromSheet = FromTable.Worksheets.ToList()[0];
// Подготовка таблицы
DataTable In = new DataTable();
In.TableName = "Исходные данные";
int k = 1;
foreach (object X in FromSheet.Columns())
{
if (FromSheet.RangeUsed().RowsUsed().ToArray()[0].Cell(k).Value.ToString() == "")
{
continue;
}
In.Columns.Add(FromSheet.RangeUsed().RowsUsed().ToArray()[0].Cell(k).Value.ToString(),
FromSheet.RangeUsed().RowsUsed().ToArray()[1].Cell(k++).Value.GetType());
}
// Подготовка счётчиков для статусной строки
string StatusStr = "Загрузка данных";
int i = 0;
int m = FromSheet.RangeUsed().RowsUsed().Skip(Head).Count();
SetStatus(StatusStr, i, m);
// Загрузка данных из книги Excel в DataTable
foreach (var row in FromSheet.RangeUsed().RowsUsed().Skip(Head))
{
object[] NewRow = new object[In.Columns.Count];
for (int j = 0; j < In.Columns.Count; j++)
{
NewRow[j] = row.Cell(j + 1).Value;
}
In.Rows.Add(NewRow);
SetStatus(StatusStr, i++, m);
Wait();
}
return(In);
}
static void Main(string[] args)
{
//bala
//DataTable dt = new DataTable();
//dt.Columns.Add("username");
//dt.Columns.Add("password");
//DataRow row1 = dt.NewRow();
//row1[0] = "admin";
//row1[1] = "pass123";
//dt.Rows.Add(row1);
//DataRow row2 = dt.NewRow();
//row2["username"] = "******";
//row2["password"] = "******";
//dt.Rows.Add(row2);
//Console.WriteLine(dt.Rows[0]["username"]);
XLWorkbook book = new XLWorkbook(@"D:\Report\OpenEMRData.xlsx");
IXLWorksheet sheet = book.Worksheet("Invalid Credential");
int rowCount = sheet.RangeUsed().RowCount();
int colCount = sheet.RangeUsed().ColumnCount();
DataTable dt = new DataTable();
for (int c = 1; c <= colCount; c++)
{
Console.WriteLine(sheet.Row(1).Cell(c).GetString());
dt.Columns.Add(sheet.Row(1).Cell(c).GetString());
}
Console.WriteLine("-----------------------------");
for (int r = 2; r <= rowCount; r++)
{
DataRow row = dt.NewRow();
for (int c = 1; c <= colCount; c++)
{
Console.WriteLine(sheet.Row(r).Cell(c).GetString());
row[c - 1] = sheet.Row(r).Cell(c).GetString();
}
dt.Rows.Add(row);
}
}
public ActionResult ExportDataToExcel(DataTable dtExcelData, string sheetName, string fileNameWithoutExtension, bool totalRequired = true, bool passwordRequired = true)
{
using (XLWorkbook wb = new XLWorkbook())
{
sheetName = sheetName.Replace(" ", "_");
fileNameWithoutExtension = fileNameWithoutExtension.Replace(" ", "_");
dtExcelData.TableName = sheetName;
IXLWorksheet workSheet = wb.Worksheets.Add(dtExcelData);
IXLRow row = workSheet.Row(dtExcelData.Rows.Count + 1);
IXLRange range = workSheet.RangeUsed();
IXLTable xlTable = range.AsTable();
if (totalRequired)
{
string colLetterForSNo = GetExcelColumnLetter(xlTable, "#");
if (!string.IsNullOrEmpty(colLetterForSNo))
{
row.Cell(colLetterForSNo).Value = string.Empty;
}
string colLetterForDaysPresent = GetExcelColumnLetter(xlTable, "Days Present");
if (!string.IsNullOrEmpty(colLetterForDaysPresent))
{
row.Cell(colLetterForDaysPresent).Value = string.Empty;
}
}
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
if (passwordRequired)
{
wb.Worksheet(sheetName).Protect("123456");
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename= " + fileNameWithoutExtension + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
return(null);
}
}
//public DataTable ToDataTable<T>(T item)
//{
// var properties = typeof(T).GetProperties().Where(p => p.GetCustomAttribute<IgnoreAttribute>() == null);
// var table = new DataTable();
// foreach (var prop in properties)
// {
// table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
// }
// var row = table.NewRow();
// foreach (var prop in properties)
// {
// if (prop.PropertyType == typeof(DateTime) && (DateTime)prop.GetValue(item) == DateTime.MinValue)
// {
// row[prop.Name] = new DateTime(1900, 01, 01);
// }
// else
// {
// row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
// }
// }
// table.Rows.Add(row);
// return table;
//}
// Credit: https://stackoverflow.com/a/53546001
public DataTable ToDataTable(string file, dynamic worksheet)
{
if (!File.Exists(file))
{
return(null);
}
var dt = new DataTable();
using (var workBook = new XLWorkbook(file))
{
IXLWorksheet workSheet = workBook.Worksheets.Contains(worksheet) ? workBook.Worksheet(worksheet) : workBook.Worksheet(1);
var firstRow = true;
foreach (var row in workSheet.RangeUsed().Rows())
{
if (firstRow)
{
foreach (var cell in row.Cells())
{
if (!string.IsNullOrEmpty(cell.Value.ToString()))
{
dt.Columns.Add(cell.Value.ToString());
}
else
{
break;
}
}
firstRow = false;
}
else
{
var i = 0;
var toInsert = dt.NewRow();
foreach (var cell in row.Cells(1, dt.Columns.Count))
{
try
{
toInsert[i] = cell.Value.ToString();
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
i++;
}
dt.Rows.Add(toInsert);
}
}
return(dt);
}
}
private static void StyleAll(IXLWorksheet ws)
{
var style = ws.RangeUsed().Style;
style
.Border.SetInsideBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
ws.Columns().AdjustToContents();
}
public IList <House> GetHouses(string path)
{
XLWorkbook wb = new XLWorkbook(path);
IXLWorksheet sheet = wb.Worksheets.First();
IXLRange RangeSheet = sheet.RangeUsed();
int rightBorder = RangeSheet.LastColumn().ColumnNumber(); //установим правую границу данных
int downBorder = RangeSheet.LastRow().RowNumber(); //установим нижнюю границу данных
List <IXLCell> Xlhouses = sheet.Cells().Where(c => c.GetValue <string>().Contains("Дом")).ToList(); //получим ячейки, в которых номера домов
List <House> houses = new List <House>();
foreach (var Xlhouse in Xlhouses)
{
House house = new House();
house.Name = Xlhouse.GetValue <string>();
house.Flats = new List <Flat>();
IXLCell cell = Xlhouse.CellBelow();
while (!(cell.GetValue <string>() == "" &&
cell.CellBelow().GetValue <string>() == "" &&
cell.CellRight().GetValue <string>() == "" &&
cell.CellRight().CellBelow().GetValue <string>() == ""
) &&
cell.WorksheetColumn().ColumnNumber() <= rightBorder
) //рассматриваем, есть ли значение в ячейке под названием дома, а также в ближайших от нее соседей
{
IXLCell cellBellowHouse = cell;
while (!(cell.GetValue <string>() == "" &&
cell.CellBelow().GetValue <string>() == ""
) &&
cell.WorksheetRow().RowNumber() <= downBorder
) //смотрим есть ли в текущей ячейке значение или той, которая находится снизу
{
if (cell.GetValue <string>().Contains("№"))
{
Flat flat = new Flat();
flat.Number = cell.GetValue <string>().Substring(1);
flat.Price = cell.CellBelow().GetValue <string>();
house.Flats.Add(flat);
}
cell = cell.CellBelow();
}
cell = cellBellowHouse.CellRight(); //после того, как закончили с колонкой, переходим в следующую колонку
//самую верхнюю под номером дома
}
houses.Add(house);
}
return(houses);
//throw new NotImplementedException();
}
/// <summary>
/// 根据key,
/// </summary>
/// <param name="sheet"></param>
/// <param name="strKey"></param>
/// <returns></returns>
public static string GetStringBaseOnkey(IXLWorksheet sheet, string strKey, int column)
{
int rows = sheet.RangeUsed().RowCount();
for (int i = 3; i <= rows; i++)
{
//找到列
if (sheet.Cell(i, 2).Value.ToString().Equals(strKey))
{
return(sheet.Cell(i, column).Value.ToString());
}
}
return("");
}
/// <summary>
/// 将数据传入到数据队列数组中
/// </summary>
/// <param name="sheet"></param>
/// <param name="queue1"></param>
/// <param name="queue2"></param>
public static void JoinDataQueue(IXLWorksheet sheet, Queue <DataQueueInfo>[] dataQueues, int targetColumn, int threadCount)
{
//读取sheet
int rows = sheet.RangeUsed().RowCount();
int dataCount = 0;
for (int i = 3; i <= rows; i++)
{
int no = i - 2;
string strKey = sheet.Cell(i, 2).Value.ToString();
string strChinese = sheet.Cell(i, 3).Value.ToString();
string strEnglish = sheet.Cell(i, 4).Value.ToString();
//获取真正的译文，确保不为空
int row = i;
int column = targetColumn;
string strTargetTranslation = sheet.Cell(i, targetColumn).Value.ToString();
//检查是否为空,是抛出异常
if (strTargetTranslation.Equals(""))
{
//清空所有入队信息
for (int j = 0; j < dataQueues.Length; j++)
{
dataQueues[j].Clear();
}
throw new Exception();
}
//数据封装
DataQueueInfo dataQueueInfo = new DataQueueInfo {
No = no,
StrKey = strKey,
Chinese = strChinese,
English = strEnglish,
TargtTranslation = strTargetTranslation,
Row = row,
Column = column
};
// 按序入队
dataQueues[dataCount].Enqueue(dataQueueInfo);
dataCount++;
if (dataCount == Environment.ProcessorCount)
{
dataCount = 0;
}
}
}
public static void ProcessExcelFile(DataSourceData dataSourceData, MemoryStream stream)
{
dataSourceData.DataStoreType = "Excel";
dataSourceData.DataStoreLocation = "Unavailable";
using (XLWorkbook workbook = new XLWorkbook(stream))
{
IXLWorksheet worksheet = workbook.Worksheets.First( );
dataSourceData.DataStoreTabName = worksheet.Name;
IXLRange range = worksheet.RangeUsed( );
if (range.RowsUsed( ).Any( ))
{
foreach (var row in range.RowsUsed( ))
{
if (row.CellsUsed( ).Any( ))
{
if (!dataSourceData.ColumnsNames.Any( ))
{
foreach (var cell in row.CellsUsed( ))
{
dataSourceData.ColumnsNames.Add(cell.Value.ToString( ).Trim( ));
}
continue;
}
FieldRow fieldRow = new FieldRow( );
foreach (var cell in row.CellsUsed( ))
{
fieldRow.Fields.Add(new Field
{
Value = cell.Value.ToString( ).Trim( ),
Approved = false,
Category = Enums.Categories.Unknown,
Column = dataSourceData.ColumnsNames[fieldRow.Fields.Count],
Row = dataSourceData.FieldRows.Count
});
}
dataSourceData.FieldRows.Add(fieldRow);
}
}
}
}
ProcessDataSourceData(dataSourceData);
}
public void Inserting_Column_Sets_Header()
{
using (var wb = new XLWorkbook())
{
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Categories")
.CellBelow().SetValue("A")
.CellBelow().SetValue("B")
.CellBelow().SetValue("C");
IXLTable table = ws.RangeUsed().CreateTable();
table.InsertColumnsAfter(1);
Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString());
}
}
