LiCcCcCcccCcc
2021-09-02 11:00
采纳率: 100%
浏览 58

多个DataTable的列名与指定值匹配 c#

多个DataTable,处理的每张表的列名我把它加到list里

 List<string> lis = new List<string>
            {
                "地市名称",
                "地市公司运营机构简称",
                "地市"
            };

lis内的值只对应一个表的一个列,
就是表A的某列可能叫"地市名称",或者 "地市公司运营机构简称",
表B的某列可能叫 "地市公司运营机构简称",或者 "地市"
反正所有可能叫的名都存在lis里了,
问题1:怎么进行正确匹配 知道表A的某列是 "地市名称", 或者 "地市公司运营机构简称", 或者 "地市";
问题2:我要输出处理完成的表 以excel格式保存到指定路径下。

  • 收藏

2条回答 默认 最新

  • CSDN专家-showbo 2021-09-02 11:04
    已采纳

    DataTable有columns属性,提取出来和lis对比就知道了

    已采纳该答案
    打赏 评论
  • flybox0384 2021-09-02 11:35

    1: datatable to list
    #region 扩展类 DateTable转List
    ///
    ///
    ///
    public static List List(this DataTable dt)
    {
    var list = new List();
    //Type t = typeof(T);
    var plist = new List(typeof(T).GetProperties());

            foreach (DataRow item in dt.Rows)
            {
                T s = Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name.ToUpper() == dt.Columns[i].ColumnName.ToUpper());
                    if (info != null)
                    {
                        if (!Convert.IsDBNull(item[i]))
                        {
                            info.SetValue(s, item[i], null);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }
        #endregion
    

    然后使用循环方式使用excel组件功能导出到excel
    2:使用Nopi导出excel
    先定义导出的列头
    NPOIHelper.ListColumnsName = new SortedList(new NoSort());
    NPOIHelper.ListColumnsName.Add("ModelNo", "型号");
    NPOIHelper.ListColumnsName.Add("DrawingNumber", "图号");
    然后导出
    ExportTable 你导出的table
    NPOIHelper.ExportExcel(ExportTable, fileName);
    导出类
    public class NPOIHelper
    {
    ///


    /// 导出列名
    ///

    public static SortedList ListColumnsName;

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public static void ExportExcel(DataTable dtSource, string filePath)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明!"));
    
            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook);
            SaveExcelFile(excelWorkbook, filePath);
        }
    
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public static byte[] ExportExcel(DataTable dtSource)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明!"));
    
            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook);
            MemoryStream excelStream = new MemoryStream();
            excelWorkbook.Write(excelStream);
            byte[] byContent = excelStream.ToArray();
            excelStream.Close();
            //excelStream.Dispose();
            return byContent;
        }
    
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
        {
            FileStream file = null;
            try
            {
                file = new FileStream(filePath, FileMode.Create);
                excelWorkBook.Write(file);
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }
        }
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected static MemoryStream SaveExcelFile(HSSFWorkbook excelWorkBook)
        {
            try
            {
                MemoryStream excelStream = new MemoryStream();
                excelWorkBook.Write(excelStream);
                return excelStream;
            }
            finally
            {
    
            }
        }
    
        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        protected static HSSFWorkbook CreateExcelFile()
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            return hssfworkbook;
        }
    
        /// <summary>
        /// 创建excel表头
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="excelSheet"></param>
        protected static void CreateHeader(HSSFSheet excelSheet)
        {
            int cellIndex = 0;
            HSSFRow newRow = (HSSFRow)excelSheet.CreateRow(0);
            //循环导出列
            foreach (DictionaryEntry de in ListColumnsName)
            {
                HSSFCell newCell = (HSSFCell)newRow.CreateCell(cellIndex);
                newCell.SetCellValue(de.Value.ToString());
                cellIndex++;
            }
        }
    
        /// <summary>
        /// 插入数据行
        /// </summary>
        protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
        {
            int rowCount = 0;
            int sheetCount = 1;
            HSSFSheet newsheet = (HSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
    
            //循环数据源导出数据集
            CreateHeader(newsheet);
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount++;
                //超出65535条数据 创建新的工作簿
                if (rowCount == 65535)
                {
                    rowCount = 1;
                    sheetCount++;
                    newsheet = (HSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
                    CreateHeader(newsheet);
                }
    
                HSSFRow newRow = (HSSFRow)newsheet.CreateRow(rowCount);
                InsertCell(dr, newRow, excelWorkbook);
            }
        }
    
        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelWorkBook"></param>
        protected static void InsertCell(DataRow drSource, HSSFRow currentExcelRow, HSSFWorkbook excelWorkBook)
        {
            for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
            {
                //列名称
                string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
                HSSFCell newCell = null;
                //Type rowType = drSource[cellIndex].GetType();
                //string drValue = drSource[cellIndex].ToString().Trim();
                Type rowType = drSource[columnsName].GetType();
                string drValue = drSource[columnsName].ToString().Trim();
                
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);
    
                        //格式化显示
                        HSSFCellStyle cellStyle = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
                        HSSFDataFormat format = (HSSFDataFormat)excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;
    
                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType + ":类型数据无法处理!"));
                }
            }
        }
    }
    
    打赏 评论

相关推荐 更多相似问题