static private SQLiteData ToSQLiteData(Excel.Worksheet workSheet) { Excel.Range range = workSheet.UsedRange; Excel.Range rows = range.Rows; Excel.Range columns = range.Columns; SQLiteData dataSet = SQLiteData.Create(workSheet.Name, workSheet.UsedRange.Value2); Program.Release(workSheet); return(dataSet); }
static public void Insert(SQLiteConnection connection, SQLiteData dataSet, Action <float> percent = null) { string defaultCommand = String.Format(Constant._COMMAND_INSERT_INTO, dataSet.SheetName, dataSet.GetColumns()); StringBuilder command = null; int rowLength = dataSet.Datas.GetLength(0); int columnLength = dataSet.Columns.Length; Command(connection, Constant._BEGIN); for (int j = Constant._ROW_START_INDEX; j <= rowLength; j++) { command = new StringBuilder(defaultCommand); for (int i = 1; i <= columnLength; i++) { if (1 != i) { command.Append("\', "); } command.Append("\'"); dynamic data = dataSet.Datas[j, i]; if (null != data) { if (data.GetType() == typeof(string)) { string stringData = data as string; stringData = stringData.Replace("\'", "\'\'"); data = stringData; } command.Append(data); } } command.Append("\')"); Command(connection, command.ToString()); float lowLength = 0.0f != (float)(rowLength - Constant._ROW_START_INDEX) ? (float)(rowLength - Constant._ROW_START_INDEX) : 1; percent?.Invoke((j - Constant._ROW_START_INDEX) / lowLength); } Command(connection, Constant._COMMIT); }
public SQLiteData[] Read(string filePath) { List <SQLiteData> result = new List <SQLiteData>(); if (!System.IO.File.Exists(filePath)) { Debug.LogError("{0} 파일 찾기 실패", filePath); return(null); } Excel.Workbook workBook = null; try { workBook = _application.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); if (null != workBook) { // UsedRange.Value2로 가져온 배열의 인덱스가 1부터 시작 for (int i = 1; i <= workBook.Worksheets.Count; i++) { SQLiteData data = ToSQLiteData(workBook.Worksheets.get_Item(i)); if (null != data) { result.Add(data); } } } } catch (Exception e) { throw e; } finally { Program.Release(workBook); } return(result.ToArray()); }
static public void CreateTable(SQLiteConnection connection, SQLiteData dataSet) { string command = String.Format(Constant._COMMAND_CREATE_TABLE, dataSet.SheetName, dataSet.GetColumnAndTypes()); Command(connection, command); }