public void Import(System.IO.Stream sr)
{
STMsExcel excel = new STMsExcel();
excel.Open(sr);
excel.SetActiveSheet(0);
DataTable dt = excel.ExportDataTable(0, 0, 100000, 6);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (Convert.ToString(dt.Rows[i][0]) == "")
{
break;
}
if (Insert(Convert.ToString(dt.Rows[i][0]), Convert.ToString(dt.Rows[i][1]), Convert.ToString(dt.Rows[i][2]), Convert.ToString(dt.Rows[i][3]), Convert.ToString(dt.Rows[i][5]), Convert.ToString(dt.Rows[i][4])) < 1)
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('导入失败,标准号" + Convert.ToString(dt.Rows[i][1]) + "');</script>");
return;
}
}
}
/// <summary>
/// 按条件 Excel导出会员信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_ExprotMemListByFilter(object sender, EventArgs e)
{
string filter = SetFilter();
string tableViews = "(select *,(select levelName from T_Member_Level where levelId= T_Member_Info.memberLevel) as levelName,(select TypeName from T_Base_MemberType where TypeCode=memberType ) as TypeName from T_Member_Info ) v ";
string sort = " order by memberid";
string fields = "*";
string sql = "select " + fields + " from " + tableViews + filter + sort;
DataTable dt = DalBase.Util_GetList(sql).Tables[0];
EditDataSourceExcel(ref dt);
string fileName = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss").Replace("-", "").Replace(" ", "").Replace(":", "") + "会员信息表" + ".xls";
string savedPath = "~/upload/";
string phyPath = System.Web.HttpContext.Current.Server.MapPath(savedPath);
if (!System.IO.Directory.Exists(phyPath))
{
System.IO.Directory.CreateDirectory(phyPath);
}
string savedFileName = Path.Combine(phyPath, fileName);
STMsExcel excel = new STMsExcel();
excel.ClearAndAddSheet("会员信息");//表格 sheet1名称
//表格首行标题样式
excel.Merge(0, 0, 1, 26); //合并第一行 前8列
excel.SetValue(0, 0, "会员信息表"); //表格首行大标题
Aspose.Cells.Style styleTitle = excel.CreateStyle();
styleTitle.Font.Color = Color.Black;
styleTitle.Font.IsBold = true;
styleTitle.Font.Size = 15;
styleTitle.BackgroundColor = Color.FromArgb(106, 182, 180);
styleTitle.HorizontalAlignment = TextAlignmentType.Center;
styleTitle.ForegroundColor = Color.FromArgb(0x99, 0xcc, 0xff);
//设置首行上下左右边框
styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.TopBorder].Color = Color.Black;
styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.BottomBorder].Color = Color.Black;
styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.LeftBorder].Color = Color.Black;
styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.RightBorder].Color = Color.Black;
excel.SetRowHeight(0, 30);
excel.SetRowStyle(0, styleTitle);
//表格 字段标题 样式
Aspose.Cells.Style style = excel.CreateStyle();
style.Font.Color = Color.Black;
style.BackgroundColor = Color.Gray;
style.Font.IsBold = true;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Black;
style.HorizontalAlignment = TextAlignmentType.Center;//水平居中
excel.SetRowStyle(1, style);
excel.SetRowHeight(1, 20);
//设置列宽
excel.SetColumnWidth(0, 8);//自动编号
excel.SetColumnWidth(1, 20);
excel.SetColumnWidth(2, 10);
excel.SetColumnWidth(3, 30);
excel.SetColumnWidth(4, 15);
excel.SetColumnWidth(5, 15);
excel.SetColumnWidth(6, 10);
excel.SetColumnWidth(7, 10);
excel.SetColumnWidth(8, 15);
excel.SetColumnWidth(9, 10);
excel.SetColumnWidth(10, 15);
excel.SetColumnWidth(11, 20);
excel.SetColumnWidth(12, 10);
excel.SetColumnWidth(13, 15);
excel.SetColumnWidth(14, 20);
excel.SetColumnWidth(15, 10);
excel.SetColumnWidth(16, 10);
excel.SetColumnWidth(13, 15);
excel.SetColumnWidth(14, 10);
excel.SetColumnWidth(15, 10);
excel.SetColumnWidth(16, 10);
excel.SetColumnWidth(17, 15);
excel.SetColumnWidth(18, 20);
excel.SetColumnWidth(19, 20);
excel.SetColumnWidth(20, 20);
excel.SetColumnWidth(21, 20);
excel.SetColumnWidth(22, 30);
excel.SetColumnWidth(23, 10);
excel.SetColumnWidth(24, 20);
excel.SetColumnWidth(25, 30);
excel.SetColumnWidth(26, 10);
//表格字段名称
excel.SetValue("A" + 2, "序号");//自动编号
excel.SetValue("B" + 2, "用户名称");
excel.SetValue("C" + 2, "会员类型");
excel.SetValue("D" + 2, "所属行业");
excel.SetValue("E" + 2, "会员级别");
excel.SetValue("F" + 2, "可托管标准");
excel.SetValue("G" + 2, "剩余托管数");
excel.SetValue("H" + 2, "可下载标准");
excel.SetValue("I" + 2, "剩余下载数");
excel.SetValue("J" + 2, "充值账户金额");
excel.SetValue("K" + 2, "已用金额");
excel.SetValue("L" + 2, "免费账户金额");
excel.SetValue("M" + 2, "已用金额");
excel.SetValue("N" + 2, "所在地区");
excel.SetValue("O" + 2, "公司名称");
excel.SetValue("P" + 2, "联系人");
excel.SetValue("Q" + 2, "性别");
excel.SetValue("R" + 2, "联系电话");
excel.SetValue("S" + 2, "传真");
excel.SetValue("T" + 2, "手机");
excel.SetValue("U" + 2, "地址");
excel.SetValue("V" + 2, "邮政编码");
excel.SetValue("W" + 2, "电子邮箱");
excel.SetValue("X" + 2, "注册时间");
excel.SetValue("Y" + 2, "会员开始日期");
excel.SetValue("Z" + 2, "会员截止日期");
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Aspose.Cells.Style style1 = excel.CreateStyle();
style1.HorizontalAlignment = TextAlignmentType.Left;//水平居左
style1.BackgroundColor = Color.FromArgb(225, 253, 254);
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.TopBorder].Color = Color.Black;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.BottomBorder].Color = Color.Black;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.LeftBorder].Color = Color.Black;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].Color = Color.Black;
excel.SetRowStyle(i + 2, style1);
excel.SetRowHeight(i + 2, 20);
excel.SetValue("A" + (3 + i), Convert.ToString(dt.Rows[i]["add_sn"]));//自动编号
excel.SetValue("B" + (3 + i), Convert.ToString(dt.Rows[i]["memberName"]));
excel.SetValue("C" + (3 + i), Convert.ToString(dt.Rows[i]["TypeName"]));
excel.SetValue("D" + (3 + i), Convert.ToString(dt.Rows[i]["memberCompanyType"]));
excel.SetValue("E" + (3 + i), Convert.ToString(dt.Rows[i]["levelName"]));
excel.SetValue("F" + (3 + i), Convert.ToString(dt.Rows[i]["TrustNumber"]));
excel.SetValue("G" + (3 + i), Convert.ToString(dt.Rows[i]["TrustNumber"]));
excel.SetValue("H" + (3 + i), Convert.ToString(dt.Rows[i]["DownloadNumber"]));
excel.SetValue("I" + (3 + i), Convert.ToString(dt.Rows[i]["DownloadNumber"]));
excel.SetValue("J" + (3 + i), Convert.ToString(dt.Rows[i]["buyMoneyAccount"]));
excel.SetValue("K" + (3 + i), Convert.ToString(dt.Rows[i]["buyMoneyAccountUsed"]));
excel.SetValue("L" + (3 + i), Convert.ToString(dt.Rows[i]["freeMoenyAccount"]));
excel.SetValue("M" + (3 + i), Convert.ToString(dt.Rows[i]["freeMoenyAccountUsed"]));
excel.SetValue("N" + (3 + i), Convert.ToString(dt.Rows[i]["areaName"]));
excel.SetValue("O" + (3 + i), Convert.ToString(dt.Rows[i]["memberCompanyName"]));
excel.SetValue("P" + (3 + i), Convert.ToString(dt.Rows[i]["memberTrueName"]));
excel.SetValue("Q" + (3 + i), Convert.ToString(dt.Rows[i]["sex"]));
excel.SetValue("R" + (3 + i), Convert.ToString(dt.Rows[i]["tel"]));
excel.SetValue("S" + (3 + i), Convert.ToString(dt.Rows[i]["fax"]));
excel.SetValue("T" + (3 + i), Convert.ToString(dt.Rows[i]["mobile"]));
excel.SetValue("U" + (3 + i), Convert.ToString(dt.Rows[i]["address"]));
excel.SetValue("V" + (3 + i), Convert.ToString(dt.Rows[i]["post"]));
excel.SetValue("W" + (3 + i), Convert.ToString(dt.Rows[i]["email"]));
excel.SetValue("X" + (3 + i), Convert.ToString(dt.Rows[i]["regType"]));
excel.SetValue("Y" + (3 + i), Convert.ToString(dt.Rows[i]["levelServiceStartTime"]));
excel.SetValue("Z" + (3 + i), Convert.ToString(dt.Rows[i]["levelServiceEndTime"]));
}
}
excel.Save(fileName, Aspose.Cells.FileFormatType.Excel97To2003, Aspose.Cells.SaveType.OpenInBrowser, Response);
FileInfo file = new FileInfo(savedFileName);
file.Delete();
}