FarPoint.Win.Spread 合并行 合并列 设置边框、字体、背景颜色、行高

protected void EXCEL(string _strWhere)
{
string strTemp = " where addtime<='" + this.txtStartTime.Text + " 23:59:59' ";
DataSet ds = bll.GetBalanceEnquiry(pageSize, page, _strWhere, strTemp, out this.totalCount);

FarPoint.Win.Spread.FpSpread fs = new FarPoint.Win.Spread.FpSpread();
FarPoint.Win.Spread.SheetView sv = new FarPoint.Win.Spread.SheetView();
sv.AllowNoteEdit = true;
sv.DataAllowAddNew = true;
sv.Protect = false;
int cell = ds.Tables[0].Rows.Count;

sv.RowCount = ds.Tables[0].Rows.Count + 10;
// sv.Cells[0, 0].Font = new System.Drawing.Font("Gerorgia", 24);

sv.Cells[0, 0].Value = "XXX余额";
sv.AddSpanCell(0, 0, 1, 8);
sv.Cells[0, 0].Font = new System.Drawing.Font("宋体", float.Parse("24"), (FontStyle)Enum.Parse(typeof(FontStyle), "Bold"), System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[0, 0].Row.Height = 50;

sv.Cells[1, 0].Text = "序号";

sv.Cells[1, 0].ColumnSpan = 1;//占两行一列
sv.Cells[1, 0].RowSpan = 2;

sv.Cells[1, 1].Text = "部门";
sv.Cells[1, 1].ColumnSpan = 1;//占两行一列
sv.Cells[1, 1].RowSpan = 2;

sv.Cells[1, 2].Text = "姓名";
sv.Cells[1, 2].ColumnSpan = 1;
sv.Cells[1, 2].RowSpan = 2;//占两行一列

sv.Cells[1, 3].Value = String.Format("截止{0}卡内余额", this.datetime);//"截止2022年7月31日卡内余额";
sv.Cells[1, 3].ColumnSpan = 4;//占一行四列
sv.Cells[1, 3].RowSpan = 1;

sv.Cells[1, 7].Text = "备注";
//合并单元格
sv.Cells[1, 7].ColumnSpan = 1;
sv.Cells[1, 7].RowSpan = 2;
sv.Cells[1, 0].Row.Height = 30;

sv.Cells[2, 3].Value = "老卡余额";
sv.Cells[2, 4].Value = "劳动报酬余额";
sv.Cells[2, 5].Value = "银行卡余额";
sv.Cells[2, 6].Value = "合计";

sv.Cells[2, 0].Row.Height = 30;

int x = 3;
foreach (DataRow row in ds.Tables[0].Rows)
{
sv.Cells[x, 0].Text = (x-3).ToString();
sv.Cells[x, 1].Text = row["DepartMent"].ToString();
sv.Cells[x, 2].Text = row["UserName"].ToString();
sv.Cells[x, 3].Text = row["OldBalance"].ToString();
sv.Cells[x, 4].Text = row["BalanceWork"].ToString();
sv.Cells[x, 5].Text = row["Balance"].ToString();
sv.Cells[x, 6].Text = row["sumBalance"].ToString();
sv.Cells[x, 7].Text = "";
//行高
sv.Rows[x].Height = 30;
//边框颜色
sv.Cells[x, 0].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[x, 1].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[x, 2].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[x, 3].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[x, 4].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[x, 4].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[x, 5].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[x, 6].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[x, 7].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
x++;
}
for (int i = 0; i < sv.NonEmptyColumnCount; i++)
{
//背景颜色
// sv.Cells[0, i].BackColor = Color.LightBlue;
//边框颜色
sv.Cells[0, i].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[1, i].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
sv.Cells[1, i].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
//设置字体
sv.Cells[1, i].Font = new System.Drawing.Font("仿宋", float.Parse("14"), (FontStyle)Enum.Parse(typeof(FontStyle), "Bold"), System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[2, i].Font = new System.Drawing.Font("仿宋", float.Parse("14"), (FontStyle)Enum.Parse(typeof(FontStyle), "Bold"), System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Columns[i].Font = new System.Drawing.Font("仿宋", 14);

//每一行都居中
sv.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
sv.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;

//列宽
if (i == 1)
{
sv.Columns[i].Width = 300;
}
else
{
sv.Columns[i].Width = 130;
}

}
FarPoint.Win.Spread.CellType.TextCellType txtcell = new FarPoint.Win.Spread.CellType.TextCellType();
txtcell.MaxLength = 30;
sv.Columns[1].CellType = txtcell;

fs.Sheets.Add(sv);
System.IO.MemoryStream ms = new MemoryStream();
fs.SaveExcel(ms);
ms.Position = 0;

Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("xxxx") + HttpUtility.UrlEncode(DateTime.Now.ToString("yyyy-MM-dd_hh_mm_ss") + ".xls", System.Text.Encoding.UTF8).ToString());
// Response.AppendHeader("Content-Disposition",String.Format("attachment;filename={0}{1}.xls", HttpUtility.UrlEncode("xxx"), this.datetime));
Response.ContentType = "application/ms-excel";
Response.BinaryWrite(ms.ToArray());
Response.Flush();

Response.End();
}