Aspose 这个插件导出的Execl 被标记了
评估版权警告
版权所有2003年至2019年aspose有限公司
using Aspose.Words;//插件
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HPSF;导出Execl
#region word 转pdf
/// <summary>
///
/// </summary>
/// <param name="filePath">word路径 </param>
/// <param name="gzdWordpdf">pdf路径</param>
/// <returns></returns>
private byte[] ConvertWordToPDF(string filePath, string gzdWordpdf)
{
var stream = new MemoryStream();
Aspose.Words.Document doc = new Aspose.Words.Document(filePath);
doc.Save(stream, Aspose.Words.SaveFormat.Pdf);
byte[] buffer = null;
if (stream != null)
{
saveTofle(stream, gzdWordpdf);
stream.Close();
}
return buffer;
}
#endregion
-
#region 导出意见告知单
private string HandleGuaranteeDoc(string rowguid)
{
string xmCode = “”;
string xmName = “”;
string tempFile = Path.GetFullPath(HttpRuntime.AppDomainAppPath.ToString() + @“FileManager\吉林省工程建设项目协同服务告知单.dot”).ToString(); //获取模板路 径,这个根据个人模板路径而定。
Document doc = new Document(tempFile);
DocumentBuilder builder = new DocumentBuilder(doc); //操作word//写入项目基本信息 string sql = "select OPNUMGATHER,PROJECTNAME,JSDW,LANDNATURE,BUILTAREA,LANDAREA,RTCOORDX,LBCOORDX,LBCOORDY,RTCOORDY,XMYWLX from gsa_base_project where row_guid='" + rowguid + "'"; DataTable dtXM = DataService.SystemDatabase.GetDataTable(sql); xmCode = dtXM.Rows[0]["OPNUMGATHER"].ToString(); xmName = dtXM.Rows[0]["PROJECTNAME"].ToString(); builder.MoveToBookmark("OPNUMGATHER"); //项目协同编码 builder.Write(dtXM.Rows[0]["OPNUMGATHER"].ToString()); builder.MoveToBookmark("PROJECTNAME"); //项目名称 builder.Write(dtXM.Rows[0]["PROJECTNAME"].ToString()); builder.MoveToBookmark("JSDW"); //建设单位 builder.Write(dtXM.Rows[0]["JSDW"].ToString()); builder.MoveToBookmark("LANDNATURE"); //项目属性用地性质 builder.Write(dtXM.Rows[0]["LANDNATURE"].ToString()); builder.MoveToBookmark("BUILTAREA"); //建筑面积 builder.Write(dtXM.Rows[0]["BUILTAREA"].ToString()); builder.MoveToBookmark("LANDAREA"); //用地规模 builder.Write(dtXM.Rows[0]["LANDAREA"].ToString()); builder.MoveToBookmark("RTCOORDX"); //东至 builder.Write(dtXM.Rows[0]["RTCOORDX"].ToString()); builder.MoveToBookmark("LBCOORDX"); //西至 builder.Write(dtXM.Rows[0]["LBCOORDX"].ToString()); builder.MoveToBookmark("LBCOORDY"); //南至 builder.Write(dtXM.Rows[0]["LBCOORDY"].ToString()); builder.MoveToBookmark("RTCOORDY"); //北至 builder.Write(dtXM.Rows[0]["RTCOORDY"].ToString()); builder.MoveToBookmark("OPTYPENAME"); //办理流程 builder.Write(dtXM.Rows[0]["XMYWLX"].ToString()); //写入意见 sql = "select dqbm ,xtbmopinion from gsa_opiniontable t,gsa_base_project g where g.row_guid=t.xmguid and xmguid='" + rowguid + "'"; DataTable dtYJ = DataService.SystemDatabase.GetDataTable(sql); for (int i = 0; i < dtYJ.Rows.Count; i++) { switch (dtYJ.Rows[i]["dqbm"].ToString()) { case "自然资源部门": builder.MoveToBookmark("ZRZYOPINION"); builder.Write(dtYJ.Rows[i]["xtbmopinion"].ToString()); break; case "发改部门": builder.MoveToBookmark("FGBM"); builder.Write(dtYJ.Rows[i]["xtbmopinion"].ToString()); break; case "住建部门": builder.MoveToBookmark("JSBMYJ"); builder.Write(dtYJ.Rows[i]["xtbmopinion"].ToString()); break; case "生态部门": builder.MoveToBookmark("STHJBMYJ"); builder.Write(dtYJ.Rows[i]["xtbmopinion"].ToString()); break; } } #region 写入事项 string sqlsx = "select t.*,a.TEMP_STAGE,a.MATTERNAME from app_relationtable t,app_model_matter a where t.matterguid in(select matterguid from app_model_matter ) and xmguid='" + rowguid + "' and a.matterguid=t.matterguid"; DataTable dtsx = DataService.SystemDatabase.GetDataTable(sqlsx); SXword(builder, dtsx); #endregion string dateStr = DateTime.Now.ToString("yyyy-MM"); string pathDir = Path.Combine(OpenWebGIS.LiquidGIS.Common.Helper.PathHelper.FileManagerPath, "GZDFiles", dateStr); if (!System.IO.Directory.Exists(pathDir)) System.IO.Directory.CreateDirectory(pathDir); //新建告知单文件 string gzdWordpath = Path.Combine(pathDir, xmCode + xmName + ".doc"); string gzdWordpdf = Path.Combine(pathDir, xmCode + xmName + ".pdf"); if (System.IO.File.Exists(gzdWordpath)) System.IO.File.Delete(gzdWordpath); string httppatch = "/FileManager/GZDFiles/" + dateStr + @"/" + xmCode + xmName + ".pdf"; //"http://" + host + ":" + Port + "/FileManager/" + dateStr + "/" + FileName; string insertsql = "update GSA_BASE_PROJECT set GZDPATH='" + httppatch + "' where row_guid='" + rowguid + "'"; int kk = DataService.SystemDatabase.ExecuteNonQuery(insertsql); if (false == System.IO.Directory.Exists(pathDir)) { //创建pic文件夹 System.IO.Directory.CreateDirectory(pathDir); } doc.Save(gzdWordpath); //保存word //转换pdf ConvertWordToPDF(gzdWordpath, gzdWordpdf); return httppatch; } #endregion #region 从datatable中将数据导出到excel
///
/// DataTable导出到Excel的MemoryStream
///
/// 源DataTable
/// 表头文本
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;#region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "http://www.openwebgis.com/"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "OPENWEBGIS"; //填加xls文件作者信息 si.ApplicationName = "OPENWEBGIS EXCEL EXPORT"; //填加xls文件创建程序信息 si.LastAuthor = "LIQUIDGIS"; //填加xls文件最后保存者信息 si.Comments = "版权所有 (C) 2007-2013 OpenWebGIS All Rights Reserved "; //填加xls文件作者信息 si.Title = "OPENWEBGIS EXCEL 文件导出"; //填加xls文件标题信息 si.Subject = "OPENWEBGIS EXCEL"; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { string caption = item.Caption; if (string.IsNullOrEmpty(caption)) caption = item.ColumnName; arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(caption).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; if (arrColWidth[j] > 250) arrColWidth[j] = 250;//The maximum column width for an individual cell is 255 characters } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } #region 表头及样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { string ColumnName = GetColnumnName(column.ColumnName); string caption = column.Caption; if (string.IsNullOrEmpty(ColumnName)) caption = column.ColumnName; else caption = ColumnName; headerRow.CreateCell(column.Ordinal).SetCellValue(caption); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet; //workbook.Dispose(); return ms; } }
#retion word 动态表格
public string SXYD(DocumentBuilder builder, string demp, string rowguid)
{
string sqlsx = “select t.*,a.TEMP_STAGE,a.MATTERNAME from app_relationtable t,app_model_matter a where t.matterguid in(select matterguid from app_model_matter ) and xmguid=’” + rowguid + “’ and a.matterguid=t.matterguid and t.bmmc=’” + demp + “’ and ( t.jsyd != ‘’ or t.SPYD !=’’)”;
DataTable dtsx = DataService.SystemDatabase.GetDataTable(sqlsx);
//设置各单元格的宽度
int w1 = 10;
int w2 = 50;
if (dtsx.Rows.Count > 0)
{
//开始插入表格
builder.StartTable();
//设置基本格式
builder.RowFormat.Borders.LineStyle = LineStyle.Thick;
builder.RowFormat.HeightRule = HeightRule.Exactly;
builder.RowFormat.Alignment = RowAlignment.Center;
builder.CellFormat.FitText = true;
//开始插入表格
Table table = builder.StartTable();
builder.CellFormat.VerticalAlignment = CellVerticalAlignment.Center;//垂直居中对齐
builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;//水平居中对齐
//builder.RowFormat.Height = 30 * dtsx.Rows.Count;//设置此行高度
for (int i = 0; i < dtsx.Rows.Count; i++)
{
//开始插入第一行表头
builder.InsertCell();
builder.CellFormat.VerticalMerge = CellMerge.Previous;
builder.CellFormat.HorizontalMerge = CellMerge.None;
builder.CellFormat.Width = 1;
builder.Writeln("建\n设\n要\n点\n和\n审\n批\n要\n点\n");
builder.CellFormat.Width = 25;
builder.InsertCell();
builder.CellFormat.VerticalMerge = CellMerge.Previous;
builder.CellFormat.HorizontalMerge = CellMerge.None;
builder.CellFormat.Width = w1;
builder.Writeln(dtsx.Rows[i]["BMMC"].ToString());
builder.CellFormat.Width = 74;
builder.InsertCell();
builder.CellFormat.HorizontalMerge = CellMerge.Previous;
builder.CellFormat.VerticalMerge = CellMerge.None;
builder.CellFormat.Width = w2;
builder.Writeln(dtsx.Rows[i]["MATTERNAME"].ToString() + ControlChar.LineBreak);
builder.CellFormat.Width = 67;
builder.InsertCell();
builder.CellFormat.HorizontalMerge = CellMerge.None;
builder.CellFormat.VerticalMerge = CellMerge.None;
builder.CellFormat.Width = w2;
builder.Writeln(dtsx.Rows[i]["JSYD"].ToString());
builder.CellFormat.Width = 175;
builder.InsertCell();
builder.CellFormat.HorizontalMerge = CellMerge.None;
builder.CellFormat.VerticalMerge = CellMerge.None;
builder.CellFormat.Width = w2;
builder.Writeln(dtsx.Rows[i]["SPYD"].ToString());
builder.CellFormat.Width = 180;
第三行 行头
builder.RowFormat.Alignment = RowAlignment.Center;
builder.EndRow();
}
table.AllowAutoFit = false;
table.AutoFit(AutoFitBehavior.FixedColumnWidths);
//table.AllowAutoFit = false;
builder.EndTable();
}
return "";
}
#endregion
///
/// DataTable导出到Excel文件
///
/// 源DataTable
/// 表头文本
/// 保存位置
public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName)
{
using (MemoryStream ms = ExportDT(dtSource, strHeaderText))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
public static bool isNumeric(String message, out double result)
{
Regex rex = new Regex(@"1?\d+[.]?\d*$");
result = -1;
if (rex.IsMatch(message))
{
result = double.Parse(message);
return true;
}
else
return false;
}
- ↩︎