public static bool DataTableToExcel2_sheet0(DataTable datatable, string filepath, out string error) { error = ""; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); try { if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return(false); } //为单元格添加样式 Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()]; //设置居中 style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //设置背景颜色 style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); // style.Pattern = BackgroundType.Solid; style.Font.IsBold = true; int rowIndex = 0; for (int i = 0; i < datatable.Columns.Count; i++) { DataColumn col = datatable.Columns[i]; string columnName = col.Caption ?? col.ColumnName; wb.Worksheets[0].Cells[rowIndex, i].PutValue(columnName); wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style); } rowIndex++; foreach (DataRow row in datatable.Rows) { for (int i = 0; i < datatable.Columns.Count; i++) { wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString()); } rowIndex++; } for (int k = 0; k < datatable.Columns.Count; k++) { wb.Worksheets[0].AutoFitColumn(k, 0, 150); } wb.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count); wb.Save(filepath); return(true); } catch (Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
private void BindCheckItem(ref Aspose.Cells.Worksheet sheet, string FormNo, string Bu) { //page 格式設定 SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape); Aspose.Cells.Cells cells = sheet.Cells; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()]; NPIMgmt oMgmt = new NPIMgmt("CZ", Bu); NPI_Standard oStandard = oMgmt.InitialLeaveMgmt(); #region//獲取主表資訊 DataTable dtMaster = oStandard.GetPrelaunchInconformity(FormNo); if (dtMaster.Rows.Count > 0) { int templateIndex = 1; //模板row起始位置 int insertIndexEnCounter = templateIndex + 1; //new row起始位置 cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1); cells.CopyRows(cells, templateIndex, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行 string url = "http://icm651.liteon.com/WF_PrelaunchReport/"; for (int i = 0; i < dtMaster.Rows.Count; i++) { DataRow dr = dtMaster.Rows[i]; string FileName = dr["FileName"].ToString(); cells[i + templateIndex, 0].PutValue(dr["ID"].ToString()); cells[i + templateIndex, 1].PutValue(dr["Dept"].ToString()); cells[i + templateIndex, 2].PutValue(dr["CheckItem"].ToString()); cells[i + templateIndex, 3].PutValue(dr["Description"].ToString()); cells[i + templateIndex, 4].PutValue(dr["Status"].ToString()); cells[i + templateIndex, 5].PutValue(dr["Remark"].ToString()); cells[i + templateIndex, 6].PutValue(dr["Suggestion"].ToString()); cells[i + templateIndex, 7].PutValue(dr["CompleteDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["CompleteDate"].ToString()).ToString("yyyy/MM/dd") : dr["CompleteDate"].ToString()); cells[i + templateIndex, 8].PutValue(dr["UpateUser"].ToString()); cells[i + templateIndex, 9].PutValue(dr["UpdateTime"].ToString().Length > 0 ? Convert.ToDateTime(dr["UpdateTime"].ToString()).ToString("yyyy/MM/dd") : dr["UpdateTime"].ToString()); if (!string.IsNullOrEmpty(FileName)) { string destFileName = url + dr["AttacheFile"].ToString(); style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; cells[i + templateIndex, 10].PutValue(FileName); cells[i + templateIndex, 10].SetStyle(style); sheet.Hyperlinks.Add(i + templateIndex, 10, 1, 1, destFileName); cells.Merge(i + templateIndex, 10, 1, 3); } } } #endregion }
/// <summary> /// 调用AsposeCell控件,生成Excel文件 /// </summary> /// <param name="datatable">生成的表格数据</param> /// <param name="relatedPath">服务器相对路径</param> /// <returns></returns> protected virtual bool GenerateExcel(DataTable datatable, string relatedPath) { #region 把DataTable转换为Excel并输出 Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //为单元格添加样式 Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; //设置居中 style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //设置背景颜色 style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); style.Pattern = Aspose.Cells.BackgroundType.Solid; style.Font.IsBold = true; int rowIndex = 0; for (int i = 0; i < datatable.Columns.Count; i++) { DataColumn col = datatable.Columns[i]; string columnName = col.Caption ?? col.ColumnName; workbook.Worksheets[0].Cells[rowIndex, i].PutValue(columnName); workbook.Worksheets[0].Cells[rowIndex, i].SetStyle(style); } rowIndex++; foreach (DataRow row in datatable.Rows) { for (int i = 0; i < datatable.Columns.Count; i++) { workbook.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString()); } rowIndex++; } for (int k = 0; k < datatable.Columns.Count; k++) { workbook.Worksheets[0].AutoFitColumn(k, 0, 150); } workbook.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count); //根据用户创建目录,确保生成的文件不会产生冲突 string realPath = Server.MapPath(relatedPath); string parentPath = Directory.GetParent(realPath).FullName; DirectoryUtil.AssertDirExist(parentPath); workbook.Save(realPath, Aspose.Cells.SaveFormat.Excel97To2003); #endregion return(true); }
/// <summary> /// 将DataTable,导出为Excel /// </summary> /// <param name="dt">DataTable对象</param> /// <param name="path">存储路径</param> /// <param name="sheet">Sheet名称</param> /// <returns></returns> public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path, string sheet) { bool succeed = false; if (dt != null) { try { // Excel Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = sheet; // 开始行、结束行;开始列、结束列 int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; // 列标题 for (int i = 0; i < colCount; i++) { Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()]; s.Font.IsBold = true; cellSheet.Cells[0, i].SetStyle(s); cellSheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName); } // 单元格数据 for (int i = 1; i <= rowCount; i++) { for (int j = 0; j < colCount; j++) { cellSheet.Cells[i, j].PutValue(dt.Rows[i - 1][j].ToString()); } } cellSheet.AutoFitColumns(); path = System.IO.Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception ex) { MessageBox.Show(ex.ToString()); succeed = false; } } return(succeed); }
public MemoryStream ToExcel <T>(IPaginatedList <T> list) { try { //Create excel workbook Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = book.Worksheets[0]; List <string> objHeaders = new List <string>(); PropertyInfo[] headerInfo = typeof(T).GetProperties(); foreach (var property in headerInfo) { var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false) .Cast <DisplayNameAttribute>().FirstOrDefault(); //Avoid columns from Base Business Entity if (property.Name == "ChangedFields") { break; } objHeaders.Add(property.Name); } sheet.Cells.ImportCustomObjects(list.Items.ToList(), objHeaders.ToArray(), true, 1, 0, list.TotalRecordCount, true, string.Empty, false); #region Header style Aspose.Cells.Style style = book.CreateStyle(); style.Font.IsBold = true; // Define a style flag struct. Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag(); flag.FontBold = true; // Get the first row in the first worksheet. Aspose.Cells.Row row = book.Worksheets[0].Cells.Rows[1]; // Apply the style to it. row.ApplyStyle(style, flag); #endregion // Auto-fit all the columns book.Worksheets[0].AutoFitColumns(); using (MemoryStream outputStream = new MemoryStream()) { //Save workbook as stream instead of saving it to the Disk book.Save(outputStream, Aspose.Cells.SaveFormat.Xlsx); //Call dispose methods sheet.Dispose(); book.Dispose(); outputStream.Position = 0; return(outputStream); } } catch (Exception) { // TODO : Handle out of memory exception throw; } }
/// <summary> /// 导出到Excel文件 /// </summary> /// <param name="dt">DataTable</param> /// <param name="path">保存路径</param> /// <returns></returns> public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path) { bool succeed = false; if (dt != null) { try { Aspose.Cells.License li = new Aspose.Cells.License(); //设置破解DLL的许可证 li.SetLicense(Application.StartupPath + @"\Resources\License.lic"); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = dt.TableName; int rowIndex = 0; int colIndex = 0; int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString()); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); path = Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception) { succeed = false; } } return(succeed); }
/// <summary> /// DataTable 导出Excel /// </summary> /// <param name="datatable">DataTable</param> /// <param name="filepath">filepath</param> /// <param name="error">错误信息</param> /// <returns>bool</returns> public static bool DataTableToExcel(System.Data.DataTable datatable, string filepath, out string error) { error = ""; try { if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return(false); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = sheet.Cells; int nRow = 0; try { for (int i = 0; i < datatable.Columns.Count; i++) { cells[nRow, i].PutValue(datatable.Columns[i].ColumnName); Aspose.Cells.Style style = new Aspose.Cells.Style(); style.Font.IsBold = true; style.Font.Size = 11; cells[nRow, i].SetStyle(style); } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } foreach (DataRow row in datatable.Rows) { nRow++; try { for (int i = 0; i < datatable.Columns.Count; i++) { if (row[i].GetType().ToString() == "System.Drawing.Bitmap") { //------插入图片数据------- System.Drawing.Image image = (System.Drawing.Image)row[i]; MemoryStream mstream = new MemoryStream(); image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg); sheet.Pictures.Add(nRow, i, mstream); } else { cells[nRow, i].PutValue(row[i]); } } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } } workbook.Save(filepath); return(true); } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path) { bool succeed = false; if (dt != null) { try { Aspose.Cells.License li = new Aspose.Cells.License(); string lic = @"<License> <Data> <SerialNumber>aed83727-21cc-4a91-bea4-2607bf991c21</SerialNumber> <EditionType>Enterprise</EditionType> <Products> <Product>Aspose.Total</Product> </Products> </Data> <Signature>CxoBmxzcdRLLiQi1kzt5oSbz9GhuyHHOBgjTf5w/wJ1V+lzjBYi8o7PvqRwkdQo4tT4dk3PIJPbH9w5Lszei1SV/smkK8SCjR8kIWgLbOUFBvhD1Fn9KgDAQ8B11psxIWvepKidw8ZmDmbk9kdJbVBOkuAESXDdtDEDZMB/zL7Y=</Signature> </License>"; Stream s = new MemoryStream(ASCIIEncoding.Default.GetBytes(lic)); li.SetLicense(s); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = "Sheet1"; int rowIndex = 0; int colIndex = 0; int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString()); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); path = Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception ex) { succeed = false; } } return(succeed); }
public static bool ExportExcelWithAspose(DataSet ds, string path) { bool succeed = false; if (ds != null && ds.Tables.Count > 0) { try { Aspose.Cells.License li = new Aspose.Cells.License(); var lic = Resources.License; Stream s = new MemoryStream(lic); li.SetLicense(s); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Worksheets.Clear(); for (int tableCount = 0; tableCount < ds.Tables.Count; tableCount++) { workbook.Worksheets.Add(ds.Tables[tableCount].TableName); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[tableCount]; // cellSheet.Name = ds.Tables[tableCount].TableName; int rowIndex = 0; int colIndex = 0; int colCount = ds.Tables[tableCount].Columns.Count; int rowCount = ds.Tables[tableCount].Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Rows[i][j].ToString()); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); } path = Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception ex) { succeed = false; } } return(succeed); }
//internal void RectangleExport() //{ // throw new NotImplementedException(); //} /// <summary> /// 导出方法 /// </summary> /// <param name="DataGridView"></param> /// <param name="dt"></param> /// <param name="Export"></param> public void DataGridViewToExcel(DataGridView DataGridView, int Export = 0) { if (DataGridView.Rows.Count < 1) { return; } int i = 0; int Col = 0; ArrayList colList = new ArrayList(); //存放不显示的列 Aspose.Cells.Workbook xlBook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet xlSheet = default(Aspose.Cells.Worksheet); string filepath = ""; Aspose.Cells.Style style = new Aspose.Cells.Style(); style.Font.Name = "宋体"; Aspose.Cells.Style stylecolor = new Aspose.Cells.Style(); stylecolor.ForegroundColor = Color.LightBlue; Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag(); flag.Font = true; xlSheet = xlBook.Worksheets[0]; //intExcelTempIndex += 1; //xlSheet.Name = "牙模盒使用记录" + intExcelTempIndex.ToString(); int Rowi = 0; int Rown = 0; int RownMax = 0; //最后Export列不导出 RownMax = System.Convert.ToInt32(DataGridView.Columns.Count - Export); //填写表头 for (i = 0; i < RownMax; i++) { if (DataGridView.Columns[i].Width <= 5 || DataGridView.Columns[i].Visible == false) { } else { colList.Add(i); xlSheet.Cells[0, Col].PutValue(DataGridView.Columns[i].HeaderText); Col++; } } //表内容 int intcount = 0; for (Rowi = 0; Rowi <= DataGridView.Rows.Count - 1; Rowi++) { if (DataGridView.Rows[Rowi].Visible == false) { intcount++; continue; } for (Rown = 0; Rown <= colList.Count - 1; Rown++) { if (DataGridView.Rows[Rowi].Cells[Rown].Value != null) { xlSheet.Cells[Rowi + 1 - intcount, Rown].PutValue(DataGridView.Rows[Rowi].Cells[Rown].Value.ToString()); } else { xlSheet.Cells[Rowi + 1 - intcount, Rown].PutValue(""); } } } xlSheet.Cells.ApplyStyle(style, flag); if (DataGridView.Rows.Count > 60000) { filepath = System.Windows.Forms.Application.StartupPath + "\\" + xlSheet.Name + ".xlsx"; } else { filepath = System.Windows.Forms.Application.StartupPath + "\\" + xlSheet.Name + ".xls"; } xlBook.Save(filepath); System.Diagnostics.Process.Start(filepath); //'打开导出Excel }
public ActionResult ExportQD(string keyValue) { //获取会议基本信息 ConferenceEntity cr = conferencebll.GetEntity(keyValue); ////获取会议签到人员信息 //List<Object> data = conferenceuserbll.GetSignTable(keyValue); ////签到 //List<Object> dataQD = data[0] as List<Object>; ////未签到 //List<Object> dataWQD = data[1] as List<Object>; ////请假 //List<Object> dataQ = data[2] as List<Object>; string path = Request.PhysicalApplicationPath; int index = 0; string fileName = HttpUtility.UrlDecode("安全会议签到") + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //定义导出文件名,一定记得编码 try { Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(); wk.Open(Server.MapPath("~/Resource/ExcelTemplate/安全会议签到表模板.xlsx")); //加载模板 Aspose.Cells.Worksheet sheet = wk.Worksheets[0]; Aspose.Cells.Style style = wk.Styles[wk.Styles.Add()]; style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //文字居中 style.IsTextWrapped = true; //自动换行 style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; string sql = string.Format(" select t.USERNAME,o.DEPTNAME,t.PHOTOURL,t.issign,t.REVIEWSTATE from BIS_ConferenceUser t left join v_userinfo o on t.userid=o.userid where t.ConferenceID='{0}'", keyValue); DataTable dt = specialequipmentbll.SelectData(sql); //参会人员集合 var listC = dt; //签到人员集合 var SdUser = listC.Select(" issign='0'"); //未签到人员集合 var YdUser = listC.Select(" issign<>'0' and REVIEWSTATE<>'2'"); //请假人数 var QUser = listC.Select(" REVIEWSTATE='2'"); sheet.Cells[1, 2].PutValue(cr.ConferenceName); //会议名称 sheet.Cells[1, 4].PutValue(cr.Locale); //会议地点 sheet.Cells[2, 2].PutValue(cr.CompereDept); //召开部门 sheet.Cells[2, 4].PutValue(cr.Compere); //主持人 sheet.Cells[3, 2].PutValue(cr.Content); //主要议题 if (SdUser.Length == 0) { sheet.Cells[5, 1].PutValue("无"); } else { sheet.Cells[5 + SdUser.Length, 0].PutValue("已签到人数"); sheet.Cells.Merge(5, 0, SdUser.Length, 1); foreach (DataRow item in SdUser) { index++; sheet.Cells[4 + index, 1].PutValue(index); sheet.Cells[4 + index, 2].PutValue(item[0].ToString()); //参会人员 sheet.Cells[4 + index, 4].PutValue(item[1].ToString()); //所属部门 if (!string.IsNullOrEmpty(item[2].ToString())) { string[] u = item[2].ToString().Split('/'); string url = path + "/" + u[1] + "/" + u[2] + "/" + u[3] + "/" + u[4] + "/" + u[5];//签名 FileStream fs = new FileStream(url, FileMode.Open); int imgIndex = sheet.Pictures.Add(4 + index, 3, fs, 120, 30); fs.Close(); sheet.Cells.SetColumnWidth(5, 20); sheet.Cells.SetRowHeight(4 + index, 44); sheet.Pictures[imgIndex].Width = 139; sheet.Pictures[imgIndex].Height = 50; sheet.Pictures[imgIndex].Top = 3; sheet.Pictures[imgIndex].Left = 6; } } } int num = SdUser.Length == 0 ? 1 : SdUser.Length; int s = SdUser.Length == 0 ? 1 : SdUser.Length; if (index > 0) { s = 0; } if (YdUser.Length == 0) { sheet.Cells[5 + num, 0].PutValue("未签到人数"); sheet.Cells[5 + num, 1].PutValue("无"); } else { sheet.Cells[5 + num, 0].PutValue("未签到人数"); sheet.Cells.Merge(5 + num, 0, YdUser.Length, 1); foreach (DataRow item in YdUser) { index++; sheet.Cells[4 + s + index, 1].PutValue(index); sheet.Cells[4 + s + index, 2].PutValue(item[0].ToString()); sheet.Cells[4 + s + index, 4].PutValue(item[1].ToString()); sheet.Cells[4 + s + index, 3].PutValue(""); } } s = YdUser.Length == 0 ? 1 : YdUser.Length; int qj = s + SdUser.Length == 0 ? 1 : s + SdUser.Length; qj = SdUser.Length == 0 ? qj + 1 : qj; int qjNum = 0; if (QUser.Length > 0) { sheet.Cells[5 + qj, 0].PutValue("请假人数"); sheet.Cells.Merge(5 + qj, 0, QUser.Length, 1); foreach (DataRow dr in QUser) { sheet.Cells[5 + qj + qjNum, 1].PutValue(index++ + 1); sheet.Cells[5 + qj + qjNum, 2].PutValue(dr[0].ToString()); sheet.Cells[5 + qj + qjNum, 4].PutValue(dr[1].ToString()); sheet.Cells[5 + qj + qjNum, 3].PutValue(""); qjNum++; } } else { qjNum += 1; sheet.Cells[5 + qj, 0].PutValue("请假人数"); sheet.Cells[5 + qj, 1].PutValue("无"); } Aspose.Cells.Range r = sheet.Cells.CreateRange(5, 0, qj + qjNum, 5); r.Style = style; HttpResponse resp = System.Web.HttpContext.Current.Response; wk.Save(HttpUtility.UrlEncode(fileName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInExcel, resp); return(ToJsonResult("0")); } catch (Exception ex) { string env = Config.GetValue("Environment"); string msg = env == "dev" ? ex.Message : "对不起,系统出错了"; return(ToJsonResult("" + msg + "")); } }
/// <summary> /// DataTable 导出Excel /// </summary> /// <param name="datatable">DataTable</param> /// <param name="filepath">filepath</param> /// <param name="error">错误信息</param> /// <returns>bool</returns> public static bool DataTableToExcel(System.Data.DataTable datatable, string filepath, out string error) { error = ""; try { if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return false; } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = sheet.Cells; int nRow = 0; try { for (int i = 0; i < datatable.Columns.Count; i++) { cells[nRow, i].PutValue(datatable.Columns[i].ColumnName); Aspose.Cells.Style style = new Aspose.Cells.Style(); style.Font.IsBold=true; style.Font.Size = 11; cells[nRow, i].SetStyle(style); } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } foreach (DataRow row in datatable.Rows) { nRow++; try { for (int i = 0; i < datatable.Columns.Count; i++) { if (row[i].GetType().ToString() == "System.Drawing.Bitmap") { //------插入图片数据------- System.Drawing.Image image = (System.Drawing.Image)row[i]; MemoryStream mstream = new MemoryStream(); image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg); sheet.Pictures.Add(nRow, i, mstream); } else { cells[nRow, i].PutValue(row[i]); } } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } } workbook.Save(filepath); return true; } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return false; } }
public static bool DataTableToExcel2(DataTable datatable, string filepath, out string error) { error = ""; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Worksheets.Clear(); try { if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return(false); } //为单元格添加样式 Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()]; //设置居中 style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //设置背景颜色 style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //style.Pattern = BackgroundType.Solid; style.Font.IsBold = true; int rowIndex = 0; //新建立sheet页 int count = datatable.Rows.Count; int totalNum = datatable.Rows.Count / EXCEL13_MaxRow + 1;//总共生成的sheet页 string sheetName = ""; int beginIndex = 0; int endIndex = 0; for (int n = 0; n < totalNum; n++) { sheetName = "sheet" + n.ToString(); wb.Worksheets.Add(sheetName); rowIndex = 0; for (int i = 0; i < datatable.Columns.Count; i++) { DataColumn col = datatable.Columns[i]; string columnName = col.Caption ?? col.ColumnName; wb.Worksheets[n].Cells[rowIndex, i].PutValue(columnName); wb.Worksheets[n].Cells[rowIndex, i].SetStyle(style); } rowIndex++; beginIndex = n * EXCEL13_MaxRow; endIndex = beginIndex + EXCEL13_MaxRow; if (endIndex >= count) { endIndex = count; } if (beginIndex < count && endIndex <= count) { for (int j = beginIndex; j < endIndex; j++) { DataRow row = datatable.Rows[j]; for (int i = 0; i < datatable.Columns.Count; i++) { wb.Worksheets[n].Cells[rowIndex, i].PutValue(row[i].ToString()); } rowIndex++; } } for (int k = 0; k < datatable.Columns.Count; k++) { wb.Worksheets[n].AutoFitColumn(k, 0, 150); } wb.Worksheets[n].FreezePanes(1, 0, 1, datatable.Columns.Count); } //创建文件 FileStream file = new FileStream(filepath, FileMode.Create); //关闭释放流,不然没办法写入数据 file.Close(); file.Dispose(); wb.Save(filepath); wb = null; return(true); } catch (Exception e) { for (int i = 0; i < datatable.Rows.Count / EXCEL13_MaxRow + 1; i++) { wb.Worksheets[i].Cells.Clear(); } wb.Worksheets.Clear(); wb = null; error = error + " DataTableToExcel: " + e.Message; return(false); } }
/// <summary> /// ASPOSE.CELL方式将DataTable导出excel(不依赖office组件) /// </summary> /// <param name="dt">DataTable数据</param> /// <param name="path">保存到物理文件全路径</param> /// <param name="hasDownload">是否要同时产生下载流操作</param> /// <param name="fileName">下载到客户端保存的文件名(文件名+扩展名)</param> /// <returns>是否导出成功</returns> public static bool ExportExcelWithAspose(System.Data.DataTable dt, string fileName = "", string path = "", bool hasDownload = true) { bool succeed = false; if (dt != null) { try { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = dt.TableName; int rowIndex = 0; int colIndex = 0; int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; //Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; //style.VerticalAlignment = Aspose.Cells.TextAlignmentType.Center; //style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //style.ShrinkToFit = true; //style.Font.Name = "Arial"; //style.Font.Size = 12; //Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); //cellSheet.Cells.ApplyStyle(style, styleFlag); //列名的处理 for (int i = 0; i < colCount; i++) { Aspose.Cells.Style style1 = new Aspose.Cells.Style(); style1.VerticalAlignment = Aspose.Cells.TextAlignmentType.Center; if (i == 0) { style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Left; } else { style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; } style1.ShrinkToFit = true; style1.Font.Name = "Arial"; style1.Font.Size = 15; style1.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].SetStyle(style1); //cellSheet.Cells[rowIndex, colIndex].SetStyle( .Style.Font.IsBold = true; //cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "微软雅黑"; colIndex++; } rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { Aspose.Cells.Style style1 = new Aspose.Cells.Style(); style1.VerticalAlignment = Aspose.Cells.TextAlignmentType.Center; if (colIndex == 0) { style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Left; } else { style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; } style1.ShrinkToFit = true; style1.Font.Name = "Arial"; style1.Font.Size = 12; cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString()); cellSheet.Cells[rowIndex, colIndex].SetStyle(style1); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); if (string.IsNullOrEmpty(path)) { path = HttpContext.Current.Server.MapPath("/Log/Error/" + Guid.NewGuid() + ".xls"); } if (!Directory.Exists(Path.GetDirectoryName(path))) { Directory.CreateDirectory(Path.GetDirectoryName(path)); } path = Path.GetFullPath(path); workbook.Save(path); try { if (hasDownload) { if (string.IsNullOrEmpty(fileName)) { fileName = Guid.NewGuid() + ".xls"; } if (string.IsNullOrEmpty(Path.GetExtension(fileName))) { fileName = fileName + ".xls"; } if (File.Exists(path)) { DownloadFileV4(HttpContext.Current.Response, path, fileName, true); } } } catch (Exception Ex2) { } succeed = true; } catch (Exception ex) { succeed = false; } } return(succeed); }