public void ColumnWidth5120() { ushort colWidth = 5120; MyXlsTestFixture.XlsDocumentDelegate docDelegate = delegate(XlsDocument doc) { Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1"); ColumnInfo columnInfo = new ColumnInfo(doc, sheet); sheet.AddColumnInfo(columnInfo); columnInfo.Width = colWidth; Assert.AreEqual(colWidth, columnInfo.Width, "Column Width setting"); }; string fileName = WriteDocument(docDelegate); //48.762 string actualString = GetCellPropertyViaExcelOle(fileName, CellProperties.Width); double actual = double.NaN; Assert.IsTrue(double.TryParse(actualString, out actual), "Column width didn't parse"); Assert.AreEqual(colWidth / 48.762, actual, 0.01, "Column width"); //NOTE: This factor (48.762) depends on the default (first) font in the file }
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 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); }
/// <summary> /// Adds a Column Info record to this Worksheet. /// </summary> /// <param name="columnInfo">The ColumnInfo object to add to this Worksheet.</param> public void AddColumnInfo(ColumnInfo columnInfo) { //TODO: Implement existence checking & deletion / overwriting / not-adding //NOTE: Don't know if this is necessary (i.e. does Excel allow "adding" values of overlapping ColInfos? _columnInfos.Add(columnInfo); }
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 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 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); }
/// <summary> /// 导出Excel /// </summary> /// <param name="path"></param> public void Export(string path) { PrintHeader(); PrintContent(); PrintFooter(); ColumnInfo colInfo = new ColumnInfo(XlsDoc, CurSheet); colInfo.ColumnIndexStart = 0; colInfo.ColumnIndexEnd = (ushort)(Content.ColumnsCount - 1); colInfo.Width = 256 * 15; CurSheet.AddColumnInfo(colInfo); XlsDoc.FileName = Path.GetFileName(path); XlsDoc.Save(Path.GetDirectoryName(path), true); }
/// <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); } } }