public static DataTable getDataExcelFromFileToDataTable(string filePath)
{
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get all Table
DataTableCollection tables = result.Tables;
//Get Table
DataTable dt = tables[0];
return(dt);
}
}
}
public void Remove()
{
DataTableCollection tbcol = _dataset[0].Tables;
tbcol.Clear();
/* _tables is array of DataTables defined in Setup */
tbcol.AddRange(_tables);
/* removing a recently added table */
int count = tbcol.Count;
tbcol.Remove(_tables[0]);
Assert.Equal(count - 1, tbcol.Count);
DataTable tbl = null;
/* removing a null reference. must generate an Exception */
try
{
tbcol.Remove(tbl);
Assert.False(true);
}
catch (Exception e)
{
Assert.Equal(typeof(ArgumentNullException), e.GetType());
}
/* removing a table that is not there in collection */
try
{
tbcol.Remove(new DataTable("newTable"));
Assert.False(true);
}
catch (Exception e)
{
Assert.Equal(typeof(ArgumentException), e.GetType());
}
}
private static DataTable ExcelToDataTable(string fileName, string SheetName)
{
// Open file and return as Stream
using (System.IO.FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
excelReader.IsFirstRowAsColumnNames = true;
//Return as dataset
DataSet result = excelReader.AsDataSet();
//Get all the tables
DataTableCollection table = result.Tables;
// store it in data table
DataTable resultTable = table[SheetName];
//excelReader.Dispose();
//excelReader.Close();
// return
return(resultTable);
}
}
}
public static DataTable ExcelToDataTable(String FileName, String SheetName)
{
using (System.IO.FileStream stream = File.Open(FileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
}
);
//Get all the tables
DataTableCollection table = result.Tables;
//storing in a DataTable
DataTable resultTable = table[SheetName];
return(resultTable);
}
}
}
/// <summary>
/// Reading all the datas from Excelsheet
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
private static DataTable ExcelToDataTable(string fileName)
{
//open file and returns as Stream
FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
//Createopenxmlreader via ExcelReaderFactory
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx
//Set the First Row as Column Name
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
//Return as DataSet
DataSet result = excelReader.AsDataSet();
//Get all the Tables
DataTableCollection table = result.Tables;
//Store it in DataTable
DataTable resultTable = table["Sheet1"];
//return
return(resultTable);
}
private async void DialogControllerImportarPlanilha_Accepting(object sender, DialogControllerAcceptingEventArgs e)
{
DataTableCollection dtcollectionImport = null;
((DialogController)sender).AcceptAction.Enabled["NoEnabled"] = false;
//Necessário para não fechar a janela após a conclusão do processamento
e.Cancel = true;
e.AcceptActionArgs.Action.Caption = "Procesando";
var parametros = (ParametrosImportSpoolJuntaExcel)e.AcceptActionArgs.SelectedObjects[0];
MemoryStream stream = new MemoryStream();
stream.Seek(0, SeekOrigin.Begin);
var arquivo = parametros.Padrao;
arquivo.SaveToStream(stream);
stream.Seek(0, SeekOrigin.Begin);
using (var excelReader = new ExcelDataReaderHelper.Excel.Reader(stream))
{
dtcollectionImport = excelReader.CreateDataTableCollection(false);
}
var import = new ImportSpoolEJunta(objectSpace, parametrosImportSpoolJuntaExcel);
var progress = new Progress <ImportProgressReport>(import.LogTrace);
await Observable.Start(() => import.ImportarSpools(dtcollectionImport["SGS"], progress));
await Observable.Start(() => import.ImportarJuntas(dtcollectionImport["SGJ"], progress));
objectSpace.CommitChanges();
e.AcceptActionArgs.Action.Caption = "Finalizado";
((DialogController)sender).AcceptAction.Enabled["NoEnabled"] = true;
}
public List <List <string> > readDataTable(string path, string sheetName)
{
using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection table = result.Tables;
DataTable resultTable = table[sheetName];
List <List <string> > dataList = new List <List <string> >();
foreach (DataRow row in resultTable.Rows)
{
List <string> rowList = new List <string>();
foreach (var item in row.ItemArray)
{
rowList.Add(item.ToString());
}
dataList.Add(rowList);
}
return(dataList);
}
}
}
private void ReadContext()
{
// Fix encoding error
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
using (var stream = File.Open(FilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = reader.AsDataSet();
DataTableCollection tables = result.Tables;
foreach (DataTable table in tables)
{
foreach (DataRow row in table.Rows)
{
// Skip header row
if (table.Rows.IndexOf(row) == 0)
{
continue;
}
int ID = Convert.ToInt32(row.ItemArray[0]);
switch (table.TableName)
{
case "PERSONS":
Persons.Add(new Person {
ID = ID, Description = Convert.ToString(row.ItemArray[1])
});
break;
case "LOGS":
Logs.Add(new Log {
ID = ID, PersonID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2])
});
break;
case "BLOCKS":
Blocks.Add(new Block {
ID = ID, LogID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2])
});
break;
case "CYCLES":
Cycles.Add(new Cycle {
ID = ID, BlockID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2])
});
break;
case "DAYS":
Days.Add(new Day {
ID = ID, CycleID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2])
});
break;
case "WORKOUTS":
Workouts.Add(new Workout {
ID = ID, DayID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2])
});
break;
case "EXERCISES":
Exercises.Add(new Exercise {
ID = ID, WorkoutID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2])
});
break;
case "SETS":
Sets.Add(new Set {
ID = ID,
ExerciseID = Convert.ToInt32(row.ItemArray[1]),
Weight = Convert.ToDouble(row.ItemArray[2]),
Intensity = Convert.ToInt32(row.ItemArray[3]),
Repetitions = Convert.ToInt32(row.ItemArray[4]),
});
break;
}
}
}
}
}
}
private void buttonSearchCalculate_Click(object sender, EventArgs e)
{
textBoxOutputs.Text = "";
textBoxOutputs.Text += "==== Scan Result ====" + Environment.NewLine;
//Toggle settings.
List <bool> columnToggles = ColumnToggles();
int totalCharacters = 0;
//Get all xlsx.
string[] files = Directory.GetFiles(".\\", "*.xlsx", SearchOption.AllDirectories);
for (int i = 0; i < files.Length; ++i)
{
int totalCharactersInFile = 0;
textBoxOutputs.Text += Environment.NewLine;
textBoxOutputs.Text += "File[" + files[i] + "]:" + Environment.NewLine;
try {
//Open the file.
using (FileStream file = File.Open(files[i], FileMode.Open, FileAccess.Read)) {
//Create excel reader.
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(file)) {
int[] characterCountForColumnFile = new int[26];
DataSet dataSet = reader.AsDataSet();
DataTableCollection dataTableCollection = dataSet.Tables;
for (int j = 0; j < dataTableCollection.Count; j++)
{
DataTable table = dataTableCollection[j];
//A ~ Z = 26 columns.
int[] characterCountForColumnTab = new int[26];
textBoxOutputs.Text += " Tab[" + j + "]: " + table.TableName + Environment.NewLine;
DataRow[] rows = table.Select();
for (int x = 0; x < rows.Length; ++x)
{
//string cell = rows[x].Field<string>(x);
//if (x < characterCountForColumnTab.Length && columnToggles[x] == true) {
// characterCountForColumnTab[x] += cell.Length;
//}
Object[] cells = rows[x].ItemArray;
for (int y = 0; y < cells.Length; ++y)
{
//Debug.Write(cells.Length);
if (cells[y] != null)
{
string content = cells[y].ToString();
//Add the character count of this cell.
if (y < characterCountForColumnTab.Length && columnToggles[y] == true)
{
characterCountForColumnTab[y] += content.Length;
}
}
}
}
//Print the column with characters.
int totalCharactersInTab = 0;
for (int x = 0; x < characterCountForColumnTab.Length; ++x)
{
if (characterCountForColumnTab[x] > 0)
{
textBoxOutputs.Text += " Column[" + COLUMN_LETTER[x] + "] characters: " + characterCountForColumnTab[x] + Environment.NewLine;
totalCharactersInTab += characterCountForColumnTab[x];
}
}
textBoxOutputs.Text += " Total characters in tab: " + totalCharactersInTab + Environment.NewLine;
totalCharactersInFile += totalCharactersInTab;
}
}
}
} catch (Exception exception) {
textBoxOutputs.Text += "Unable to open file[" + files[i] + "]. Ignore it(" + exception.Message + ")";
}
textBoxOutputs.Text += "Total characters in file: " + totalCharactersInFile + Environment.NewLine;
totalCharacters += totalCharactersInFile;
}
textBoxOutputs.Text += Environment.NewLine;
textBoxOutputs.Text += "Total characters: " + totalCharacters + Environment.NewLine;
textBoxOutputs.Text += Environment.NewLine;
textBoxOutputs.Text += "== " + files.Length + " files scanned ==" + Environment.NewLine;
}
public override object RecuperarItem(object item, int valor, object valorDefault)
{
DataTableCollection linhaItem = (DataTableCollection)item;
return(base.ItemRetorno(linhaItem[valor], valorDefault));
}
public static void Read(string filename)
{
if (!CuelistCtrl.saved)
{
bool?result = DialogCtrl.Show(DialogType.QUESTION, OptionType.YESNO, "Open Cuelist", "Do you want to save the current cuelist?");
if (result == true)
{
if (!Write(false))
{
return;
}
}
}
if (filename == null)
{
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
dlg.Filter = "CC Files (*.cc)|*.cc";
bool?result = dlg.ShowDialog();
if (result == true)
{
filename = dlg.FileName;
}
else
{
return;
}
}
using (DataSet dataSet = new DataSet("Cuelist"))
{
try { dataSet.ReadXml(filename); }
catch (Exception e)
{
LogCtrl.Error(e.Message);
DialogCtrl.Show(DialogType.ERROR, OptionType.OKCANCEL, "Error", "Can't open file!");
return;
}
DataTableCollection collection = dataSet.Tables;
ClearCuelist();
for (int i = 0; i < collection.Count; i++)
{
DataTable table = collection[i];
switch (table.TableName)
{
case "Settings":
SetSettingsFromTable(table);
break;
case "Script":
ScriptlistCtrl.LoadScripts(table);
break;
case "Beamer":
BeamerlistCtrl.LoadBeamers(table);
break;
case "MidiMap":
MidiController.LoadMidiMap(table);
break;
default:
CuelistCtrl.SetCuesFromTable(table);
break;
}
}
}
CuelistCtrl.saved = true;
SetPath(filename);
CuelistCtrl.DisableEditMode();
RecentFilesCtrl.Add(filename);
LogCtrl.Status("Open file: " + filename);
}
/// <summary>
/// <summary>
/// Search data source meta-data for a target.
/// </summary>
/// <param name="request">Search target.</param>
/// <param name="cancellationToken">Cancellation token.</param>
public virtual Task <string[]> Search(Target request, CancellationToken cancellationToken)
{
string target = request.target == "select metric" ? "" : request.target;
// Attempt to parse an expression that has SQL SELECT syntax
bool parseSelectExpression(string selectExpression, out string tableName, out string[] fieldNames, out string expression, out string sortField, out int topCount)
{
tableName = null;
fieldNames = null;
expression = null;
sortField = null;
topCount = 0;
if (string.IsNullOrWhiteSpace(selectExpression))
{
return(false);
}
// RegEx instance used to parse meta-data for target search queries using a reduced SQL SELECT statement syntax
if (s_selectExpression is null)
{
s_selectExpression = new Regex(@"(SELECT\s+(TOP\s+(?<MaxRows>\d+)\s+)?(\s*(?<FieldName>\w+)(\s*,\s*(?<FieldName>\w+))*)?\s*FROM\s+(?<TableName>\w+)\s+WHERE\s+(?<Expression>.+)\s+ORDER\s+BY\s+(?<SortField>\w+))|(SELECT\s+(TOP\s+(?<MaxRows>\d+)\s+)?(\s*(?<FieldName>\w+)(\s*,\s*(?<FieldName>\w+))*)?\s*FROM\s+(?<TableName>\w+)\s+WHERE\s+(?<Expression>.+))|(SELECT\s+(TOP\s+(?<MaxRows>\d+)\s+)?(\s*(?<FieldName>\w+)(\s*,\s*(?<FieldName>\w+))*)?\s*FROM\s+(?<TableName>\w+))", RegexOptions.Compiled | RegexOptions.IgnoreCase);
}
Match match = s_selectExpression.Match(selectExpression.ReplaceControlCharacters());
if (!match.Success)
{
return(false);
}
tableName = match.Result("${TableName}").Trim();
fieldNames = match.Groups["FieldName"].Captures.Cast <Capture>().Select(capture => capture.Value).ToArray();
expression = match.Result("${Expression}").Trim();
sortField = match.Result("${SortField}").Trim();
string maxRows = match.Result("${MaxRows}").Trim();
if (string.IsNullOrEmpty(maxRows) || !int.TryParse(maxRows, out topCount))
{
topCount = int.MaxValue;
}
return(true);
}
return(Task.Factory.StartNew(() =>
{
return TargetCache <string[]> .GetOrAdd($"search!{target}", () =>
{
if (!(request.target is null))
{
// Attempt to parse search target as a SQL SELECT statement that will operate as a filter for in memory metadata (not a database query)
if (parseSelectExpression(request.target.Trim(), out string tableName, out string[] fieldNames, out string expression, out string sortField, out int takeCount))
{
DataTableCollection tables = Metadata.Tables;
List <string> results = new List <string>();
if (tables.Contains(tableName))
{
DataTable table = tables[tableName];
List <string> validFieldNames = new List <string>();
for (int i = 0; i < fieldNames?.Length; i++)
{
string fieldName = fieldNames[i].Trim();
if (table.Columns.Contains(fieldName))
{
validFieldNames.Add(fieldName);
}
}
fieldNames = validFieldNames.ToArray();
if (fieldNames.Length == 0)
{
fieldNames = table.Columns.Cast <DataColumn>().Select(column => column.ColumnName).ToArray();
}
// If no filter expression or take count was specified, limit search target results - user can
// still request larger results sets by specifying desired TOP count.
if (takeCount == int.MaxValue && string.IsNullOrWhiteSpace(expression))
{
takeCount = MaximumSearchTargetsPerRequest;
}
void executeSelect(IEnumerable <DataRow> queryOperation)
{
results.AddRange(queryOperation.Take(takeCount).Select(row => string.Join(",", fieldNames.Select(fieldName => row[fieldName].ToString()))));
}
if (string.IsNullOrWhiteSpace(expression))
{
if (string.IsNullOrWhiteSpace(sortField))
{
executeSelect(table.Select());
}
else
{
if (Common.IsNumericType(table.Columns[sortField].DataType))
{
decimal parseAsNumeric(DataRow row)
{
decimal.TryParse(row[sortField].ToString(), out decimal result);
return result;
}
executeSelect(table.Select().OrderBy(parseAsNumeric));
}
else
{
executeSelect(table.Select().OrderBy(row => row[sortField].ToString()));
}
}
}
else
{
executeSelect(table.Select(expression, sortField));
}
foreach (DataRow row in table.Select(expression, sortField).Take(takeCount))
{
results.Add(string.Join(",", fieldNames.Select(fieldName => row[fieldName].ToString())));
}
}
return results.ToArray();
}
}
// Non "SELECT" style expressions default to searches on ActiveMeasurements meta-data table
return Metadata.Tables["ActiveMeasurements"].Select($"ID LIKE '{InstanceName}:%' AND PointTag LIKE '%{target}%'").Take(MaximumSearchTargetsPerRequest).Select(row => $"{row["PointTag"]}").ToArray();
});
}, cancellationToken));
private static void Execute(Options options)
{
/* 判断 ExcelPath 为目录路径还是文件路径 */
string[] excelPaths = null;
if (Directory.Exists(options.ExcelPath))
{
DirectoryInfo dirInfo = new DirectoryInfo(options.ExcelPath);
excelPaths = dirInfo.GetFiles().Where(fileInfo => ExcelUtil.IsSupported(fileInfo.Name)).Select(fileInfo => fileInfo.FullName).ToArray();
}
else if (File.Exists(options.ExcelPath))
{
if (ExcelUtil.IsSupported(options.ExcelPath))
{
excelPaths = new[] { options.ExcelPath };
}
}
if (excelPaths == null)
{
Console.WriteLine("No supported excel file found.");
return;
}
/* 对目标 Excel 文件进行转译 */
foreach (var excelPath in excelPaths)
{
string excelName = Path.GetFileName(excelPath);
Console.WriteLine("[{0}]", excelName);
DataTableCollection dataTables = ExcelReader.ReadExcelToDataTables(excelPath);
foreach (DataTable dataTable in dataTables)
{
/* 开始转换 DataTable */
string sheetName = dataTable.TableName;
int rowsCount = dataTable.Rows.Count;
int columnsCount = dataTable.Columns.Count;
Console.WriteLine(" sheet {0}(rows: {1}, column: {2})...", sheetName, rowsCount.ToString(), columnsCount.ToString());
ESheet sheetType = ExcelUtil.GetSheetType(sheetName);
string fileName = sheetType switch {
ESheet.EnumSheet => options.EnumNamePrefix + sheetName.Substring(4),
ESheet.ParamSheet => options.ParamNamePrefix + sheetName.Substring(5),
ESheet.ClassSheet => options.ClassNamePrefix + sheetName,
_ => null
};
/* 生成 JSON 数据 */
Console.WriteLine(" generate json...");
string jsonContent = DataWriter.DataTableToJSON(dataTable, options);
if (!string.IsNullOrEmpty(jsonContent))
{
if (!Directory.Exists(options.JSONPath))
{
Directory.CreateDirectory(options.JSONPath);
}
string jsonPath = string.Format("{0}/{1}.json", options.JSONPath, fileName);
File.WriteAllText(jsonPath, jsonContent, Encoding.UTF8);
}
/* 生成 C# 代码 */
Console.WriteLine(" generate csharp code...");
string codeContent = CodeWriter.DataTableToCSharp(dataTable, excelName, options);
if (!string.IsNullOrEmpty(codeContent))
{
if (!Directory.Exists(options.CSharpCodePath))
{
Directory.CreateDirectory(options.CSharpCodePath);
}
string codePath = string.Format("{0}/{1}.cs", options.CSharpCodePath, fileName);
File.WriteAllText(codePath, codeContent, Encoding.UTF8);
}
}
}
}
}
/// <summary>
/// Recupera la prima tabella di una DataTableCollection se esiste, null altrimenti
/// </summary>
/// <param name="tables">collection di DataTable</param>
/// <returns>La prima DataTable disponibile della collection</returns>
public static DataTable FirstOrDefault(this DataTableCollection tables)
{
return(tables == null || tables.Count == 0 ? null : tables[0]);
}
/// <summary>
/// 投资信息显示
/// </summary>
/// <returns></returns>
public DataTable Tendershowmain()
{
DataTableCollection dc = SqlHelper.GetTableProducts("pro_qyyTenderShowMain", null);
return(dc[0]);
}
/// <summary>
/// 债权转让信息显示
/// </summary>
/// <returns></returns>
public DataTable Zhuaiquanshowmain()
{
DataTableCollection dc = SqlHelper.GetTableProducts("proc_qyyZhaiquanShowmain", null);
return(dc[0]);
}
/// <summary>
/// 将table转换为json
/// </summary>
/// <param name="sheetDataTable"></param>
/// <returns></returns>
public string ConvertDataTableToJson(DataTableCollection excelCollection)
{
StringBuilder sbs = new StringBuilder();
sbs.Append("{\n");
// 遍历分页
for (int sheetIndex = 0; sheetIndex < excelCollection.Count; sheetIndex++)
{
DataTable sheetDataTable = excelCollection[sheetIndex];
if (sheetDataTable.Rows.Count > 0)
{
sbs.Append("\t\"" + sheetDataTable.TableName + "\":[\n\t\t");
string str = "";
int startRow;
int startCol;
// 查找表头，确定开始行数和开始列数
GetStartRowAndCol(sheetDataTable, out startRow, out startCol);
DataRow startRowData = sheetDataTable.Rows[startRow]; // 开始的第一行，用作key值
DataColumn startColData = sheetDataTable.Columns[startCol]; // 开始的第一列
// 遍历行
for (int row = startRow + 1; row < sheetDataTable.Rows.Count; row++)
{
DataRow dr = sheetDataTable.Rows[row];
if (dr[startColData.ColumnName].ToString() == string.Empty)
{
break;
}
// 遍历列
string result = "";
for (int col = startCol; col < sheetDataTable.Columns.Count; col++)
{
DataColumn dc = sheetDataTable.Columns[col];
if (dr[dc.ColumnName].ToString() == string.Empty || startRowData[dc.ColumnName].ToString() == string.Empty)
{
break;
}
result += string.Format(",\n\t\t\t\"{0}\":\"{1}\"", startRowData[dc.ColumnName], dr[dc.ColumnName]);
}
result = result.Substring(1);
result = ",\n\t\t{" + result + "\n\t\t}";
str += result;
}
if (str == "")
{
Debug.LogError(sheetDataTable.TableName + "没找到表头或表为空（表头名要和分页名相同）");
}
str = str.Substring(4);
sbs.Append(str);
if (sheetIndex == excelCollection.Count - 1)
{
sbs.Append("\n\t]");
}
else
{
sbs.Append("\n\t],\n\t");
}
}
}
sbs.Append("\n}");
return(sbs.ToString());
}
public static void go()
{
// Set Access connection and select strings.
// The path to BugTypes.MDB must be changed if you build the sample
// from the command line:
#if USINGPROJECTSYSTEM
string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\BugTypes.MDB";
#else
string strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Aaron\\Documents" +
"\\Databases\\PurdueGPA.accdb";
#endif
string strAccessSelect = "SELECT * FROM tblHW";
// Create the dataset and add the Categories table to it:
DataSet myDataSet = new DataSet();
OleDbConnection myAccessConn = null;
try
{
myAccessConn = new OleDbConnection(strAccessConn);
}
catch (Exception ex)
{
MessageBox.Show("Error: Failed to create a database connection. \n{0}", ex.Message);
return;
}
try
{
OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
myAccessConn.Open();
myDataAdapter.Fill(myDataSet, "tblHW");
}
catch (Exception ex)
{
MessageBox.Show("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return;
}
finally
{
myAccessConn.Close();
}
// A dataset can contain multiple tables, so let's get them all
// into an array:
DataTableCollection dta = myDataSet.Tables;
foreach (DataTable dt in dta)
{
MessageBox.Show("Found data table {0}", dt.TableName);
}
// The next two lines show two different ways you can get the
// count of tables in a dataset:
MessageBox.Show(myDataSet.Tables.Count + " tables in data set");
MessageBox.Show(dta.Count + " tables in data set");
// The next several lines show how to get information on a
// specific table by name from the dataset:
MessageBox.Show(myDataSet.Tables["tblHW"].Rows.Count + " rows in tblHW table");
// The column info is automatically fetched from the database, so
// we can read it here:
MessageBox.Show(myDataSet.Tables["tblHW"].Columns.Count + " columns in tblHW table");
DataColumnCollection drc = myDataSet.Tables["tblHW"].Columns;
int i = 0;
foreach (DataColumn dc in drc)
{
// Print the column subscript, then the column's name and its
// data type:
MessageBox.Show("Column name " + dc.ColumnName + " is " + i + " of type " + dc.DataType);
}
DataRowCollection dra = myDataSet.Tables["tblHW"].Rows;
foreach (DataRow dr in dra)
{
// Print the CategoryID as a subscript, then the CategoryName:
MessageBox.Show("CategoryName " + dr[0] + " is " + dr[1]);
}
}
/// <summary>
/// 只支持.xlsx格式，不支持03版本excel(.xls)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static List <ExcelTable> Read(string filePath)
{
Debug.Log("read file:" + filePath);
List <ExcelTable> excelTables = new List <ExcelTable>();
string fileName = Path.GetFileName(filePath);
FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
DataSet result = excelReader.AsDataSet();
//获得一个xlsx文件中所有的tables
DataTableCollection tables = result.Tables;
fs.Close();
foreach (DataTable table in tables)
{
ExcelTable et = new ExcelTable();
et.tableName = table.TableName;
////中文表不读，用于整体性的说明、注释作用
////要导出的表需要以t_开头
if (et.tableName.ContainChinese() || !et.tableName.StartsWith("t_"))
{
continue;
}
Debug.Log("begin read------>fileName:" + fileName + ",tableName:" + et.tableName);
et.rowCount = table.Rows.Count;
if (et.rowCount < 6)
{
Debug.LogWarnning("fileName:" + fileName + ", tableName:" + et.tableName + ", line count less than 6, we will not handle with it");
continue;
}
for (int i = 0; i < table.Columns.Count; i++)
{
//init ExcelField
string name = table.Rows[0][i].ToString();
if (name.ContainChinese())//中文字段用于注释，不处理
{
continue;
}
string typeDes = table.Rows[1][i].ToString();
string tag = table.Rows[3][i].ToString();
if (i == 0 && tag == "KVT")//check KVT format whether correct
{
et.isKVT = true;
if (table.Rows[0][0].ToString() != "Key" ||
table.Rows[0][1].ToString() != "Value" ||
table.Rows[0][2].ToString() != "KeyDes")
{
Debug.ThrowException("table:" + et.tableName + "is KVT,but field name is not correct!");
}
if (table.Rows[1][0].ToString() != "string")
{
Debug.ThrowException("table:" + et.tableName + "is KVT,but key's type is not string");
}
}
string defaultValue = table.Rows[2][i].ToString();
string fieldDes = table.Rows[4][i].ToString();
string fieldDesMore = table.Rows[5][i].ToString();
ExcelField field = new ExcelField(et.tableName, name, typeDes, defaultValue, tag, fieldDes, fieldDesMore, i == 0 ? true : false);
//read Field datas
for (int j = 6; j < et.rowCount; j++)
{
string tmpValue = table.Rows[j][i].ToString().Trim();
field.AddData(tmpValue == "" ? defaultValue : tmpValue);
}
et.AddExcelField(field);
}
#region old
//////for (int i = 0; i < et.rowCount; i++)
//////{
////// if (i == 0)//获得key
////// {
////// for (int j = 0; j < et.columnCount; j++)
////// {
////// var key = table.Rows[0][j].ToString();
////// if (j == 0)//主键
////// {
////// et.primaryKeyName = key;
////// et.primaryKeyType = table.Rows[1][j].ToString();
////// if (table.Rows[2][j].ToString().Contains("KVT"))
////// {
////// et.isKVPairTable = true;
////// et.valueKeyType = table.Rows[1][1].ToString();
////// if (table.Rows[0][1].ToString() != "Value" || table.Rows[0][2].ToString() != "KeyDes")
////// {
////// throw new Exception(et.tableName + "表有问题，KVT表，要求第二个字段名为 Value，第三个字段名为 KeyDes");
////// }
////// }
////// else
////// {
////// et.isKVPairTable = false;
////// }
////// }
////// Console.WriteLine("@@初始化map, key:" + key);
////// et.tableContent[key] = new List<string>();
////// if (et.keys.Contains(key))
////// {
////// throw new Exception("fatal error, table:" + et.tableName + ",有重复的字段:" + key);
////// }
////// else
////// {
////// if (!key.ContainChinese() && key != "KeyDes")//字段非中文才加入,且 KeyDes字段不用加入
////// {
////// et.keys.Add(key);
////// }
////// }
////// }
////// Console.WriteLine("@@初始化map key完毕");
////// }
////// else
////// {
////// for (int k = 0; k < et.columnCount; k++)
////// {
////// string key = table.Rows[0][k].ToString();
////// string property = table.Rows[i][k].ToString();
////// Console.WriteLine("table:" + et.tableName + " , key: " + key + " , property：" + property);
////// //主键的数据要保持唯一性
////// if (k == 0 && i > 4 && et.tableContent[key].Contains(property))
////// {
////// throw new Exception("fatal error, table:" + et.tableName + "的主键" + key + "，property:" + property);
////// }
////// else
////// {
////// if (property == "")
////// {
////// property = "囧";//对于数据表中，用户没有填写的空格，默认赋值为 囧 ，读取的时候需要特殊处理
////// }
////// et.tableContent[key].Add(property);
////// }
////// }
////// }
//////}
#endregion
excelTables.Add(et);
}
return(excelTables);
}
internal FeatureTableCollection(DataTableCollection dataTables)
{
_dataTables = dataTables;
}
public static DataTable ReadDataTable(DataTableCollection dataTableCollection, string tableName)
{
return((from DataTable dtbl in dataTableCollection where dtbl.TableName == tableName select dtbl).FirstOrDefault());
}
public PurchaserDetailsBuilder(DataTableCollection tables)
{
this.tables = tables;
}
/// <summary>
/// Populates the collections internal.
/// </summary>
/// <param name="tables">The tables.</param>
/// <param name="filter">The filter.</param>
internal void PopulateCollectionsInternal(DataTableCollection tables, string filter)
{
PopulateCollections(tables, filter);
}
public DataTableCollectionLexer(JsonSerializer serializer, DataTableCollection dataTables)
{
_serializer = serializer;
_dataTables = dataTables;
}
public static DataTable[] ToArray(this DataTableCollection tables)
{
return(tables.AsEnumerable().ToArray());
}
private void FilterSort_Load(object sender, EventArgs e)
{
// Connection string
string connString = @"server=.\sql2012; database=AdventureWorks; Integrated Security=true";
// Query1
string sql1 = @" select *
from Production.Product
where Name Like 'Mountain%'";
// Query2
string sql2 = @" select *
from Production.Location
where CostRate > 10.0 ";
// Combine queries
string sql = sql1 + sql2;
// Create connection
SqlConnection conn = new SqlConnection(connString);
try
{
// Create Data Adapter
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sql, conn);
// Create and Fill Data Set
DataSet ds = new DataSet();
da.Fill(ds, "Production.Product");
// Get the data tables collection
DataTableCollection dtc = ds.Tables;
// Display data from first data table
//
// Display output header
txtSort.AppendText("Results from Product table:\n");
txtSort.AppendText("***************************************************\n");
txtSort.AppendText("Name\t\t\t\tProductNumber\n");
txtSort.AppendText("_________________________________________\n");
// set display filter
string fl = "Color = 'Black'";
// Set sort
string srt = "ProductNumber asc";
// display filtered and sorted data
foreach (DataRow row in dtc["Production.Product"].Select(fl, srt))
{
txtSort.AppendText(row["Name"].ToString().PadRight(25));
txtSort.AppendText("\t\t");
txtSort.AppendText(row["ProductNumber"].ToString());
txtSort.AppendText(Environment.NewLine);
}
txtSort.AppendText("============================================\n");
// Display data from second data table
// Display output header
txtSort.AppendText("Results from Location table:\n");
txtSort.AppendText("***********************************************\n");
txtSort.AppendText("Name\t\t\tCostRate\n");
txtSort.AppendText("__________________________________________\n");
// Display data
foreach (DataRow row in dtc[1].Rows)
{
txtSort.AppendText(row["Name"].ToString().PadRight(25));
txtSort.AppendText("\t");
txtSort.AppendText(row["CostRate"].ToString());
txtSort.AppendText(Environment.NewLine);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.StackTrace);
}
finally
{
// Connection close
conn.Close();
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
try
{
pbar.Visible = true;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// Loop over DataTables in DataSet.
DataSet ds = new DataSet();
DataTable dtGST = new DataTable();
foreach (DataGridViewColumn col in dgvGST.Columns)
{
dtGST.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvGST.Rows)
{
DataRow dRow = dtGST.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
dtGST.Rows.Add(dRow);
}
DataTable dtCate = new DataTable();
foreach (DataGridViewColumn col in dgvCategory.Columns)
{
dtCate.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvCategory.Rows)
{
DataRow dRow = dtCate.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
dtCate.Rows.Add(dRow);
}
ds.Tables.Add(dtGST);
ds.Tables.Add(dtCate);
DataTableCollection collection = ds.Tables;
for (int i = collection.Count; i > 0; i--)
{
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
System.Data.DataTable table = collection[i - 1];
xlWorksheet.Name = table.TableName;
for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
}
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
pbar.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btneExcelReport_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
System.Data.DataSet ds = new DataSet();
ds = gstR1Controller.getGSTR1Report(Convert.ToDateTime(dtpFrom.Value.ToShortDateString()), Convert.ToDateTime(dtpTo.Value.ToShortDateString()), Utility.FinancilaYearId, "GSTR1");
DataTableCollection collection = ds.Tables;
ds.Tables[0].TableName = "b2b";
ds.Tables[0].Columns["GSTIN_UINofRecipient"].ColumnName = "GSTIN/UIN of Recipient";
ds.Tables[0].Columns["InvoiceNumber"].ColumnName = "Invoice Number";
ds.Tables[0].Columns["Invoicedate"].ColumnName = "Invoice date";
ds.Tables[0].Columns["InvoiceValue"].ColumnName = "Invoice Value";
ds.Tables[0].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply";
ds.Tables[0].Columns["ReverseCharge"].ColumnName = "Reverse Charge";
ds.Tables[0].Columns["InvoiceType"].ColumnName = "Invoice Type";
ds.Tables[0].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN";
ds.Tables[0].Columns["Rate"].ColumnName = "Rate";
ds.Tables[0].Columns["TaxableValue"].ColumnName = "Taxable Value";
ds.Tables[0].Columns["CessAmount"].ColumnName = "Cess Amount";
ds.Tables[1].TableName = "b2cl";
ds.Tables[1].Columns["InvoiceNumber"].ColumnName = "Invoice Number";
ds.Tables[1].Columns["Invoicedate"].ColumnName = "Invoice date";
ds.Tables[1].Columns["InvoiceValue"].ColumnName = "Invoice Value";
ds.Tables[1].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply";
ds.Tables[1].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN";
ds.Tables[1].Columns["Rate"].ColumnName = "Rate";
ds.Tables[1].Columns["TaxableValue"].ColumnName = "Taxable Value";
ds.Tables[1].Columns["CessAmount"].ColumnName = "Cess Amount";
ds.Tables[2].TableName = "b2cs";
ds.Tables[2].Columns["Type"].ColumnName = "Type";
ds.Tables[2].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply";
ds.Tables[2].Columns["Rate"].ColumnName = "Rate";
ds.Tables[2].Columns["TaxableValue"].ColumnName = "Taxable Value";
ds.Tables[2].Columns["CessAmount"].ColumnName = "Cess Amount";
ds.Tables[2].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN";
ds.Tables[3].TableName = "cndr";
ds.Tables[3].Columns["GSTIN_UINofRecipient"].ColumnName = "GSTIN/UIN of Recipient";
ds.Tables[3].Columns["Invoice_AdvanceReceiptNumber"].ColumnName = "Invoice/Advance Receipt Number";
ds.Tables[3].Columns["Invoice_AdvanceReceiptdate"].ColumnName = "Invoice/Advance Receipt date";
ds.Tables[3].Columns["Note_RefundVoucherNumber"].ColumnName = "Note/Refund Voucher Number";
ds.Tables[3].Columns["Note_RefundVoucherdate"].ColumnName = "Note/Refund Voucher date";
ds.Tables[3].Columns["DocumentType"].ColumnName = "Document Type";
ds.Tables[3].Columns["ReasonForIssuingdocument"].ColumnName = "Reason For Issuing document";
ds.Tables[3].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply";
ds.Tables[3].Columns["Note_RefundVoucherValue"].ColumnName = "Note/Refund Voucher Value";
ds.Tables[3].Columns["Rate"].ColumnName = "Rate";
ds.Tables[3].Columns["TaxableValue"].ColumnName = "Taxable Value";
ds.Tables[3].Columns["CessAmount"].ColumnName = "Cess Amount";
ds.Tables[3].Columns["PreGST"].ColumnName = "Pre GST";
ds.Tables[4].TableName = "cndur";
ds.Tables[4].Columns["URType"].ColumnName = "UR Type";
ds.Tables[4].Columns["Note_RefundVoucherNumber"].ColumnName = "Note/Refund Voucher Number";
ds.Tables[4].Columns["Note_RefundVoucherdate"].ColumnName = "Note/Refund Voucher date";
ds.Tables[4].Columns["DocumentType"].ColumnName = "Document Type";
ds.Tables[4].Columns["Invoice_AdvanceReceiptNumber"].ColumnName = "Invoice/Advance Receipt Number";
ds.Tables[4].Columns["Invoice_AdvanceReceiptdate"].ColumnName = "Invoice/Advance Receipt date";
ds.Tables[4].Columns["ReasonForIssuingdocument"].ColumnName = "Reason For Issuing document";
ds.Tables[4].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply";
ds.Tables[4].Columns["Note_RefundVoucherValue"].ColumnName = "Note/Refund Voucher Value";
ds.Tables[4].Columns["Rate"].ColumnName = "Rate";
ds.Tables[4].Columns["TaxableValue"].ColumnName = "Taxable Value";
ds.Tables[4].Columns["CessAmount"].ColumnName = "Cess Amount";
ds.Tables[4].Columns["PreGST"].ColumnName = "Pre GST";
ds.Tables[5].TableName = "exp";
ds.Tables[5].Columns["ExportType"].ColumnName = "Export Type";
ds.Tables[5].Columns["InvoiceNumber"].ColumnName = "Invoice Number";
ds.Tables[5].Columns["Invoicedate"].ColumnName = "Invoice date";
ds.Tables[5].Columns["InvoiceValue"].ColumnName = "Invoice Value";
ds.Tables[5].Columns["PortCode"].ColumnName = "Port Code";
ds.Tables[5].Columns["ShippingBillNumber"].ColumnName = "Shipping Bill Number";
ds.Tables[5].Columns["ShippingBillDate"].ColumnName = "Shipping Bill Date";
ds.Tables[5].Columns["Rate"].ColumnName = "Rate";
ds.Tables[5].Columns["TaxableValue"].ColumnName = "Taxable Value";
ds.Tables[6].TableName = "at";
ds.Tables[6].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply";
ds.Tables[6].Columns["Rate"].ColumnName = "Rate";
ds.Tables[6].Columns["GrossAdvanceReceived"].ColumnName = "Gross Advance Received";
ds.Tables[6].Columns["CessAmount"].ColumnName = "Cess Amount";
ds.Tables[7].TableName = "atadj";
ds.Tables[7].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply";
ds.Tables[7].Columns["Rate"].ColumnName = "Rate";
ds.Tables[7].Columns["GrossAdvanceAdjusted"].ColumnName = "Gross Advance Adjusted";
ds.Tables[7].Columns["CessAmount"].ColumnName = "Cess Amount";
ds.Tables[8].TableName = "exemp";
ds.Tables[8].Columns["Description"].ColumnName = "Description";
ds.Tables[8].Columns["NilRatedSupplies"].ColumnName = "Nil Rated Supplies";
ds.Tables[8].Columns["Exempted"].ColumnName = "Exempted (other than nil rated/non GST supply )";
ds.Tables[8].Columns["NonGSTsupplies"].ColumnName = "Non-GST supplies";
ds.Tables[9].TableName = "hsn";
ds.Tables[9].Columns["HSNCode"].ColumnName = "HSN";
ds.Tables[9].Columns["Description"].ColumnName = "Description";
ds.Tables[9].Columns["UQC"].ColumnName = "UQC";
ds.Tables[9].Columns["TotalQuantity"].ColumnName = "Total Quantity";
ds.Tables[9].Columns["TaxableValue"].ColumnName = "Taxable Value";
ds.Tables[9].Columns["IntegratedTaxAmount"].ColumnName = "Integrated Tax Amount";
ds.Tables[9].Columns["CentralTaxAmount"].ColumnName = "Central Tax Amount";
ds.Tables[9].Columns["State_UTTaxAmount"].ColumnName = "State/UT Tax Amount";
ds.Tables[9].Columns["CessAmount"].ColumnName = "Cess Amount";
for (int i = collection.Count; i > 0; i--)
{
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
System.Data.DataTable table = collection[i - 1];
xlWorksheet.Name = table.TableName;
for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
}
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void popoloWs(DataSet excO)
{
cbWorkSheet.Items.Clear();
dtbc = excO.Tables;
cbWorkSheet.Items.AddRange(dtbc.Cast <DataTable>().Select(t => t.TableName).ToArray <string>());
}
