Как извлечь данные из Excel не открывая файл? - C#
Формулировка задачи:
ВОПРОС
Как извлечь данные из Excel не открывая файл?
СЦЕНАРИЙ
1. В поле "ID" вводим значение "ID" (ID - записи восьмизначное число)
2. В поле "СОДЕРЖАНИЕ ЗАПИСИ" появляется запись из базы данных, которое соответсnвует ID.
Прилагаю болванку для кода
ПРИЛОЖЕНИЕ.
1. Проект
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace ExcelDb { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } private void groupBox2_Enter(object sender, EventArgs e) { } private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { } } }
Решение задачи: «Как извлечь данные из Excel не открывая файл?»
textual
Листинг программы
class WorkBookWorker { readonly List<SheetWorker> _sheetInfos = new List<SheetWorker>(); readonly OleDbConnection _connection; public WorkBookWorker(string filePath) { _connection = new OleDbConnection( "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + filePath + ";Extended Properties=Excel 8.0;" ); _connection.Open(); var adapter = new OleDbDataAdapter( "select * from [Clients$]", _connection); var table = new DataTable(); adapter.Fill(table); string fileName = new FileInfo(filePath).Name; var sheets = table.Rows.Cast<DataRow>().Select(row => row.Field<string>(1)).Distinct(); foreach (var sheet in sheets) { if (sheet == null) continue; SheetWorker sheetInfo = new SheetWorker(_connection, sheet, fileName); if(sheetInfo.isCorrect) _sheetInfos.Add(sheetInfo); } } public List<ProtocolRow> FindRows(Func<ProtocolRow, bool> rule) { List<ProtocolRow> rows = new List<ProtocolRow>(); foreach (var sheet in _sheetInfos) { sheet.FindRows(rule, ref rows); } return rows; } public List<ProtocolRow> FindRows(Func<string, bool> rule) { List<ProtocolRow> rows = new List<ProtocolRow>(); foreach (var sheet in _sheetInfos) { sheet.FindRows(rule, ref rows); } return rows; } public void ReplaceRows(Func<string, bool> rule, Func<string, string> convertor) { foreach (var sheet in _sheetInfos) { sheet.ReplaceRows(rule, convertor, _connection); } _connection.Close(); } } class SheetWorker { readonly DataTable _table; public readonly bool isCorrect = true; public readonly string fileName; public readonly string sheetName; public SheetWorker(OleDbConnection connection, string sheetName, string fileName) { this.fileName = fileName; this.sheetName = sheetName; var adapter = new OleDbDataAdapter( "select * from [" + sheetName + "$]", connection); _table = new DataTable(); try { adapter.Fill(_table); } catch (Exception) { isCorrect = false; } } public void FindRows(Func<ProtocolRow, bool> rule, ref List<ProtocolRow> rows) { rows.AddRange( _table.Rows.Cast<DataRow>().Select(row => new ProtocolRow(row, this)).Where(rule)); } public void FindRows(Func<string, bool> rule, ref List<ProtocolRow> rows) { rows.AddRange( _table.Rows.Cast<DataRow>(). Where(row => (row.ItemArray.Length > 5 && rule(row[5].ToString())) || (row.ItemArray.Length > 6 && rule(row[6].ToString()))). Select(row => new ProtocolRow(row, this)).ToArray()); } public void ReplaceRows(Func<string, bool> selectionRule, Func<string, string> stringConvertor, OleDbConnection connection) { Func<DataRow, bool> rule = row => row.ItemArray.Length > 5 && selectionRule(row[5].ToString()); var correctRows = _table.Rows.Cast<DataRow>().Where(rule).Distinct().ToArray(); if (correctRows.Any()) { StringBuilder builder = new StringBuilder("Update ["); builder.Append(sheetName); builder.Append("$] Set "); builder.Append(_table.Columns[5].ColumnName); builder.Append(" = '"); foreach (var correctRow in correctRows) { StringBuilder commandStr = new StringBuilder(builder.ToString()); string oldRowName = correctRow[5].ToString(); string rowName = stringConvertor(oldRowName); commandStr.Append(rowName); commandStr.Append("' Where "); commandStr.Append(_table.Columns[5].ColumnName); commandStr.Append(" = '"); commandStr.Append(oldRowName); commandStr.Append("'"); OleDbCommand command = new OleDbCommand(commandStr.ToString(), connection); command.ExecuteNonQuery(); } } rule = row => row.ItemArray.Length > 6 && selectionRule(row[6].ToString()); correctRows = _table.Rows.Cast<DataRow>().Where(rule).Distinct().ToArray(); if (correctRows.Any()) { StringBuilder builder = new StringBuilder("Update ["); builder.Append(sheetName); builder.Append("$] Set "); builder.Append(_table.Columns[6].ColumnName); builder.Append(" = '"); foreach (var correctRow in correctRows) { StringBuilder commandStr = new StringBuilder(builder.ToString()); string oldRowName = correctRow[6].ToString(); string rowName = stringConvertor(oldRowName); commandStr.Append(rowName); commandStr.Append("' Where "); commandStr.Append(_table.Columns[6].ColumnName); commandStr.Append(" = '"); commandStr.Append(oldRowName); commandStr.Append("'"); OleDbCommand command = new OleDbCommand(commandStr.ToString(), connection); command.ExecuteNonQuery(); } } } } class ProtocolRow { public string Output { get; set; } public string ByteNumber { get; set; } public string Type { get; set; } public string CustomName { get; set; } public string ServerName { get; set; } public string ClientName { get; set; } SheetWorker _sheetInfo; public string SheetName { get { return _sheetInfo.sheetName; } } public string FileName { get { return _sheetInfo.fileName; } } public ProtocolRow(DataRow row, SheetWorker sheetInfo) { _sheetInfo = sheetInfo; Output = row[0].ToString(); ByteNumber = row[1].ToString(); Type = row[3].ToString(); CustomName = row[4].ToString(); ServerName = row[5].ToString(); if(row.ItemArray.Length < 7) return; ClientName = row[6].ToString(); } }
ИИ поможет Вам:
- решить любую задачу по программированию
- объяснить код
- расставить комментарии в коде
- и т.д