public static void exportExcelFile(TableExcelData data, string filePath)
        {
            var doc  = new XmlDocument();
            var root = doc.CreateElement("root");

            doc.AppendChild(root);

            foreach (var row in data.Rows)
            {
                var item = doc.CreateElement("item");
                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var hdr = data.Headers[i];
                    var val = row.StrList[i];
                    item.SetAttribute(hdr.FieldName, val);
                }
                root.AppendChild(item);
            }

            // 保存
            using (FileStream fs = File.Create(filePath))
            {
                var writer = new XmlTextWriter(fs, Encoding.UTF8);
                writer.Formatting = Formatting.Indented;
                doc.Save(writer);
                writer.Close();
            }
        }
Exemplo n.º 2
0
        public static void ExportExcelFile(TableExcelData data, string filePath, string fileName)
        {
            var csString = new StringBuilder();

            csString.AppendTemplate(fileName);

            File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(csString.ToString()));
        }
Exemplo n.º 3
0
        public static void exportExcelFile(TableExcelData data, string filePath)
        {
            List <Dictionary <string, object> > lst = ExportData(data);

            var    indent = AppData.Config.OutputLuaWithIndent ? Formatting.Indented : Formatting.None;
            string output = SerializeWithCustomIndenting(lst);

            File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(output));
        }
Exemplo n.º 4
0
        public static void ExportExcelFile(TableExcelData data, string filePath)
        {
            var csString = new StringBuilder();

            csString.AppendPrologue();
            csString.AppendTitle(filePath);
            csString.AppendProperty(data, string.Empty, 1);
            csString.AppendEnd();

            File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(csString.ToString()));
        }
Exemplo n.º 5
0
        private static void _writeToDataSheet(ISheet sheet, TableExcelData data)
        {
            var lst = data.Headers.Select(a => a.FieldName).ToList();

            _writeToSheet(sheet, lst);

            foreach (var row in data.Rows)
            {
                _writeToSheet(sheet, row.StrList);
            }
        }
