예제 #1
0
        public static Xlsx Create(string filePath)
        {
            FileInfo newFile = new FileInfo(filePath);

            using (ExcelPackage pck = new ExcelPackage(newFile))
            {
                try
                {
                    string name = Path.GetFileNameWithoutExtension(filePath);
                    name = name.Substring(0, 1).ToUpper() + name.Substring(1);
                    ExcelWorkbook workBook = pck.Workbook;
                    if (workBook != null)
                    {
                        Xlsx excel      = new Xlsx(filePath, name);
                        int  cnt        = workBook.Worksheets.Count;
                        var  enumerator = workBook.Worksheets.GetEnumerator();
                        bool isSuccess  = true;
                        while (enumerator.MoveNext())
                        {
                            ExcelWorksheet tDS   = enumerator.Current;
                            XlsxSheet      sheet = XlsxSheet.Create(tDS, excel);
                            if (sheet == null)
                            {
                                isSuccess = false;
                                MainEntry.Log(string.Format("Sheet:{0} failed", tDS.Name));
                                break;
                            }
                            excel.AddSeet(sheet);
                        }
                        if (isSuccess)
                        {
                            return(excel);
                        }
                        MainEntry.Log(string.Format("ExcelWorkbook Failed, Not Parse Sheet Right: {0}", filePath));
                    }
                    else
                    {
                        MainEntry.Log(string.Format("ExcelWorkbook null, Not Parse Right: {0}", filePath));
                    }
                    return(null);
                }
                catch (Exception e)
                {
                    MainEntry.Log(string.Format("ErrorInfo: {0}, StackTrace: {1}, filePath: {2}", e.Message, e.StackTrace, filePath));
                }
            }
            return(null);
        }
예제 #2
0
        private static bool ExportXlsxFile(string filePath)
        {
            Xlsx xlsx = Xlsx.Create(filePath);

            if (xlsx != null)
            {
                xlsx.InitializeRecord();
                bool res = ExportCS(xlsx);
                res &= ExportXml(xlsx);
                return(res);
            }
            else
            {
                MainEntry.Log(string.Format("ExportXlsxFile Wrong: {0}", filePath));
                return(false);
            }
        }
예제 #3
0
        public static XlsxSheet Create(ExcelWorksheet tDS, Xlsx parent)
        {
            XlsxSheet           sheet = new XlsxSheet(parent, tDS.Name);
            List <string>       variableNames;
            List <DataTypeEnum> variableTypes;
            List <DataSideEnum> variableSides;
            string managerName = null;
            string keys        = null;

            MainEntry.Log(string.Format("-------------------SheetName: {0} Begin", tDS.Name));
            List <XlsxRow> datas = CleanRowAndCols(tDS, sheet, out managerName, out keys, out variableNames, out variableTypes, out variableSides);

            if (datas != null)
            {
                sheet.Set(managerName, keys, variableNames, variableTypes, variableSides, datas);
                MainEntry.Log(string.Format("-------------------SheetName: {0} End", tDS.Name));
                return(sheet);
            }
            MainEntry.Log(string.Format("*******************SheetName: {0} failed", tDS.Name));
            return(null);
        }
예제 #4
0
        private static List <XlsxRow> CleanRowAndCols(ExcelWorksheet tDS, XlsxSheet sheet, out string managerName, out string keys, out List <string> variableNames, out List <DataTypeEnum> variableTypes, out List <DataSideEnum> variableSides)
        {
            variableNames = new List <string>();
            variableTypes = new List <DataTypeEnum>();
            variableSides = new List <DataSideEnum>();
            managerName   = null;
            keys          = null;
            ExcelRange range = tDS.Cells;

            object[,] values = (object[, ])range.Value;
            int           rows     = values.GetLength(0);
            int           cols     = values.GetLength(1);
            HashSet <int> skipCols = new HashSet <int>();
            HashSet <int> skipRows = new HashSet <int>();

            if (rows > 0 && cols > 0)
            {
                // List,Group<ColName>,Map<ColName>,Map<ColName1, ColName2>
                string value = (string)values[SHEET_DATA_MANAGER, 0];
                if (value == null)
                {
                    MainEntry.Log(string.Format("****************SheetName: {0} Wrong At [0, 0] End", tDS.Name));
                    return(null);
                }
                ParseManagerType(value, ref managerName, ref keys);
                if (managerName == null)
                {
                    MainEntry.Log(string.Format("****************SheetName: {0} Wrong ManagerName {1} End", tDS.Name, value));
                    return(null);
                }
            }
            for (int i = 0; i < cols; ++i)
            {
                if (values[VARIABLE_SIDE_ROW, i] == null)
                {
                    MainEntry.Log(string.Format("skip null col: [{0}, {1}], maybe last col", VARIABLE_SIDE_ROW, i));
                    skipCols.Add(i);
                    continue;
                }
                string       v    = values[VARIABLE_SIDE_ROW, i].ToString().Trim().ToUpper();
                DataSideEnum side = EnumUtils.StringToEnum <DataSideEnum>(v);
                if (side == DataSideEnum.SKIP)
                {
                    MainEntry.Log(string.Format("skip flag col: [{0}, {1}]", VARIABLE_SIDE_ROW, i));
                    skipCols.Add(i);
                    continue;
                }
                if (values[VARIABLE_NAME_ROW, i] == null)
                {
                    // log
                    MainEntry.Log(string.Format("Not Define Variable Name: [{0}, {1}]", VARIABLE_NAME_ROW, i));
                    return(null);
                }
                if (values[VARIABLE_TYPE_ROW, i] == null)
                {
                    // log
                    MainEntry.Log(string.Format("Not Define Variable Type: [{0}, {1}]", VARIABLE_TYPE_ROW, i));
                    return(null);
                }
                variableSides.Add(side);
                v = values[VARIABLE_NAME_ROW, i].ToString().Trim();
                variableNames.Add(v);
                v = values[VARIABLE_TYPE_ROW, i].ToString().Trim();
                try
                {
                    DataTypeEnum dataType = EnumUtils.StringToEnum <DataTypeEnum>(v.ToUpper());
                    variableTypes.Add(dataType);
                }
                catch (Exception e)
                {
                    MainEntry.Log(string.Format("Not Define Variable Side: [{0}, {1}] = {2}, Error: {3}", VARIABLE_TYPE_ROW, i, v, e.Message));
                    return(null);
                }
            }
            for (int i = VALUE_START_ROW; i < rows; ++i)
            {
                if (values[i, 0] != null)
                {
                    string v = values[i, 0].ToString().Trim();
                    if (v.StartsWith(SKIP_ROW))
                    {
                        MainEntry.Log(string.Format("skip flag row: [{0}, 0]", i));
                        skipRows.Add(i);
                    }
                }
                else
                {
                    MainEntry.Log(string.Format("skip null row: [{0}, 0], maybe last row", i));
                    skipRows.Add(i);
                }
            }
            List <XlsxRow> originalDatas = new List <XlsxRow>();
            int            rowIndex      = 0;

            for (int i = VALUE_START_ROW; i < rows; ++i)
            {
                if (skipRows.Contains(i))
                {
                    continue;
                }
                List <string> rowData = new List <string>();
                XlsxRow       row     = new XlsxRow(rowIndex++, rowData, sheet);
                originalDatas.Add(row);
                for (int j = 0; j < cols; ++j)
                {
                    if (skipCols.Contains(j))
                    {
                        continue;
                    }
                    rowData.Add(values[i, j] != null ? values[i, j].ToString() : null);
                }
            }
            return(originalDatas);
        }