c# Excel 合并列导入

有导出,就有导入,又时候还会有合并列导入.
下面展示一些 c#代码
前台使用了 layui

    <link href="~/Resources/layui/css/layui.css" rel="stylesheet" />
    <script src="~/Resources/layui/layui.js"></script>
    <script src="~/Resources/layui/layui.all.js"></script>
 
  layui.use('upload', function () {
            var upload = layui.upload;
            //指定允许上传的文件类型
            upload.render({
                elem: '#wanchengDaoru'//被点击的按钮的id
                , url: '/XXX/XXXXX'
                , accept: "file"  //文件,默认是图片,弹出的提示不同
                , exts: 'xlsx|xls' //普通文件
                //, before: function (obj) {
                //    //var orderID = $("#orderID").val();
                //   // this.data = { "jiance_id": checkJian_id, "shuxing": "" }//携带动态参数
                //}
                , done: function (data) {
                    var success = data.success;
                    var msg = data.msg;
                    var swalType = success ? 'success' : 'error';
                    var text = success ? '保存成功' : '保存失败';
                    swal({
                        title: "提示信息",
                        text: msg,
                        type: swalType,
                        showCancelButton: false,
                        confirmButtonColor: "#DD6B55",
                        confirmButtonText: "确定",
                        closeOnConfirm: true
                    }, function () {
                        //swal.close();
                        if (success) {
                        
                        } else {
                        }


                    });

                }
            });

        })

后台对应的写法

    //导入excel文件信息
        public ActionResult BingHaiImportExcel()
        {
            HttpFileCollectionBase file = Request.Files;//获取选中文件

            if (file.Count > 0)
            {
            //文件重新命名
                string _guid = DateTime.Now.ToString("yyyy年MM月dd日") + "-" + Guid.NewGuid().ToString().ToUpper().Replace("-", "");
                string fileName = file[0].FileName;
                Stream fs = file[0].InputStream;
                if (file != null && file[0].ContentLength > 0)
                {
                    using (StreamReader sr = new StreamReader(fs, Encoding.UTF8))
                    {
                    //文件保存本地路径
                        string save_diretion = Server.MapPath("../upload/");
                        string save_path = save_diretion + _guid + "-" + fileName;
                        try
                        {
                            file[0].SaveAs(save_path);
                            if (fileName.EndsWith(".xls") || fileName.EndsWith(".xlsx"))
                            {
                                string message = "";
                                message = ReadFromExcelFilewh(save_path);

                                if (message != "")
                                {
                                    return Json(new
                                    {
                                        code = 0,
                                        src = "",
                                        name = "",
                                        msg = "导入数据失败," + message,
                                        success = false
                                    }, JsonRequestBehavior.AllowGet);
                                }
                            }
                        }
                        catch (Exception e)
                        {
                            // System.IO.File.Delete(save_path);
                            return Json(new
                            {
                                code = 0,
                                src = "",
                                name = "",
                                msg = "导入数据失败," + e.Message,
                                success = false
                            }, JsonRequestBehavior.AllowGet);
                            throw;

                        }
                    }

                }
            }

            return Json(new
            {
                code = 1,
                src = "",
                name = "",
                msg = "导入数据成功",
                success = true
            }, JsonRequestBehavior.AllowGet);

        }
        public string ReadFromExcelFilewh(string save_path)
        {
            string message = "";
            List<Parameter> insertSqlList = new List<Parameter>();
            using (MySqlConnection conn = new MySqlConnection(MySqlStr))
            {
                conn.Open();
                //开启事务   
                MySqlTransaction SqlTransaction = conn.BeginTransaction();

                //将事务应用于Command   
                MySqlCommand cmd = new MySqlCommand();
                //  string PatrolTaskGuid = System.Guid.NewGuid().ToString("N");
                cmd.Connection = conn;
                cmd.Transaction = SqlTransaction;
                try
                {
                //这里写了两种后缀对应的 workBook  的类型,
                	   if (fileName.EndsWith(".xls"))
                    {
                        HSSFWorkbook workBook = new HSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
                        HSSFSheet sheet1 = (HSSFSheet)workBook.GetSheetAt(0);
					}else{
                    XSSFWorkbook workBook = new XSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
					}
					//后续统一的方法
                    #region  sheet1 添加表
                    
                    string plan_id = "";//主表id                  
                    int rowCount = sheet1.LastRowNum;
                    IRow row = sheet1.GetRow(0);

                    //去掉前三行
                    int ishebing = 0;
                    string qiaoliangming = "";
                    for (int a = 2; a <= sheet1.LastRowNum; a++)
                    {
                        // count = a;
                        row = sheet1.GetRow(a); //读取当前行数据
                        if (row != null)
                        {
                            #region 参数
                            string Systime = DateTime.Now.ToString();
                            string qiaoliangmingc = "";
                           //判断是否是合并项
                            if (isMergedRegion(sheet1, a, 1))
                            {
                                qiaoliangmingc = getMergedRegionValue(sheet1, a, 1);
                                if (qiaoliangmingc == qiaoliangming)
                                {
                                    ishebing++;
                                }
                                else
                                {
                                    qiaoliangming = qiaoliangmingc;
                                    ishebing = 1;
                                }
                            }
                            else
                            {
                                ishebing = 1;
                                qiaoliangmingc = getCellValue(row.GetCell(1)).ToString();
                            }
                            string Sourceresult = "";
                            if (isMergedRegion(sheet1, a, 2))
                            {
                                Sourceresult = getMergedRegionValue(sheet1, a, 2);
                            }
                            else
                            {
                                Sourceresult = getCellValue(row.GetCell(2)).ToString();
                            }

                            string Diseases = isNullCell(row.GetCell(3)).ToString();
                            string DangersNum = isNullCell(row.GetCell(4)).ToString();
                            string Measures = isNullCell(row.GetCell(5)).ToString();

							//获取时间
                            string Stardate = "";
                            ICell cellOrderDate = row.GetCell(6);
                            if (cellOrderDate != null)
                            {
                                if (cellOrderDate.CellType == CellType.Numeric)
                                {
                                    if (DateUtil.IsCellDateFormatted(cellOrderDate))
                                    {
                                        Stardate = DateTime.FromOADate(cellOrderDate.NumericCellValue).ToString("yyyy-MM-dd");
                                    }
                                    else
                                    {
                                        Stardate = DateTime.FromOADate(cellOrderDate.NumericCellValue).ToString("yyyy-MM-dd");
                                    }
                                }
                                else
                                {
                                    Stardate = row.GetCell(6).ToString();
                                }
                            }

                            #endregion

                        }
                        else
                        {
                            return "";
                        }
                    }

                    dbHelperMySQL.ExecuteNonQueryTran(insertSqlList);

                    #endregion
                    cmd.Transaction.Commit();
                }
                catch (Exception e)
                {
                    message = e.Message;
                    cmd.Transaction.Rollback();
                }
            }
            return message;

        }
        ```

```c# 
		// 一些用到的方法类
        private bool isMergedRegion(XSSFSheet sheet, int row, int column)
        {
            int sheetMergeCount = sheet.NumMergedRegions;//.getNumMergedRegions();
            for (int i = 0; i < sheetMergeCount; i++)
            {
                NPOI.SS.Util.CellRangeAddress range = sheet.GetMergedRegion(i);
                int firstColumn = range.FirstColumn;// .getFirstColumn();
                int lastColumn = range.LastColumn;//.getLastColumn();
                int firstRow = range.FirstRow;//.getFirstRow();
                int lastRow = range.LastRow;//getLastRow();
                if (row >= firstRow && row <= lastRow)
                {
                    if (column >= firstColumn && column <= lastColumn)
                    {
                        return true;
                    }
                }
            }
            return false;
        }


        /**
    * 获取合并单元格的值
    * @param sheet
    * @param row
    * @param column
    * @return
    */
        public string getMergedRegionValue(XSSFSheet sheet, int row, int column)
        {
            int sheetMergeCount = sheet.NumMergedRegions;

            for (int i = 0; i < sheetMergeCount; i++)
            {
                NPOI.SS.Util.CellRangeAddress ca = sheet.GetMergedRegion(i);
                int firstColumn = ca.FirstColumn;// .getFirstColumn();
                int lastColumn = ca.LastColumn;//.getLastColumn();
                int firstRow = ca.FirstRow;//.getFirstRow();
                int lastRow = ca.LastRow;//getLastRow();

                if (row >= firstRow && row <= lastRow)
                {
                    if (column >= firstColumn && column <= lastColumn)
                    {
                        IRow fRow = sheet.GetRow(firstRow);
                        ICell fCell = fRow.GetCell(firstColumn);
                        return getCellValue(fCell);
                    }
                }
            }

            return null;
        }

        /**
    * 获取单元格的值
    * @param cell
    * @return
    */

        public string getCellValue(ICell cell)
        {
            string value = "";
            if (cell != null)
            {   //Numeric 类型
                if (cell.CellType == CellType.Numeric)
                {
                    double value2 = cell.NumericCellValue;
                    value = value2 * 100 + "%";
                    return value;
                }
                else if (cell.CellType == CellType.String)
                {
                    value = cell.StringCellValue;
                }
            }
            else
            {
                value = "";
            }
            return value;
        }



  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