Exemplo n.º 6
0
        public static void genExcel(TableExcelData data, string filePath)
        {
            Util.MakesureFolderExist(Path.GetDirectoryName(filePath));

            var ext = Path.GetExtension(filePath).ToLower();

            if (ext != ".xls" && ext != ".xlsx")
            {
                throw new Exception(string.Format("无法识别的文件扩展名 {0}", ext));
            }

            var workbook = ext == ".xls" ? (IWorkbook) new HSSFWorkbook() : (IWorkbook) new XSSFWorkbook();
            var sheet1   = workbook.CreateSheet(AppData.Config.SheetNameForField);
            var sheet2   = workbook.CreateSheet(AppData.Config.SheetNameForData);

            //创建新字体
            var font = workbook.CreateFont();

            font.IsBold = true;

            //创建新样式
            var style = workbook.CreateCellStyle();

            style.SetFont(font);
            style.FillPattern         = FillPattern.SolidForeground;
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            //默认样式
            for (short i = 0; i < workbook.NumCellStyles; i++)
            {
                workbook.GetCellStyleAt(i).VerticalAlignment = VerticalAlignment.Center;
            }

            _writeToDefSheet(sheet1, data.Headers);
            _writeToDataSheet(sheet2, data);

            var tmppath = Path.Combine(Path.GetDirectoryName(filePath),
                                       string.Format("{0}.tmp{1}", Path.GetFileNameWithoutExtension(filePath), ext));

            using (var fs = File.Open(tmppath, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
            {
                workbook.Write(fs);
            }
            var content = File.ReadAllBytes(tmppath);

            File.Delete(tmppath);
            File.WriteAllBytes(filePath, content);
        }
Exemplo n.º 7
0
        private static string BuildItemString(TableExcelData data)
        {
            var ids       = new List <string>();
            var keys      = new List <string>();
            var luaString = new StringBuilder();

            foreach (var row in data.Rows)
            {
                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var    hdr  = data.Headers[i];
                    var    val  = row.StrList[i];
                    var    name = hdr.FieldName.ToLower();
                    string s    = string.Empty;
                    if (name == "id")
                    {
                        int.TryParse(val, out int n);
                        s = n.ToString();
                        ids.Add(s);
                    }
                    else if (name == "key")
                    {
                        s = string.Format("\"{0}\"", val);
                        keys.Add(s);
                    }
                }
            }

            appendFormatLineEx(luaString, 0, "local indexItems = ");
            appendFormatLineEx(luaString, 0, "{{");
            for (int i = 0; i < ids.Count; i++)
            {
                appendFormatLineEx(luaString, 1, "[{0}] = items[{1}],", ids[i], i + 1);
            }
            appendFormatLineEx(luaString, 0, "}}");
            luaString.AppendLine();

            appendFormatLineEx(luaString, 0, "local idItems = ");
            appendFormatLineEx(luaString, 0, "{{");
            for (int i = 0; i < keys.Count; i++)
            {
                appendFormatLineEx(luaString, 1, "[{0}] = items[{1}],", keys[i], i + 1);
            }
            appendFormatLineEx(luaString, 0, "}}");
            luaString.AppendLine();
            return(luaString.ToString());
        }
Exemplo n.º 8
0
        private static void exportLua_Impl(TableExcelData data, string filePath)
        {
            var luaString = new StringBuilder();

            luaString.Append(BuildCommentString(data.Headers));

            appendFormatLineEx(luaString, 0, "local items =");
            appendFormatLineEx(luaString, 0, "{{");

            luaString.Append(BuildDataString(data, string.Empty, 2));

            appendFormatLineEx(luaString, 0, "}}");
            luaString.AppendLine();
            luaString.Append(BuildItemString(data));
            luaString.Append(BuildFuncString(Path.GetFileName(filePath)));
            File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(luaString.ToString()));
        }
        private static void exportLua_Impl1(TableExcelData data, string filePath)
        {
            var sb = new StringBuilder();

            appendFormatLineEx(sb, 0, "local data = ");
            appendFormatLineEx(sb, 0, "{{");
            foreach (var row in data.Rows)
            {
                appendFormatLineEx(sb, 1, "{{");
                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var    hdr = data.Headers[i];
                    var    val = row.StrList[i];
                    string s   = string.Empty;
                    switch (hdr.FieldType)
                    {
                    case "string":
                        s = string.Format("\"{0}\"", val);
                        break;

                    case "int":
                    {
                        int n = 0;
                        int.TryParse(val, out n);
                        s = n.ToString();
                    }
                    break;

                    case "double":
                    {
                        double n = 0;
                        double.TryParse(val, out n);
                        s = n.ToString();
                    }
                    break;
                    }
                    appendFormatLineEx(sb, 2, "[\"{0}\"] = {1},", hdr.FieldName, s);
                }
                appendFormatLineEx(sb, 1, "}},");
            }
            appendFormatLineEx(sb, 0, "}}");
            sb.AppendLine();
            appendFormatLineEx(sb, 0, "return data");

            File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(sb.ToString()));
        }
Exemplo n.º 10
0
        public static void exportExcelFile(TableExcelData data, string filePath)
        {
            List <Dictionary <string, object> > lst = data.Rows.Select(a =>
            {
                var r = new Dictionary <string, object>();
                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var hdr    = data.Headers[i];
                    var val    = a.StrList[i];
                    object obj = null;
                    switch (hdr.FieldType)
                    {
                    case "string":
                        obj = val;
                        break;

                    case "int":
                        {
                            int n = 0;
                            int.TryParse(val, out n);
                            obj = n;
                        }
                        break;

                    case "double":
                        {
                            double n = 0;
                            double.TryParse(val, out n);
                            obj = n;
                        }
                        break;
                    }
                    r[hdr.FieldName] = obj;
                }
                return(r);
            }).ToList();

            string output = JsonConvert.SerializeObject(lst, Formatting.Indented);

            File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(output));
        }
