C# WPS导入导出【V9】版本

一：生成WPS dll

    下载WPS专业版本就能得到"WPSOfficePIA.exe",个人版本需要手动生成,有点麻烦。

    就在你安装路径下面：XX:\WPS office个人版本\WPS Office\9.1.0.4953\office6

    项目引用：Excel.dll、Office.dll,

二：导入导出功能

  

        /// <summary>
        /// 判断是否安装WPS
        /// </summary>
        /// <returns></returns>
        private bool IsWPS()
        {
            Type type = Type.GetTypeFromProgID("Ket.Application");//V9版本类型
            if (type == null)//没有安装V9版本
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 导出WPS ET模板
        /// </summary>
        /// <param name="strColumnName">模板列名称</param>
        /// <param name="strFileName">文件名称</param>
        /// <param name="strPath">保存路径</param>
        public void WPSCrateET(string strColumnName, string strFileName, string strPath)
        {
            try
            {

                List<string> strConlumnNames = strColumnName.Split(',').ToList<string>();
                List<string> listName = new List<string>();
                if (strConlumnNames.Count<=256)
                {
                    #region WPS ET模板导出
                    //创建App实例
                    Excel.Application etApp = new Excel.Application();
                    etApp.Visible = false;
                    etApp.UserControl = false;
                    Excel.Workbooks etBooks = etApp.Workbooks;
                    //创建新工作薄
                    Excel.Workbook etBook = etBooks.Add(true);
                    //创建工作表
                    Excel.Worksheet etSheet = etApp.Sheets.Add(Type.Missing, Type.Missing, 1) as Excel.Worksheet;
                    etSheet.Name =strFileName;
                    listName.Add(etSheet.Name);
                    for (int j = 0; j < strConlumnNames.Count; j++)
                    {
                        string strColumnTitleName = GetExcelColumnLabel(j, 1);
                        etSheet.Range[strColumnTitleName].Value = strConlumnNames[j];
                        
                    }
                    //删除多余的表格
                    for (int i = etBook.Worksheets.Count; i > 0; i--)
                    {

                        Excel.Worksheet sheet = etBook.Worksheets[i] as Excel.Worksheet;
                        string strNamedelete = sheet.Name;
                        if (!listName.Contains(strNamedelete))
                        {
                            sheet.Delete();
                        }
                    }

                    etBook.SaveAs(strPath);//保存
                    etApp.Workbooks.Close();//关闭所有Workbook
                    etApp.Quit();//关闭Application
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(etBook);//释放Workbook
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);//释放Application
                    #endregion
                }
                else
                {
                    throw new Exception("最多256列");
                }
               

            }
            catch (Exception ex)
            {
                Aukeys.MsgTool.ShowMessage(ex.Message);
            }
            
        }
        /// <summary>
        /// 导出WPS ET数据
        /// </summary>
        /// <param name="strFileName">文件名称</param>
        /// <param name="strPath">保存路径</param>
        /// <param name="dt">数据源</param>
        public void WPSExportET(string strFileName,string strPath, System.Data.DataTable dtSource)
        {

            try
            {
                if (!IsWPS())
                {
                    Aukeys.MsgTool.ShowMessage("请安装WPS");
                    return;
                }
                List<string> listName = new List<string>();
                if (dtSource != null && dtSource.Rows.Count > 0)
                {
                    //创建App实例
                    Excel.Application etApp = new Excel.Application();
                    etApp.Visible = false;
                    etApp.UserControl = false;
                    Excel.Workbooks etBooks = etApp.Workbooks;
                    //创建新工作薄
                    Excel.Workbook etBook = etBooks.Add(true);
                    //计算当前工作薄中表数量
                    int WorksheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtSource.Rows.Count) / 60000));
                    for (int i = 0; i < WorksheetCount; i++)
                    {
                        //创建工作表
                        Excel.Worksheet etSheet = etApp.Sheets.Add(Type.Missing, Type.Missing, 1) as Excel.Worksheet;
                        etSheet.Name = "第"+(i+1)+"个" + strFileName;
                        listName.Add(etSheet.Name);
                        //计算要取列名称
                        int indexStartRow = 60000 * ((i+1) - 1);
                        int indexEndRow = 60000 * (i+1);
                        if (dtSource.Rows.Count<indexEndRow)
                        {
                            indexEndRow = dtSource.Rows.Count;
                        }
                        for (int j = indexStartRow; j <= indexEndRow; j++)
                        {
                            for (int y = 0; y < dtSource.Columns.Count; y++)
                            {
                                string strColumnTitleName = GetExcelColumnLabel(y, j + 1);
                                if (j == 0)
                                {

                                    etSheet.Range[strColumnTitleName].Value = dtSource.Columns[y].ColumnName;
                                }
                                else
                                {
                                    etSheet.Range[strColumnTitleName].Value = dtSource.Rows[j-1][y];
                                }

                            }
                        }
                       
                    }
                    //删除多余的表格
                    for (int i = etBook.Worksheets.Count; i > 0; i--)
                    {

                        Excel.Worksheet sheet = etBook.Worksheets[i] as Excel.Worksheet;
                        string strNamedelete = sheet.Name;
                        if (!listName.Contains(strNamedelete))
                        {
                            sheet.Delete();
                        }
                    }
                    etBook.SaveAs(strPath);//保存
                    etApp.Workbooks.Close();//关闭所有Workbook
                    etApp.Quit();//关闭Application
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(etBook);//释放Workbook
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);//释放Application
                }
                else
                {
                    MessageBox.Show("没有数据,暂时不支持导出");
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 导入WPS ET
        /// </summary>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public System.Data.DataTable WPSImportET(string strFileName,System.Data.DataTable dt)
        {
           
            try
            {

                //创建App实例
                Excel.Application etApp = new Excel.Application();
                etApp.Visible = false;
                etApp.UserControl = false;
                //创建新工作薄
                Excel.Workbook etBook = etApp.Workbooks.Open(strFileName);
                Excel.Worksheet etsheet = etBook.Worksheets[1] as Excel.Worksheet;//获取第一个工作薄
                //sheet.GetRowEnumerator();
                //获取数据区域
                Excel.Range myRange = etsheet.UsedRange;
                //获取ET中的行数与列数
                int rowsCount = myRange.Rows.Count;
                int colsCount = myRange.Columns.Count;

                dt = GetEtData(dt, myRange, rowsCount, colsCount);
                etApp.Workbooks.Close();//关闭所有Workbook
                etApp.Quit();//关闭Application
                System.Runtime.InteropServices.Marshal.ReleaseComObject(etBook);//释放Workbook
                System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);//释放Application

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }
        /// <summary>
        /// 根据活动表获取数据
        /// </summary>
        /// <param name="range">表数据</param>
        /// <param name="rowsCount">行数</param>
        /// <param name="colsCount">列数</param>
        /// <returns></returns>
        private System.Data.DataTable GetEtData(System.Data.DataTable dt,Excel.Range range, int rowsCount, int colsCount)
        {
            //创建一个DataTable对象
            System.Data.DataRow row = null;
            //填入数据
            for (int i = 0; i < rowsCount; i++)
            {
                row = dt.NewRow();
                for (int j = 0; j < colsCount; j++)
                {
                    Excel.Range column = range.get_Item(i + 1, j + 1) as Excel.Range;
                    string columnText = column.Text as string;
                    row[j] = columnText;
                }
                dt.Rows.Add(row);
            }
            return dt;
        }
        /// <summary>
        /// 根据列编号获取列别名
        /// </summary>
        /// <param name="num">列编号（从0开始）</param>
        /// <returns></returns>
        public static string GetExcelColumnLabel(int num,int row)
        {
            string temp = "";
            double i = Math.Floor(Math.Log(25.0 * (num) / 26.0 + 1) / Math.Log(26)) + 1;
            if (i > 1)
            {
                double sub = num - 26 * (Math.Pow(26, i - 1) - 1) / 25;
                for (double j = i; j > 0; j--)
                {
                    temp = temp + (char)(sub / Math.Pow(26, j - 1) + 65);
                    sub = sub % Math.Pow(26, j - 1);
                }
            }
            else
            {
                temp = temp + (char)(num + 65);
            }
            return temp + row;
        }