• 历史数据的导出


    引用几个dll


                #region 第一行各列字段
                DataTable ExcelTable = new DataTable();
                ExcelTable.Columns.Add("编号", typeof(string));
                ExcelTable.Columns.Add("房屋类型", typeof(string));
                ExcelTable.Columns.Add("地址", typeof(string));
                ExcelTable.Columns.Add("面积", typeof(string));
                ExcelTable.Columns.Add("价格", typeof(string));
                ExcelTable.Columns.Add("创造时间", typeof(string));
                #endregion
                
                xlsManager xl = new xlsManager();
                List<xls> LIST = xl.getList();
                
                
                #region  遍历
                foreach (var test in LIST)
                {
                    var newRow = ExcelTable.NewRow();
                    newRow["编号"] = test.Id;
                    newRow["地址"] = test.Address;
                    newRow["面积"] = test.Area;
                    newRow["创造时间"] = test.CreateDate;
                    newRow["房屋类型"] = test.HouseType;
                    newRow["价格"] = test.Price;
                    ExcelTable.Rows.Add(newRow);
                }
                #endregion
                
                #region 生成.XLS
                string headName = string.Format("{0}_{1}", DateTime.Now.ToString("yyyy-MM-dd"), LIST[0].Id);
                string FileName = "";
                FileName = string.Format("{0}_{1}.xls", DateTime.Now.ToString("yyyy-MM-dd"), LIST[0].Id);
                ExportByWeb(ExcelTable, headName, FileName);
                #endregion
                
                
            public static List<xls> qwe()
            {
                List<xls> list = new List<xls>();
                string sql = "select * from Houses";
                DataTable dt = DBHelper.GetInstance().GetData(sql);
                foreach (DataRow item in dt.Rows)
                {
                    xls ho = new xls();
                    ho.Id = int.Parse(item["Id"].ToString());
                    ho.Address = item["Address"].ToString();
                    ho.Area = double.Parse(item["Area"].ToString());
                    ho.CreateDate = DateTime.Parse(item["CreateDate"].ToString());
                    ho.HouseType = item["HouseType"].ToString();
                    ho.Price = double.Parse(item["Price"].ToString());
                    list.Add(ho);
                }
                return list;
            }
            
            /// <summary>
            /// 用于Web导出
            /// </summary>
            /// <param name="dtSource">源DataTable</param>
            /// <param name="strHeaderText">表头文本</param>
            /// <param name="strFileName">文件名</param>
            public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
            {
                HttpContext curContext = HttpContext.Current;

                // 设置编码和附件格式
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = Encoding.UTF8;
                curContext.Response.Charset = "";
                curContext.Response.AppendHeader("Content-Disposition",
                    "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

                curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
                curContext.Response.End();
            }
            /// <summary>
            /// DataTable导出到Excel的MemoryStream
            /// </summary>
            /// <param name="dtSource">源DataTable</param>
            /// <param name="strHeaderText">表头文本</param>
            public static MemoryStream Export(DataTable dtSource, string strHeaderText)
            {


                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();

                ICellStyle cellStyle = workbook.CreateCellStyle();


                //文字水平和垂直对齐方式
                cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                //是否换行
                cellStyle.WrapText = true;
                //缩小字体填充
                //cellStyle.ShrinkToFit = true;


                var dateStyle = workbook.CreateCellStyle();
                var format = workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                //取得列宽
                int[] arrColWidth = new int[dtSource.Columns.Count];
                foreach (DataColumn item in dtSource.Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }
                int rowIndex = 0;
                foreach (DataRow row in dtSource.Rows)
                {
                    #region 新建表,填充表头,填充列头,样式
                    if (rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }

                        #region 表头及样式

                        var titleRow = sheet.CreateRow(0);
                        titleRow.HeightInPoints = 25;
                        var titleCell = titleRow.CreateCell(0);
                        titleCell.SetCellValue(strHeaderText);
                        titleCell.CellStyle.CloneStyleFrom(cellStyle);

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));


                        #endregion


                        #region 列头及样式
                        {
                            var headerRow = sheet.CreateRow(1);




                            foreach (DataColumn column in dtSource.Columns)
                            {

                                var headCell = headerRow.CreateCell(column.Ordinal);
                                headCell.SetCellValue(column.ColumnName);

                                headerRow.GetCell(column.Ordinal).CellStyle.CloneStyleFrom(cellStyle);

                                //设置列宽
                                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                            }
                            //headerRow.Dispose();
                        }
                        #endregion

                        rowIndex = 2;
                    }
                    #endregion

                    #region 填充内容
                    var dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        var newCell = dataRow.CreateCell(column.Ordinal);

                        string drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);

                                newCell.CellStyle = dateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                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.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }

                    }
                    #endregion

                    rowIndex++;
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;

                    //sheet.Dispose();
                    //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
                    return ms;
                }
            }

  • 相关阅读:
    Django REST framework 1
    爬虫基本原理
    QueryDict对象
    Django组件ModelForm
    MongoDB
    Algorithm
    BOM
    CSS
    Vue
    AliPay
  • 原文地址:https://www.cnblogs.com/IWantPower/p/6097049.html
Copyright © 2020-2023  润新知