static object[,] LayDuLieu(string path)
{
var xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
var xlWorkBook = xlApp.Workbooks.Open(path);
var xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range xlRange = xlWorkSheet.UsedRange;
object[,] dt = (object[, ])xlRange.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
xlApp.Quit();
return(dt);
}
private static List <string> GetDropDownList(string DropDownValue, Microsoft.Office.Interop.Excel.Workbook xlWorkbook)
{
List <string> ValueList = new List <string>();
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets.get_Item(DropDownValue);
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
object[,] valueArray = (object[, ])xlRange.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
for (int row = 1; row <= xlWorksheet.UsedRange.Rows.Count; ++row)
{
if (valueArray[row, 1] != null && valueArray[row, 1].ToString() != "")
{
ValueList.Add(valueArray[row, 1].ToString());
}
}
return(ValueList);
}
private static List <Card> SetCardValues(Microsoft.Office.Interop.Excel.Workbook xlWorkbook)
{
List <Card> cardList = new List <Card>();
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets.get_Item(1);
// Get the range of cells which has data.
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
// Get an object array of all of the cells in the worksheet with their values.
object[,] valueArray = (object[, ])xlRange.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
for (int row = 2; row <= xlWorksheet.UsedRange.Rows.Count; ++row)
{
Card Page = new Card();
//if (valueArray[row, 1] != null && valueArray[row, 1].ToString() != "")
//{
// Page.Question = valueArray[row, 1].ToString();
//}
//if (valueArray[row, 2] != null && valueArray[row, 2].ToString() != "")
//{
// Page.Variable_Name = valueArray[row, 2].ToString();
//}
//if (valueArray[row, 3] != null && valueArray[row, 3].ToString() != "")
//{
// Page.Question_Type = GetFieldType(valueArray[row, 3].ToString());
//}
//if (valueArray[row, 4] != null && valueArray[row, 4].ToString() != "")
//{
// Page.Required = bool.Parse(valueArray[row, 4].ToString());
//}
//if (valueArray[row, 5] != null && valueArray[row, 5].ToString() != "")
//{
// Page.List_Values = GetDropDownList(valueArray[row, 5].ToString(), xlWorkbook);
//}
//if (valueArray[row, 6] != null && valueArray[row, 6].ToString() != "")
//{
// Page.If_Condition = valueArray[row, 6].ToString();
//}
//if (valueArray[row, 7] != null && valueArray[row, 7].ToString() != "")
//{
// Page.Then_Question = valueArray[row, 7].ToString();
//}
//if (valueArray[row, 8] != null && valueArray[row, 8].ToString() != "")
//{
// Page.Else_Question = valueArray[row, 8].ToString();
//}
//Page.PageName = "Page " + (row - 1).ToString();
//Page.PageId = row - 1;
//cardList.Add(Page);
if (xlWorksheet.Cells[row, 1] != null && xlWorksheet.Cells[row, 1].Text != "")
{
Page.Question = xlWorksheet.Cells[row, 1].Text;
}
//
if (xlWorksheet.Cells[row, 2] != null && xlWorksheet.Cells[row, 2].Text != "")
{
Page.Title = xlWorksheet.Cells[row, 2].Text;
}
if (xlWorksheet.Cells[row, 3] != null && xlWorksheet.Cells[row, 3].Text != "")
{
Page.Description = xlWorksheet.Cells[row, 3].Text;
}
//
if (xlWorksheet.Cells[row, 4] != null && xlWorksheet.Cells[row, 4].Text != "")
{
string Text = xlWorksheet.Cells[row, 4].Text;
Page.Variable_Name = Text.Replace(" ", "_");
}
if (xlWorksheet.Cells[row, 5] != null && xlWorksheet.Cells[row, 5].Text != "")
{
Page.Question_Type = GetFieldType(xlWorksheet.Cells[row, 5].Text);
}
if (xlWorksheet.Cells[row, 6] != null && xlWorksheet.Cells[row, 6].Text != "")
{
Page.Required = bool.Parse(xlWorksheet.Cells[row, 6].Text);
}
if (xlWorksheet.Cells[row, 7] != null && xlWorksheet.Cells[row, 7].Text != "")
{
Page.List_Values = GetDropDownList(xlWorksheet.Cells[row, 7].Text, xlWorkbook);
}
if (xlWorksheet.Cells[row, 8] != null && xlWorksheet.Cells[row, 8].Text != "")
{
Page.If_Condition = xlWorksheet.Cells[row, 8].Text;
}
if (xlWorksheet.Cells[row, 9] != null && xlWorksheet.Cells[row, 9].Text != "")
{
Page.Then_Question = xlWorksheet.Cells[row, 9].Text;
}
if (xlWorksheet.Cells[row, 10] != null && xlWorksheet.Cells[row, 10].Text != "")
{
Page.Else_Question = xlWorksheet.Cells[row, 10].Text;
}
Page.PageName = "Page " + (row - 1).ToString();
Page.PageId = row - 1;
cardList.Add(Page);
}
return(cardList);
}
object ExcelGetField(Microsoft.Office.Interop.Excel.Range Cell, string tracciato_field, out string errore)
{
errore = "";
object val = Cell.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
object val2 = Cell.Value2;
string[] ff = tracciato_field.Split(';');
string fieldname = ff[0];
string ftype = ff[2].ToLower().Trim(); //(intero/numero/stringa/codificato/data)
errore = "";
//int len = Convert.ToInt32(ff[3]);
if (val == null)
{
return(DBNull.Value);
}
if (val == DBNull.Value)
{
return(val);
}
if (val.ToString() == "")
{
return(DBNull.Value);
}
decimal numero;
try {
switch (ftype)
{
case "intero": {
string X = val.ToString().Trim().TrimStart('0');
if (X == "")
{
return(0);
}
return(Convert.ToInt32(X));
}
case "stringa":
return(val.ToString().TrimEnd(new char[] { ' ' }));
case "numero":
if (isNumeric(val, out numero))
{
return(Convert.ToDecimal(numero));
}
else
{
errore = " Errore interno nel tracciato per tipo numerico " + fieldname + " di tipo " + ftype + " e di valore " +
val.ToString().Trim().TrimStart('0');
return(null);
}
case "data": // DateTime.FromOADate and DateTime.ToOADate
return(DateTime.FromOADate(Convert.ToDouble(val2)));
case "codificato": {
string[] codici = ff[4].Split('|');
for (int i = 0; i < codici.Length; i++)
{
if (val.ToString().ToLower() == codici[i].ToLower())
{
return(val);
}
}
errore = " Errore interno nel tracciato per tipo codificato " + fieldname + " di tipo " + ftype + " e di valore " +
val.ToString().Trim().TrimStart('0');
return(null);
}
default: {
errore = " Errore interno nel tracciato per tipo " + ftype + " e valore " + val.ToString().Trim().TrimStart('0');
return(null);
}
}
}
catch {
errore = " Errore nella decodifica del campo " + fieldname + " di tipo " + ftype + " e di valore " + val.ToString().Trim().TrimStart('0');
return(null);
}
}
static List <Query> DomainQuery(LoadingManager LM_item)
{
string ColumnsInsert = null;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[LM_item.ExcelSheetName];
rangeSelection = worksheet.UsedRange;
string Phase = null;
string Desc = null;
string q = null;
ColumnsInsert = "(";
List <Query> Queries = new List <Query>();
List <Query> Queries_by_Phases = new List <Query>();
//object[,] values = (object[,])rangeSelection.Value;
object[,] values = (object[, ])rangeSelection.get_Value();
int maxC = values.GetLength(1);
for (int c = 3; c <= maxC; c++)
{
if (c > 3)
{
ColumnsInsert += ',';
}
ColumnsInsert += values[1, c];
}
ColumnsInsert += ")";
int maxR = values.GetLength(0);
for (int r = 2; r <= maxR; r++)
{
if (values[r, 1] != null)
{
Phase = values[r, 1].ToString();
}
for (int c = 3; c <= maxC; c++)
{
if (values[r, c] != null)
{
if (c > 3)
{
q += ',';
q += values[r, c].ToString().Trim();
}
else
{
q = "(" + values[r, c].ToString().Trim();
}
}
}
if (q != null)
{
q += ")";
Queries.Add(new Query(LM_item.ExcelSheetName, q, Desc, Phase));
}
q = null;
Desc = null;
Phase = null;
}
var phases = Queries.Select(x => x.Phase).GroupBy(x => x).ToArray();
foreach (var item in phases)
{
var c = Queries.Where(x => x.Phase == item.Key).ToArray();
string Query_P = null;
foreach (var q1 in c)
{
if (Query_P != null)
{
Query_P += (',' + q1.Query_Text);
}
else
{
Query_P = q1.Query_Text;
}
}
Queries_by_Phases.Add(new Query(LM_item.ExcelSheetName, LM_item.InnitCommand + " insert Into " + LM_item.TableName + ColumnsInsert + " Values " + Query_P, null, item.Key));
}
return(Queries_by_Phases);
}
public void CSV_import_Click(object sender, RoutedEventArgs e)
{
var ifd = new wf.OpenFileDialog();
ifd.ShowDialog();
importpath = ifd.FileName; if (importpath == "")
{
Textmessage("Es wurde keine zu importierende Datei angegeben.");
}
else
{
treeView.Items.Clear();
Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(importpath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range usedRange = workSheet.UsedRange;
object[,] valueArray = (object[, ])usedRange.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
List <string[]> allLines = new List <string[]>();
string selectedPath = "";
if (valueArray.GetLength(0) > 1)
{
selectedPath = valueArray[2, 1].ToString();
var rootDirectoryInfo = new DirectoryInfo(@selectedPath);
root = Node.GetTree(rootDirectoryInfo);
treeView.Items.Add(root);
root.FullPathDi = SelectedPath;
for (int i = 2; i < valueArray.GetLength(0); i++)
{
string[] values = new string[6];
values[0] = valueArray[i, 1].ToString();
values[1] = valueArray[i, 2].ToString();
values[2] = valueArray[i, 3].ToString();
values[3] = valueArray[i, 4].ToString();
values[4] = valueArray[i, 5].ToString();
values[5] = valueArray[i, 6].ToString();
allLines.Add(values);
}
}
else
{
Textmessage("Fehler beim importieren.");
return;
}
importcheck2(root, allLines);
//string targetFile = importpath + "Umwandler.csv";
//convertExcelToCSV(importpath, "Tabelle1", targetFile);
//importCSV(root, targetFile);
OK.IsEnabled = true;
}
}
