protected virtual void SetSheetColumnsWith(Worksheet worksheet, int sheetIndex, List <ColumnData> columnSetting, DataTable data) { for (int i = 0; i < columnSetting.Count; i++) { ColumnInfo col; if (!columnSetting[i].Width.HasValue) { // 如果是DateTime类型的列,设默认长度20; if (data != null && data.Rows != null && data.Rows.Count > 0) { int rowIndex = 0; object t = null; while ((t == null || t == DBNull.Value) && rowIndex < data.Rows.Count) { t = data.Rows[rowIndex][columnSetting[i].FieldIndex.Value]; rowIndex++; } if (t != null && t != DBNull.Value && (t.GetType() == typeof(DateTime) || t.GetType() == typeof(DateTime?))) { col = new ColumnInfo(this.m_xlsDocument, worksheet); col.Width = (ushort)(20 * this.COLUMN_WIDTH_SCALE); col.ColumnIndexStart = (ushort)(i); col.ColumnIndexEnd = (ushort)(i); worksheet.AddColumnInfo(col); } } continue; } col = new ColumnInfo(this.m_xlsDocument, worksheet); col.Width = (ushort)(columnSetting[i].Width.Value * this.COLUMN_WIDTH_SCALE); col.ColumnIndexStart = (ushort)(i); col.ColumnIndexEnd = (ushort)(i); worksheet.AddColumnInfo(col); } }
protected void Test() { XlsDocument xls = new XlsDocument(); xls.FileName = "D:\\12333333.xls"; string sheetName = "chc 实例"; Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"chc 实例"的sheet页 Cells cells = sheet.Cells; //Cells实例是sheet页中单元格(cell)集合 //单元格1-base Cell cell = cells.Add(1, 2, "抗"); //设定第一行,第二例单元格的值 cell.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中 cell.Font.FontName = "方正舒体"; //设定字体 cell.Font.Height = 20 * 20; //设定字大小(字体大小是以 1/20 point 为单位的) cell.UseBorder = true; //使用边框 cell.BottomLineStyle = 2; //设定边框底线为粗线 cell.BottomLineColor = Colors.DarkRed; //设定颜色为暗红 //cell的格式还可以定义在一个xf对象中 CellFormat cellXF = xls.NewXF(); //为xls生成一个XF实例(XF是cell格式对象) cellXF.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中 cellXF.Font.FontName = "方正舒体"; //设定字体 cellXF.Font.Height = 20 * 20; //设定字大小(字体大小是以 1/20 point 为单位的) cellXF.UseBorder = true; //使用边框 cellXF.BottomLineStyle = 2; //设定边框底线为粗线 cellXF.BottomLineColor = Colors.DarkRed; //设定颜色为暗红 cell = cells.Add(2, 2, "震", cellXF); //以设定好的格式填加cell cellXF.Font.FontName = "仿宋_GB2312"; cell = cells.Add(3, 2, "救", cellXF); //格式可以多次使用 ColumnInfo colInfo = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo.ColumnIndexStart = 1; //起始列为第二列 colInfo.ColumnIndexEnd = 5; //终止列为第六列 colInfo.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) colInfo.ColumnIndexEnd = 6; //可以更改列对象的值 ColumnInfo colInfo2 = new ColumnInfo(xls, sheet); //通过新生成一个列格式对象,才到能设定其它列宽度 colInfo2.ColumnIndexStart = 7; colInfo2.ColumnIndexEnd = 8; colInfo2.Width = 1 * 256; sheet.AddColumnInfo(colInfo2); MergeArea meaA = new MergeArea(1, 2, 3, 4); //一个合并单元格实例(合并第一行、第三例 到 第二行、第四例) sheet.AddMergeArea(meaA); //填加合并单元格 cellXF.VerticalAlignment = VerticalAlignments.Centered; cellXF.Font.Height = 48 * 20; cellXF.Font.Bold = true; cellXF.Pattern = 3; //设定单元格填充风格。如果设定为0,则是纯色填充 cellXF.PatternBackgroundColor = Colors.DarkRed; //填充的底色 cellXF.PatternColor = Colors.DarkGreen; //设定填充线条的颜色 cell = cells.Add(1, 3, "灾", cellXF); xls.Save(); }
/// <summary> /// 绑定数据库生成XLS报表 /// </summary> /// <param name="ds">获取DataSet数据集</param> /// <param name="xlsName">报表表名</param> private void xlsGridview(DataTable dt, string xlsName) { XlsDocument xls = new XlsDocument(); xls.FileName = Server.UrlEncode(xlsName); int rowIndex = 1; int colIndex = 0; Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称 //设置列格式 ColumnInfo colInfo = new ColumnInfo(xls, sheet); colInfo.ColumnIndexStart = 0; colInfo.ColumnIndexEnd = 8; colInfo.Width = 20 * 256; sheet.AddColumnInfo(colInfo); //设置样式 XF xf = xls.NewXF(); xf.HorizontalAlignment = HorizontalAlignments.Centered; xf.VerticalAlignment = VerticalAlignments.Centered; xf.TextWrapRight = true; xf.UseBorder = true; xf.TopLineStyle = 1; xf.TopLineColor = Colors.Black; xf.BottomLineStyle = 1; xf.BottomLineColor = Colors.Black; xf.LeftLineStyle = 1; xf.LeftLineColor = Colors.Black; xf.RightLineStyle = 1; xf.RightLineColor = Colors.Black; xf.Font.Bold = true; Cells cells = sheet.Cells; foreach (DataColumn col in dt.Columns) { colIndex++; Cell cell = cells.Add(1, colIndex, col.ColumnName, xf); cell.Font.Bold = true; } foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dt.Columns) { colIndex++; Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型 //如果你数据库里的数据都是数字的话 最好转换一下,不然导入到Excel里是以字符串形式显示。 cell.Font.FontFamily = FontFamilies.Roman; //字体 cell.Font.Bold = false; //字体为粗体 } } xls.Send(); Response.Flush(); Response.End(); }
/// <summary> /// 调整表格各列的宽度 /// </summary> private void AdjustColumnWidth() { for (ushort i = 1; i <= columnCount; i++) { int width = GetMaxColumnWidth(i); ColumnInfo columnInfo = new ColumnInfo(xls, worksheet); columnInfo.ColumnIndexStart = (ushort)(i - 1); //索引从0开始 columnInfo.ColumnIndexEnd = (ushort)(i - 1); //索引从0开始 columnInfo.Width = (ushort)((width + 2) * 256); worksheet.AddColumnInfo(columnInfo); } ; }
/// <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); }
protected void CreateExcelColumns(Worksheet worksheet, ushort width, params ushort[] colIndexs) { if (colIndexs.Length > 0) { ColumnInfo col; for (int i = 0; i < colIndexs.Length; i++) { col = new ColumnInfo(this.m_xlsDocument, worksheet); col.Width = (ushort)(width * this.COLUMN_WIDTH_SCALE); col.ColumnIndexStart = colIndexs[i]; col.ColumnIndexEnd = colIndexs[i]; worksheet.AddColumnInfo(col); } } }
/// <summary> /// Cria a tabela que receberá os dados na planilha /// </summary> public static void CreateDataTable(String[] columnNames, int[] columnWidths, int rowCount) { // Define o nome da planilha e a largura das colunas fileStream = new FileStream(@"C:\work\CadastroProdutos.xls", FileMode.Create, FileAccess.ReadWrite); document = new XlsDocument(); worksheet = document.Workbook.Worksheets.Add("Cadastro de Produtos"); ColumnInfo info = new ColumnInfo(document, worksheet); info.ColumnIndexStart = 1; info.ColumnIndexEnd = (ushort)columnNames.Length; info.Width = 5100; worksheet.AddColumnInfo(info); // Prepara o plano de fundo da planilha for (int row = 1; row < rowCount + 10; row++) { for (int col = 1; col < columnNames.Length + 3; col++) { SetCellPattern(row, col); } } // Insere o título (agrupa as celulas que fazem parte do título) worksheet.Cells.Merge(5, 5, 2, columnNames.Length + 1); Cell titleCell = worksheet.Cells.Add(5, 2, "Cadastro de Produtos"); titleCell.HorizontalAlignment = HorizontalAlignments.Centered; titleCell.Font.Height = 400; titleCell.Font.Bold = true; titleCell.Font.FontFamily = FontFamilies.Roman; // Cria celulas com os nomes das colunas for (int ndx = 0; ndx < columnNames.Length; ndx++) { Cell columnName = worksheet.Cells.Add(6, ndx + 2, columnNames[ndx]); columnName.Font.Bold = true; columnName.PatternColor = Colors.Default1F; columnName.Pattern = 1; SetCellBorder(columnName); } }
/// <summary> /// Cria a tabela que receberá os dados do relatório /// </summary> public void CreateDataTable(String[] columnNames, int[] columnWidths, int rowCount) { // Define o nome da planilha e a largura das colunas reportSheet = document.Workbook.Worksheets.Add(reportHeaders[0]); ColumnInfo info = new ColumnInfo(document, reportSheet); info.ColumnIndexStart = 1; info.ColumnIndexEnd = (ushort)columnNames.Length; info.Width = 5100; reportSheet.AddColumnInfo(info); // Prepara o plano de fundo da planilha this.rowCount = rowCount; for (int row = 1; row < rowCount + 10; row++) { for (int col = 1; col < columnNames.Length + 3; col++) { SetCellPattern(row, col); } } // Insere o cabeçalho da planilha ProcessHeaders(columnNames.Length); // Cria celulas com os nomes das colunas for (int ndx = 0; ndx < columnNames.Length; ndx++) { Cell columnName = reportSheet.Cells.Add(6, ndx + 2, columnNames[ndx]); columnName.Font.Bold = true; columnName.PatternColor = Colors.Default1F; columnName.Pattern = 1; SetCellBorder(columnName); } // Cria o totalizador totalizer = new ReportTotalizer(columnNames.Length); }
/// <summary> /// 利用MyXls控件的一个方法,导出到电子表格 /// </summary> /// <param name="dsxls">数据集,列明应该是中文滴</param> /// <param name="filename">导出文件名</param> /// <param name="sheetsname">导出的sheet名</param> /// <param name="title">大标题列表,根据dt中表格的顺序添加</param> /// <param name="title2">大标题2</param> /// <param name="rowwidth">每个列的宽度</param> /// <param name="lujing">保存路径</param> public static void goxls_3(DataSet dsxls, string filename, string sheetsname, ArrayList title, string title2, int rowwidth, string lujing) { org.in2bits.MyXls.XlsDocument doc = new XlsDocument(); doc.FileName = filename; Worksheet sheet = doc.Workbook.Worksheets.Add(sheetsname); Cells cells = sheet.Cells; ColumnInfo colInfo = new ColumnInfo(doc, sheet); colInfo.ColumnIndexStart = 0; colInfo.ColumnIndexEnd = (ushort)(dsxls.Tables[0].Columns.Count - 1); colInfo.Width = (ushort)(rowwidth * 256); sheet.AddColumnInfo(colInfo); XF xf_col = doc.NewXF(); xf_col.HorizontalAlignment = HorizontalAlignments.Centered; xf_col.VerticalAlignment = VerticalAlignments.Centered; xf_col.Pattern = 1; xf_col.PatternColor = Colors.Grey; xf_col.UseBorder = true; xf_col.TopLineStyle = 1; xf_col.TopLineColor = Colors.Black; xf_col.BottomLineStyle = 1; xf_col.BottomLineColor = Colors.Black; xf_col.LeftLineStyle = 1; xf_col.LeftLineColor = Colors.Black; xf_col.RightLineStyle = 1; xf_col.RightLineColor = Colors.Black; xf_col.Font.Bold = true; xf_col.Font.Height = 10 * 20; xf_col.Font.ColorIndex = 1; int totalrow = 0; for (int i = 0; i < dsxls.Tables.Count; i++) { int rowCount = dsxls.Tables[i].Rows.Count; int colCount = dsxls.Tables[i].Columns.Count; totalrow = totalrow + dsxls.Tables[i].Rows.Count; int rowMin = i > 0 ? totalrow - rowCount + (i * 3) + 1 : 1; int colMin = 1; int rowtitle = 0; string title1 = title[i].ToString(); if (title1.Trim() != "" && title2.Trim() != "") { MergeArea meaA1 = new MergeArea(rowMin, rowMin, 1, dsxls.Tables[i].Columns.Count); sheet.AddMergeArea(meaA1);//填加合并单元格 Cell cell_title1 = cells.Add(rowMin, 1, title[i]); cell_title1.Font.Height = 12 * 18; cell_title1.HorizontalAlignment = HorizontalAlignments.Centered; cell_title1.Font.Bold = true; MergeArea meaA2 = new MergeArea(rowMin + 1, rowMin + 1, 1, dsxls.Tables[i].Columns.Count); sheet.AddMergeArea(meaA2);//填加合并单元格 Cell cell_title2 = cells.Add(rowMin + 1, 1, title2); cell_title2.Font.Height = 10 * 20; rowtitle = 2; } for (int row = rowtitle; row < rowCount + rowtitle + 1; row++) { if (row == rowtitle) { for (int col = 1; col <= colCount; col++) { Cell cell = cells.Add(rowMin + row, colMin + col - 1, dsxls.Tables[i].Columns[col - 1].ColumnName, xf_col); } } else { for (int col = 1; col <= colCount; col++) { Cell cell = cells.Add(rowMin + row, colMin + col - 1, dsxls.Tables[i].Rows[row - 1 - rowtitle][col - 1].ToString()); } } } } doc.Save(lujing, true); //doc.Send(); //Response.Flush(); //Response.End(); }
public void ProcessRequest(HttpContext context) { //导表智能中心订单进度表 List <OrderInfoSchedu> orderInfoSchedus = new List <OrderInfoSchedu>(); int mounth = DateTime.Now.Month; int year = DateTime.Now.Year; using (JDJS_WMS_DB_USEREntities wms = new JDJS_WMS_DB_USEREntities()) { var orders = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Intention == 2 || r.Intention == 3 || r.Intention == 4); foreach (var item in orders) { int orderID = Convert.ToInt32(item.Order_ID); if (item.Intention == 4) { var overProcess = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.OrderID == orderID && r.isFlag != 0); var endTime = overProcess.OrderByDescending(r => r.EndTime).FirstOrDefault(); if (endTime != null) { var time = Convert.ToDateTime(endTime.EndTime).Month; var yeartime = Convert.ToDateTime(endTime.EndTime).Year; if (time != mounth && year != yeartime) { break; } } } OrderInfoSchedu orderInfoSchedu = new OrderInfoSchedu(); var orderInf0 = wms.JDJS_WMS_Order_Guide_Schedu_Table.Where(r => r.OrderID == orderID).FirstOrDefault(); if (orderInf0 != null) { orderInfoSchedu.Client = orderInf0.ClientName; if (orderInf0.EndTime != null) { orderInfoSchedu.EndTime = Convert.ToDateTime(orderInf0.EndTime).Date.ToShortDateString(); } else { orderInfoSchedu.EndTime = "/"; } if (orderInf0.ExpectEndTime != null) { orderInfoSchedu.ExpectEndTime = Convert.ToDateTime(orderInf0.ExpectEndTime).Date.ToShortDateString(); } else { orderInfoSchedu.ExpectEndTime = "/"; } if (orderInf0.FileDownTime != null) { orderInfoSchedu.FileDownTime = Convert.ToDateTime(orderInf0.FileDownTime).Date.ToShortDateString(); } else { orderInfoSchedu.FileDownTime = "/"; } } orderInfoSchedu.Name = item.Product_Name; orderInfoSchedu.OrderNum = item.Order_Number; orderInfoSchedu.EngineerName = item.Engine_Program_Manager; var processes = wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.OrderID == orderID && r.sign != 0); orderInfoSchedu.jiawei = processes.Count().ToString(); var schedu1 = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.OrderID == orderID && r.isFlag == 1); var schedu2 = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.OrderID == orderID && r.isFlag == 2); var schedu3 = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.OrderID == orderID && r.isFlag == 3); if (schedu1.Count() > 0 && schedu2.Count() < 1 && schedu3.Count() < 1) { orderInfoSchedu.State = "等待生产中"; var blankInfo = wms.JDJS_WMS_Blank_Table.Where(r => r.OrderID == orderID); if (blankInfo.Count() < 1) { orderInfoSchedu.State = "待料"; } var toolInfo = wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.OrderID == orderID && r.sign != 0 && r.toolPreparation == 1); if (toolInfo.Count() < 1) { orderInfoSchedu.State = "待备刀装刀"; } } if (schedu1.Count() < 1 && schedu2.Count() < 1 && schedu3.Count() < 1) { orderInfoSchedu.State = "编程中"; } if ((schedu2.Count() > 0)) { orderInfoSchedu.State = "生产中"; } if (schedu1.Count() > 0 && schedu3.Count() > 0) { orderInfoSchedu.State = "生产中"; } if (schedu1.Count() < 1 && schedu2.Count() < 1 && schedu3.Count() > 0) { orderInfoSchedu.State = "已完成"; } orderInfoSchedus.Add(orderInfoSchedu); } } XlsDocument doc = new XlsDocument(); doc.FileName = DateTime.Now.ToString("yyyyMMddhhmmssms") + "订单进度" + ".xls"; Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1"); Cells cells = sheet.Cells; ColumnInfo col = new ColumnInfo(doc, sheet); //创建列样式对象 col.ColumnIndexStart = 2; //起始列,索引从0开始 col.ColumnIndexEnd = 2; //结束列,索引从0开始,这样为第1列、第2列使用此样式 col.Width = 8888; //宽度,字节长度,ushort类型 0~65535 sheet.AddColumnInfo(col); //将列样式作用于此工作表 ColumnInfo col2 = new ColumnInfo(doc, sheet); //创建列样式对象 col2.ColumnIndexStart = 3; //起始列,索引从0开始 col2.ColumnIndexEnd = 3; //结束列,索引从0开始,这样为第1列、第2列使用此样式 col2.Width = 3333; //宽度,字节长度,ushort类型 0~65535 sheet.AddColumnInfo(col2); //将列样式作用于此工作表 ColumnInfo col6 = new ColumnInfo(doc, sheet); //创建列样式对象 col6.ColumnIndexStart = 6; //起始列,索引从0开始 col6.ColumnIndexEnd = 6; //结束列,索引从0开始,这样为第1列、第2列使用此样式 col6.Width = 5555; //宽度,字节长度,ushort类型 0~65535 sheet.AddColumnInfo(col6); //将列样式作用于此工作表 ColumnInfo col7 = new ColumnInfo(doc, sheet); //创建列样式对象 col7.ColumnIndexStart = 7; //起始列,索引从0开始 col7.ColumnIndexEnd = 7; //结束列,索引从0开始,这样为第1列、第2列使用此样式 col7.Width = 5555; //宽度,字节长度,ushort类型 0~65535 sheet.AddColumnInfo(col7); //将列样式作用于此工作表 ColumnInfo col8 = new ColumnInfo(doc, sheet); //创建列样式对象 col8.ColumnIndexStart = 8; //起始列,索引从0开始 col8.ColumnIndexEnd = 8; //结束列,索引从0开始,这样为第1列、第2列使用此样式 col8.Width = 5555; //宽度,字节长度,ushort类型 0~65535 sheet.AddColumnInfo(col8); //将列样式作用于此工作表 MergeArea ma1 = new MergeArea(1, 1, 1, 10); //合并单元格,第2行第5列 到 第3行第7列 sheet.AddMergeArea(ma1); //添加合并单元格到工作表 MergeArea ma2 = new MergeArea(2, 2, 2, 9); //合并单元格,第2行第5列 到 第3行第7列 sheet.AddMergeArea(ma2); //添加合并单元格到工作表 XF xf = doc.NewXF(); //单元格样式对象 xf.VerticalAlignment = VerticalAlignments.Centered; //垂直居中 xf.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中 xf.Pattern = 0; //填充风格,0为无色填充,1为没有间隙的纯色填充 xf.PatternColor = Colors.Green; //填充背景底色 xf.Font.ColorIndex = 0; //字体前景色颜色,未知值 xf.Font.FontName = "黑体"; //字体 xf.Font.Height = 15 * 15; //字体大小 //xf.UseBorder = false ; //使用边框 //xf.BottomLineStyle = 1; //边框样式 //xf.BottomLineColor = Colors.Black; //边框颜色 XF xf1 = doc.NewXF(); //单元格样式对象 xf1.VerticalAlignment = VerticalAlignments.Centered; //垂直居中 xf1.HorizontalAlignment = HorizontalAlignments.Right; //水平居中 xf1.Pattern = 0; //填充风格,0为无色填充,1为没有间隙的纯色填充 xf1.PatternColor = Colors.Green; //填充背景底色 xf1.Font.ColorIndex = 0; //字体前景色颜色,未知值 xf1.Font.FontName = "宋体"; //字体 xf1.Font.Height = 13 * 13; //字体大小 //xf1.UseBorder = false ; //使用边框 //xf1.BottomLineStyle = 1; //边框样式 //xf1.BottomLineColor = Colors.Black; //边框颜色 XF xf2 = doc.NewXF(); //单元格样式对象 xf2.VerticalAlignment = VerticalAlignments.Centered; //垂直居中 xf2.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中 xf2.Pattern = 0; //填充风格,0为无色填充,1为没有间隙的纯色填充 xf2.PatternColor = Colors.Green; //填充背景底色 xf2.Font.ColorIndex = 0; //字体前景色颜色,未知值 xf2.Font.FontName = "宋体"; //字体 xf2.Font.Height = 13 * 13; //字体大小 //xf2.UseBorder = false; //使用边框 //xf2.BottomLineStyle = 1; //边框样式 //xf2.BottomLineColor = Colors.Black; //边框颜色 XF xf3 = doc.NewXF(); //单元格样式对象 xf3.VerticalAlignment = VerticalAlignments.Centered; //垂直居中 xf3.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中 xf3.Pattern = 1; //填充风格,0为无色填充,1为没有间隙的纯色填充 xf3.PatternColor = Colors.Green; //填充背景底色 xf3.Font.ColorIndex = 0; //字体前景色颜色,未知值 xf3.Font.FontName = "宋体"; //字体 xf3.Font.Height = 13 * 13; //字体大小 //xf2.UseBorder = false; //使用边框 //xf2.BottomLineStyle = 1; //边框样式 //xf2.BottomLineColor = Colors.Black; //边框颜色 var DATA = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月"; cells.Add(1, 1, DATA + "智能中心项目进度表", xf); //添加单元格内容,第2行,第5列,内容,索引从1开始 cells.Add(2, 2, "日期:" + DateTime.Now.Date.ToShortDateString(), xf1); //添加单元格内容,第2行,第5列,内容,索引从1开始 cells.Add(3, 1, "序号", xf2); cells.Add(3, 2, "客户名称", xf2); cells.Add(3, 3, "客户零件编号", xf2); cells.Add(3, 4, "内部零件编号", xf2); cells.Add(3, 5, "工程师", xf2); cells.Add(3, 6, "夹位", xf2); cells.Add(3, 7, "文件下发时间", xf2); cells.Add(3, 8, "预计编程完成时间", xf2); cells.Add(3, 9, "完成时间", xf2); cells.Add(3, 10, "备注", xf2); int index = 4; for (int i = 0; i < orderInfoSchedus.Count(); i++) { cells.Add(i + 4, 1, (i + 1).ToString(), xf2); cells.Add(i + 4, 2, orderInfoSchedus[i].Client, xf2); cells.Add(i + 4, 3, orderInfoSchedus[i].Name, xf2); cells.Add(i + 4, 4, orderInfoSchedus[i].OrderNum, xf2); cells.Add(i + 4, 5, orderInfoSchedus[i].EngineerName, xf2); cells.Add(i + 4, 6, orderInfoSchedus[i].jiawei, xf2); cells.Add(i + 4, 7, orderInfoSchedus[i].FileDownTime, xf2); cells.Add(i + 4, 8, orderInfoSchedus[i].ExpectEndTime, xf2); cells.Add(i + 4, 9, orderInfoSchedus[i].EndTime, xf2); cells.Add(i + 4, 10, orderInfoSchedus[i].State, xf2); index++; } PathInfo pathInfo = new PathInfo(); string path = pathInfo.upLoadPath() + @"ExcelFile\"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } doc.Save(path); context.Response.Write(pathInfo.downLoadPath() + @"ExcelFile\" + doc.FileName); }
/// <summary> /// DataSet转Excel(SheetName使用对应DataTable的TableName)。 /// 不支持在已存在的Excel文件中添加新的sheet(如果文件已存在,则会覆盖)。 /// </summary> /// <param name="ds">待写入Excel的DataSet对象</param> /// <param name="excelFilePath">Excel文件路径</param> /// <param name="isColumnWritten">DataTable的列名是否要写入</param> /// <param name="cellStyle">单元格格式。如果为null,则不设置单元格格式</param> public static void ToExcel(DataSet ds, string excelFilePath, bool isColumnWritten, CellStyle cellStyle) { if (string.IsNullOrEmpty(excelFilePath)) { throw new ArgumentException(ErrorFilePath); } if (ds == null || ds.Tables.Count <= 0) { return; } //创建Excel文档 XlsDocument xls = new XlsDocument(); xls.SummaryInformation.Author = Author; xls.SummaryInformation.Subject = Subject; xls.SummaryInformation.Comments = Comments; xls.DocumentSummaryInformation.Company = Company; foreach (DataTable dt in ds.Tables) { if (dt == null || dt.Rows.Count <= 0) { continue; } int cellRow = 1;//从第几行开始写数据 string sheetName = GetValidSheetName(dt.TableName); if (string.IsNullOrEmpty(sheetName) || xls.Workbook.Worksheets.Any(worksheet => worksheet.Name == sheetName)) { sheetName = string.Format("Sheet{0}", xls.Workbook.Worksheets.Count + 1); } Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); sheet.SheetType = WorksheetTypes.Worksheet; Cells cells = sheet.Cells; //写标题 if (isColumnWritten) { #region 设置标题格式 XF xfDataHead = xls.NewXF(); if (cellStyle != null) { xfDataHead.HorizontalAlignment = cellStyle.TitleFontCenter ? HorizontalAlignments.Centered : HorizontalAlignments.Left; xfDataHead.Font.FontName = cellStyle.TitleFontName; xfDataHead.Font.Bold = cellStyle.TitleFontBold; xfDataHead.Font.Height = cellStyle.TitleFontSize; if (cellStyle.Border) { xfDataHead.UseBorder = true; xfDataHead.TopLineStyle = 1; xfDataHead.BottomLineStyle = 1; xfDataHead.LeftLineStyle = 1; xfDataHead.RightLineStyle = 1; } xfDataHead.CellLocked = false; xfDataHead.UseProtection = false; xfDataHead.UseNumber = true; xfDataHead.UseMisc = true; } #endregion for (int cellColumn = 1; cellColumn <= dt.Columns.Count; cellColumn++) { if (cellStyle != null) { cells.Add(cellRow, cellColumn, dt.Columns[cellColumn - 1].Caption, xfDataHead); } else { cells.Add(cellRow, cellColumn, dt.Columns[cellColumn - 1].Caption); } } cellRow++; } #region 设置内容格式 XF xfDataContent = xls.NewXF(); if (cellStyle != null) { xfDataContent.HorizontalAlignment = HorizontalAlignments.Left; xfDataContent.Font.FontName = cellStyle.ContentFontName; xfDataContent.Font.Height = cellStyle.ContentFontSize; if (cellStyle.Border) { xfDataContent.UseBorder = true; xfDataContent.TopLineStyle = 1; xfDataContent.BottomLineStyle = 1; xfDataContent.LeftLineStyle = 1; xfDataContent.RightLineStyle = 1; } xfDataContent.CellLocked = false; xfDataContent.UseProtection = false; xfDataContent.UseNumber = true; xfDataContent.UseMisc = true; } #endregion //写内容 foreach (DataRow dr in dt.Rows) { for (int cellColumn = 1; cellColumn <= dt.Columns.Count; cellColumn++) { if (cellStyle != null) { ColumnInfo colInfo = new ColumnInfo(xls, sheet) { ColumnIndexStart = (ushort)(cellColumn - 1), ColumnIndexEnd = (ushort)(cellColumn - 1), Width = cellStyle.ColumnWidth }; sheet.AddColumnInfo(colInfo); } object obj = dr[cellColumn - 1] != DBNull.Value ? (dt.Columns[cellColumn - 1].DataType == typeof(DateTime) ? Convert.ToDateTime(dr[cellColumn - 1]).ToString("yyyy-MM-dd HH:mm:ss") : dr[cellColumn - 1]) : string.Empty; if (cellStyle != null) { cells.Add(cellRow, cellColumn, obj, xfDataContent); } else { cells.Add(cellRow, cellColumn, obj); } } cellRow++; } } xls.FileName = Path.GetFileName(excelFilePath); xls.Save(Path.GetDirectoryName(excelFilePath), true); //xls.Send(XlsDocument.SendMethods.Attachment);//浏览器保存文件 }
/// <summary> /// 绑定数据库生成XLS报表 /// </summary> /// <param name="ds">获取DataSet数据集</param> /// <param name="xlsName">报表表名</param> private void xlsGridview(DataTable dt, string xlsName) { XlsDocument xls = new XlsDocument(); xls.FileName = Server.UrlEncode(xlsName); int rowIndex = 2; int colIndex = 0; Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称 Cells cells = sheet.Cells; //设置列格式 ColumnInfo colInfo = new ColumnInfo(xls, sheet); colInfo.ColumnIndexStart = 0; colInfo.ColumnIndexEnd = 8; colInfo.Width = 17 * 256; sheet.AddColumnInfo(colInfo); //设置样式 XF xf = xls.NewXF(); xf.HorizontalAlignment = HorizontalAlignments.Centered; xf.VerticalAlignment = VerticalAlignments.Centered; xf.TextWrapRight = true; xf.UseBorder = true; xf.TopLineStyle = 1; xf.TopLineColor = Colors.Black; xf.BottomLineStyle = 1; xf.BottomLineColor = Colors.Black; xf.LeftLineStyle = 1; xf.LeftLineColor = Colors.Black; xf.RightLineStyle = 1; xf.RightLineColor = Colors.Black; xf.Font.Bold = true; // MergeRegion(ref sheet, xf, xlsName.Substring(0, xlsName.Length - 4), 1, 1, 1, 6); MergeRegion(ref sheet, xf, "分公司", 2, 2, 1, 1); MergeRegion(ref sheet, xf, "外包单位", 2, 2, 2, 2); MergeRegion(ref sheet, xf, "障碍与服务指标(85分)", 2, 2, 3, 3); MergeRegion(ref sheet, xf, "基础管理考核(15分)", 2, 2, 4, 4); MergeRegion(ref sheet, xf, "额外奖罚", 2, 2, 5, 5); MergeRegion(ref sheet, xf, "汇总得分", 2, 2, 6, 6); //填充数据 foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dt.Columns) { colIndex++; Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型 //如果你数据库里的数据都是数字的话 最好转换一下,不然导入到Excel里是以字符串形式显示。 cell.Font.FontFamily = FontFamilies.Roman; //字体 if (colIndex != 1) { cell.Font.Bold = false; //字体为粗体 } } } MergeRegion(ref sheet, xf, "设计院", 3, 4, 2, 2); MergeRegion(ref sheet, xf, "北京合力", 5, 8, 2, 2); xls.Send(); Response.Flush(); Response.End(); }
///// <summary> ///// 绑定数据库生成XLS报表 ///// </summary> ///// <param name="ds">获取DataSet数据集</param> ///// <param name="xlsName">报表表名</param> private void xlsGridview(DataTable dt, string xlsName) { XlsDocument xls = new XlsDocument(); xls.FileName = System.Web.HttpUtility.UrlEncode(xlsName, System.Text.Encoding.UTF8); int rowIndex = 5; int colIndex = 0; Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称 Cells cells = sheet.Cells; //设置列格式 ColumnInfo colInfo = new ColumnInfo(xls, sheet); colInfo.ColumnIndexStart = 0; colInfo.ColumnIndexEnd = 8; colInfo.Width = 18 * 256; sheet.AddColumnInfo(colInfo); //设置样式 XF xf = xls.NewXF(); xf.HorizontalAlignment = HorizontalAlignments.Centered; xf.VerticalAlignment = VerticalAlignments.Centered; xf.TextWrapRight = true; xf.UseBorder = true; xf.TopLineStyle = 1; xf.TopLineColor = Colors.Black; xf.BottomLineStyle = 1; xf.BottomLineColor = Colors.Black; xf.LeftLineStyle = 1; xf.LeftLineColor = Colors.Black; xf.RightLineStyle = 1; xf.RightLineColor = Colors.Black; xf.Font.Bold = true; // MergeRegion(ref sheet, xf, xlsName.Substring(0, xlsName.Length - 4), 1, 1, 1, 10); MergeRegion(ref sheet, xf, "姓名", 2, 5, 1, 1); MergeRegion(ref sheet, xf, "日常工作(权重30分)", 2, 2, 2, 4); MergeRegion(ref sheet, xf, "KPI考核(权重60分)", 2, 2, 5, 8); MergeRegion(ref sheet, xf, "胜任度(权重10分)", 2, 2, 9, 9); MergeRegion(ref sheet, xf, "得分", 2, 5, 10, 10); Cell cell1 = cells.Add(3, 2, "A", xf); Cell cell2 = cells.Add(3, 3, "B", xf); Cell cell3 = cells.Add(3, 4, "C", xf); Cell cell4 = cells.Add(3, 5, "D", xf); Cell cell5 = cells.Add(3, 6, "E", xf); MergeRegion(ref sheet, xf, "F", 3, 3, 7, 8); Cell cell6 = cells.Add(3, 9, "G", xf); Cell cell7 = cells.Add(4, 2, "劳动纪律", xf); Cell cell8 = cells.Add(4, 3, "信息报道", xf); Cell cell9 = cells.Add(4, 4, "基础报表", xf); Cell cell10 = cells.Add(4, 5, "网运指标", xf); Cell cell11 = cells.Add(4, 6, "工作效率", xf); MergeRegion(ref sheet, xf, "其它加扣分", 4, 4, 7, 8); Cell cell12 = cells.Add(4, 9, "贡献度胜任度职业道德", xf); Cell cell13 = cells.Add(5, 2, "迟到或早退一次扣1分、事假每天扣2分,病假每天扣1分执行。无故旷工半天,扣5分。无故旷工一天,扣10分,扣分随天数增加而增加.", xf); Cell cell14 = cells.Add(5, 3, "每人每月至少上报信息1篇,每差一篇扣5分。凡信息被市公司采用的加2分,被省公司采用的加5分。", xf); Cell cell15 = cells.Add(5, 4, "未按时出规定报表一次扣2分,被省公司通报一次扣5分。", xf); Cell cell16 = cells.Add(5, 5, "本岗位挂靠指标完成情况", xf); Cell cell17 = cells.Add(5, 6, "不能按时按要求完成本岗位工作任务(周工作计划内容、日常工作内容),效率低的每次每项扣2分", xf); MergeRegion(ref sheet, xf, "全省有通报的各项工作中,排名前三加5分,排名前6加3分,低于全省平均水平扣2分,排名12名之后扣3分,排名后三扣5分", 5, 5, 7, 8); Cell cell18 = cells.Add(5, 9, "部门员工得分=部门正职得分*50+部门副职得分*30%+员工互评得分*20%; 部门副职得分=员工打分均值", xf); //填充数据 foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dt.Columns) { colIndex++; Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型 //如果你数据库里的数据都是数字的话 最好转换一下,不然导入到Excel里是以字符串形式显示。 cell.Font.FontFamily = FontFamilies.Roman; //字体 cell.Font.Bold = false; //字体为粗体 } } xls.Send(); Response.Flush(); Response.End(); }
/// <summary> /// 导出SO的excel通用方法 /// </summary> /// <param name="myData">导出的数据</param> private void ExportExcel(List <ExcelData> myData) { //列宽: ushort[] colWidth = new ushort[] { 16, 16, 16, 14, 18, 16, 28, 28, 16, 20, 16, 14, 18, 24, 32, 14, 14, 14, 14, 14, 14, 16, 14, 14, 14, 14, 14, 18, 18, 60 }; //列名: string[] colName = new string[] { "审核结果", "流水号", "订单号", "下单日期", "办事处", "订单类型", "购货客户", "国外客户", "产品类别", "产品用途", "币别", "汇率", "产品代码", "产品名称", "规格型号", "单位", "数量", "单价", "含税单价", "成交价", "成本", "价税合计", "费用率%", "MU%", "税率%", "佣金率%", "佣金", "发货地点", "交货属性", "摘要" }; //設置excel文件名和sheet名 XlsDocument xls = new XlsDocument(); xls.FileName = string.Format("销售订单_{0}.xls", DateTime.Now.ToString("yyyyMMdd")); Worksheet sheet = xls.Workbook.Worksheets.Add("订单信息列表"); //设置各种样式 //标题样式 XF boldXF = xls.NewXF(); boldXF.HorizontalAlignment = HorizontalAlignments.Centered; boldXF.Font.Height = 12 * 20; boldXF.Font.FontName = "宋体"; boldXF.Font.Bold = true; //设置列宽 ColumnInfo col; for (ushort i = 0; i < colWidth.Length; i++) { col = new ColumnInfo(xls, sheet); col.ColumnIndexStart = i; col.ColumnIndexEnd = i; col.Width = (ushort)(colWidth[i] * 256); sheet.AddColumnInfo(col); } Cells cells = sheet.Cells; int rowIndex = 1; int colIndex = 1; //设置标题 foreach (var name in colName) { cells.Add(rowIndex, colIndex++, name, boldXF); } foreach (var d in myData) { colIndex = 1; cells.Add(++rowIndex, colIndex, d.auditStatus); cells.Add(rowIndex, ++colIndex, d.h.sys_no); cells.Add(rowIndex, ++colIndex, d.h.order_no); cells.Add(rowIndex, ++colIndex, ((DateTime)d.h.order_date).ToString("yyyy-MM-dd")); cells.Add(rowIndex, ++colIndex, d.h.agency1_name); cells.Add(rowIndex, ++colIndex, d.h.order_type_name); cells.Add(rowIndex, ++colIndex, d.h.customer_name); cells.Add(rowIndex, ++colIndex, d.h.oversea_customer_name); cells.Add(rowIndex, ++colIndex, d.h.product_type_name); cells.Add(rowIndex, ++colIndex, d.h.product_use); cells.Add(rowIndex, ++colIndex, d.h.currency_name); cells.Add(rowIndex, ++colIndex, d.h.exchange_rate); cells.Add(rowIndex, ++colIndex, d.e.item_no); cells.Add(rowIndex, ++colIndex, d.e.item_name); cells.Add(rowIndex, ++colIndex, d.e.item_model); cells.Add(rowIndex, ++colIndex, d.e.unit_name); cells.Add(rowIndex, ++colIndex, d.e.qty); cells.Add(rowIndex, ++colIndex, d.e.unit_price); cells.Add(rowIndex, ++colIndex, d.e.tax_price); cells.Add(rowIndex, ++colIndex, d.e.deal_price); cells.Add(rowIndex, ++colIndex, d.e.cost); cells.Add(rowIndex, ++colIndex, d.e.qty * d.e.tax_price); cells.Add(rowIndex, ++colIndex, d.e.fee_rate); cells.Add(rowIndex, ++colIndex, d.e.MU); cells.Add(rowIndex, ++colIndex, d.e.tax_rate); cells.Add(rowIndex, ++colIndex, d.e.commission_rate); cells.Add(rowIndex, ++colIndex, d.e.commission); cells.Add(rowIndex, ++colIndex, d.h.delivery_place_name); cells.Add(rowIndex, ++colIndex, d.h.receive_place_name); cells.Add(rowIndex, ++colIndex, d.h.summary); } //合计行 cells.Add(++rowIndex, 1, "合计:"); cells.Add(rowIndex, 17, myData.Sum(d => d.e.qty)); cells.Add(rowIndex, 22, myData.Sum(d => d.e.qty * d.e.tax_price)); xls.Send(); }
public void ExportPOData(string queryJson) { GetK3POParams p = JsonConvert.DeserializeObject <GetK3POParams>(queryJson); p.poNumbers = p.poNumbers == null ? "" : p.poNumbers.Trim();//去掉前后空格 p.itemInfo = p.itemInfo == null ? "" : p.itemInfo.Trim(); p.account = currentAccount; p.userId = currentUser.userId; p.userNumber = currentUser.userName; p.k3HasAudit = true; List <K3POs> result; List <decimal> qtyList; try { result = new DRSv().GetPOs(p).OrderBy(po => po.poDate).Take(1000).ToList(); //一次性关联1000条到数据库查询会导致内存溢出,所以大于500条的,分2次获取 var infos = result.Take(500).Select(r => new IDModel() { interId = r.poId, entryId = r.poEntryId }).ToList(); qtyList = new DRSv().GetPOTransitQty(infos); if (result.Count() > 500) { infos = result.Skip(500).Select(r => new IDModel() { interId = r.poId, entryId = r.poEntryId }).ToList(); qtyList.AddRange(new DRSv().GetPOTransitQty(infos)); } } catch { return; } ushort[] colWidth = new ushort[] { 18, 10, 20, 24, 32, 16, 16, 16, 16, 12, 24, 12, 16, 12, 12, 16, 12, 12, 16 }; string[] colName = new string[] { "订单编号", "分录号", "PR单号", "物料名称", "规格型号", "订单数量", "入库数量", "申请数量", "可申请数量", "订料员", "申购部门", "单位", "物料编码", "订单类型", "采购方式", "订单日期", "贸易类型", "币别", "采购员" }; //設置excel文件名和sheet名 XlsDocument xls = new XlsDocument(); xls.FileName = "订单明细表_" + DateTime.Now.ToString("MMddHHmmss"); Worksheet sheet = xls.Workbook.Worksheets.Add("订单列表"); //设置各种样式 //标题样式 XF boldXF = xls.NewXF(); boldXF.HorizontalAlignment = HorizontalAlignments.Centered; boldXF.Font.Height = 12 * 20; boldXF.Font.FontName = "宋体"; boldXF.Font.Bold = true; //设置列宽 ColumnInfo col; for (ushort i = 0; i < colWidth.Length; i++) { col = new ColumnInfo(xls, sheet); col.ColumnIndexStart = i; col.ColumnIndexEnd = i; col.Width = (ushort)(colWidth[i] * 256); sheet.AddColumnInfo(col); } Cells cells = sheet.Cells; int rowIndex = 1; int colIndex = 1; //设置标题 foreach (var name in colName) { cells.Add(rowIndex, colIndex++, name, boldXF); } for (var i = 0; i < result.Count(); i++) { var d = result[i]; var q = qtyList[i]; colIndex = 1; //"订单编号", "分录号", "PR单号", "物料名称", "规格型号", "订单数量", //"入库数量", "申请数量", "可申请数量", "订料员", "申购部门","单位","物料编码", //"订单类型","采购方式","订单日期","贸易类型","币别","采购员" cells.Add(++rowIndex, colIndex, d.poNo); cells.Add(rowIndex, ++colIndex, d.poEntryId); cells.Add(rowIndex, ++colIndex, d.prNo); cells.Add(rowIndex, ++colIndex, d.itemName); cells.Add(rowIndex, ++colIndex, d.itemModel); cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.orderQty)); cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.realteQty)); cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", q)); cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.orderQty - d.realteQty - q)); cells.Add(rowIndex, ++colIndex, d.matOrderName); cells.Add(rowIndex, ++colIndex, d.departmentName); cells.Add(rowIndex, ++colIndex, d.unitName); cells.Add(rowIndex, ++colIndex, d.itemNumber); cells.Add(rowIndex, ++colIndex, d.billType); cells.Add(rowIndex, ++colIndex, d.buyType); cells.Add(rowIndex, ++colIndex, ((DateTime)d.poDate).ToString("yyyy-MM-dd")); cells.Add(rowIndex, ++colIndex, d.tradeTypeName); cells.Add(rowIndex, ++colIndex, d.unitName); cells.Add(rowIndex, ++colIndex, d.buyerName); } xls.Send(); WLog("导出K3订单Excel", "行数:" + result.Count() + ";" + queryJson); //return "已成功导出行数:"+result.Count(); }
/// <summary> /// 导出CH的excel通用方法 /// </summary> /// <param name="myData">导出的数据</param> private void ExportExcel(List <ExcelData> myData) { //列名: string[] colName = new string[] { "审核结果", "公司", "流水号", "订单类型", "下单日期", "制单人", "产品类别", "客户编码", "客户名称", "营业员", "付款条款", "摘要", "产品代码", "产品名称", "规格型号", "单位", "数量", "含税单价", "金额", "税率%", "交货日期", "备料单号", "BOM表码", "备注" }; //設置excel文件名和sheet名 XlsDocument xls = new XlsDocument(); xls.FileName = string.Format("仪器工业申请单_{0}.xls", DateTime.Now.ToString("yyyyMMdd")); Worksheet sheet = xls.Workbook.Worksheets.Add("订单信息列表"); //设置各种样式 //标题样式 XF boldXF = xls.NewXF(); boldXF.HorizontalAlignment = HorizontalAlignments.Centered; boldXF.Font.Height = 12 * 20; boldXF.Font.FontName = "宋体"; boldXF.Font.Bold = true; //设置列宽 ColumnInfo col; for (ushort i = 0; i < colName.Length; i++) { col = new ColumnInfo(xls, sheet); col.ColumnIndexStart = i; col.ColumnIndexEnd = i; col.Width = (ushort)(18 * 256); sheet.AddColumnInfo(col); } Cells cells = sheet.Cells; int rowIndex = 1; int colIndex = 1; //设置标题 foreach (var name in colName) { cells.Add(rowIndex, colIndex++, name, boldXF); } foreach (var d in myData) { colIndex = 1; //"审核结果","公司","流水号","下单日期","制单人","产品类别","客户编码","客户名称","营业员","付款条款", //"摘要","产品代码","产品名称","规格型号","单位","数量","含税单价","金额","税率%","交货日期", //"备料单号","BOM表码","备注" cells.Add(++rowIndex, colIndex, d.auditStatus); cells.Add(rowIndex, ++colIndex, d.h.account); cells.Add(rowIndex, ++colIndex, d.h.sys_no); cells.Add(rowIndex, ++colIndex, d.h.order_type_name); cells.Add(rowIndex, ++colIndex, d.h.apply_time.ToShortDateString()); cells.Add(rowIndex, ++colIndex, d.h.applier_name); cells.Add(rowIndex, ++colIndex, d.h.product_type_name); cells.Add(rowIndex, ++colIndex, d.h.customer_no); cells.Add(rowIndex, ++colIndex, d.h.customer_name); cells.Add(rowIndex, ++colIndex, d.h.clerk_name); cells.Add(rowIndex, ++colIndex, d.h.clear_type_name); cells.Add(rowIndex, ++colIndex, d.h.summary); cells.Add(rowIndex, ++colIndex, d.e.product_no); cells.Add(rowIndex, ++colIndex, d.e.product_name); cells.Add(rowIndex, ++colIndex, d.e.product_model); cells.Add(rowIndex, ++colIndex, d.e.unit_name); cells.Add(rowIndex, ++colIndex, d.e.qty); cells.Add(rowIndex, ++colIndex, d.e.tax_price); cells.Add(rowIndex, ++colIndex, d.e.qty * d.e.tax_price); cells.Add(rowIndex, ++colIndex, d.e.tax_rate); cells.Add(rowIndex, ++colIndex, d.e.fetch_date.ToShortDateString()); cells.Add(rowIndex, ++colIndex, d.e.bl_no); cells.Add(rowIndex, ++colIndex, d.e.bom_no); cells.Add(rowIndex, ++colIndex, d.e.comment); } xls.Send(); }
/// < summary> /// 保存Excel /// neo 2010-12-02 /// < /summary> /// < param name="ExcelData"> < /param> /// < param name="sExcelFileName"> < /param> /// < param name="sExcelDir"> < /param> /// < param name="sSheetName"> < /param> public static string SaveAsExcel(DataTable ExcelData, string sExcelDir, string sSheetName) { if (ExcelData == null) { return(string.Empty); } // 生成随机唯一文件名 string sExcelFileName = Guid.NewGuid().ToString() + ".xls"; // 创建Excel文档 XlsDocument ExcelDoc = new XlsDocument(); ExcelDoc.FileName = sExcelFileName; // 添加Sheet Worksheet Sheet = ExcelDoc.Workbook.Worksheets.Add(sSheetName); //Sheet.Name = sSheetName; // 添加第一行列名 ColumnInfo ColumnInfo1 = new ColumnInfo(ExcelDoc, Sheet); ColumnInfo1.ColumnIndexStart = 0; ColumnInfo1.ColumnIndexEnd = Convert.ToUInt16(ExcelData.Columns.Count - 1); ColumnInfo1.Width = 25 * 200; Sheet.AddColumnInfo(ColumnInfo1); int i = 1; foreach (DataColumn ColumnObj in ExcelData.Columns) { Cell CellObj = Sheet.Cells.Add(1, i, ColumnObj.ColumnName); CellObj.Font.Weight = FontWeight.Bold; CellObj.Pattern = 1; CellObj.PatternColor = Colors.Silver; CellObj.UseBorder = true; CellObj.LeftLineStyle = 1; CellObj.LeftLineColor = Colors.Black; CellObj.RightLineStyle = 1; CellObj.RightLineColor = Colors.Black; CellObj.TopLineStyle = 1; CellObj.TopLineColor = Colors.Black; CellObj.TopLineStyle = 1; CellObj.BottomLineColor = Colors.Black; i++; } int j = 2; foreach (DataRow RowObj in ExcelData.Rows) { int m = 1; foreach (DataColumn ColumnObj in ExcelData.Columns) { object oValue = RowObj[ColumnObj.ColumnName]; if (oValue.GetType().FullName == "System.Byte") { oValue = Convert.ToInt16(oValue); } else if (oValue.GetType().FullName == "System.DBNull") { oValue = string.Empty; } else if (oValue.GetType().FullName == "System.String") { oValue = oValue.ToString(); } else if (oValue.GetType().FullName == "System.DateTime") { oValue = Convert.ToDateTime(oValue).ToString(); } Cell CellObj = Sheet.Cells.Add(j, m, oValue); CellObj.UseBorder = true; CellObj.LeftLineStyle = 1; CellObj.LeftLineColor = Colors.Black; CellObj.RightLineStyle = 1; CellObj.RightLineColor = Colors.Black; CellObj.TopLineStyle = 1; CellObj.TopLineColor = Colors.Black; CellObj.TopLineStyle = 1; CellObj.BottomLineColor = Colors.Black; m++; } j++; } string sPath = sExcelDir + sExcelFileName; if (File.Exists(sPath) == true) { File.Delete(sPath); } ExcelDoc.Save(sExcelDir, true); return(sPath); }
private void btnExportExcel_Click(object sender, EventArgs e) { if (gvPaymentList.Rows.Count > 0) { beginDate = dtpBeginDate.Value.Date; endDate = dtpEndDate.Value.AddDays(1).Date; IList <MRS.Model.AccountBook> accountBooks = new List <MRS.Model.AccountBook>(); accountBooks = bllAccountBook.Select_AccountBook_PaymentList(beginDate, endDate); System.Text.StringBuilder fileName = new StringBuilder(); fileName.Append("医保报销帐单 "); fileName.Append(DateTime.Now.Year.ToString()); fileName.Append(DateTime.Now.Month.ToString().PadLeft(2, '0')); fileName.Append(DateTime.Now.Day.ToString().PadLeft(2, '0')); fileName.Append(".xls"); XlsDocument xlsDoc = new XlsDocument(); xlsDoc.FileName = fileName.ToString(); //定义Excel文档属性. xlsDoc.SummaryInformation.Author = "Cobe lee"; xlsDoc.SummaryInformation.Title = "医保报销帐单"; xlsDoc.SummaryInformation.Comments = "This workbook generated by ZPITC! http://www.tiyi.biz"; xlsDoc.SummaryInformation.Subject = "医保报销帐单"; xlsDoc.DocumentSummaryInformation.Company = "镇海石化工业贸易有限责任公司"; Worksheet sheet = xlsDoc.Workbook.Worksheets.Add("Sheet1"); xlsDoc.Workbook.Worksheets.Add("Sheet2"); xlsDoc.Workbook.Worksheets.Add("Sheet3"); Cells cells = sheet.Cells; //以下定义 银行帐号 及 身份证号 的列宽. ColumnInfo col0 = new ColumnInfo(xlsDoc, sheet); col0.ColumnIndexEnd = 0; col0.ColumnIndexStart = 0; col0.Width = 21 * 269; sheet.AddColumnInfo(col0); ColumnInfo col3 = new ColumnInfo(xlsDoc, sheet); col3.ColumnIndexEnd = 3; col3.ColumnIndexStart = 3; col3.Width = 21 * 269; sheet.AddColumnInfo(col3); //以下定义文档标题 副标题 int rint = 1; cells.Merge(rint, rint, 1, 5); Cell cell = cells.Add(rint, 1, "医保报销帐单"); cell.Font.Height = 16 * 20; cell.HorizontalAlignment = HorizontalAlignments.Centered; rint++; cells.Merge(rint, rint, 1, 5); Cell cell2 = cells.Add(rint, 1, "( " + beginDate.ToShortDateString() + "至" + dtpEndDate.Value.ToShortDateString() + " )"); cell2.Font.Height = 12 * 20; cell2.Font.FontName = "宋体"; cell2.HorizontalAlignment = HorizontalAlignments.Centered; rint++; FormatHead(cells.Add(rint, 1, "银行帐号")); FormatHead(cells.Add(rint, 2, "金额")); FormatHead(cells.Add(rint, 3, "姓名")); FormatHead(cells.Add(rint, 4, "身份证号")); FormatHead(cells.Add(rint, 5, "备注")); foreach (MRS.Model.AccountBook account in accountBooks) { rint++; PMS.BLL.MedicarePerson bll_mp = new PMS.BLL.MedicarePerson(); string bankAccount = bll_mp.GetMedicarePerson(account.YBH).BankAccount; if (bankAccount != null) { FormatCellNumber(cells.Add(rint, 1, bankAccount)); FormatCellAmount(cells.Add(rint, 2, Convert.ToDouble(account.BXJE))); FormatCellName(cells.Add(rint, 3, account.Name)); FormatCellNumber(cells.Add(rint, 4, account.IdentityCard)); FormatCellName(cells.Add(rint, 5, "报销")); } } rint++; FormatCellButtom(cells.Add(rint, 1, "")); FormatCellButtom(cells.Add(rint, 2, "")); FormatCellButtom(cells.Add(rint, 3, "")); FormatCellButtom(cells.Add(rint, 4, "")); FormatCellButtom(cells.Add(rint, 5, "")); saveFileDialog1.DefaultExt = "xls"; saveFileDialog1.FileName = fileName.ToString(); saveFileDialog1.Filter = "Excel 97-2003 工作簿(*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { string path = System.IO.Path.GetDirectoryName(saveFileDialog1.FileName); try { xlsDoc.Save(path, true); } catch { MessageBox.Show("文件 \"" + System.IO.Path.GetFileName(saveFileDialog1.FileName) + "\" 正被其它程序打开,\n请关闭该文件,并重试!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } else { MessageBox.Show("无医保报销帐单!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
/// <summary> /// 将tables写入EXCEL文件,已预制格式,可覆盖同名文件 /// </summary> /// <param name="file_path">绝对路径,可覆盖同名文件</param> /// <param name="tables"></param> public static void WriteAsXls(string file_path, System.Data.DataTable[] tables) { XlsDocument xls = new XlsDocument(); xls.FileName = System.IO.Path.GetFileName(file_path); for (int i = 0; i < tables.Length; i++) { System.Data.DataTable table = tables[i]; if (table.Columns.Count > BIFF8.MaxCols) { throw new ApplicationException(string.Format("Table {0} has too many columns {1} to fit on Worksheet {2} with the given startCol {3}", table.TableName, table.Columns.Count, BIFF8.MaxCols, 1)); } if (table.Rows.Count > (BIFF8.MaxRows - 1)) { throw new ApplicationException(string.Format("Table {0} has too many rows {1} to fit on Worksheet {2} with the given startRow {3}", table.TableName, table.Rows.Count, (BIFF8.MaxRows - 1), 1)); } Worksheet sheet = xls.Workbook.Worksheets.Add(table.TableName); ColumnInfo ci = new ColumnInfo(xls, sheet); ci.Width = (20 * 256); ci.ColumnIndexEnd = (ushort)table.Rows.Count; sheet.AddColumnInfo(ci); int row = 1, col = 1; foreach (System.Data.DataColumn dataColumn in table.Columns) { Cell cellHeader = sheet.Cells.Add(row, col++, dataColumn.ColumnName); cellHeader.Font.Bold = true; cellHeader.HorizontalAlignment = HorizontalAlignments.Centered; cellHeader.Pattern = 1; cellHeader.PatternColor = Colors.Silver; cellHeader.UseBorder = true; cellHeader.TopLineStyle = 1; cellHeader.TopLineColor = Colors.Silver; cellHeader.BottomLineStyle = 1; cellHeader.BottomLineColor = Colors.Silver; cellHeader.LeftLineStyle = 1; cellHeader.LeftLineColor = Colors.Silver; cellHeader.RightLineStyle = 1; cellHeader.RightLineColor = Colors.Silver; } foreach (System.Data.DataRow dataRow in table.Rows) { row++; col = 1; foreach (object dataItem in dataRow.ItemArray) { object value = dataItem; if (dataItem == DBNull.Value) { value = null; } if (dataRow.Table.Columns[col - 1].DataType == typeof(byte[])) { value = string.Format("[ByteArray({0})]", ((byte[])value).Length); } Cell cellbody = sheet.Cells.Add(row, col++, value); } } } xls.Save(System.IO.Path.GetDirectoryName(file_path), true); }
protected void Page_Load(object sender, EventArgs e) { org.in2bits.MyXls.XlsDocument doc = new XlsDocument(); doc.FileName = "TestingAgain.xls"; for (int s = 1; s <= 5; s++) { string sheetName; //if (this.IsPostBack == true) //{ sheetName = Request.Form["txtSheet" + s].Replace(",", string.Empty); //} //else sheetName = string.Empty; if (sheetName.Trim() == string.Empty) { continue; } int rowMin, rowCount, colMin, colCount; try { rowMin = int.Parse(Request.Form["txtRowMin" + s]) + 1; rowCount = int.Parse(Request.Form["txtRows" + s]); colMin = int.Parse(Request.Form["txtColMin" + s]); colCount = int.Parse(Request.Form["txtCols" + s]); } catch { continue; } if (rowCount > 5000) { rowCount = 5000; } if (rowCount < 0) { rowCount = 0; } if (rowMin < 1) { rowMin = 1; } if (rowMin > 32767) { rowMin = 32767; } if (colCount > 50) { colCount = 50; } if (colCount < 1) { colCount = 1; } if (colMin < 1) { colMin = 1; } if (colMin > 100) { colMin = 100; } if (sheetName.Length > 35) { sheetName = sheetName.Substring(0, 35); } Worksheet sheet = doc.Workbook.Worksheets.AddNamed(sheetName); Cells cells = sheet.Cells; Cell miCelda; ColumnInfo cinfo; cinfo = new ColumnInfo(doc, sheet); cinfo.ColumnIndexStart = 1; cinfo.ColumnIndexEnd = 1; cinfo.Width = 200; miCelda = cells.AddValueCell(1, 1, "TU ABUELA"); sheet.AddColumnInfo(cinfo); for (int row = 0; row <= rowCount; row++) { if (row == 0) { for (int col = 1; col <= colCount; col++) { cells.AddValueCell((ushort)(rowMin + row), (ushort)(colMin + col - 1), "Fld" + col); } } else { for (int col = 1; col <= colCount; col++) { cells.AddValueCell((ushort)(rowMin + row), (ushort)(colMin + col - 1), row + col); } } } } doc.Send(); Response.Flush(); Response.End(); }
//数据的导入和导出 protected void ExportExcel(DataTable dt, string filename) { XlsDocument xls = new XlsDocument(); //新建一个xls文档 xls.FileName = filename; //设定文件名 string sheetName = "动车票价和定员表"; Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"chc 实例"的sheet页 Cells cells = sheet.Cells; //Cells实例是sheet页中单元格(cell)集合 ////设置列的宽度 ColumnInfo colInfo = new ColumnInfo(xls, sheet);//生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo.ColumnIndexStart = 0; //起始列为第一列,ColumnIndexStart是从0开始 colInfo.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) //列样式 XF cellXFColumnText = xls.NewXF(); cellXFColumnText.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中 cellXFColumnText.Font.FontName = "宋体"; //设定字体 cellXFColumnText.Font.Height = 10 * 20; //设定字大小(字体大小是以 1/20 point 为单位的) cellXFColumnText.Pattern = 1; //设定单元格填充风格。如果设定为0,则是纯色填充 cellXFColumnText.PatternBackgroundColor = Colors.Grey; //填充的底色 cellXFColumnText.PatternColor = Colors.Grey; //设定填充线条的颜色 cellXFColumnText.BottomLineStyle = 1; //设定边框底线为粗线 cellXFColumnText.BottomLineColor = Colors.Grey; cellXFColumnText.TopLineStyle = 1; //设定边框底线为粗线 cellXFColumnText.TopLineColor = Colors.Grey; cellXFColumnText.LeftLineStyle = 1; cellXFColumnText.LeftLineColor = Colors.Grey; cellXFColumnText.RightLineStyle = 1; cellXFColumnText.RightLineColor = Colors.Grey; cells.Add(1, 1, "序号", cellXFColumnText); //设定第一行,第一列单元格的值 cells.Add(1, 2, "动车类型", cellXFColumnText); //设定第一行,第二列单元格的值 cells.Add(1, 3, "动车类别", cellXFColumnText); //设定第一行,第三列单元格的值 cells.Add(1, 4, "一等座基本票价", cellXFColumnText); //设定第一行,第四列单元格的值 cells.Add(1, 5, "一等座定员", cellXFColumnText); //设定第一行,第五列单元格的值 cells.Add(1, 6, "二等座基本票价", cellXFColumnText); cells.Add(1, 7, "二等座定员", cellXFColumnText); cells.Add(1, 8, "动卧上铺基本票价", cellXFColumnText); cells.Add(1, 9, "动卧下铺基本票价", cellXFColumnText); cells.Add(1, 10, "动卧定员", cellXFColumnText); cells.Add(1, 11, "商务座基本票价", cellXFColumnText); cells.Add(1, 12, "商务座定员", cellXFColumnText); //列值样式 XF cellXFColumnValue = xls.NewXF(); cellXFColumnValue.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中 cellXFColumnValue.Font.FontName = "宋体"; //设定字体 cellXFColumnValue.Font.Height = 10 * 20; //设定字大小(字体大小是以 1/20 point 为单位的) cellXFColumnValue.UseBorder = true; //使用边框 cellXFColumnValue.BottomLineStyle = 1; //设定边框底线为粗线 cellXFColumnValue.BottomLineColor = Colors.Grey; cellXFColumnValue.TopLineStyle = 1; //设定边框底线为粗线 cellXFColumnValue.TopLineColor = Colors.Grey; cellXFColumnValue.LeftLineStyle = 1; cellXFColumnValue.LeftLineColor = Colors.Grey; cellXFColumnValue.RightLineStyle = 1; cellXFColumnValue.RightLineColor = Colors.Grey; int currentRow = 2; foreach (DataRow dr in dt.Rows) { cells.Add(currentRow, 1, Convert.ToString(dr["id"]), cellXFColumnValue); //动车类型 cells.Add(currentRow, 2, Convert.ToString(dr["HIGHTRAINTYPE"]), cellXFColumnValue); //动车类型 cells.Add(currentRow, 3, Convert.ToString(dr["MILETYPE"]), cellXFColumnValue); //里程 cells.Add(currentRow, 4, Convert.ToString(dr["RATE1"]), cellXFColumnValue); //一等座票价 cells.Add(currentRow, 5, Convert.ToString(dr["PCOUNT1"]), cellXFColumnValue); cells.Add(currentRow, 6, Convert.ToString(dr["RATE2"]), cellXFColumnValue); cells.Add(currentRow, 7, Convert.ToString(dr["PCOUNT2"]), cellXFColumnValue); cells.Add(currentRow, 8, Convert.ToString(dr["RATE3"]), cellXFColumnValue); cells.Add(currentRow, 9, Convert.ToString(dr["RATE31"]), cellXFColumnValue); cells.Add(currentRow, 10, Convert.ToString(dr["PCOUNT3"]), cellXFColumnValue); cells.Add(currentRow, 11, Convert.ToString(dr["RATE4"]), cellXFColumnValue); cells.Add(currentRow, 12, Convert.ToString(dr["PCOUNT4"]), cellXFColumnValue); currentRow++; } xls.Send(XlsDocument.SendMethods.Inline); }
/// <summary> /// 利用MyXls控件的一个方法,导出到电子表格 /// </summary> /// <param name="dsxls">数据集,列明应该是中文滴</param> /// <param name="filename">导出文件名</param> /// <param name="sheetsname">导出的sheet名</param> /// <param name="title">大标题,如果留空,则不显示大标题</param> /// <param name="rowwidth">每个列的宽度</param> /// <param name="lujing">保存路径</param> public static void goxls(DataSet dsxls, string filename, string sheetsname, string title, int rowwidth, string lujing) { org.in2bits.MyXls.XlsDocument doc = new XlsDocument(); doc.FileName = filename; Worksheet sheet = doc.Workbook.Worksheets.Add(sheetsname); Cells cells = sheet.Cells; ColumnInfo colInfo = new ColumnInfo(doc, sheet); colInfo.ColumnIndexStart = 0; colInfo.ColumnIndexEnd = (ushort)(dsxls.Tables[0].Columns.Count - 1); colInfo.Width = (ushort)(rowwidth * 256); sheet.AddColumnInfo(colInfo); XF xf_col = doc.NewXF(); xf_col.HorizontalAlignment = HorizontalAlignments.Centered; xf_col.VerticalAlignment = VerticalAlignments.Centered; xf_col.Pattern = 1; xf_col.PatternColor = Colors.Default30; xf_col.UseBorder = true; xf_col.TopLineStyle = 1; xf_col.TopLineColor = Colors.Black; xf_col.BottomLineStyle = 1; xf_col.BottomLineColor = Colors.Black; xf_col.LeftLineStyle = 1; xf_col.LeftLineColor = Colors.Black; xf_col.RightLineStyle = 1; xf_col.RightLineColor = Colors.Black; xf_col.Font.Bold = true; xf_col.Font.Height = 11 * 20; xf_col.Font.ColorIndex = 1; int rowCount = dsxls.Tables[0].Rows.Count; int colCount = dsxls.Tables[0].Columns.Count; int rowMin = 1, colMin = 1; int rowbegin = 0; if (title.Trim() != "") { MergeArea meaA = new MergeArea(1, 1, 1, dsxls.Tables[0].Columns.Count); sheet.AddMergeArea(meaA);//填加合并单元格 Cell cell_title = cells.Add(1, 1, title); cell_title.Font.Height = 15 * 20; rowbegin = 1; } for (int row = rowbegin; row < rowCount + rowbegin + 1; row++) { if (row == rowbegin) { for (int col = 1; col <= colCount; col++) { Cell cell = cells.Add(rowMin + row, colMin + col - 1, dsxls.Tables[0].Columns[col - 1].ColumnName, xf_col); } } else { for (int col = 1; col <= colCount; col++) { Cell cell = cells.Add(rowMin + row, colMin + col - 1, dsxls.Tables[0].Rows[row - 1 - rowbegin][col - 1].ToString()); } } } try { doc.Save(lujing, true); } catch (Exception ex) { ; } //doc.Send(); //Response.Flush(); //Response.End(); }
/// <summary> /// 把相关数据导出到Excel文件中 /// </summary> /// <param name="dtSource">数据源内容</param> /// <param name="strFileName">导出的Excel文件名</param> public static void Export(DataTable dtSource, string strFileName, string SheetName = "Sheet1", Int32 startRow = 0, Int32 startColumn = 0) { XlsDocument xls = new XlsDocument(); xls.FileName = DateTimeHelper.GetServerDateTime2().ToString("yyyyMMddHHmmssffff", System.Globalization.DateTimeFormatInfo.InvariantInfo); xls.SummaryInformation.Author = "吴建明"; //填加xls文件作者信息 xls.SummaryInformation.NameOfCreatingApplication = "jCodes 项目管理"; //填加xls文件创建程序信息 xls.SummaryInformation.LastSavedBy = "吴建明"; //填加xls文件最后保存者信息 xls.SummaryInformation.Comments = "吴建明添加的项目信息"; //填加xls文件作者信息 xls.SummaryInformation.Title = "项目管理信息"; //填加xls文件标题信息 xls.SummaryInformation.Subject = "jCodes项目管理信息"; //填加文件主题信息 xls.DocumentSummaryInformation.Company = "jCodes.cn"; //填加文件公司信息 Worksheet sheet = xls.Workbook.Worksheets.Add(SheetName); //状态栏标题名称 Cells cells = sheet.Cells; ushort[] colArr = new ushort[dtSource.Columns.Count]; // 保存最大的字节数用于调整最佳宽度 Int32 colIdx = 0; foreach (DataColumn col in dtSource.Columns) { Cell cell = cells.Add(1 + startRow, col.Ordinal + 1 + startColumn, col.ColumnName); cell.Font.FontName = "宋体"; // 字体 cell.HorizontalAlignment = HorizontalAlignments.Centered; cell.VerticalAlignment = VerticalAlignments.Centered; cell.UseBorder = true; // 使用边框 cell.TopLineStyle = 2; // 上边框样式 cell.TopLineColor = Colors.Black; // 上边框颜色 cell.LeftLineStyle = 2; // 左边框样式 cell.LeftLineColor = Colors.Black; // 左边框颜色 cell.RightLineStyle = 2; // 右边框样式 cell.RightLineColor = Colors.Black; // 右边框颜色 cell.BottomLineStyle = 2; cell.BottomLineColor = Colors.Black; cell.Pattern = 1; // 单元格填充风格。如果设定为0,则是纯色填充(无色),1代表没有间隙的实色 cell.PatternColor = Colors.Default2B; // ffff99 cell.PatternBackgroundColor = Colors.Default2B; // 填充的底色 cell.Font.Bold = true; //字体为粗体 // 保存数据对象 colArr[colIdx++] = (ushort)(System.Text.Encoding.Default.GetBytes(col.ColumnName.ToCharArray()).Length + 2); // 默认再加2个字节 } sheet.Rows[(ushort)(startRow + 1)].RowHeight = 18 * 20; #region 填充内容 XF dateStyle = xls.NewXF(); dateStyle.Format = "yyyy-mm-dd"; for (int i = 0; i < dtSource.Rows.Count; i++) { colIdx = 0; for (int j = 0; j < dtSource.Columns.Count; j++) { int rowIndex = i + 2; int colIndex = j + 1; string drValue = dtSource.Rows[i][j].ToString(); Cell cell = null; switch (dtSource.Rows[i][j].GetType().ToString()) { case "System.String": //字符串类型 cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, drValue); if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(drValue.ToCharArray()).Length + 2)) { colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(drValue.ToCharArray()).Length + 2); } break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, dateV, dateStyle); if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(dateV.ToString().ToCharArray()).Length + 2)) { colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(dateV.ToString().ToCharArray()).Length + 2); } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, boolV); if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(boolV.ToString().ToCharArray()).Length + 2)) { colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(boolV.ToString().ToCharArray()).Length + 2); } break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, intV); if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(intV.ToString().ToCharArray()).Length + 2)) { colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(intV.ToString().ToCharArray()).Length + 2); } break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, doubV); if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(doubV.ToString().ToCharArray()).Length + 2)) { colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(doubV.ToString().ToCharArray()).Length + 2); } break; case "System.DBNull": //空值处理 cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, null); break; default: cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, null); break; } // 下一列 colIdx++; if (null != cell) { 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.Pattern = 1; // 单元格填充风格。如果设定为0,则是纯色填充(无色),1代表没有间隙的实色 cell.PatternColor = Colors.White; // 白色 cell.PatternBackgroundColor = Colors.White; // 填充的底色 } } } #endregion #region 宽度最优 colIdx = 0; foreach (DataColumn c in dtSource.Columns) { ColumnInfo col = new ColumnInfo(xls, sheet); // 列对象 col.ColumnIndexStart = (ushort)(startColumn + colIdx); // 起始列为第1列,索引从0开始 col.ColumnIndexEnd = (ushort)(startColumn + colIdx); // 终止列为第1列,索引从0开始 col.Width = (ushort)((ushort)colArr[colIdx] * 256); // 列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(col); // 把格式附加到sheet页上 colIdx++; } #endregion xls.FileName = strFileName; xls.Save(true); }
/// <summary> /// 导出申请单excel,包括我的申请和我的审批 /// </summary> /// <param name="fc"></param> public void ExporDRData(string queryJson, string pageType) { SearchMyApplyParams p = JsonConvert.DeserializeObject <SearchMyApplyParams>(queryJson); p.account = currentAccount; p.userId = currentUser.userId; p.userName = currentUser.userName; var result = new List <CheckApplyListModel>(); string fileName = ""; if (pageType.Equals("apply")) { fileName = "我的送货申请_"; result = new DRSv().SearchMyApplyList(p, canCheckAll).ToList(); } else if (pageType.Equals("audit")) { fileName = "审核送货申请_"; result = new DRSv().SearchMyAuditList(p, canCheckAll).ToList(); } ushort[] colWidth = new ushort[] { 16, 18, 12, 16, 10, 24, 32, 16, 16, 12, 18, 18, 16, 16, 16, 16, 16, 18, 16, 18, 16 }; string[] colName = new string[] { "发货日期", "送货单号", "申请状态", "订单编号", "分录号", "物料名称", "规格型号", "订单数量", "申请数量", "单位", "物料编码", "备注", "订单类型", "采购方式", "订料员", "采购员", "贸易类型", "PR单号", "采购日期", "入库单号", "入库日期" }; //設置excel文件名和sheet名 XlsDocument xls = new XlsDocument(); xls.FileName = fileName + DateTime.Now.ToString("MMddHHmmss"); Worksheet sheet = xls.Workbook.Worksheets.Add("申请列表"); //设置各种样式 //标题样式 XF boldXF = xls.NewXF(); boldXF.HorizontalAlignment = HorizontalAlignments.Centered; boldXF.Font.Height = 12 * 20; boldXF.Font.FontName = "宋体"; boldXF.Font.Bold = true; //设置列宽 ColumnInfo col; for (ushort i = 0; i < colWidth.Length; i++) { col = new ColumnInfo(xls, sheet); col.ColumnIndexStart = i; col.ColumnIndexEnd = i; col.Width = (ushort)(colWidth[i] * 256); sheet.AddColumnInfo(col); } Cells cells = sheet.Cells; int rowIndex = 1; int colIndex = 1; //设置标题 foreach (var name in colName) { cells.Add(rowIndex, colIndex++, name, boldXF); } foreach (var d in result) { colIndex = 1; //"发货日期", "送货单号", "申请状态", "订单编号", "分录号", "物料名称", //"规格型号", "订单数量", "申请数量", "单位", "物料编码","订单类型", //"采购方式","订料员","采购员","贸易类型","PR单号","采购日期","入库单号","入库日期" cells.Add(++rowIndex, colIndex, ((DateTime)d.sendDate).ToString("yyyy-MM-dd")); cells.Add(rowIndex, ++colIndex, d.billNo); cells.Add(rowIndex, ++colIndex, d.pStatus); cells.Add(rowIndex, ++colIndex, d.poNo); cells.Add(rowIndex, ++colIndex, d.poEntryId); cells.Add(rowIndex, ++colIndex, d.itemName); cells.Add(rowIndex, ++colIndex, d.itemModel); cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.poQty)); cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.sendQty)); cells.Add(rowIndex, ++colIndex, d.unitName); cells.Add(rowIndex, ++colIndex, d.itemNumber); cells.Add(rowIndex, ++colIndex, d.comment); cells.Add(rowIndex, ++colIndex, d.billType); cells.Add(rowIndex, ++colIndex, d.buyType); cells.Add(rowIndex, ++colIndex, d.matOrderName); cells.Add(rowIndex, ++colIndex, d.buyerName); cells.Add(rowIndex, ++colIndex, d.tradeTypeName); cells.Add(rowIndex, ++colIndex, d.prNo); cells.Add(rowIndex, ++colIndex, ((DateTime)d.poDate).ToString("yyyy-MM-dd")); cells.Add(rowIndex, ++colIndex, d.inStockBillNo); cells.Add(rowIndex, ++colIndex, d.inStockBillDate == null?"":((DateTime)d.inStockBillDate).ToString("yyyy-MM-dd")); } xls.Send(); WLog("导出申请单Excel", fileName + ":" + queryJson); }
/// <summary> /// 导出生成excel文件 /// </summary> /// <param name="dt">datatable</param> /// <param name="fileName">文件名.xls</param> /// <param name="longColumnsIndex">需要设置长宽度的列的集合eg."1,2,3,4"</param> public static void CreateXls(DataTable dt, string fileName, string longColumnsIndex) { XlsDocument xls = new XlsDocument(); if (System.Web.HttpContext.Current.Request.UserAgent.ToLower().IndexOf("msie") > -1) { xls.FileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8); } else { xls.FileName = fileName; } int rowIndex = 1; int colIndex = 0; Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称 Cells cells = sheet.Cells; //设置整体列宽带 ColumnInfo colInfo = new ColumnInfo(xls, sheet); colInfo.ColumnIndexStart = 0; colInfo.ColumnIndexEnd = 22; colInfo.Width = 14 * 256; sheet.AddColumnInfo(colInfo); if (longColumnsIndex.Length > 0) { //单独设置需要长宽度的列 string[] longIndexs = longColumnsIndex.Split(','); if (longIndexs.Length > 0) { //遍历数组,设置每一列的宽度 for (int i = 0; i < longIndexs.Length; i++) { ColumnInfo colInfo1 = new ColumnInfo(xls, sheet); colInfo1.ColumnIndexStart = ushort.Parse(longIndexs[i]); colInfo1.ColumnIndexEnd = ushort.Parse(longIndexs[i]); colInfo1.Width = 36 * 256; sheet.AddColumnInfo(colInfo1); } } } //设置样式 XF xf = xls.NewXF(); xf.UseProtection = false; xf.HorizontalAlignment = HorizontalAlignments.Centered; xf.VerticalAlignment = VerticalAlignments.Centered; xf.TextWrapRight = true; xf.UseBorder = true; xf.TopLineStyle = 1; xf.TopLineColor = Colors.Black; xf.BottomLineStyle = 1; xf.BottomLineColor = Colors.Black; xf.LeftLineStyle = 1; xf.LeftLineColor = Colors.Black; xf.RightLineStyle = 1; xf.RightLineColor = Colors.Black; xf.Font.Bold = true; // foreach (DataColumn col in dt.Columns) { colIndex++; Cell cell = cells.Add(1, colIndex, col.ColumnName, xf); } //sheet.Rows[1].RowHeight = 24 * 20; //填充数据 foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dt.Columns) { colIndex++; Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型 //如果你数据库里的数据都是数字的话 最好转换一下,不然导入到Excel里是以字符串形式显示。 cell.Font.FontFamily = FontFamilies.Roman; //字体 cell.Font.Bold = false; //字体为粗体 } //设置行高 //sheet.Rows[(ushort)rowIndex].RowHeight = 24 * 20; } xls.Send(XlsDocument.SendMethods.Attachment); }
public void ProcessRequest(HttpContext context) { List <DataLists> dataLists = new List <DataLists>(); { int LocationID = 1; //List<DataList> dataLists = new List<DataList>(); int RunMachNum = 0; int AllMachNum; using (JDJS_WMS_DB_USEREntities wms = new JDJS_WMS_DB_USEREntities()) { var devices = wms.JDJS_WMS_Device_Info; AllMachNum = devices.Count(); foreach (var device in devices) { int cncids = device.ID; var state = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault(); if (state != null) { if (state.ProgState == 1) { int cncid = device.ID; var platecnc = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == cncid); if (platecnc.Count() > 0) { //RunMachNum++; } } } DataLists data = new DataLists(); data.cncNum = device.MachNum; data.cncType = device.MachState; data.State = "/"; data.Time = DateTime.Now.Date.ToString(); var state1 = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault(); if (state1 != null) { if (state1.ProgState != -1) { data.State = "开机"; RunMachNum++; } } var processing = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 2); if (processing.Count() > 0) { int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID); int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID); string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString(); data.doingFileName = OrderNum + "-P" + processNum.ToString(); data.JiaWei = processNum.ToString() + "夹"; data.doingProcess = OrderNum + "-" + processNum.ToString() + "序"; var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1); data.surplusNumber = willdo.Count().ToString(); { var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID); if (cncqu.Count() < 1) { data.progress = "0.000000"; } else { var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault(); if (cncstate != null) { int states = Convert.ToInt32(cncstate.ProgState); if (states == 1) { var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault(); if (timestate != null) { double timeMin = Convert.ToDouble(timestate.NowRunTime); double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime); double rate = timeMin / alltime; if (rate < 0) { data.progress = "0.000000"; } else if (rate >= 1) { data.progress = "0.999999"; } else { data.progress = rate.ToString("0.000000"); } } else { data.progress = "0.000000"; } } else { DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time); double nowtime = (DateTime.Now - time).TotalMinutes; var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList(); if (info.Count() < 1) { data.progress = "0.000000"; } else { var timeInfo = info.OrderByDescending(r => r.EndTime); DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime); if (oldTime > time) { data.progress = "1.000000"; } else { data.progress = "0.000000"; } } } } else { data.progress = "0.000000"; } } } var next = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1).OrderBy(r => r.StartTime); if (next.Count() > 0) { int ids = Convert.ToInt32(next.FirstOrDefault().ProcessID); int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID); string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == next.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString(); data.waitingFileName = OrderNums + "-P" + processNums.ToString(); } var nextTask = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1 && r.ProcessID != processing.FirstOrDefault().ProcessID).OrderBy(r => r.StartTime); if (nextTask.Count() > 0) { int ids = Convert.ToInt32(nextTask.FirstOrDefault().ProcessID); int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID); string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == nextTask.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString(); data.waitingProcess = OrderNums + "-" + processNums.ToString() + "序"; } } else { processing = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1).OrderBy(r => r.StartTime); if (processing.Count() > 0) { if (processing.Count() > 1) { var pro = processing.ToList(); int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID); int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID); string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString(); data.doingFileName = OrderNum + "-P" + processNum.ToString(); data.JiaWei = processNum.ToString() + "夹"; data.doingProcess = OrderNum + "-" + processNum.ToString() + "序"; var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1); data.surplusNumber = willdo.Count().ToString(); { var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID); if (cncqu.Count() < 1) { data.progress = "0.000000"; } else { var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault(); if (cncstate != null) { int states = Convert.ToInt32(cncstate.ProgState); if (states == 1) { var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault(); if (timestate != null) { double timeMin = Convert.ToDouble(timestate.NowRunTime); double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime); double rate = timeMin / alltime; if (rate < 0) { data.progress = "0.000000"; } else if (rate >= 1) { data.progress = "0.999999"; } else { data.progress = rate.ToString("0.000000"); } } else { data.progress = "0.000000"; } } else { DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time); double nowtime = (DateTime.Now - time).TotalMinutes; var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList(); if (info.Count() < 1) { data.progress = "0.000000"; } else { var timeInfo = info.OrderByDescending(r => r.EndTime); DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime); if (oldTime > time) { data.progress = "1.000000"; } else { data.progress = "0.000000"; } } } } else { data.progress = "0.000000"; } } } int ids = Convert.ToInt32(pro[1].ProcessID); int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID); int idsss = Convert.ToInt32(pro[1].OrderID); string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == idsss).FirstOrDefault().Order_Number.ToString(); data.waitingFileName = OrderNums + "-P" + processNums.ToString(); var nextTask = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1 && r.ProcessID != ids).OrderBy(r => r.StartTime); if (nextTask.Count() > 0) { int idss = Convert.ToInt32(nextTask.FirstOrDefault().ProcessID); int processNumss = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == idss).FirstOrDefault().ProcessID); string OrderNumss = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == nextTask.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString(); data.waitingProcess = OrderNumss + "-" + processNumss.ToString() + "序"; } } else { int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID); int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID); string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString(); data.doingFileName = OrderNum + "-P" + processNum.ToString(); data.JiaWei = processNum.ToString() + "夹"; data.doingProcess = OrderNum + "-" + processNum.ToString() + "序"; var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1); data.surplusNumber = willdo.Count().ToString(); { var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID); if (cncqu.Count() < 1) { data.progress = "0.000000"; } else { var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault(); if (cncstate != null) { int states = Convert.ToInt32(cncstate.ProgState); if (states == 1) { var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault(); if (timestate != null) { double timeMin = Convert.ToDouble(timestate.NowRunTime); double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime); double rate = timeMin / alltime; if (rate < 0) { data.progress = "0.000000"; } else if (rate >= 1) { data.progress = "0.999999"; } else { data.progress = rate.ToString("0.000000"); } } else { data.progress = "0.000000"; } } else { DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time); double nowtime = (DateTime.Now - time).TotalMinutes; var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList(); if (info.Count() < 1) { data.progress = "0.000000"; } else { var timeInfo = info.OrderByDescending(r => r.EndTime); DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime); if (oldTime > time) { data.progress = "1.000000"; } else { data.progress = "0.000000"; } } } } else { data.progress = "0.000000"; } } } } } else { data.doingFileName = "无排配"; data.JiaWei = "/"; } } var endProcess = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == cncids && r.isFlag != 0); var end = endProcess.OrderByDescending(r => r.EndTime).FirstOrDefault(); if (end != null) { data.StopTime = end.EndTime.ToString(); } dataLists.Add(data); } foreach (var item in dataLists) { item.RunMachNum = RunMachNum.ToString(); item.AllMachNum = AllMachNum.ToString(); item.OtherMachNum = (AllMachNum - RunMachNum).ToString(); } var lists = dataLists.OrderByDescending(r => r.progress); System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); var model = new { code = 0, data = lists }; var json = serializer.Serialize(model); } } XlsDocument doc = new XlsDocument(); doc.FileName = DateTime.Now.ToString("yyyyMMddhhmmssms") + "样机开机状况.xls"; Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1"); Cells cells = sheet.Cells; ColumnInfo col = new ColumnInfo(doc, sheet); //创建列样式对象 col.ColumnIndexStart = 5; //起始列,索引从0开始 col.ColumnIndexEnd = 5; //结束列,索引从0开始,这样为第1列、第2列使用此样式 col.Width = 8888; //宽度,字节长度,ushort类型 0~65535 sheet.AddColumnInfo(col); //将列样式作用于此工作表 ColumnInfo col2 = new ColumnInfo(doc, sheet); //创建列样式对象 col2.ColumnIndexStart = 2; //起始列,索引从0开始 col2.ColumnIndexEnd = 2; //结束列,索引从0开始,这样为第1列、第2列使用此样式 col2.Width = 4444; //宽度,字节长度,ushort类型 0~65535 sheet.AddColumnInfo(col2); //将列样式作用于此工作表 ColumnInfo col4 = new ColumnInfo(doc, sheet); //创建列样式对象 col4.ColumnIndexStart = 4; //起始列,索引从0开始 col4.ColumnIndexEnd = 4; //结束列,索引从0开始,这样为第1列、第2列使用此样式 col4.Width = 4444; //宽度,字节长度,ushort类型 0~65535 sheet.AddColumnInfo(col4); //将列样式作用于此工作表 MergeArea ma1 = new MergeArea(1, 1, 1, 7); //合并单元格,第2行第5列 到 第3行第7列 sheet.AddMergeArea(ma1); //添加合并单元格到工作表 MergeArea ma2 = new MergeArea(2, 2, 2, 6); //合并单元格,第2行第5列 到 第3行第7列 sheet.AddMergeArea(ma2); //添加合并单元格到工作表 XF xf = doc.NewXF(); //单元格样式对象 xf.VerticalAlignment = VerticalAlignments.Centered; //垂直居中 xf.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中 xf.Pattern = 0; //填充风格,0为无色填充,1为没有间隙的纯色填充 xf.PatternColor = Colors.Green; //填充背景底色 xf.Font.ColorIndex = 0; //字体前景色颜色,未知值 xf.Font.FontName = "黑体"; //字体 xf.Font.Height = 15 * 15; //字体大小 //xf.UseBorder = false ; //使用边框 //xf.BottomLineStyle = 1; //边框样式 //xf.BottomLineColor = Colors.Black; //边框颜色 XF xf1 = doc.NewXF(); //单元格样式对象 xf1.VerticalAlignment = VerticalAlignments.Centered; //垂直居中 xf1.HorizontalAlignment = HorizontalAlignments.Right; //水平居中 xf1.Pattern = 0; //填充风格,0为无色填充,1为没有间隙的纯色填充 xf1.PatternColor = Colors.Green; //填充背景底色 xf1.Font.ColorIndex = 0; //字体前景色颜色,未知值 xf1.Font.FontName = "宋体"; //字体 xf1.Font.Height = 13 * 13; //字体大小 //xf1.UseBorder = false ; //使用边框 //xf1.BottomLineStyle = 1; //边框样式 //xf1.BottomLineColor = Colors.Black; //边框颜色 XF xf2 = doc.NewXF(); //单元格样式对象 xf2.VerticalAlignment = VerticalAlignments.Centered; //垂直居中 xf2.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中 xf2.Pattern = 0; //填充风格,0为无色填充,1为没有间隙的纯色填充 xf2.PatternColor = Colors.Green; //填充背景底色 xf2.Font.ColorIndex = 0; //字体前景色颜色,未知值 xf2.Font.FontName = "宋体"; //字体 xf2.Font.Height = 13 * 13; //字体大小 //xf2.UseBorder = false; //使用边框 //xf2.BottomLineStyle = 1; //边框样式 //xf2.BottomLineColor = Colors.Black; //边框颜色 XF xf3 = doc.NewXF(); //单元格样式对象 xf3.VerticalAlignment = VerticalAlignments.Centered; //垂直居中 xf3.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中 xf3.Pattern = 1; //填充风格,0为无色填充,1为没有间隙的纯色填充 xf3.PatternColor = Colors.Green; //填充背景底色 xf3.Font.ColorIndex = 0; //字体前景色颜色,未知值 xf3.Font.FontName = "宋体"; //字体 xf3.Font.Height = 13 * 13; //字体大小 //xf2.UseBorder = false; //使用边框 //xf2.BottomLineStyle = 1; //边框样式 //xf2.BottomLineColor = Colors.Black; //边框颜色 cells.Add(1, 1, "智能中心样机开机状况", xf); //添加单元格内容,第2行,第5列,内容,索引从1开始 cells.Add(2, 2, "日期:" + DateTime.Now.Date.ToShortDateString(), xf1); //添加单元格内容,第2行,第5列,内容,索引从1开始 cells.Add(3, 1, "类别", xf2); cells.Add(3, 2, "机台号", xf2); cells.Add(3, 3, "文件名", xf2); cells.Add(3, 4, "夹位", xf2); cells.Add(3, 5, "开机状态", xf2); cells.Add(3, 6, "CNC预计结束时间", xf2); cells.Add(3, 7, "备注", xf2); int index = 4; for (int i = 0; i < dataLists.Count(); i++) { cells.Add(i + 4, 1, dataLists[i].cncType, xf2); cells.Add(i + 4, 2, dataLists[i].cncNum, xf2); if (dataLists[i].doingFileName == "无排配") { cells.Add(i + 4, 3, dataLists[i].doingFileName, xf2); cells.Add(i + 4, 4, dataLists[i].JiaWei, xf2); cells.Add(i + 4, 5, dataLists[i].State, xf2); } else { cells.Add(i + 4, 3, dataLists[i].doingFileName, xf3); cells.Add(i + 4, 4, dataLists[i].JiaWei, xf3); cells.Add(i + 4, 5, dataLists[i].State, xf3); } cells.Add(i + 4, 6, dataLists[i].StopTime, xf2); cells.Add(i + 4, 7, "", xf2); index++; } cells.Add(index, 1, "机台总数", xf); //cells.Add(index, 2, dataLists[lastIndex].AllMachNum, xf2); cells.Add(index, 3, "实际开机数量", xf); //cells.Add(index, 4, dataLists[lastIndex].RunMachNum, xf2); cells.Add(index, 5, "未开机数量", xf); //cells.Add(index, 6, dataLists[lastIndex].OtherMachNum, xf2); if (dataLists.Count() > 0) { int lastIndex = dataLists.Count() - 1; //cells.Add(index, 1, "机台总数", xf); cells.Add(index, 2, dataLists[lastIndex].AllMachNum, xf2); //cells.Add(index, 3, "实际开机数量", xf); cells.Add(index, 4, dataLists[lastIndex].RunMachNum, xf2); //cells.Add(index, 5, "未开机数量", xf); cells.Add(index, 6, dataLists[lastIndex].OtherMachNum, xf2); } PathInfo pathInfo = new PathInfo(); string path = pathInfo.upLoadPath() + @"ExcelFile\"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } doc.Save(path); context.Response.Write(pathInfo.downLoadPath() + @"ExcelFile\" + doc.FileName); }
/// <summary> /// 导出excel通用方法 /// </summary> /// <param name="myData">导出的数据</param> private void ExportExcel(List <ExcelData> myData) { //列名: string[] colName = new string[] { "审核结果", "流水号", "备料日期", "客户型号", "版本号", "产品类别", "客户编码", "客户名称", "终端客户编码", "终端客户名称", "产品代码", "产品名称", "产品型号", "备料数量(粒)", "营业员", "产品用途", "订料良率", "产品类型", "表面处理", "是否半孔板", "是否出样", "贸易类型", "事业部", "备料类型", "协议号", "制单人", "备料项目", "备注", "消耗计划" }; //設置excel文件名和sheet名 XlsDocument xls = new XlsDocument(); xls.FileName = string.Format("电子备料单_{0}.xls", DateTime.Now.ToString("yyyyMMdd")); Worksheet sheet = xls.Workbook.Worksheets.Add("备料信息列表"); //设置各种样式 //标题样式 XF boldXF = xls.NewXF(); boldXF.HorizontalAlignment = HorizontalAlignments.Centered; boldXF.Font.Height = 12 * 20; boldXF.Font.FontName = "宋体"; boldXF.Font.Bold = true; //设置列宽 ColumnInfo col; for (ushort i = 0; i < colName.Length; i++) { col = new ColumnInfo(xls, sheet); col.ColumnIndexStart = i; col.ColumnIndexEnd = i; col.Width = (ushort)(16 * 256); sheet.AddColumnInfo(col); } Cells cells = sheet.Cells; int rowIndex = 1; int colIndex = 1; //设置标题 foreach (var name in colName) { cells.Add(rowIndex, colIndex++, name, boldXF); } foreach (var d in myData) { colIndex = 1; // "审核结果","流水号","备料日期","客户型号","版本号","产品类别","客户编码","客户名称","终端客户编码","终端客户名称", // "产品代码","产品名称","产品型号","备料数量(粒)","营业员","产品用途","订料良率","产品类型","表面处理","是否半孔板", // "是否出样","贸易类型","事业部","备料类型","协议号","制单人","备料项目","备注","消耗计划" cells.Add(++rowIndex, colIndex, d.auditStatus); cells.Add(rowIndex, ++colIndex, d.h.sys_no); cells.Add(rowIndex, ++colIndex, d.h.bill_date.ToString("yyyy-MM-dd")); cells.Add(rowIndex, ++colIndex, d.h.customer_pn); cells.Add(rowIndex, ++colIndex, d.h.version_no); cells.Add(rowIndex, ++colIndex, d.h.product_type); cells.Add(rowIndex, ++colIndex, d.h.customer_no); cells.Add(rowIndex, ++colIndex, d.h.customer_name); cells.Add(rowIndex, ++colIndex, d.h.zz_customer_no); cells.Add(rowIndex, ++colIndex, d.h.zz_customer_name); cells.Add(rowIndex, ++colIndex, d.h.product_no); cells.Add(rowIndex, ++colIndex, d.h.product_name); cells.Add(rowIndex, ++colIndex, d.h.product_model); cells.Add(rowIndex, ++colIndex, d.h.bl_qty); cells.Add(rowIndex, ++colIndex, d.h.clerk_name); cells.Add(rowIndex, ++colIndex, d.h.usage); cells.Add(rowIndex, ++colIndex, d.h.good_percent); cells.Add(rowIndex, ++colIndex, d.h.product_classification); cells.Add(rowIndex, ++colIndex, d.h.surface_type); cells.Add(rowIndex, ++colIndex, d.h.is_half_hole); cells.Add(rowIndex, ++colIndex, d.h.is_make_sample); cells.Add(rowIndex, ++colIndex, d.h.trade_type); cells.Add(rowIndex, ++colIndex, d.h.bus_name); cells.Add(rowIndex, ++colIndex, d.h.bl_type); cells.Add(rowIndex, ++colIndex, d.h.protocol_no); cells.Add(rowIndex, ++colIndex, d.h.applier_name); cells.Add(rowIndex, ++colIndex, d.h.bl_project); cells.Add(rowIndex, ++colIndex, d.h.comment); cells.Add(rowIndex, ++colIndex, d.h.bl_plan); } xls.Send(); }
/// <summary> /// 将DataTable导出为Xls文件 /// neo 2010-12-02 /// </summary> /// <param name="ExcelData"></param> /// <param name="GridView1"></param> /// <param name="sExcelDir"></param> /// <param name="sSheetName"></param> /// <returns>返回生成Excel文件本地路径</returns> public static string SaveAsExcel(DataTable ExcelData, GridView GridView1, string sExcelDir, string sSheetName) { if (ExcelData == null) { return(string.Empty); } NameValueCollection ExcelColumns = new NameValueCollection(); foreach (DataControlField GridColumn in GridView1.Columns) { // 隐藏列 不导出 if (GridColumn.Visible == false) { continue; } // 只导出BoundField列 if (GridColumn.GetType().FullName != "System.Web.UI.WebControls.BoundField") { continue; } // 如果没有列名 不导出 if (GridColumn.HeaderText == string.Empty) { continue; } BoundField BoundField1 = GridColumn as BoundField; ExcelColumns.Add(BoundField1.HeaderText, BoundField1.DataField); } // 生成随机唯一文件名 string sExcelFileName = Guid.NewGuid().ToString() + ".xls"; // 创建Excel文档 XlsDocument ExcelDoc = new XlsDocument(); ExcelDoc.FileName = sExcelFileName; // 添加Sheet Worksheet Sheet = ExcelDoc.Workbook.Worksheets.Add(sSheetName); //Sheet.Name = sSheetName; // 添加第一行列名 ColumnInfo ColumnInfo1 = new ColumnInfo(ExcelDoc, Sheet); ColumnInfo1.ColumnIndexStart = 0; ColumnInfo1.ColumnIndexEnd = Convert.ToUInt16(ExcelColumns.Count - 1); ColumnInfo1.Width = 25 * 150; Sheet.AddColumnInfo(ColumnInfo1); // 生成第一行Excel列 int i = 1; foreach (string sHeaderText in ExcelColumns.AllKeys) { Cell CellObj = Sheet.Cells.Add(1, i, sHeaderText); CellObj.Font.Weight = FontWeight.Bold; CellObj.Pattern = 1; CellObj.PatternColor = Colors.Silver; CellObj.UseBorder = true; CellObj.LeftLineStyle = 1; CellObj.LeftLineColor = Colors.Black; CellObj.RightLineStyle = 1; CellObj.RightLineColor = Colors.Black; CellObj.TopLineStyle = 1; CellObj.TopLineColor = Colors.Black; CellObj.TopLineStyle = 1; CellObj.BottomLineColor = Colors.Black; i++; } int j = 2; foreach (DataRow RowObj in ExcelData.Rows) { int m = 1; foreach (string sHeaderText in ExcelColumns.AllKeys) { // 获取对应的DataField string sDataField = ExcelColumns[sHeaderText]; object oValue = RowObj[sDataField]; if (oValue.GetType().FullName == "System.Byte") { oValue = Convert.ToInt16(oValue); } else if (oValue.GetType().FullName == "System.DBNull") { oValue = string.Empty; } else if (oValue.GetType().FullName == "System.String") { oValue = oValue.ToString(); } else if (oValue.GetType().FullName == "System.DateTime") { oValue = Convert.ToDateTime(oValue).ToString(); } Cell CellObj = Sheet.Cells.Add(j, m, oValue); CellObj.UseBorder = true; CellObj.LeftLineStyle = 1; CellObj.LeftLineColor = Colors.Black; CellObj.RightLineStyle = 1; CellObj.RightLineColor = Colors.Black; CellObj.TopLineStyle = 1; CellObj.TopLineColor = Colors.Black; CellObj.TopLineStyle = 1; CellObj.BottomLineColor = Colors.Black; m++; } j++; } string sPath = sExcelDir + sExcelFileName; if (File.Exists(sPath) == true) { File.Delete(sPath); } ExcelDoc.Save(sExcelDir, true); return(sPath); }
//导出数据到Excel void butExportData_Click(object sender, EventArgs e) { XlsDocument xls = new XlsDocument(); //新建一个xls文档 xls.FileName = "train.xls"; //设定文件名 List <String> lineNameList = BusinessRule.Line.GetTrainLindName(); JTable tab1 = new WebFrame.Data.JTable(); tab1.TableName = "linestationview"; foreach (String m in lineNameList) { string sheetName = m; Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"chc 实例"的sheet页 Cells cells = sheet.Cells; //Cells实例是sheet页中单元格(cell)集合 ////设置列的宽度 #region 设置列的宽度 ColumnInfo colInfo = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo.ColumnIndexStart = 0; //起始列为第一列,ColumnIndexStart是从0开始 colInfo.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) ColumnInfo colInfo1 = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo1.ColumnIndexStart = 1; //起始列为第一列,ColumnIndexStart是从0开始 colInfo1.Width = 20 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo1); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) ColumnInfo colInfo2 = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo2.ColumnIndexStart = 2; //起始列为第一列,ColumnIndexStart是从0开始 colInfo2.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo2); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) ColumnInfo colInfo3 = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo3.ColumnIndexStart = 3; //起始列为第一列,ColumnIndexStart是从0开始 colInfo3.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo3); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) ColumnInfo colInfo4 = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo4.ColumnIndexStart = 4; //起始列为第一列,ColumnIndexStart是从0开始 colInfo4.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo4); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) ColumnInfo colInfo5 = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo5.ColumnIndexStart = 5; //起始列为第一列,ColumnIndexStart是从0开始 colInfo5.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo5); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) ColumnInfo colInfo6 = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo6.ColumnIndexStart = 6; //起始列为第一列,ColumnIndexStart是从0开始 colInfo6.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo6); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) ColumnInfo colInfo7 = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo7.ColumnIndexStart = 7; //起始列为第一列,ColumnIndexStart是从0开始 colInfo7.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo7); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) ColumnInfo colInfo8 = new ColumnInfo(xls, sheet); //生成列格式对象 //设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) colInfo8.ColumnIndexStart = 8; //起始列为第一列,ColumnIndexStart是从0开始 colInfo8.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(colInfo8); //把格式附加到sheet页上(注:AddColumnInfo方法有点小问题,不给把colInfo对象多次附给sheet页) #endregion XF cellXFColumnText = xls.NewXF(); cellXFColumnText.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中 cells.Add(1, 1, "线名代码", cellXFColumnText); //设定第一行,第一列单元格的值 cells.Add(1, 2, "线名", cellXFColumnText); //设定第一行,第二列单元格的值 cells.Add(1, 3, "站名代码", cellXFColumnText); //设定第一行,第三列单元格的值 cells.Add(1, 4, "始发站", cellXFColumnText); //设定第一行,第四列单元格的值 cells.Add(1, 5, "到达站", cellXFColumnText); //设定第一行,第四列单元格的值 cells.Add(1, 6, "里程", cellXFColumnText); cells.Add(1, 7, "局内", cellXFColumnText); cells.Add(1, 8, "电气化", cellXFColumnText); cells.Add(1, 9, "轮渡标志", cellXFColumnText); int currentRow = 2; DataTable dt = BusinessRule.Line.GetTrainLindData(tab1, m); foreach (DataRow dr in dt.Rows) { String jnFlag = dr["jnflag"].ToString().Trim(); if (jnFlag != String.Empty) { jnFlag = "是"; } String dqh = dr["dqh"].ToString().Trim(); if (dqh != String.Empty) { dqh = "是"; } String shipFlag = dr["shipflag"].ToString().Trim(); if (shipFlag != String.Empty) { shipFlag = "是"; } cells.Add(currentRow, 1, Convert.ToString(dr["lineid"]), cellXFColumnText); cells.Add(currentRow, 2, Convert.ToString(dr["linename"]), cellXFColumnText); cells.Add(currentRow, 3, Convert.ToString(""), cellXFColumnText); cells.Add(currentRow, 4, Convert.ToString(dr["Astation"]), cellXFColumnText); cells.Add(currentRow, 5, Convert.ToString(dr["bstation"]), cellXFColumnText); cells.Add(currentRow, 6, Convert.ToString(dr["miles"]), cellXFColumnText); cells.Add(currentRow, 7, jnFlag, cellXFColumnText); cells.Add(currentRow, 8, dqh, cellXFColumnText); cells.Add(currentRow, 9, shipFlag, cellXFColumnText); currentRow++; } } tab1.Close(); xls.Send(XlsDocument.SendMethods.Inline); }
/// <summary> /// 直接导出Excel /// </summary> /// <param name="dataTable">数据</param> /// <param name="filePathName">文件保存路径(E:\\excelname.xls)</param> /// <param name="sheetName">表单名称(表单1)</param> /// <param name="headers">头部显示的文字(编号,名称,...),如果为空则取dataTable的头</param> /// <returns>返回成功与否</returns> public static bool Export(DataTable dataTable, string filePathName, string sheetName, params string[] headers) { try { if (dataTable == null) { return(false); } if (filePathName == null) { return(false); } if (sheetName == null || sheetName.Trim() == "") { sheetName = "Sheet"; } if (headers == null) { headers = new string[0]; } string path = Path.GetDirectoryName(filePathName); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } List <string> headerList = headers.ToList(); if (headers.Length < dataTable.Columns.Count) { for (int i = headers.Length; i < dataTable.Columns.Count; i++) { headerList.Add(dataTable.Columns[i].ColumnName); } } XlsDocument xls = new XlsDocument();//新建Excel int startIndex = 0; int endIndex = startIndex + 65533; if (dataTable.Rows.Count < endIndex + 1) { endIndex = dataTable.Rows.Count - 1; } string sheetText = sheetName; int count = 0; do { //新建一个表单 Worksheet ws = xls.Workbook.Worksheets.Add(sheetText); //开始到出数据 ColumnInfo columnInfo = new ColumnInfo(xls, ws);//头 columnInfo.ColumnIndexStart = 0; columnInfo.ColumnIndexEnd = (ushort)(headerList.Count - 1); columnInfo.Width = 21 * 256; ws.AddColumnInfo(columnInfo); for (ushort i = 1; i <= headerList.Count; i++) { ws.Cells.Add((ushort)1, i, headerList[i - 1]); ws.Rows[1].GetCell(i).Font.Bold = true; ws.Rows[1].GetCell(i).Font.Height = 13 * 20; ws.Rows[1].GetCell(i).TopLineStyle = 1; ws.Rows[1].GetCell(i).BottomLineStyle = 2; ws.Rows[1].GetCell(i).LeftLineStyle = 1; ws.Rows[1].GetCell(i).RightLineStyle = 1; ws.Rows[1].GetCell(i).Pattern = 1; ws.Rows[1].GetCell(i).PatternColor = org.in2bits.MyXls.Colors.Default1F; } for (int i = startIndex; i <= endIndex; i++) { object[] objs = dataTable.Rows[i].ItemArray; for (ushort j = 1; j <= objs.Length; j++) { if (objs[j - 1] == DBNull.Value) { objs[j - 1] = ""; } else if (objs[j - 1].GetType().Equals(typeof(DateTime))) { objs[j - 1] = ((DateTime)objs[j - 1]).ToString("yyyy-MM-dd HH:mm:ss"); } else { Type type = objs[j - 1].GetType(); if (type.Equals(typeof(int)) || type.Equals(typeof(Int64)) || type.Equals(typeof(float)) || type.Equals(typeof(double)) || type.Equals(typeof(decimal)) || type.Equals(typeof(byte)) || type.Equals(typeof(ushort)) || type.Equals(typeof(uint)) || type.Equals(typeof(ulong))) { objs[j - 1] = "'" + objs[j - 1]; } } ws.Cells.Add((ushort)(i + 2 - startIndex), j, objs[j - 1]); ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).Font.Height = 13 * 20; ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).TopLineStyle = 1; ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).BottomLineStyle = 1; ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).LeftLineStyle = 1; ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).RightLineStyle = 1; } } //--结束导出数据 //下一个表单计算 startIndex = endIndex + 1; if (startIndex >= dataTable.Rows.Count) { break; } else { endIndex = startIndex + 65533; if (dataTable.Rows.Count < endIndex + 1) { endIndex = dataTable.Rows.Count - 1; } sheetText = sheetName + "-" + (++count); } } while (true); xls.Save(filePathName, true); xls = null; return(true); } catch { return(false); } }