Exemplo n.º 11
0
        private static TableExcelData _readDataFromWorkbook(IWorkbook wb, string defSheetName, int row, int column, string dataSheetName)
        {
            var rows = new List <TableExcelRow>();

            var sheet1 = wb.GetSheet(defSheetName);

            if (sheet1 == null)
            {
                throw new Exception(string.Format("'{0}'工作簿不存在", defSheetName));
            }

            var sheet2 = wb.GetSheet(dataSheetName);

            if (sheet2 == null)
            {
                throw new Exception(string.Format("'{0}'工作簿不存在", dataSheetName));
            }

            //加载字段
            var headers = _readHeadersFromDefSheet(sheet1, row, column, defSheetName);
            var h1      = headers.Find(a => a.FieldName.ToLower() == "id");

            if (h1 == null)
            {
                throw new Exception(string.Format("'{0}'工作簿{1}行{2}列缺失id字段!", defSheetName, row, column + 1));
            }

            var h2 = headers.Find(a => a.FieldName.ToLower() == "key");

            if (h2 == null)
            {
                throw new Exception(string.Format("'{0}'工作簿{1}行{2}列缺失key字段!", defSheetName, row, column + 1));
            }

            //加载数据
            var headers2      = _readHeadersFromDataSheet(sheet2, dataSheetName);
            var headerIndexes = new int[headers.Count];

            _checkFieldsSame(headers, headers2, headerIndexes, dataSheetName);

            foreach (var ds in _readDataFromDataSheet(sheet2, headers2.Count))
            {
                var rowData = new List <string>();
                for (int i = 0; i < headers.Count; i++)
                {
                    var idx = headerIndexes[i];
                    rowData.Add(ds[idx]);
                }
                rows.Add(new TableExcelRow()
                {
                    StrList = rowData
                });
            }

            var data = new TableExcelData(headers, rows);

            for (int r = row; r <= sheet1.LastRowNum; r++)
            {
                var rd = sheet1.GetRow(r);
                if (rd == null)
                {
                    continue;
                }
                var str1 = _convertCellToString(rd.GetCell(column + 0));
                var str2 = _convertCellToString(rd.GetCell(column + 1));
                if (string.IsNullOrEmpty(str2))
                {
                    continue;
                }
                else
                {
                    var cell = rd.GetCell(column + 1);
                    if (cell.Hyperlink != null && cell.Hyperlink.Type == HyperlinkType.Document)
                    {
                        string s    = cell.Hyperlink.Address;
                        char[] addr = s.ToCharArray();
                        int    x    = 0;
                        int    y    = 0;
                        if (s.Length == 6)
                        {
                            x = addr[5] - '0';
                            y = addr[4] - 'A';
                        }
                        else
                        {
                            x = addr[6] - '0';
                            y = (addr[4] - 'A' + 1) * 26 + addr[5] - 'A';
                        }
                        data.ChildData.Add(str1, _readDataFromWorkbook(wb, defSheetName, x, y, str1));
                    }
                }
            }
            return(data);
        }
Exemplo n.º 12
0
        private static string BuildDataString(TableExcelData data, string key, int iDeep)
        {
            bool bWithIndent = AppData.Config.OutputLuaWithIndent;
            var  luaString = new StringBuilder();
            int  lbracket = 0, rbracket = 0;;

            foreach (var row in data.Rows)
            {
                if (key == string.Empty)
                {
                    if (bWithIndent == true)
                    {
                        for (int t = 1; t <= iDeep - 1; t++)
                        {
                            luaString.Append("    ");
                        }
                    }
                    else
                    {
                        luaString.Append("    ");
                    }
                }
                if (key != string.Empty && key != row.StrList[1])
                {
                    continue;
                }

                if (bWithIndent == true)
                {
                    luaString.Append("{\n");
                }
                else
                {
                    luaString.Append("{");
                }
                lbracket++;
                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var hdr  = data.Headers[i];
                    var name = hdr.FieldName.ToLower();
                    var val  = row.StrList[i];

                    if (key != string.Empty && (name == "id" || name == "key"))
                    {
                        continue;
                    }

                    if (string.IsNullOrEmpty(val) && !(hdr.FieldType.Contains("group") || hdr.FieldType == "string" || hdr.FieldType == "table"))
                    {
                        continue;
                    }

                    string s = string.Empty;
                    switch (hdr.FieldType)
                    {
                    case "string":
                        s = string.Format("\"{0}\"", val);
                        break;

                    case "string(nil)":
                        s = string.Format("\"{0}\"", val);
                        break;

                    case "int":
                    {
                        int.TryParse(val, out int n);
                        s = n.ToString();
                    }
                    break;

                    case "double":
                    {
                        double.TryParse(val, out double n);
                        s = n.ToString();
                    }
                    break;

                    case "bool":
                    {
                        bool.TryParse(val, out bool n);
                        s = n == true ? "true" : "false";
                    }
                    break;

                    case var a when a.Contains("group"):
                    {
                        s = string.Format("{{{0}}}", val);
                    }
                    break;

                    case "color":
                    {
                        s = string.Format("{0:X}", val);
                    }
                    break;

                    case "table":
                    {
                        s = BuildDataString(data.ChildData[hdr.FieldName], row.StrList[1], iDeep + 1);
                    }
                    break;
                    }
                    if (!string.IsNullOrEmpty(s))
                    {
                        if (bWithIndent == true)
                        {
                            for (int t = 1; t <= iDeep; t++)
                            {
                                luaString.Append("    ");
                            }
                        }

                        if (name == "id")
                        {
                            luaString.AppendFormat("{0} = {1},", "index", s);
                        }
                        else if (name == "key")
                        {
                            luaString.AppendFormat("{0} = {1},", "id", s);
                        }
                        else
                        {
                            luaString.AppendFormat("{0} = {1},", name, s);
                        }

                        if (bWithIndent == true)
                        {
                            luaString.Append("\n");
                        }
                        else
                        {
                            luaString.Append(" ");
                        }
                    }
                }

                if (key != string.Empty && bWithIndent == true)
                {
                    for (int t = 1; t <= iDeep - 1; t++)
                    {
                        luaString.Append("    ");
                    }
                }

                if (key == string.Empty)
                {
                    if (bWithIndent == true)
                    {
                        luaString.AppendLine("    },\n");
                    }
                    else
                    {
                        luaString.AppendLine("},");
                    }
                }
                else
                {
                    luaString.Append("}");
                }

                rbracket++;
            }
            if (lbracket != rbracket)
            {
                Log.Err("key: {2} Bracket mismatch {0} to {1}", lbracket, rbracket, key);
            }
            //else if (lbracket == 0)
            //    luaString.Append("{}");

            return(luaString.ToString());
        }