要在C#中使用Microsoft.Office.Interop.Excel库来合并Excel单元格,可以按照以下步骤进行操作: 首先,需要创建一个Excel应用程序对象和一个工作簿对象。可以使用以下代码创建它们: ``` Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook myWorkbook = xlApp.Workbooks.Open(savePath); ``` 然后,获取活动工作表并确定要操作的单元格范围。可以使用以下代码获取工作表和记录的行数: ``` Microsoft.Office.Interop.Excel.Worksheet mySheet = myWorkbook.ActiveSheet; int excelCount = mySheet.UsedRange.CurrentRegion.Rows.Count; ``` 接下来,可以调用合并单元格的函数来实现单元格合并。可以使用以下代码来合并A和B单元格: ``` MergeCell(ref mySheet, 1, excelCount, "A"); MergeCell(ref mySheet, 1, excelCount, "B"); ``` 最后,为了避免弹出保存和覆盖的提示框,可以将DisplayAlerts和AlertBeforeOverwriting属性设置为false: ``` xlApp.DisplayAlerts = false; xlApp.AlertBeforeOverwriting = false; ``` 请注意,上述代码中的MergeCell函数是自定义的函数,用于实际执行单元格合并的操作。您需要根据自己的需求来实现这个函数。 以上是在C#中使用Microsoft.Office.Interop.Excel合并Excel单元格的步骤。希望对您有所帮助! <span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [C#Excel单元格合并](https://blog.csdn.net/jiangzhaobao/article/details/7963130)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *2* [C#合并Excel单元格](https://blog.csdn.net/Andrewniu/article/details/89416680)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值