jiance-analysis/DPTools/components/MTLFF/ExcelTools.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using DevExpress.XtraSpreadsheet;
using ClosedXML.Excel;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraGrid;

namespace Uninpho.Tools.components.MTLFF
{
    public class ExcelTools
    {
        public static string filenameall;
        public static string chezhan;
        public static string daocha;
        public static string zhecha;
        public static string jianceshijian;
        public static string jianceren;
        public static string shejituhao;
        public static string xianluming;
        public static string zhenhaoqs;//起始枕号
        public static string zhenhaozz;//终止枕号
        public static GridControl gridControl01;
        public static GridControl gridControl02;
        public static GridControl gridControl03;
        public static GridView gridView01;
        public static GridView gridView02;
        public static GridView gridView03;
        public static SpreadsheetControl sheetControldemo;
        /// <summary>
        /// 文件转字节
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <returns></returns>
        public static string GetFileData(string fileUrl)
        {

            FileStream fs = new FileStream(fileUrl, FileMode.Open, FileAccess.Read);
            try
            {

                byte[] buffur = new byte[fs.Length];
                fs.Read(buffur, 0, (int)fs.Length);
                string numberarr = "";
                for (int i = 0; i < buffur.Length; i++)
                {
                    if (i == buffur.Length - 1)
                    {
                        numberarr += buffur[i].ToString();
                        break;
                    }
                    numberarr += buffur[i].ToString() + ",";
                }
                return numberarr;
            }
            catch (Exception ex)
            {
                //MessageBoxHelper.ShowPrompt(ex.Message);
                return null;
            }
            finally
            {
                if (fs != null)
                {

                    //关闭资源
                    fs.Close();
                }
            }
        }
        
        /// <summary>
        /// 字节转文件
        /// </summary>
        /// <param name="pReadByte"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static bool writeFile(string pReadBytearr, string fileName)
        {
            var pReadBytearrss = pReadBytearr.Split(',');
            byte[] pReadByte = new byte[pReadBytearrss.Length];
            for (int i = 0; i < pReadBytearrss.Length-1; i++)
            {
                pReadByte[i] = Convert.ToByte(pReadBytearrss[i].ToString());
            }
            FileStream pFileStream = null;
            try
            {
                pFileStream = new FileStream(fileName, FileMode.OpenOrCreate);
                pFileStream.Write(pReadByte, 0, pReadByte.Length);
            }
            catch
            {
                return false;
            }
            finally
            {
                if (pFileStream != null)
                    pFileStream.Close();
            }
            return true;
        }
        /// <summary>
        /// 添加抬头信息
        /// </summary>
        public static void initNewTableExcel()
        {
            var workbook = new XLWorkbook(filenameall);
            IXLWorksheet XLWorksheet = workbook.Worksheet(1);
            XLWorksheet.Row(2).Cell(3).SetValue(chezhan);
            XLWorksheet.Row(2).Cell(6).SetValue(daocha);
            XLWorksheet.Row(2).Cell(9).SetValue(zhecha );
            XLWorksheet.Row(2).Cell(12).SetValue(shejituhao );
            XLWorksheet.Row(2).Cell(14).SetValue(xianluming);
            XLWorksheet.Row(2).Cell(17).SetValue(jianceren);
            XLWorksheet.Row(2).Cell(20).SetValue(jianceshijian);
            workbook.Save();
        }
        /// <summary>
        /// 添加逐枕几何抬头信息
        /// </summary>
        public static void initNewTableExcelzzjh()
        {
            var workbook = new XLWorkbook(filenameall);
            IXLWorksheet XLWorksheet = workbook.Worksheet(1);
            IXLStyle style = XLWorksheet.Style;
            XLWorksheet.Row(2).Cell(2).SetValue(chezhan);
            XLWorksheet.Row(2).Cell(4).SetValue(daocha);
            XLWorksheet.Row(2).Cell(6).SetValue(zhecha);
            XLWorksheet.Row(2).Cell(8).SetValue(shejituhao);
            XLWorksheet.Row(3).Cell(2).SetValue(jianceren);
            XLWorksheet.Row(2).Cell(10).SetValue(jianceshijian);
            CreateExcel2zhenhaoqujian(XLWorksheet, style);
            workbook.Save();
        }

        /// <summary>
        /// 逐枕几何通过枕号区间创建excel
        /// </summary>
        public static void CreateExcel2zhenhaoqujian(IXLWorksheet XLWorksheet, IXLStyle style)
        {
            int startZH = int.Parse(zhenhaoqs);
            int endZH = int.Parse(zhenhaozz);
            int startrow = 6;
            int rowcount = (int)Math.Ceiling((decimal)(endZH - startZH+1) / 2);
            for (int i = 0; i < rowcount; i++)
            {
                XLWorksheet.Row(startrow).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                XLWorksheet.Row(startrow).Style.Font.Bold = true;
                changeBorder(XLWorksheet, startrow, 1);
                changeBorder(XLWorksheet, startrow, 2);
                changeBorder(XLWorksheet, startrow, 3);
                changeBorder(XLWorksheet, startrow, 4);
                changeBorder(XLWorksheet, startrow, 5);
                changeBorder(XLWorksheet, startrow, 6);
                changeBorder(XLWorksheet, startrow, 7);
                changeBorder(XLWorksheet, startrow, 8);
                changeBorder(XLWorksheet, startrow, 9);
                changeBorder(XLWorksheet, startrow, 10);
                changeBorder(XLWorksheet, startrow, 11);
                XLWorksheet.Row(startrow).Cell(1).SetValue(startZH.ToString());
                if (endZH >= startZH + rowcount)
                {
                    XLWorksheet.Row(startrow).Cell(6).SetValue((startZH + rowcount).ToString());
                }
                startrow++;
                startZH++;
            }
        }
        private static void changeBorder(IXLWorksheet XLWorksheet,int row,int culum)
        {
            XLWorksheet.Row(row).Cell(culum).Style.Border.TopBorder = XLBorderStyleValues.Medium;
            XLWorksheet.Row(row).Cell(culum).Style.Border.BottomBorder = XLBorderStyleValues.Medium;
            XLWorksheet.Row(row).Cell(culum).Style.Border.LeftBorder = XLBorderStyleValues.Medium;
            XLWorksheet.Row(row).Cell(culum).Style.Border.RightBorder = XLBorderStyleValues.Medium;
        }
    }
}