Exemplo n.º 13
0
 public static void exportExcelFile(TableExcelData data, string filePath)
 {
     exportLua_Impl(data, filePath);
 }
        private static void exportLua_Impl2(TableExcelData data, string filePath)
        {
            var sb = new StringBuilder();

            var ids  = new List <string>();
            var keys = new List <string>();

            //items
            appendFormatLineEx(sb, 0, "local items = ");
            appendFormatLineEx(sb, 0, "{{");
            foreach (var row in data.Rows)
            {
                sb.Append("    { ");
                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var    hdr = data.Headers[i];
                    var    val = row.StrList[i];
                    string s   = string.Empty;
                    switch (hdr.FieldType)
                    {
                    case "string":
                        s = string.Format("\"{0}\"", val);
                        break;

                    case "int":
                    {
                        int n = 0;
                        int.TryParse(val, out n);
                        s = n.ToString();
                    }
                    break;

                    case "double":
                    {
                        double n = 0;
                        double.TryParse(val, out n);
                        s = n.ToString();
                    }
                    break;
                    }
                    sb.AppendFormat("{0} = {1}, ", hdr.FieldName, s);

                    if (hdr.FieldName == "Id")
                    {
                        ids.Add(s);
                    }
                    else if (hdr.FieldName == "KeyName")
                    {
                        keys.Add(s);
                    }
                }
                sb.AppendLine("},");
            }
            appendFormatLineEx(sb, 0, "}}");
            sb.AppendLine();

            //idItems
            appendFormatLineEx(sb, 0, "local idItems = ");
            appendFormatLineEx(sb, 0, "{{");
            for (int i = 0; i < ids.Count; i++)
            {
                appendFormatLineEx(sb, 1, "[{0}] = items[{1}],", ids[i], i + 1);
            }
            appendFormatLineEx(sb, 0, "}}");
            sb.AppendLine();

            //keyItems
            appendFormatLineEx(sb, 0, "local keyItems = ");
            appendFormatLineEx(sb, 0, "{{");
            for (int i = 0; i < keys.Count; i++)
            {
                appendFormatLineEx(sb, 1, "[{0}] = items[{1}],", keys[i], i + 1);
            }
            appendFormatLineEx(sb, 0, "}}");
            sb.AppendLine();

            sb.Append(
                @"local data = { Items = items, IdItems = idItems, KeyItems = keyItems, }
function data:getById(id)
    return self.IdItems[id]
end
function data:getByKey(key)
    return self.KeyItems[key]
end
return data
");

            File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(sb.ToString()));
        }
