dsidealDoc/长春市教育局相关文档/MergeMailExcel/Main.cs

using System.Data;
using System.Diagnostics;
using Aspose.Cells;

namespace MergeMailExcel
{
    public partial class Main : Form
    {
        public Main()
        {
            InitializeComponent();
        }

        //最终生成的EXCEL文件名称
        readonly string _hzFileName = "汇总表.xlsx";

        private int fRow, fCol;

        bool CheckFileName(string fileName)
        {
            if (fileName == _hzFileName) return true;//检查名称时，对于汇总表这个名称，需要放行
            if (!fileName.StartsWith("【")) return false;//需要以"【"开头
            if (fileName.IndexOf("】") <= 0) return false;//需要包含"】"
            var ls = System.Text.RegularExpressions.Regex.Matches(fileName, "【").Count;
            var rs = System.Text.RegularExpressions.Regex.Matches(fileName, "】").Count;
            if (ls != 1 || rs != 1) return false;//【】只能出现一次
            return true;
        }

        public string Getzm(int idx)
        {
            var dividend = idx;
            var columnName = string.Empty;

            while (dividend > 0)
            {
                var modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo) + columnName;
                dividend = (dividend - modulo) / 26;
            }
            return columnName;
        }


        private void button1_Click(object sender, EventArgs e)
        {
            fRow = 0; fCol=0;
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            folderBrowserDialog1.Description = "请选择一个包含下载好Excel的文件夹。";
            folderBrowserDialog1.RootFolder = Environment.SpecialFolder.Desktop;
            folderBrowserDialog1.ShowNewFolderButton = false;
            if (folderBrowserDialog1.ShowDialog() != DialogResult.OK) return;
            var folderPath = folderBrowserDialog1.SelectedPath;
            var files = Directory.GetFiles(folderPath);

            //读取Excel
            var dt = new DataTable();

            var flag = false;
            var fileName = "";
            try
            {
                foreach (var fileFullPath in files)
                {
                    fileName = fileFullPath;
                    fileName = fileName.Substring(fileName.LastIndexOf("\\") + 1);
                    if (fileName == _hzFileName) continue;

                    if (!CheckFileName(fileName))
                    {
                        MessageBox.Show("发生不符合标准要求的文件名：" + fileName, "错误", MessageBoxButtons.OK,
                            MessageBoxIcon.Error);
                        return;
                    }

                    if (fileFullPath[(fileFullPath.LastIndexOf('.') + 1)..].ToLower() == "xlsx")
                    {
                        var wk = new Workbook(fileFullPath);
                        var ws = wk.Worksheets[0];
                        var rowsCnt = ws.Cells.MaxRow + 1;
                        var colCnt = ws.Cells.MaxColumn + 1;

                        if (rowsCnt != 3 && rowsCnt != 4)
                        {
                            MessageBox.Show("本程序只能处理三行或四行的模板 ！存在问题的文件：" + fileName + "。", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }

                        if (fRow == 0) fRow = rowsCnt;
                        if (fCol == 0) fCol = colCnt;

                        if (rowsCnt != fRow || colCnt != fCol)
                        {
                            MessageBox.Show("发现行与列不相符的文件：" + fileName + "。第一个文件行数："+fRow+",不符合文件行数:"+rowsCnt+",第一个文件列数："+fCol+",不符合文件列数："+colCnt, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }

                        var dt2 = ws.Cells.ExportDataTable(0, 0, rowsCnt, colCnt);

                        if (!flag)
                        {
                            //构建DataTable结构
                            for (var i = 0; i < dt2.Columns.Count; i++) dt.Columns.Add(i.ToString());
                            flag = true;
                        }
                        //加进去
                        dt.Rows.Add(dt2.Rows[fRow - 1].ItemArray);
                    }
                }

                if (files.Length > 0)
                {
                    var hzFile = folderPath + "\\" + _hzFileName;
                    File.Copy(files[0], hzFile, true);

                    //导出即可
                    var wk = new Workbook(hzFile);
                    var ws = wk.Worksheets[0];
                    var startRowCnt = ws.Cells.MaxRow + 1;

                    //创建样式
                    var style = wk.Styles[wk.Styles.Add()];
                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线  
                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线  
                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线  
                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线   
                    style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的水平对齐方式文字居中
                    style.VerticalAlignment = TextAlignmentType.Center;
                    style.Font.Name = "等线"; //字体
                    style.Font.Size = 11; //设置字体大小

                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            if (j == 0) ws.Cells[i + fRow - 1, j].PutValue(dt.Rows[i][j].ToString());
                            else
                            {
                                double v;
                                double.TryParse(dt.Rows[i][j].ToString(), out v);
                                ws.Cells[i + fRow - 1, j].PutValue(v);
                            }
                            ws.Cells[i + fRow - 1, j].SetStyle(style); //添加样式
                        }
                    }
                    //添加 SUM和列
                    var rowsCnt = ws.Cells.MaxRow + 1;
                    var colCnt = ws.Cells.MaxColumn;


                    //设置背景颜色
                    style.ForegroundColor = Color.LightSkyBlue;
                    style.Pattern = BackgroundType.Solid;
                    style.Font.IsBold = true;

                    ws.Cells[rowsCnt, 0].PutValue("总计");
                    ws.Cells[rowsCnt, 0].SetStyle(style); //添加样式

                    for (var i = 1; i <= colCnt; i++)
                    {
                        var zm = Getzm(i + 1);
                        ws.Cells[rowsCnt, i].Formula = "=sum(" + zm + startRowCnt+":" + zm + rowsCnt + ")";
                        ws.Cells[rowsCnt, i].SetStyle(style); //添加样式
                    }
                    wk.Save(hzFile); //保存
                    MessageBox.Show("恭喜，汇总表保存成功！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    Process.Start("rundll32.exe", "shell32,OpenAs_RunDLL "+ hzFile);
                }
                else
                {
                    MessageBox.Show("没有找到可以合并计算的文件 ！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch (IOException ioErr)
            {
                MessageBox.Show("是不是目录下的Excel文件您正在打开中？", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (CellsException ce)
            {
                MessageBox.Show(fileName + "格式不正确！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
    }
}