winform 导入导出EXCEL（更新）

昨天写的代码导出EXCEL格式不正确，后来在网上找了很久，终于找到了解决方案，调用了第三方控件org.in2bits.MyXls，此方法也可以适用在ASP.net中,在xls.Save();后面再加一句xls.Send();发送到客户端就可以了。

private void MyExportToExcel(DataTable dt, string filename)
        {
            try
            {
                SaveFileDialog dlg = new SaveFileDialog();
                dlg.Filter = "Execl files (*.xls)|*.xls";
                dlg.FilterIndex = 0;
                dlg.RestoreDirectory = true;//是否重置到原始路径
                dlg.Title = "保存为Excel文件";
                dlg.FileName = filename;
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    //生成Excel开始
                    XlsDocument xls = new XlsDocument();//创建空xls文档
                    xls.FileName = dlg.FileName;//保存路径，如果直接发送到客户端的话只需要名称 生成名称
                    Worksheet sheet = xls.Workbook.Worksheets.AddNamed(filename); //创建一个工作页

                    //设置文档列属性
                    ColumnInfo cinfo = new ColumnInfo(xls, sheet);//设置xls文档的指定工作页的列属性
                    cinfo.Collapsed = true;
                    cinfo.Width = 90 * 60;//列宽度
                    sheet.AddColumnInfo(cinfo);
                    //设置文档列属性结束
                    //创建列
                    Cells cells = sheet.Cells; //获得指定工作页列集合
                    //创建列表头
                    //Cell title = cells.Add(2, 1, "ID");
                    //title.HorizontalAlignment = HorizontalAlignments.Right;
                    //title.VerticalAlignment = VerticalAlignments.Centered;
                    int colIndex = 0;
                    //定制表格标题
                    foreach (DataColumn dc in dt.Columns)
                    {
                        colIndex++;
                        cells.Add(1, colIndex, dc.Caption);
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (dt.Rows[i][j].GetType() == System.Type.GetType("System.DateTime"))//判断类型
                            {
                                if (System.DBNull.Value.Equals(dt.Rows[i][j]))
                                    cells.Add(i + 2, j + 1, null);
                                else
                                    cells.Add(i + 2, j + 1, (Convert.ToDateTime(dt.Rows[i][j])).ToString("yyyy-MM-dd"));//格式化字符串，否则在XLS的日期格式会不正确
                            }
                            else
                                cells.Add(i + 2, j + 1, dt.Rows[i][j].ToString());
                        }
                    }
                    xls.Save();//保存到服务器
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

在附带一些参数设置，我这里不需要，所以没写进去，有需要的可以进行修改，代码复上

　　//创建列样式创建列时引用
        XF cellXF = xls.NewXF();
        cellXF.VerticalAlignment = VerticalAlignments.Centered;
        cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
        cellXF.Font.Height =24*12;        
        cellXF.Font.Bold =true;
        cellXF.Pattern =1;//设定单元格填充风格。如果设定为0，则是纯色填充
        cellXF.PatternBackgroundColor = Colors.Red;//填充的背景底色
        cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
        //创建列样式结束
        //创建列
        Cells cells = sheet.Cells; //获得指定工作页列集合
        //列操作基本
        Cell cell=cells.Add(1, 1, "这里写你要添加有标题",cellXF);//添加标题列返回一个列  参数：行 列 名称 样式对象
        //设置XY居中
        cell.HorizontalAlignment = HorizontalAlignments.Centered;
        cell.VerticalAlignment = VerticalAlignments.Centered;
        //设置字体
        cell.Font.Bold =true;//设置粗体
        cell.Font.ColorIndex =0;//设置颜色码           
        cell.Font.FontFamily = FontFamilies.Roman;//设置字体 默认为宋体               
        //创建列结束 

EXCEL导入

 

        private bool MyInExcelData(string filePath)
        {
            try
            {
                #region 定义
                string loginname;
                #endregion

 

                //加载要导入的Excel
                XlsDocument xls = new XlsDocument(filePath);//加载外部Excel
                //获得Excel中的指定一个工作页
                for (int i = 0; i < xls.Workbook.Worksheets.Count; i++)
                {
                    Worksheet sheet = xls.Workbook.Worksheets[i];
                    //读取数据 循环每sheet工作页的每一行,不读取前两行
                    for (int j = 2; j < sheet.Rows.Count; j++)     //从第二行开始，第一行是标题
                    {
                        #region
                        loginname = sheet.Rows[ushort.Parse(j.ToString())].GetCell(1).Value.ToString();   //EXCEL中行和列都是从1开始
                         这里写入条件就可以了……

                        #endregion
                    }

 

                }
                return true;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }