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()); } }