Exemplo n.º 15
0
        //-- 构造成员 ---------------------------------------------------------------------------------------------------------------------------
        private static void AppendProperty(this StringBuilder sb, TableExcelData data, string key, int deep)
        {
            for (int i = 0; i < data.Headers.Count; i++)
            {
                var header = data.Headers[i];

                if (key != string.Empty && (header.FieldName.ToLower() == "id" || header.FieldName.ToLower() == "key"))
                {
                    continue;
                }

                string type = header.FieldType;
                string name = header.FieldName;
                string desc = header.FieldDesc;

                if (desc != string.Empty)
                {
                    if (name.ToLower() == "id")
                    {
                        desc = "唯一数字索引 (1~N)";
                    }
                    else if (name.ToLower() == "key")
                    {
                        desc = "唯一字符串索引";
                    }
                    sb.AppendComment(desc, deep);
                }

                if (type.Contains("group"))
                {
                    type = GetGroupType(type, data.Rows[0].StrList[i]);
                    sb.AppendListProperty(type, name, deep);
                }
                else if (type == "table")
                {
                    sb.AppendNestedClassTitle(name, deep);
                    sb.AppendProperty(data.ChildData[name], data.Rows[1].StrList[1], deep + 1);
                    sb.AppendNestedClassEnd(deep);
                }
                else
                {
                    if (name.ToLower() == "id")
                    {
                        name = "Index";
                    }
                    if (name.ToLower() == "key")
                    {
                        name = "Id";
                    }

                    if (type.ToLower() == "string(nil)")
                    {
                        type = "string";
                    }
                    if (type.ToLower() == "double")
                    {
                        type = "float";
                    }
                    if (type.ToLower() == "double(64)")
                    {
                        type = "double";
                    }
                    if (type.ToLower() == "color")
                    {
                        type = "string";
                    }
                    sb.AppendNormalProperty(type, name, deep);
                }
                if (i != data.Headers.Count - 1)
                {
                    sb.AppendLine();
                }
            }
        }
Exemplo n.º 16
0
        public static List <Dictionary <string, object> > ExportData(TableExcelData data)
        {
            List <Dictionary <string, object> > lst = new List <Dictionary <string, object> >();

            foreach (var row in data.Rows)
            {
                var r = new Dictionary <string, object>();

                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var hdr = data.Headers[i];
                    var val = row.StrList[i];

                    if (string.IsNullOrEmpty(val) && !(hdr.FieldType == "table"))
                    {
                        continue;
                    }
                    object obj = null;
                    switch (hdr.FieldType)
                    {
                    case "string":
                    case "string(nil)":
                    case "color":
                        obj = val;
                        break;

                    case "int":
                    {
                        int.TryParse(val, out int n);
                        obj = n;
                    }
                    break;

                    case "double":
                    {
                        double.TryParse(val, out double n);
                        obj = n;
                    }
                    break;

                    case "double(64)":
                    {
                        double.TryParse(val, out double n);
                        obj = n;
                    }
                    break;

                    case "bool":
                    {
                        bool.TryParse(val, out bool n);
                        obj = n;
                    }
                    break;

                    case var a when a.Contains("group"):
                    {
                        var  str     = val.Split(',');
                        bool succeed = false;
                        {
                            var numlist = new List <int>();
                            foreach (var s in str)
                            {
                                succeed = int.TryParse(s, out int n);
                                if (succeed == false)
                                {
                                    break;
                                }
                                numlist.Add(n);
                            }
                            obj = numlist;
                        }

                        if (succeed == false)
                        {
                            var numlist = new List <double>();
                            foreach (var s in str)
                            {
                                succeed = double.TryParse(s, out double n);
                                if (succeed == false)
                                {
                                    break;
                                }
                                numlist.Add(n);
                            }
                            obj = numlist;
                        }

                        if (succeed == false)
                        {
                            var strlist = new List <string>();
                            foreach (var s in str)
                            {
                                strlist.Add(s.Replace("\"", string.Empty));
                            }
                            obj = strlist;
                        }
                    }
                    break;

                    case "table":
                    {
                        obj = ExportDataSub(data.ChildData[hdr.FieldName], row.StrList[1]);
                        break;
                    }
                    }
                    if (hdr.FieldName.ToLower() == "id")
                    {
                        r["Index"] = obj;
                    }
                    else if (hdr.FieldName.ToLower() == "key")
                    {
                        r["Id"] = obj;
                    }
                    else
                    {
                        r[hdr.FieldName] = obj;
                    }
                }
                lst.Add(r);
            }
            return(lst);
        }
