private void AddDefaultStyleXF()
{
XF xf = _doc.NewXF();
xf.IsStyleXF = true;
xf.CellLocked = true;
Add(xf);
}
private void EnsureXF()
{
if (_currentXF == null)
{
_currentXF = _doc.NewXF();
}
}
/// <summary>
/// export excel file
/// </summary>
/// <param name="excelInfo">DataTable need to be export.</param>
public void exportExcel(DataTable excelInfo)
{
var xls = new XlsDocument();
// set excel file
xls.FileName = HttpContext.Current.Server.UrlEncode(excelInfo.TableName) + ".xls"; //;"Detail.xls";
// create excel sheet page
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
// set column width
var ci = new ColumnInfo(xls, sheet);
ci.ColumnIndexStart = 1;
ci.ColumnIndexEnd = (ushort) (excelInfo.Columns.Count - 1);
ci.Width = 13*256;
sheet.AddColumnInfo(ci);
// output content
Cells cells = sheet.Cells;
// set title style
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
// output title
cells.Merge(1, 1, 1, excelInfo.Columns.Count);
cells.Add(1, 1, excelInfo.TableName, xf);
// append cell style
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.BottomLineStyle = 1;
xf.LeftLineStyle = 1;
xf.RightLineStyle = 1;
// output data
for (int i = 0; i < excelInfo.Columns.Count; i++)
{
// output output table header
cells.Add(2, i + 1, excelInfo.Columns[i].ColumnName, xf);
// output data
for (int j = 0; j < excelInfo.Rows.Count; j++)
{
// check data type
if (IsFloat(excelInfo.Rows[j][i].ToString()))
{
cells.Add(j + 3, i + 1, Convert.ToDecimal(excelInfo.Rows[j][i]), xf);
}
else
{
cells.Add(j + 3, i + 1, excelInfo.Rows[j][i].ToString(), xf);
}
}
}
xls.Send();
}
protected void btnExport_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();//新建一个xls文档
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss"));
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = false; // 使用边框
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF1.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF1.UseBorder = false; // 使用边框
titleXF1.Font.Bold = true;
titleXF1.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
// 开始填充数据到单元格
org.in2bits.MyXls.Cells cells = sheet.Cells;
cells.Add(1, 1, "NO#", titleXF1);
cells.Add(1, 2, "Claim Type", titleXF1);
cells.Add(1, 3, "Amount", titleXF1);
cells.Add(1, 4, "Owner", titleXF1);
cells.Add(1, 5, "Process", titleXF1);
cells.Add(1, 6, "Current Approver", titleXF1);
cells.Add(1, 7, "Submit Date", titleXF1);
cells.Add(1, 8, "Remark", titleXF1);
//添加数据
string json = GridData.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;
XmlDocument doc = new XmlDocument();
doc.LoadXml(xml.InnerXml);
for (int i = 0; i < doc.SelectNodes("records").Item(0).SelectNodes("record").Count; i++)
{
if (!string.IsNullOrEmpty(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml))
{
cells.Add(2 + i, 3, Convert.ToDouble(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml), titleXF);
}
else
{
cells.Add(2 + i, 3, "", titleXF);
}
cells.Add(2 + i, 1, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("No").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 2, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Type1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 4, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Person").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 5, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Status1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 6, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Approver").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 7, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("CreadedDate").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 8, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Remark").Item(0).InnerXml, titleXF);
}
xls.Send();
}
protected void btnExport_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();//新建一个xls文档
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string MainID = Request.QueryString["ID"].ToString();
cs.DBCommand dbc = new cs.DBCommand();
string sqlmain = "select * from ETravel where ID=" + MainID;
DataTable dtMain = dbc.GetData("eReimbursement", sqlmain);
if (dtMain.Rows.Count != 1)
{
ErrorHandle("Data Error.");
return;
}
string sqlTocity = "select (ROW_NUMBER() OVER (ORDER BY Tocity)-1) % 4 AS SubRow,(ROW_NUMBER() OVER (ORDER BY Tocity) - 1) / 4 AS Row,Tocity from (select distinct Tocity from ETraveleDetail where [No]='" + MainID + "') t1";
DataTable dtTocity = dbc.GetData("eReimbursement", sqlTocity);
int pagecount = 0;
for (int i = 0; i < dtTocity.Rows.Count; i++)
{
if (Convert.ToInt32(dtTocity.Rows[i]["Row"].ToString())>pagecount)
{
pagecount = Convert.ToInt32(dtTocity.Rows[i]["Row"].ToString());
}
}
for (int j = 0; j < pagecount + 1; j++)
{
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss" + j.ToString()));
//首行空白行
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = false; // 使用边框
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
//第二行
XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF1.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF1.UseBorder = false; // 使用边框
titleXF1.Font.Underline = UnderlineTypes.Single;
titleXF1.Font.Height = 18 * 20;
XF columnTitleXF41 = xls.NewXF();
XF columnTitleXF42 = xls.NewXF();
columnTitleXF42.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF42.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF42.Font.Height = 12 * 20;
XF columnTitleXF43 = xls.NewXF();
columnTitleXF43.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF43.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF43.Font.Height = 12 * 20;
columnTitleXF43.UseBorder = true;
columnTitleXF43.LeftLineStyle = 2;
columnTitleXF43.TopLineStyle = 2;
columnTitleXF43.RightLineStyle = 2;
columnTitleXF43.BottomLineStyle = 2;
XF columnTitleXF44 = xls.NewXF();
columnTitleXF44.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF44.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF44.Font.Height = 12 * 20;
columnTitleXF44.UseBorder = true;
columnTitleXF44.TopLineStyle = 2;
columnTitleXF44.BottomLineStyle = 2;
XF columnTitleXF46 = xls.NewXF();
columnTitleXF46.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF46.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF46.Font.Height = 12 * 20;
columnTitleXF46.UseBorder = true;
columnTitleXF46.TopLineStyle = 2;
columnTitleXF46.BottomLineStyle = 2;
columnTitleXF46.RightLineStyle = 2;
XF columnTitleXF412 = xls.NewXF();
columnTitleXF412.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF412.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF412.Font.Height = 12 * 20;
columnTitleXF412.UseBorder = true;
columnTitleXF412.TopLineStyle = 2;
columnTitleXF412.BottomLineStyle = 2;
columnTitleXF412.RightLineStyle = 2;
XF columnTitleXF62 = xls.NewXF();
columnTitleXF62.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF62.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF62.Font.Height = 10 * 20;
columnTitleXF62.Font.Bold = true;
columnTitleXF62.UseBorder = true;
columnTitleXF62.LeftLineStyle = 2;
columnTitleXF62.TopLineStyle = 2;
columnTitleXF62.RightLineStyle = 2;
columnTitleXF62.BottomLineStyle = 2;
XF columnTitleXF63 = xls.NewXF();
columnTitleXF63.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF63.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF63.Font.Height = 10 * 20;
columnTitleXF63.UseBorder = true;
columnTitleXF63.TopLineStyle = 2;
columnTitleXF63.LeftLineStyle = 1;
XF columnTitleXF64 = xls.NewXF();
columnTitleXF64.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF64.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF64.Font.Height = 10 * 20;
columnTitleXF64.UseBorder = true;
columnTitleXF64.TopLineStyle = 2;
columnTitleXF64.RightLineStyle = 1;
XF columnTitleXF66 = xls.NewXF();
columnTitleXF66.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF66.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF66.Font.Height = 10 * 20;
columnTitleXF66.UseBorder = true;
columnTitleXF66.TopLineStyle = 2;
XF columnTitleXF67 = xls.NewXF();
columnTitleXF67.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF67.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF67.Font.Height = 10 * 20;
columnTitleXF67.Font.Bold = true;
columnTitleXF67.UseBorder = true;
columnTitleXF67.LeftLineStyle = 2;
columnTitleXF67.TopLineStyle = 2;
columnTitleXF67.BottomLineStyle = 1;
XF columnTitleXF68 = xls.NewXF();
columnTitleXF68.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF68.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF68.Font.Height = 10 * 20;
columnTitleXF68.UseBorder = true;
columnTitleXF68.TopLineStyle = 2;
columnTitleXF68.RightLineStyle = 2;
XF columnTitleXF72 = xls.NewXF();
columnTitleXF72.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF72.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF72.Font.Height = 10 * 20;
columnTitleXF72.Font.Bold = true;
columnTitleXF72.UseBorder = true;
columnTitleXF72.LeftLineStyle = 2;
columnTitleXF72.RightLineStyle = 2;
columnTitleXF72.BottomLineStyle = 2;
XF columnTitleXF73 = xls.NewXF();
columnTitleXF73.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF73.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF73.Font.Height = 10 * 20;
columnTitleXF73.UseBorder = true;
columnTitleXF73.LeftLineStyle = 1;
columnTitleXF73.TopLineStyle = 1;
columnTitleXF73.RightLineStyle = 1;
columnTitleXF73.BottomLineStyle = 2;
XF columnTitleXF77 = xls.NewXF();
columnTitleXF77.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF77.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF77.Font.Height = 10 * 20;
columnTitleXF77.Font.Bold = true;
columnTitleXF77.UseBorder = true;
columnTitleXF77.LeftLineStyle = 2;
columnTitleXF77.TopLineStyle = 1;
columnTitleXF77.RightLineStyle = 1;
columnTitleXF77.BottomLineStyle = 2;
XF columnTitleXF78 = xls.NewXF();
columnTitleXF78.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF78.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF78.Font.Height = 10 * 20;
columnTitleXF78.Font.Bold = true;
columnTitleXF78.UseBorder = true;
columnTitleXF78.TopLineStyle = 1;
columnTitleXF78.RightLineStyle = 2;
columnTitleXF78.BottomLineStyle = 2;
XF columnTitleXF82 = xls.NewXF();
columnTitleXF82.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF82.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF82.Font.Height = 10 * 20;
columnTitleXF82.UseBorder = true;
columnTitleXF82.LeftLineStyle = 2;
columnTitleXF82.TopLineStyle = 2;
columnTitleXF82.RightLineStyle = 2;
columnTitleXF82.BottomLineStyle = 1;
XF columnTitleXF83 = xls.NewXF();
columnTitleXF83.HorizontalAlignment = HorizontalAlignments.Right;
columnTitleXF83.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF83.Font.Height = 10 * 20;
columnTitleXF83.UseBorder = true;
columnTitleXF83.LeftLineStyle = 1;
columnTitleXF83.TopLineStyle = 1;
columnTitleXF83.RightLineStyle = 1;
columnTitleXF83.BottomLineStyle = 1;
XF columnTitleXF812 = xls.NewXF();
columnTitleXF812.HorizontalAlignment = HorizontalAlignments.Right;
columnTitleXF812.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF812.Font.Height = 10 * 20;
columnTitleXF812.UseBorder = true;
columnTitleXF812.LeftLineStyle = 1;
columnTitleXF812.TopLineStyle = 1;
columnTitleXF812.RightLineStyle = 2;
columnTitleXF812.BottomLineStyle = 1;
XF columnTitleXF92 = xls.NewXF();
columnTitleXF92.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF92.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF92.Font.Height = 10 * 20;
columnTitleXF92.UseBorder = true;
columnTitleXF92.LeftLineStyle = 2;
columnTitleXF92.TopLineStyle = 1;
columnTitleXF92.RightLineStyle = 2;
columnTitleXF92.BottomLineStyle = 1;
XF columnTitleXF93 = xls.NewXF();
columnTitleXF93.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF93.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF93.Font.Height = 10 * 20;
columnTitleXF93.UseBorder = true;
columnTitleXF93.LeftLineStyle = 1;
columnTitleXF93.TopLineStyle = 1;
XF columnTitleXF192 = xls.NewXF();
columnTitleXF192.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF192.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF192.Font.Height = 10 * 20;
columnTitleXF192.UseBorder = true;
columnTitleXF192.LeftLineStyle = 2;
columnTitleXF192.TopLineStyle = 1;
columnTitleXF192.RightLineStyle = 2;
columnTitleXF192.BottomLineStyle = 2;
XF columnTitleXF202 = xls.NewXF();
columnTitleXF202.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF202.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF202.Font.Height = 10 * 20;
columnTitleXF202.UseBorder = true;
columnTitleXF202.LeftLineStyle = 2;
columnTitleXF202.TopLineStyle = 2;
XF columnTitleXF208 = xls.NewXF();
columnTitleXF208.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF208.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF208.Font.Height = 10 * 20;
columnTitleXF208.UseBorder = true;
columnTitleXF208.LeftLineStyle = 1;
XF columnTitleXF215 = xls.NewXF();
columnTitleXF215.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF215.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF215.Font.Height = 10 * 20;
columnTitleXF215.UseBorder = true;
columnTitleXF215.LeftLineStyle = 1;
// 列标题行
ColumnInfo col1 = new ColumnInfo(xls, sheet); // 列对象
col1.ColumnIndexStart = 0; // 起始列为第1列，索引从0开始
col1.ColumnIndexEnd = 0; // 终止列为第1列，索引从0开始
col1.Width = 256; // 列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(col1); // 把格式附加到sheet页上
ColumnInfo col2 = new ColumnInfo(xls, sheet);
col2 = new ColumnInfo(xls, sheet); // 列对象
col2.ColumnIndexStart = 1;
col2.ColumnIndexEnd = 1;
col2.Width = 7680;
sheet.AddColumnInfo(col2);
ColumnInfo col3 = new ColumnInfo(xls, sheet);
col3 = new ColumnInfo(xls, sheet); // 列对象
col3.ColumnIndexStart = 2;
col3.ColumnIndexEnd = 9;
col3.Width = 1800;
sheet.AddColumnInfo(col3);
ColumnInfo col4 = new ColumnInfo(xls, sheet);
col4 = new ColumnInfo(xls, sheet); // 列对象
col4.ColumnIndexStart = 10;
col4.ColumnIndexEnd = 11;
col4.Width = 3900;
sheet.AddColumnInfo(col4);
//行
RowInfo rol1 = new RowInfo();
rol1.RowHeight = 10 * 20;
rol1.RowIndexStart = 1;
rol1.RowIndexEnd = 1;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 20 * 20;
rol1.RowIndexStart = 2;
rol1.RowIndexEnd = 2;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 5 * 20;
rol1.RowIndexStart = 3;
rol1.RowIndexEnd = 3;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 5 * 20;
rol1.RowIndexStart = 5;
rol1.RowIndexEnd = 5;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 18 * 20;
rol1.RowIndexStart = 6;
rol1.RowIndexEnd = 23;
sheet.AddRowInfo(rol1);
// 数据单元格样式
XF dataXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
dataXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
dataXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
dataXF.UseBorder = true; // 使用边框
dataXF.LeftLineStyle = 1; // 左边框样式
dataXF.LeftLineColor = Colors.Black; // 左边框颜色
dataXF.BottomLineStyle = 1; // 下边框样式
dataXF.BottomLineColor = Colors.Black; // 下边框颜色
dataXF.Font.FontName = "宋体";
dataXF.Font.Height = 9 * 20; // 设定字大小（字体大小是以 1/20 point 为单位的）
dataXF.UseProtection = false; // 默认的就是受保护的，导出后需要启用编辑才可修改
dataXF.TextWrapRight = true; // 自动换行
// 合并单元格
MergeArea titleArea = new MergeArea(1, 1, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(2, 2, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(3, 3, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 3, 6);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 7, 10);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 11, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 11, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 3, 4);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 5, 6);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 7, 8);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 9, 10);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 7, 2, 2);
sheet.AddMergeArea(titleArea);
// 开始填充数据到单元格
org.in2bits.MyXls.Cells cells = sheet.Cells;
cells.Add(1, 1, "", titleXF);
cells.Add(2, 1, "Travel Expense Report", titleXF1);
cells.Add(3, 1, "", titleXF);
cells.Add(4, 1, "", columnTitleXF41);
cells.Add(4, 2, "Applicant:", columnTitleXF42);
cells.Add(4, 3, dtMain.Rows[0]["Person"].ToString(), columnTitleXF43);
cells.Add(4, 4, "", columnTitleXF44);
cells.Add(4, 5, "", columnTitleXF44);
cells.Add(4, 6, "", columnTitleXF46);
cells.Add(4, 7, "Travel Period:", columnTitleXF42);
string bb = "";
if (dtMain.Rows[0]["Bdate"].ToString()!="")
{
bb += Convert.ToDateTime(dtMain.Rows[0]["Bdate"].ToString()).ToString("yyyy/MM/dd");
}
if (dtMain.Rows[0]["Edate"].ToString() != "")
{
bb += " - " + Convert.ToDateTime(dtMain.Rows[0]["Edate"].ToString()).ToString("yyyy/MM/dd");
}
cells.Add(4, 11, bb, columnTitleXF43);
cells.Add(4, 12, "", columnTitleXF412);
cells.Add(5, 1, "", columnTitleXF41);
cells.Add(6, 1, "", columnTitleXF41);
cells.Add(6, 2, "Travel Destination", columnTitleXF62);
cells.Add(6, 3, "", columnTitleXF63);
cells.Add(6, 4, "", columnTitleXF64);
cells.Add(6, 5, "", columnTitleXF63);
cells.Add(6, 6, "", columnTitleXF64);
cells.Add(6, 7, "", columnTitleXF63);
cells.Add(6, 8, "", columnTitleXF64);
cells.Add(6, 9, "", columnTitleXF63);
cells.Add(6, 10, "", columnTitleXF64);
cells.Add(6, 11, "Total Expenses", columnTitleXF67);
cells.Add(6, 12, "", columnTitleXF68);
cells.Add(7, 1, "", columnTitleXF41);
cells.Add(7, 2, "", columnTitleXF72);
cells.Add(7, 3, "", columnTitleXF73);
cells.Add(7, 4, "", columnTitleXF73);
cells.Add(7, 5, "", columnTitleXF73);
cells.Add(7, 6, "", columnTitleXF73);
cells.Add(7, 7, "", columnTitleXF73);
cells.Add(7, 8, "", columnTitleXF73);
cells.Add(7, 9, "", columnTitleXF73);
cells.Add(7, 10, "", columnTitleXF73);
cells.Add(7, 11, "Reimbursement", columnTitleXF77);
cells.Add(7, 12, "Company Paid", columnTitleXF78);
cells.Add(8, 1, "", columnTitleXF41);
cells.Add(8, 2, "1. Air Ticket - Int'l", columnTitleXF82);
cells.Add(9, 1, "", columnTitleXF41);
cells.Add(9, 2, "Domestic", columnTitleXF92);
cells.Add(10, 1, "", columnTitleXF41);
cells.Add(10, 2, "2. Hotel Bill", columnTitleXF92);
cells.Add(11, 1, "", columnTitleXF41);
cells.Add(11, 2, "3. Meals", columnTitleXF92);
cells.Add(12, 1, "", columnTitleXF41);
cells.Add(12, 2, "4. Entertainment", columnTitleXF92);
cells.Add(13, 1, "", columnTitleXF41);
cells.Add(13, 2, "5. Car Rental/Transportation", columnTitleXF92);
cells.Add(14, 1, "", columnTitleXF41);
cells.Add(14, 2, "6. Communication", columnTitleXF92);
cells.Add(15, 1, "", columnTitleXF41);
cells.Add(15, 2, "7. Local Trip NTD800(CNY60)/day", columnTitleXF92);
cells.Add(16, 1, "", columnTitleXF41);
cells.Add(16, 2, "8. Overseas Trip USD15/day", columnTitleXF92);
cells.Add(17, 1, "", columnTitleXF41);
cells.Add(17, 2, "9. Airport Tax/Travel Insurance", columnTitleXF92);
cells.Add(18, 1, "", columnTitleXF41);
cells.Add(18, 2, "10. Others", columnTitleXF92);
cells.Add(19, 1, "", columnTitleXF41);
cells.Add(19, 2, "Total", columnTitleXF192);
cells.Add(20, 1, "", columnTitleXF41);
cells.Add(20, 2, "Remarks: USD: NTD =", columnTitleXF202);
cells.Add(20, 12, "Total Trip Expense", columnTitleXF208);
cells.Add(21, 7, "Less: Advance", columnTitleXF215);
cells.Add(22, 7, "Bal Due to Company", columnTitleXF215);
cells.Add(23, 7, "Bal. Due to Employee", columnTitleXF215);
for (int i = 0; i < 12; i++)
{
for (int ii = 0; ii < 9; ii++)
{
cells.Add(8 + i, 3 + ii, "", columnTitleXF83);
}
}
for (int i = 0; i < 12; i++)
{
cells.Add(8 + i, 12, "", columnTitleXF812);
}
string sqlDetail = "select * from ETraveleDetail where [No]='" + MainID + "'";
DataTable dtDetail = dbc.GetData("eReimbursement", sqlDetail);
decimal row1TC = 0M;
decimal row1TP = 0M;
decimal row2TC = 0M;
decimal row2TP = 0M;
decimal row3TC = 0M;
decimal row3TP = 0M;
decimal row4TC = 0M;
decimal row4TP = 0M;
decimal row5TC = 0M;
decimal row5TP = 0M;
decimal row6TC = 0M;
decimal row6TP = 0M;
decimal row7TC = 0M;
decimal row7TP = 0M;
decimal row8TC = 0M;
decimal row8TP = 0M;
decimal row9TC = 0M;
decimal row9TP = 0M;
decimal row10TC = 0M;
decimal row10TP = 0M;
decimal row11TC = 0M;
decimal row11TP = 0M;
for (int p = 0; p < dtTocity.Rows.Count; p++)
{
if (Convert.ToInt32(dtTocity.Rows[p]["Row"].ToString()) == j)
{
decimal row1Pamount = 0M;
decimal row1Camount = 0M;
decimal row2Pamount = 0M;
decimal row2Camount = 0M;
decimal row3Pamount = 0M;
decimal row3Camount = 0M;
decimal row4Pamount = 0M;
decimal row4Camount = 0M;
decimal row5Pamount = 0M;
decimal row5Camount = 0M;
decimal row6Pamount = 0M;
decimal row6Camount = 0M;
decimal row7Pamount = 0M;
decimal row7Camount = 0M;
decimal row8Pamount = 0M;
decimal row8Camount = 0M;
decimal row9Pamount = 0M;
decimal row9Camount = 0M;
decimal row10Pamount = 0M;
decimal row10Camount = 0M;
decimal row11Pamount = 0M;
decimal row11Camount = 0M;
decimal column0TC = 0M;
decimal column1TP = 0M;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
if (dtDetail.Rows[i]["Tocity"].ToString() == dtTocity.Rows[p]["Tocity"].ToString())
{
if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012023")//Air Ticket - Int'l
{
row1Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row1Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row1TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row1TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012011" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012021")//Hotel Bill
{
row3Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row3Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row3TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row3TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62010901" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010910" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010920")//Enter
{
row5Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row5Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row5TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row5TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62011901" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011910" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011920" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011930" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011940" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012013")//Car
{
row6Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row6TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62010501" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010510" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010520" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010530" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010540" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010550" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010560")//Commu
{
row7Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row7TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row7TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012012")//Local
{
row8Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row8Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row8TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row8TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012022")//Oversea
{
row9Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row9Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row9TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row9TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62020630")//Airport
{
row10Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row10Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row10TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row10TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012014" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012024")//Others
{
row11Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row11TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else
{
row11Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row11TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
column0TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
column1TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
}
cells.Add(6, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, dtTocity.Rows[p]["Tocity"].ToString(), columnTitleXF63);
cells.Add(7, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, "Reim", columnTitleXF73);
cells.Add(7, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, "Comp", columnTitleXF73);
if (row1Pamount != 0M)
{
cells.Add(8, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row1Pamount, columnTitleXF83);
}
if (row1Camount != 0M)
{
cells.Add(8, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row1Camount, columnTitleXF83);
}
if (row2Pamount != 0M)
{
cells.Add(9, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row2Pamount, columnTitleXF83);
}
if (row2Camount != 0M)
{
cells.Add(9, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row2Camount, columnTitleXF83);
}
if (row3Pamount != 0M)
{
cells.Add(10, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row3Pamount, columnTitleXF83);
}
if (row3Camount != 0M)
{
cells.Add(10, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row3Camount, columnTitleXF83);
}
if (row4Pamount != 0M)
{
cells.Add(11, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row4Pamount, columnTitleXF83);
}
if (row4Camount != 0M)
{
cells.Add(11, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row4Camount, columnTitleXF83);
}
if (row5Pamount != 0M)
{
cells.Add(12, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row5Pamount, columnTitleXF83);
}
if (row5Camount != 0M)
{
cells.Add(12, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row5Camount, columnTitleXF83);
}
if (row6Pamount != 0M)
{
cells.Add(13, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row6Pamount, columnTitleXF83);
}
if (row6Camount != 0M)
{
cells.Add(13, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row6Camount, columnTitleXF83);
}
if (row7Pamount != 0M)
{
cells.Add(14, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row7Pamount, columnTitleXF83);
}
if (row7Camount != 0M)
{
cells.Add(14, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row7Camount, columnTitleXF83);
}
if (row8Pamount != 0M)
{
cells.Add(15, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row8Pamount, columnTitleXF83);
}
if (row8Camount != 0M)
{
cells.Add(15, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row8Camount, columnTitleXF83);
}
if (row9Pamount != 0M)
{
cells.Add(16, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row9Pamount, columnTitleXF83);
}
if (row9Camount != 0M)
{
cells.Add(16, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row9Camount, columnTitleXF83);
}
if (row10Pamount != 0M)
{
cells.Add(17, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row10Pamount, columnTitleXF83);
}
if (row10Camount != 0M)
{
cells.Add(17, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row10Camount, columnTitleXF83);
}
if (row11Pamount != 0M)
{
cells.Add(18, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row11Pamount, columnTitleXF83);
}
if (row11Camount != 0M)
{
cells.Add(18, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row11Camount, columnTitleXF83);
}
if (column0TC != 0M)
{
cells.Add(19, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, column0TC, columnTitleXF83);
}
if (column1TP != 0M)
{
cells.Add(19, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, column1TP, columnTitleXF83);
}
}
}
if (row1TC != 0M)
{
cells.Add(8, 11, row1TC, columnTitleXF83);
}
if (row1TP != 0M)
{
cells.Add(8, 12, row1TP, columnTitleXF812);
}
if (row2TC != 0M)
{
cells.Add(9, 11, row2TC, columnTitleXF83);
}
if (row2TP != 0M)
{
cells.Add(9, 12, row2TP, columnTitleXF812);
}
if (row3TC != 0M)
{
cells.Add(10, 11, row3TC, columnTitleXF83);
}
if (row3TP != 0M)
{
cells.Add(10, 12, row3TP, columnTitleXF812);
}
if (row4TC != 0M)
{
cells.Add(11, 11, row4TC, columnTitleXF83);
}
if (row4TP != 0M)
{
cells.Add(11, 12, row4TP, columnTitleXF812);
}
if (row5TC != 0M)
{
cells.Add(12, 11, row5TC, columnTitleXF83);
}
if (row5TP != 0M)
{
cells.Add(12, 12, row5TP, columnTitleXF812);
}
if (row6TC != 0M)
{
cells.Add(13, 11, row6TC, columnTitleXF83);
}
if (row6TP != 0M)
{
cells.Add(13, 12, row6TP, columnTitleXF812);
}
if (row7TC != 0M)
{
cells.Add(14, 11, row7TC, columnTitleXF83);
}
if (row7TP != 0M)
{
cells.Add(14, 12, row7TP, columnTitleXF812);
}
if (row8TC != 0M)
{
cells.Add(15, 11, row8TC, columnTitleXF83);
}
if (row8TP != 0M)
{
cells.Add(15, 12, row8TP, columnTitleXF812);
}
if (row9TC != 0M)
{
cells.Add(16, 11, row9TC, columnTitleXF83);
}
if (row9TP != 0M)
{
cells.Add(16, 12, row9TP, columnTitleXF812);
}
if (row10TC != 0M)
{
cells.Add(17, 11, row10TC, columnTitleXF83);
}
if (row10TP != 0M)
{
cells.Add(17, 12, row10TP, columnTitleXF812);
}
if (row11TC != 0M)
{
cells.Add(18, 11, row11TC, columnTitleXF83);
}
if (row11TP != 0M)
{
cells.Add(18, 12, row11TP, columnTitleXF812);
}
decimal tc = row1TC + row2TC + row3TC + row4TC + row5TC + row6TC + row7TC + row8TC + row9TC + row10TC + row11TC;
if (tc!=0M)
{
cells.Add(19, 11, tc, columnTitleXF83);
}
decimal tp = row1TP + row2TP + row3TP + row4TP + row5TP + row6TP + row7TP + row8TP + row9TP + row11TP;
if (tp != 0M)
{
cells.Add(19, 12, tp, columnTitleXF812);
}
}
xls.Send();
}
public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmssffff", System.Globalization.DateTimeFormatInfo.InvariantInfo);
xls.SummaryInformation.Author = "yongfa365"; //填加xls文件作者信息
xls.SummaryInformation.NameOfCreatingApplication = "liu yongfa"; //填加xls文件创建程序信息
xls.SummaryInformation.LastSavedBy = "LastSavedBy"; //填加xls文件最后保存者信息
xls.SummaryInformation.Comments = "Comments"; //填加xls文件作者信息
xls.SummaryInformation.Title = "title"; //填加xls文件标题信息
xls.SummaryInformation.Subject = "Subject";//填加文件主题信息
xls.DocumentSummaryInformation.Company = "company";//填加文件公司信息
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");//状态栏标题名称
Cells cells = sheet.Cells;
foreach (DataColumn col in dtSource.Columns)
{
Cell cell = cells.Add(1, col.Ordinal + 1, col.ColumnName);
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = true; //字体为粗体
}
#region 填充内容
XF dateStyle = xls.NewXF();
dateStyle.Format = "yyyy-mm-dd";
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int rowIndex = i + 2;
int colIndex = j + 1;
string drValue = dtSource.Rows[i][j].ToString();
switch (dtSource.Rows[i][j].GetType().ToString())
{
case "System.String"://字符串类型
cells.Add(rowIndex, colIndex, drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cells.Add(rowIndex, colIndex, dateV, dateStyle);
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cells.Add(rowIndex, colIndex, boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cells.Add(rowIndex, colIndex, intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cells.Add(rowIndex, colIndex, doubV);
break;
case "System.DBNull"://空值处理
cells.Add(rowIndex, colIndex, null);
break;
default:
cells.Add(rowIndex, colIndex, null);
break;
}
}
}
#endregion
//foreach (DataRow row in dtSource.Rows)
//{
// rowIndex++;
// colIndex = 0;
// foreach (DataColumn col in dtSource.Columns)
// {
// colIndex++;
// cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString());//全都当文本型处理
// }
//}
xls.FileName = strFileName;
xls.Save();
}
public static void PutDemandInfo(string strid, HttpResponseBase rs)
{
string idstr = strid;
//生成Excel开始
XlsDocument xls = new XlsDocument();
xls.FileName = "军队物资集中采购需求计划表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("需求采购任务表");
Worksheet sheet1 = xls.Workbook.Worksheets.Add("需求采购任务分发物资列表");
Worksheet sheet2 = xls.Workbook.Worksheets.Add("需求采购任务分发列表");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
ColumnInfo cinfo1 = new ColumnInfo(xls, sheet1);
ColumnInfo cinfo2 = new ColumnInfo(xls, sheet2);
ColumnInfo cinfo3 = new ColumnInfo(xls, sheet1);
ColumnInfo cinfo4 = new ColumnInfo(xls, sheet1);
ColumnInfo cinfo5 = new ColumnInfo(xls, sheet1);
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Width = 100 * 40;//列宽度
sheet.AddColumnInfo(cinfo);
cinfo1.ColumnIndexStart = 0;//列开始
cinfo1.ColumnIndexEnd = 10;//列结束
cinfo1.Width = 100 * 40;//列宽度
sheet1.AddColumnInfo(cinfo1);
cinfo2.ColumnIndexStart = 0;//列开始
cinfo2.ColumnIndexEnd = 10;//列结束
cinfo2.Width = 100 * 40;//列宽度
sheet2.AddColumnInfo(cinfo2);
cinfo3.ColumnIndexStart = 0;//列开始
cinfo3.ColumnIndexEnd = 0;//列结束
cinfo3.Width = 10000;//列宽度
cinfo4.ColumnIndexStart = 6;//列开始
cinfo4.ColumnIndexEnd = 6;//列结束
cinfo4.Width = 2800;//列宽度
cinfo5.ColumnIndexStart = 8;//列开始
cinfo5.ColumnIndexEnd = 8;//列结束
cinfo5.Width = 2800;//列宽度
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.Font.Height = 20 * 12;
cellXF.Font.Weight = FontWeight.SemiBold;
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Font.Height = 20 * 10;
cellXF1.Font.Weight = FontWeight.Normal;
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
cells.Merge(1, 1, 1, 10);
Cell header = cells.Add(1, 1, "需求采购任务");
header.Font.Height = 250;
header.Font.Weight = FontWeight.Bold;
header.HorizontalAlignment = HorizontalAlignments.Centered;
for (int i = 1; i <= 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(2, i, "管理部门", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(2, i, "包含物资项", cellXF); break;
case 3: cell = cells.Add(2, i, "包含分发项", cellXF); break;
case 4: cell = cells.Add(2, i, "采购机构", cellXF); break;
case 5: cell = cells.Add(2, i, "采购方式建议", cellXF); break;
case 6: cell = cells.Add(2, i, "下达时间", cellXF); break;
case 7: cell = cells.Add(2, i, "完成时间", cellXF); break;
case 8: cell = cells.Add(2, i, "联系人", cellXF); break;
case 9: cell = cells.Add(2, i, "联系电话", cellXF); break;
case 10: cell = cells.Add(2, i, "描述", cellXF); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
需求采购任务 plan = 需求采购任务管理.查找需求采购任务(long.Parse(strid));
for (int n = 1; n <= 10; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(3, n, plan.需求发起单位链接.用户数据.单位信息.单位名称, cellXF1); break;
case 2: cell = cells.Add(3, n, plan.物资列表.Count, cellXF1); break;
case 3: cell = cells.Add(3, n, plan.分发列表.Count, cellXF1); break;
case 4: cell = cells.Add(3, n, plan.当前处理单位链接.用户数据.单位信息.单位名称, cellXF1); break;
case 5: cell = cells.Add(3, n, plan.采购方式.ToString(), cellXF1); break;
case 6: cell = cells.Add(3, n, plan.基本数据.添加时间.ToString("yyyy/MM/dd"), cellXF1); break;
case 7: cell = cells.Add(3, n, plan.建议完成时间.ToString("yyyy/MM/dd"), cellXF1); break;
case 8: cell = cells.Add(3, n, plan.联系人, cellXF1); break;
case 9: cell = cells.Add(3, n, plan.联系电话, cellXF1); break;
case 10: cell = cells.Add(3, n, plan.描述, cellXF1); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
Cells cells1 = sheet1.Cells; //获得指定工作页列集合
cells1.Merge(1, 1, 1, 10);
Cell header1 = cells1.Add(1, 1, "需求采购任务分发物资");
header1.Font.Height = 250;
header1.Font.Weight = FontWeight.Bold;
header1.HorizontalAlignment = HorizontalAlignments.Centered;
for (int i = 1; i <= 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells1.Add(2, i, "物资名称", cellXF); sheet1.AddColumnInfo(cinfo3); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells1.Add(2, i, "规格型号", cellXF); break;
case 3: cell = cells1.Add(2, i, "计量单位", cellXF); break;
case 4: cell = cells1.Add(2, i, "数量", cellXF); break;
case 5: cell = cells1.Add(2, i, "单价", cellXF); break;
case 6: cell = cells1.Add(2, i, "预算金额", cellXF); break;
case 7: cell = cells1.Add(2, i, "质量技术标准", cellXF); sheet1.AddColumnInfo(cinfo4); break;
case 8: cell = cells1.Add(2, i, "交货期限", cellXF); break;
case 9: cell = cells1.Add(2, i, "采购方式建议", cellXF); sheet1.AddColumnInfo(cinfo5); break;
case 10: cell = cells1.Add(2, i, "备注", cellXF); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
for (int i = 0; i < plan.物资列表.Count; i++)
{
for (int n = 1; n <= 10; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.物资名称, cellXF1); break;
case 2: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.规格型号, cellXF1); break;
case 3: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.计量单位, cellXF1); break;
case 4: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.数量, cellXF1); break;
case 5: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.单价, cellXF1); break;
case 6: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.预算金额, cellXF1); break;
case 7: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.技术指标, cellXF1); break;
case 8: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.交货期限.ToString("yyyy/MM/dd"), cellXF1); break;
case 9: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.建议采购方式, cellXF1); break;
case 10: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.备注, cellXF1); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
Cells cells2 = sheet2.Cells; //获得指定工作页列集合
cells2.Merge(1, 1, 1, 9);
Cell header2 = cells2.Add(1, 1, "需求采购任务分发");
header2.Font.Height = 250;
header2.Font.Weight = FontWeight.Bold;
header2.HorizontalAlignment = HorizontalAlignments.Centered;
for (int i = 1; i < 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells2.Add(2, i, "物资名称", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells2.Add(2, i, "规格型号", cellXF); break;
case 3: cell = cells2.Add(2, i, "计量单位", cellXF); break;
case 4: cell = cells2.Add(2, i, "收货单位名称", cellXF); break;
case 5: cell = cells2.Add(2, i, "分配数量", cellXF); break;
case 6: cell = cells2.Add(2, i, "提货方式", cellXF); break;
case 7: cell = cells2.Add(2, i, "运输方式", cellXF); break;
case 8: cell = cells2.Add(2, i, "到站", cellXF); break;
case 9: cell = cells2.Add(2, i, "备注", cellXF); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
for (int i = 0; i < plan.分发列表.Count; i++)
{
for (int n = 1; n <= 9; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.物资名称, cellXF1); break;
case 2: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.规格型号, cellXF1); break;
case 3: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.计量单位, cellXF1); break;
case 4: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.收货单位名称, cellXF1); break;
case 5: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.分配数量, cellXF1); break;
case 6: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.提货方式.ToString(), cellXF1); break;
case 7: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.运输方式.ToString(), cellXF1); break;
case 8: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.到站, cellXF1); break;
case 9: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.备注, cellXF1); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
public static void PutOutExcel(string strid, HttpResponseBase rs)//导出电话号码
{
string idstr = strid;
//生成Excel开始
XlsDocument xls = new XlsDocument();
xls.FileName = "供应商信息电话号码" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("供应商信息电话号码");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 0;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 2; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "供应商名称", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, 2, "联系号码", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
List<供应商> model = new List<供应商>();
if (!string.IsNullOrWhiteSpace(idstr))
{
string[] sid = strid.Split(',');
for (int j = 0; j < sid.Length; j++)
{
if (!string.IsNullOrWhiteSpace(sid[j]))
{
model.Add(用户管理.查找用户<供应商>(long.Parse(sid[j])));
}
}
}
for (int m = 0; m < model.Count(); m++)
{
for (int n = 1; n <= 2; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, model[m].企业基本信息.企业名称, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, model[m].企业联系人信息.联系人手机, cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
public static void PutExcel(string type, string strid, HttpResponseBase rs)//导出供应商所有信息
{
string idstr = strid;
//生成Excel开始
XlsDocument xls = new XlsDocument();
if (type == "supplier")
{
xls.FileName = "供应商信息表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("供应商");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 7;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 8; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "供应商名称", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, i, "认证级别", cellXF); break;
case 3: cell = cells.Add(1, i, "联系人", cellXF); break;
case 4: cell = cells.Add(1, i, "联系电话", cellXF); break;
case 5: cell = cells.Add(1, i, "主要经营范围", cellXF); break;
case 6: cell = cells.Add(1, i, "是否是全军物资采购供应商", cellXF); break;
case 7: cell = cells.Add(1, i, "供应商类别", cellXF); break;
case 8: cell = cells.Add(1, i, "备注", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
List<供应商> model = new List<供应商>();
if (!string.IsNullOrWhiteSpace(idstr))
{
string[] sid = strid.Split(',');
for (int j = 0; j < sid.Length; j++)
{
if (!string.IsNullOrWhiteSpace(sid[j]))
{
model.Add(用户管理.查找用户<供应商>(long.Parse(sid[j])));
}
}
}
for (int m = 0; m < model.Count(); m++)
{
for (int n = 1; n <= 8; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, model[m].企业基本信息.企业名称, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, model[m].供应商用户信息.认证级别.ToString(), cellXF1); break;
case 3: cell = cells.Add(m + 2, n, model[m].企业联系人信息.联系人姓名, cellXF1); break;
case 4: cell = cells.Add(m + 2, n, model[m].企业联系人信息.联系人手机, cellXF1); break;
case 5: cell = cells.Add(m + 2, n, model[m].营业执照信息.经营范围, cellXF1); break;
case 6: cell = cells.Add(m + 2, n, model[m].供应商用户信息.用户来源.ToString(), cellXF1); break;
case 7:
if (model[m].供应商用户信息.协议供应商 && model[m].供应商用户信息.应急供应商)
{
cell = cells.Add(m + 2, n, "应急协议供应商", cellXF1);
}
else if (model[m].供应商用户信息.应急供应商 && !model[m].供应商用户信息.协议供应商)
{
cell = cells.Add(m + 2, n, "应急供应商", cellXF1);
}
else if (model[m].供应商用户信息.协议供应商 && !model[m].供应商用户信息.应急供应商)
{
cell = cells.Add(m + 2, n, "协议供应商", cellXF1);
}
else
{
cell = cells.Add(m + 2, n, "普通供应商", cellXF1);
}
break;
case 8: cell = cells.Add(m + 2, n, "", cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
}
else
{
xls.FileName = "单位用户列表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("单位用户");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 9;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "登陆账号", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, i, "单位名称", cellXF); break;
case 3: cell = cells.Add(1, i, "单位代号", cellXF); break;
case 4: cell = cells.Add(1, i, "所属管理单位", cellXF); break;
case 5: cell = cells.Add(1, i, "级别", cellXF); break;
case 6: cell = cells.Add(1, i, "联系人", cellXF); break;
case 7: cell = cells.Add(1, i, "联系人职务", cellXF); break;
case 8: cell = cells.Add(1, i, "联系电话", cellXF); break;
case 9: cell = cells.Add(1, i, "手机", cellXF); break;
case 10: cell = cells.Add(1, i, "联系地址", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
List<单位用户> model = new List<单位用户>();
if (!string.IsNullOrWhiteSpace(idstr))
{
string[] sid = strid.Split(',');
for (int j = 0; j < sid.Length; j++)
{
if (!string.IsNullOrWhiteSpace(sid[j]))
{
model.Add(用户管理.查找用户<单位用户>(long.Parse(sid[j])));
}
}
}
for (int m = 0; m < model.Count(); m++)
{
for (int n = 1; n <= 8; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, model[m].登录信息.登录名, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, model[m].单位信息.单位名称, cellXF1); break;
case 3: cell = cells.Add(m + 2, n, model[m].单位信息.单位代号, cellXF1); break;
case 4: cell = cells.Add(m + 2, n, model[m].单位信息.所属单位, cellXF1); break;
case 5: cell = cells.Add(m + 2, n, model[m].单位信息.单位级别.ToString(), cellXF1); break;
case 6: cell = cells.Add(m + 2, n, model[m].联系方式.联系人, cellXF1); break;
case 7: cell = cells.Add(m + 2, n, model[m].联系人职务, cellXF1); break;
case 8:cell = cells.Add(m + 2, n, model[m].联系方式.固定电话, cellXF1);break;
case 9: cell = cells.Add(m + 2, n, model[m].联系方式.手机, cellXF1); break;
case 10: cell = cells.Add(m + 2, n, model[m].所属地域.地域, cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
public static void OutBidGysExcel(string strid, HttpResponseBase rs)
{
string idstr = strid;
//生成Excel开始
XlsDocument xls = new XlsDocument();
xls.FileName = "报价供应商信息表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("报价供应商");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 7;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 4; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "序号", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, i, "供应商名称", cellXF); break;
case 3: cell = cells.Add(1, i, "报价（包括费用）", cellXF); break;
case 4: cell = cells.Add(1, i, "备注", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
var bid = 网上竞标管理.查找网上竞标(long.Parse(strid));
var bjgys = bid.报价供应商列表;
for (int m = 0; m < bjgys.Count(); m++)
{
for (int n = 1; n <= 4; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, m + 1, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, bjgys[m].报价供应商.用户数据.企业基本信息.企业名称, cellXF1); break;
case 3: cell = cells.Add(m + 2, n, bjgys[m].总价, cellXF1); break;
case 4: cell = cells.Add(m + 2, n, bjgys[m].备注, cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
for (int i = 1; i <= 4; i++)
{
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(bjgys.Count + 2, i, "", cellXF1); break;
case 2: cell = cells.Add(bjgys.Count + 2, i, "", cellXF1); break;
case 3: cell = cells.Add(bjgys.Count + 2, i, "参与人员签字：", cellXF1); break;
case 4: cell = cells.Add(bjgys.Count + 2, i, "", cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
public static void PutExcelAll(HttpResponseBase rs)//导出供单位用户所有信息
{
//生成Excel开始
XlsDocument xls = new XlsDocument();
xls.FileName = "单位用户列表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("单位用户");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 9;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.TopLineStyle = 1;
cellXF.TopLineColor = Colors.Black;
cellXF.BottomLineStyle = 1;
cellXF.BottomLineColor = Colors.Black;
cellXF.TopLineStyle = 1;
cellXF.TopLineColor = Colors.Black;
cellXF.LeftLineStyle = 1;
cellXF.LeftLineColor = Colors.Black;
cellXF.RightLineStyle = 1;
cellXF.RightLineColor = Colors.Black;
cellXF.Font.Height = 24 * 16;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.Font.Height = 24 * 14;
cellXF1.UseBorder = true;
cellXF1.BottomLineStyle = 1;
cellXF1.BottomLineColor = Colors.Black;
cellXF1.TopLineStyle = 1;
cellXF1.TopLineColor = Colors.Black;
cellXF1.LeftLineStyle = 1;
cellXF1.LeftLineColor = Colors.Black;
cellXF1.RightLineStyle = 1;
cellXF1.RightLineColor = Colors.Black;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "登陆账号", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, i, "单位名称", cellXF); break;
case 3: cell = cells.Add(1, i, "单位代号", cellXF); break;
case 4: cell = cells.Add(1, i, "所属管理单位", cellXF); break;
case 5: cell = cells.Add(1, i, "级别", cellXF); break;
case 6: cell = cells.Add(1, i, "联系人", cellXF); break;
case 7: cell = cells.Add(1, i, "联系人职务", cellXF); break;
case 8: cell = cells.Add(1, i, "联系电话", cellXF); break;
case 9: cell = cells.Add(1, i, "手机", cellXF); break;
case 10: cell = cells.Add(1, i, "联系地址", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
IEnumerable<单位用户> model = 用户管理.查询用户<单位用户>(0,0);
for (int m = 0; m < model.Count(); m++)
{
for (int n = 1; n <= 10; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, model.ElementAt(m).登录信息.登录名, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, model.ElementAt(m).单位信息.单位名称, cellXF1); break;
case 3: cell = cells.Add(m + 2, n, model.ElementAt(m).单位信息.单位代号, cellXF1); break;
case 4: cell = cells.Add(m + 2, n, model.ElementAt(m).单位信息.所属单位, cellXF1); break;
case 5: cell = cells.Add(m + 2, n, model.ElementAt(m).单位信息.单位级别.ToString(), cellXF1); break;
case 6: cell = cells.Add(m + 2, n, model.ElementAt(m).联系方式.联系人, cellXF1); break;
case 7: cell = cells.Add(m + 2, n, model.ElementAt(m).联系人职务, cellXF1); break;
case 8: cell = cells.Add(m + 2, n, model.ElementAt(m).联系方式.固定电话, cellXF1); break;
case 9: cell = cells.Add(m + 2, n, model.ElementAt(m).联系方式.手机, cellXF1); break;
case 10: cell = cells.Add(m + 2, n, model.ElementAt(m).所属地域.地域, cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
static void SaveXLS(List<string> codes)
{
try
{
XlsDocument xlsDoc = new XlsDocument();
xlsDoc.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
XF titleXF = xlsDoc.NewXF();
titleXF.Font.Bold = true;
titleXF.Font.FontName = "宋体";
Worksheet sheet = xlsDoc.Workbook.Worksheets.Add("sheet1");
for (int i = 0; i < codes.Count; i++)
{
sheet.Cells.Add(i + 1, 1, codes[i]);
}
xlsDoc.Save();
}
catch (Exception ex)
{
throw new Exception("生成Excel文件失败", ex);
}
}
/// <summary>
/// Export data to excel.
/// </summary>
/// <param name="fileName">fileName</param>
/// <param name="sheetName">sheetName</param>
/// <param name="title">title</param>
/// <param name="subtitle">subtitle</param>
/// <param name="datas">datas</param>
/// <param name="colors">colors</param>
public static void ExportDataToExcel(String fileName, String sheetName, String title, String subtitle, DataTable data, Dictionary <Int32, Excel.Color> colors)
{
if (data == null)
{
MessageBox.Show("数据源为空，导出数据失败。", "系统警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (data.Columns.Count == 0)
{
MessageBox.Show("数据源无任何列信息，导出数据失败。", "系统警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (data.Rows.Count == 0)
{
MessageBox.Show("数据源无数据，导出数据失败。", "系统警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (data == null || data.Columns.Count == 0)
{
return;
}
if (String.IsNullOrWhiteSpace(fileName))
{
fileName = String.Format("{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));
}
if (String.IsNullOrWhiteSpace(sheetName))
{
sheetName = "ExcelSheet";
}
//创建Excel管理对象
var xls = new Excel.XlsDocument();
xls.SummaryInformation.Title = title;
xls.SummaryInformation.Author = "Intelligent Access Management System";
xls.DocumentSummaryInformation.Company = "Delta GreenTech(China) Co., Ltd.";
xls.SummaryInformation.Comments = null;
//Sheet标题样式
var titleXF = xls.NewXF();
titleXF.HorizontalAlignment = Excel.HorizontalAlignments.Centered;
titleXF.VerticalAlignment = Excel.VerticalAlignments.Centered;
titleXF.UseBorder = true;
titleXF.BottomLineStyle = 1;
titleXF.BottomLineColor = Excel.Colors.Black;
titleXF.RightLineStyle = 1;
titleXF.RightLineColor = Excel.Colors.Black;
titleXF.Font.Bold = true;
titleXF.Font.Height = 15 * 20;
//Sheet副标题样式
var subtitleXF = xls.NewXF();
subtitleXF.HorizontalAlignment = Excel.HorizontalAlignments.Right;
subtitleXF.VerticalAlignment = Excel.VerticalAlignments.Centered;
subtitleXF.UseBorder = true;
subtitleXF.RightLineStyle = 1;
subtitleXF.RightLineColor = Excel.Colors.Black;
subtitleXF.Font.Bold = false;
subtitleXF.Font.Height = 10 * 20;
//列标题样式
var coltitleXF = xls.NewXF();
coltitleXF.HorizontalAlignment = Excel.HorizontalAlignments.Centered;
coltitleXF.VerticalAlignment = Excel.VerticalAlignments.Centered;
coltitleXF.UseBorder = true;
coltitleXF.TopLineStyle = 1;
coltitleXF.TopLineColor = Excel.Colors.Black;
coltitleXF.BottomLineStyle = 1;
coltitleXF.BottomLineColor = Excel.Colors.Black;
coltitleXF.RightLineStyle = 1;
coltitleXF.RightLineColor = Excel.Colors.Black;
coltitleXF.Pattern = 1;
coltitleXF.PatternBackgroundColor = Excel.Colors.Grey;
coltitleXF.PatternColor = Excel.Colors.Grey;
coltitleXF.Font.Bold = true;
coltitleXF.Font.Height = 11 * 20;
coltitleXF.Font.ColorIndex = 1;
//数据单元格样式
var dataXF = xls.NewXF();
dataXF.HorizontalAlignment = Excel.HorizontalAlignments.Left;
dataXF.VerticalAlignment = Excel.VerticalAlignments.Centered;
dataXF.UseBorder = true;
dataXF.RightLineStyle = 1;
dataXF.RightLineColor = Excel.Colors.Black;
dataXF.BottomLineStyle = 1;
dataXF.BottomLineColor = Excel.Colors.Black;
dataXF.UseProtection = false;
dataXF.TextWrapRight = true;
dataXF.Font.Height = 10 * 20;
var sheetSize = 50000;
var sheetCount = 1;
if (data.Rows.Count > sheetSize)
{
sheetCount = (int)Math.Ceiling((float)data.Rows.Count / (float)sheetSize);
}
for (var i = 1; i <= sheetCount; i++)
{
//创建表单
Excel.Worksheet sheet;
if (sheetCount == 1)
{
sheet = xls.Workbook.Worksheets.Add(sheetName);
}
else
{
sheet = xls.Workbook.Worksheets.Add(String.Format("{0}-{1}", sheetName, i));
}
//设置标题栏
ushort startDataIndex = 1;
if (!String.IsNullOrWhiteSpace(title))
{
for (var k = 1; k <= data.Columns.Count; k++)
{
sheet.Cells.Add(1, k, title, titleXF);
}
sheet.AddMergeArea(new Excel.MergeArea(1, 1, 1, data.Columns.Count));
sheet.Rows[1].RowHeight = 40 * 20;
startDataIndex++;
}
//设置副标题
if (!String.IsNullOrWhiteSpace(subtitle))
{
for (var k = 1; k <= data.Columns.Count; k++)
{
sheet.Cells.Add(2, k, subtitle, subtitleXF);
}
sheet.AddMergeArea(new Excel.MergeArea(2, 2, 1, data.Columns.Count));
sheet.Rows[2].RowHeight = 20 * 20;
startDataIndex++;
}
//列设置
var col = new Excel.ColumnInfo(xls, sheet);
col.ColumnIndexStart = 0;
col.ColumnIndexEnd = (ushort)(data.Columns.Count - 1);
col.Width = 20 * 256;
sheet.AddColumnInfo(col);
//设置列标题
for (var k = 1; k <= data.Columns.Count; k++)
{
sheet.Cells.Add(startDataIndex, k, data.Columns[k - 1].ColumnName, coltitleXF);
}
sheet.Rows[startDataIndex].RowHeight = 20 * 20;
startDataIndex++;
for (var k = 0; k < sheetSize; k++)
{
int r = (i - 1) * sheetSize + k;
if (r >= data.Rows.Count)
{
break;
}
if (colors != null && colors.ContainsKey(r))
{
dataXF.Pattern = 1;
dataXF.PatternBackgroundColor = colors[r];
dataXF.PatternColor = colors[r];
}
else
{
dataXF.Pattern = 1;
dataXF.PatternBackgroundColor = Excel.Colors.White;
dataXF.PatternColor = Excel.Colors.White;
}
for (var g = 1; g <= data.Columns.Count; g++)
{
sheet.Cells.Add(startDataIndex, g, data.Rows[r][g - 1], dataXF);
}
startDataIndex++;
}
}
var dataSaveFileDialog = new SaveFileDialog();
dataSaveFileDialog.DefaultExt = "xls";
dataSaveFileDialog.FileName = fileName;
dataSaveFileDialog.Filter = "Excel 工作薄|*.xls|所有文件|*.*";
dataSaveFileDialog.Title = "文件另存为";
if (dataSaveFileDialog.ShowDialog() == DialogResult.OK)
{
using (var fs = new FileStream(dataSaveFileDialog.FileName, FileMode.Create)) {
fs.Write(xls.Bytes.ByteArray, 0, xls.Bytes.Length);
fs.Flush();
MessageBox.Show("数据导出完成", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}