Exemplo n.º 17
0
        public static TableExcelData ImportFile(string filePath)
        {
            byte[] content = GzipHelper.processGZipDecode(File.ReadAllBytes(filePath));
            var    ms      = new MemoryStream(content);
            var    br      = new BinaryReader(ms);

            if (br.ReadInt32() != 1)
            {
                throw new Exception("无法识别的文件版本号");
            }

            var r = new TableExcelData();

            while (true)
            {
                var fieldName = br.ReadUtf8String();
                if (string.IsNullOrEmpty(fieldName))
                {
                    break;
                }

                string fieldType;
                byte   ftype = br.ReadByte();
                switch (ftype)
                {
                case 1:
                    fieldType = "int";
                    break;

                case 2:
                    fieldType = "double";
                    break;

                case 3:
                    fieldType = "string";
                    break;

                case 4:
                    fieldType = "group";
                    break;

                case 5:
                    fieldType = "bool";
                    break;

                case 6:
                    fieldType = "color";
                    break;

                case 7:
                    fieldType = "table";
                    break;

                case 8:
                    fieldType = "string(nil)";
                    break;

                case 9:
                    fieldType = "group(int)";
                    break;

                case 10:
                    fieldType = "group(double)";
                    break;

                case 11:
                    fieldType = "group(bool)";
                    break;

                case 12:
                    fieldType = "group(string)";
                    break;

                case 13:
                    fieldType = "double(64)";
                    break;

                default:
                    throw new Exception(string.Format("无法识别的字段类型 fieldName:{0} fieldType:{1}", fieldName, ftype));
                }
                r.Headers.Add(new TableExcelHeader()
                {
                    FieldName = fieldName, FieldType = fieldType
                });
            }

            while (br.BaseStream.Position < br.BaseStream.Length)
            {
                var lst = new List <string>();
                for (int i = 0; i < r.Headers.Count; i++)
                {
                    var hdr = r.Headers[i];
                    switch (hdr.FieldType)
                    {
                    case "string":
                        lst.Add(br.ReadUtf8String());
                        break;

                    case "int":
                        lst.Add(br.ReadInt32().ToString());
                        break;

                    case "double":
                        lst.Add(br.ReadDouble().ToString());
                        break;

                    case var a when a.Contains("group"):
                        lst.Add(br.ReadUtf8String());

                        break;

                    case "bool":
                        lst.Add(br.ReadBoolean().ToString());
                        break;

                    case "color":
                        lst.Add(br.ReadInt32().ToString());
                        break;

                    case "table":
                        lst.Add(br.ReadUtf8String());
                        break;

                    case "string(nil)":
                        lst.Add(br.ReadUtf8String());
                        break;
                    }
                }
                r.Rows.Add(new TableExcelRow()
                {
                    StrList = lst
                });
            }

            return(r);
        }
Exemplo n.º 18
0
        public static void genExcel(TableExcelData data, string filePath)
        {
            Util.MakesureFolderExist(Path.GetDirectoryName(filePath));

            var ext = Path.GetExtension(filePath).ToLower();

            if (ext != ".xls" && ext != ".xlsx")
            {
                throw new Exception(string.Format("无法识别的文件扩展名 {0}", ext));
            }

            var workbook = ext == ".xls" ? (IWorkbook) new HSSFWorkbook() : (IWorkbook) new XSSFWorkbook();
            var sheet    = workbook.CreateSheet(AppData.Config.SheetNameForData);

            //创建新字体
            var font = workbook.CreateFont();

            font.IsBold = true;

            //创建新样式
            var style = workbook.CreateCellStyle();

            style.SetFont(font);
            style.FillPattern         = FillPattern.SolidForeground;
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            //默认样式
            for (short i = 0; i < workbook.NumCellStyles; i++)
            {
                workbook.GetCellStyleAt(i).VerticalAlignment = VerticalAlignment.Center;
            }

            try
            {
                IRow row0 = sheet.GetRow(0);
                IRow row1 = sheet.GetRow(1);
                IRow row2 = sheet.GetRow(2);
                if (row0 == null)
                {
                    row0 = sheet.CreateRow(0);
                }
                if (row1 == null)
                {
                    row1 = sheet.CreateRow(1);
                }
                if (row2 == null)
                {
                    row2 = sheet.CreateRow(2);
                }

                row0.Height = 50;

                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var cell0 = row0.GetCell(i);
                    var cell1 = row1.GetCell(i);
                    var cell2 = row2.GetCell(i);
                    if (cell0 == null)
                    {
                        cell0 = row0.CreateCell(i);
                    }
                    if (cell1 == null)
                    {
                        cell1 = row1.CreateCell(i);
                    }
                    if (cell2 == null)
                    {
                        cell2 = row2.CreateCell(i);
                    }

                    cell0.SetCellValue(data.Headers[i].FieldDesc);
                    cell1.SetCellValue(data.Headers[i].FieldType);
                    cell2.SetCellValue(data.Headers[i].FieldName);
                }
            }
            catch (Exception)
            {
                throw new Exception(string.Format("{0} 表创建失败!", filePath));
            }
            var tmppath = Path.Combine(Path.GetDirectoryName(filePath),
                                       string.Format("{0}.tmp{1}", Path.GetFileNameWithoutExtension(filePath), ext));

            using (var fs = File.Open(tmppath, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
            {
                workbook.Write(fs);
            }
            var content = File.ReadAllBytes(tmppath);

            File.Delete(tmppath);
            File.WriteAllBytes(filePath, content);
        }
Exemplo n.º 19
0
        public static void exportExcelFile(TableExcelData data, string filePath)
        {
            const int version = 1;

            var ms = new MemoryStream();
            var bw = new BinaryWriter(ms, Encoding.UTF8);

            bw.Write(version);

            foreach (var hdr in data.Headers)
            {
                bw.WriteUtf8String(hdr.FieldName);
                byte ftype = 0;
                switch (hdr.FieldType)
                {
                case "int":
                    ftype = 1;
                    break;

                case "double":
                    ftype = 2;
                    break;

                case "string":
                    ftype = 3;
                    break;

                default:
                    throw new Exception(string.Format("无法识别的字段类型 {0} 名称 {1}", hdr.FieldType, hdr.FieldName));
                }
                bw.Write(ftype);
            }
            bw.Write((byte)0);

            foreach (var row in data.Rows)
            {
                for (int i = 0; i < data.Headers.Count; i++)
                {
                    var hdr = data.Headers[i];
                    var val = row.StrList[i];
                    switch (hdr.FieldType)
                    {
                    case "int":
                    {
                        int n = 0;
                        int.TryParse(val, out n);
                        bw.Write(n);
                    }
                    break;

                    case "double":
                    {
                        double n = 0;
                        double.TryParse(val, out n);
                        bw.Write(n);
                    }
                    break;

                    case "string":
                        bw.WriteUtf8String(val);
                        break;
                    }
                }
            }

            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }

            File.WriteAllBytes(filePath, GzipHelper.processGZipEncode(ms.GetBuffer(), (int)ms.Length));

            bw.Close();
            ms.Close();
        }
        public static TableExcelData importFile(string filePath)
        {
            var content = GzipHelper.processGZipDecode(File.ReadAllBytes(filePath));
            var ms      = new MemoryStream(content);
            var br      = new BinaryReader(ms);

            if (br.ReadInt32() != 1)
            {
                throw new Exception("无法识别的文件版本号");
            }

            var r = new TableExcelData();

            while (true)
            {
                var fieldName = br.ReadUtf8String();
                if (string.IsNullOrEmpty(fieldName))
                {
                    break;
                }

                var fieldType = string.Empty;
                var ftype     = br.ReadByte();
                switch (ftype)
                {
                case 1:
                    fieldType = "int";
                    break;

                case 2:
                    fieldType = "double";
                    break;

                case 3:
                    fieldType = "string";
                    break;

                default:
                    throw new Exception(string.Format("无法识别的字段类型 fieldName:{0} fieldType:{1}", fieldName, ftype));
                }
                r.Headers.Add(new TableExcelHeader()
                {
                    FieldName = fieldName, FieldType = fieldType
                });
            }

            while (br.BaseStream.Position < br.BaseStream.Length)
            {
                var lst = new List <string>();
                for (int i = 0; i < r.Headers.Count; i++)
                {
                    var hdr = r.Headers[i];
                    switch (hdr.FieldType)
                    {
                    case "string":
                        lst.Add(br.ReadUtf8String());
                        break;

                    case "int":
                        lst.Add(br.ReadInt32().ToString());
                        break;

                    case "double":
                        lst.Add(br.ReadDouble().ToString());
                        break;
                    }
                }
                r.Rows.Add(new TableExcelRow()
                {
                    StrList = lst
                });
            }

            return(r);
        }