public void CreateXlsHeaderMedalResult(Worksheet worksheet, int row)
{
SetCellStyle(worksheet, row, true);
worksheet.Cells[row, 0].PutValue("Medal");
worksheet.Cells[row, 1].PutValue("Points");
worksheet.Cells[row, 2].PutValue("Result");
worksheet.AutoFitRows();
worksheet.AutoFitColumns();
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Instantiate a new Workbook
Workbook wb = new Workbook();
// Get the first (default) worksheet
Worksheet _worksheet = wb.Worksheets[0];
// Create a range A1:B1
Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2);
// Merge the cells
range.Merge();
// Insert value to the merged cell A1
_worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end";
// Create a style object
Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle();
// Set wrapping text on
style.IsTextWrapped = true;
// Apply the style to the cell
_worksheet.Cells[0, 0].SetStyle(style);
// Create an object for AutoFitterOptions
AutoFitterOptions options = new AutoFitterOptions();
// Set auto-fit for merged cells
options.AutoFitMergedCells = true;
// Autofit rows in the sheet(including the merged cells)
_worksheet.AutoFitRows(options);
dataDir = dataDir + "AutoFitMergedCells.out.xlsx";
// Save the Excel file
wb.Save(dataDir);
// ExEnd:1
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
/// <summary>
/// 数据导出到Excel
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="filePath">保存文件夹路径</param>
/// <param name="fileName">保存文件名称</param>
/// <param name="saveFileType">保存文件类型（.xls;.xlsx）</param>
/// <returns>文件物理路径</returns>
public static string SaveDataTableToExcel(DataTable dt, string filePath, string fileName, EnumExcelFileType saveFileType)
{
string savePath = Path.Combine(filePath, fileName);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
try
{
Workbook workBook = new Workbook();
workBook.Worksheets.Clear();
workBook.Worksheets.Add(dt.TableName);
Worksheet ws = workBook.Worksheets[dt.TableName];
ws.FreezePanes(1, 1, 1, 0); //冻结第一行
int rowscount = dt.Rows.Count;
for (int col = 0; col < dt.Columns.Count; col++)
{
ws.Cells[0, col].PutValue(dt.Columns[col].Caption);
}
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < rowscount; row++)
{
ws.Cells[row + 1, col].PutValue(dt.Rows[row].ItemArray[col].ToString());
}
}
if (dt.Columns.Count > 0)
{
ws.AutoFitColumns();
}
if (dt.Rows.Count > 0)
{
ws.AutoFitRows();
}
if (saveFileType == EnumExcelFileType.Excel2003)
{
workBook.Save(savePath, SaveFormat.Excel97To2003);
}
else
{
workBook.Save(savePath, SaveFormat.Xlsx);
}
}
catch (Exception e)
{
LogScopeHelper.Error(e.Message, e);
return(string.Empty);
}
return(savePath);
}
/// <summary>
/// DataSet导出为Excel
/// </summary>
/// <param name="ds"></param>
/// <param name="filePath"></param>
/// <param name="title"></param>
/// <returns></returns>
public static bool DataSetToExcel(DataSet ds, string filePath, string fileName = "报表", string[] sheetName = null,
string[,] colName = null)
{
bool result = false;
try
{
Workbook book = new Workbook();
for (int k = 0; k < ds.Tables.Count; k++)
{
DataTable dt = ds.Tables[k];
Worksheet sheet = book.Worksheets[k];
if (sheetName != null)
{
sheet.Name = sheetName[k];
}
for (int i = 0; i < dt.Columns.Count; i++)
{
if (colName != null)
{
sheet.Cells[0, i].PutValue(colName[k, i]);
}
else
{
sheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName);
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.Cells[i + 1, j].PutValue(dt.Rows[i][j].ToString());
}
}
sheet.AutoFitColumns();
sheet.AutoFitRows();
book.Worksheets.Add();
}
//保存Excel文件
string fileToSave = Path.Combine(filePath, fileName);
book.Save(fileToSave);
result = true;
}
catch (Exception ex)
{
result = false;
}
return(result);
}
public static void ExportFromDataTable(string fileName, DataTable dataTable)
{
Workbook workbook = new Workbook();
Worksheet workSheet = workbook.Worksheets[0];
Cells cells = workSheet.Cells;
Row row = cells.Rows[0];
Style titleStyle = new Style();
titleStyle.Font.IsBold = true;
titleStyle.Borders.SetStyle(CellBorderType.Thin);
titleStyle.Borders.DiagonalStyle = CellBorderType.None;
for (int i = 0; i < dataTable.Columns.Count; i++)
{
Cell cell = row[i];
cell.Value = dataTable.Columns[i].ColumnName;
cell.SetStyle(titleStyle);
}
workSheet.Cells.ImportDataTable(dataTable, false, 1, 0);
Style cellStyle = new Style();
cellStyle.Borders.SetStyle(CellBorderType.Thin);
cellStyle.Borders.DiagonalStyle = CellBorderType.None;
//Range range = cells.CreateRange(1, 0, cells.MaxDataRow - 1, cells.MaxDataColumn - 1);
//range.SetStyle(cellStyle);
for (int i = 1; i <= cells.MaxDataRow; i++)
{
for (int j = 0; j <= cells.MaxDataColumn; j++)
{
Cell cell = cells.GetCell(i, j);
cell.SetStyle(cellStyle);
}
}
workSheet.AutoFitColumns();
workSheet.AutoFitRows();
HttpResponse response = HttpContext.Current.Response;
//清空Response缓存内容
response.Clear();
response.Buffer = true;
//确定字符的编码格式
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName));
response.ContentType = "application/ms-excel";
response.Charset = "utf-8";
response.ContentEncoding = System.Text.Encoding.UTF8;
//写入Response
workbook.Save(response.OutputStream, SaveFormat.Excel97To2003);
response.End();
}
public void CreateXlsHeader(Worksheet worksheet, int row)
{
SetCellStyle(worksheet, row, true);
worksheet.Cells[row, 0].PutValue("Loan ref number");
worksheet.Cells[row, 1].PutValue("Loan amount");
worksheet.Cells[row, 2].PutValue("Repayments");
worksheet.Cells[row, 3].PutValue("Date Applied ");
worksheet.Cells[row, 4].PutValue("Outstanding");
worksheet.Cells[row, 5].PutValue("Status");
worksheet.AutoFitRows();
worksheet.AutoFitColumns();
}
public void CreateXlsHeader(Worksheet worksheet, int row, int column)
{
SetCellStyle(worksheet, row, column, true);
worksheet.Cells[row, column].PutValue("Due Date");
worksheet.Cells[row, column + 1].PutValue("Principal");
worksheet.Cells[row, column + 2].PutValue("Interest");
worksheet.Cells[row, column + 3].PutValue("Rate");
worksheet.Cells[row, column + 4].PutValue("Fees");
//worksheet.Cells.Merge(row, column + 5, 1, 3);
worksheet.Cells[row, column + 5].PutValue("Total");
SetHeaderBackgroundColor(worksheet, row, column);
worksheet.AutoFitRows();
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiate a new Workbook
Workbook wb = new Workbook();
//Get the first (default) worksheet
Worksheet _worksheet = wb.Worksheets[0];
//Create a range A1:B1
Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2);
//Merge the cells
range.Merge();
//Insert value to the merged cell A1
_worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end";
//Create a style object
Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle();
//Set wrapping text on
style.IsTextWrapped = true;
//Apply the style to the cell
_worksheet.Cells[0, 0].SetStyle(style);
//Create an object for AutoFitterOptions
AutoFitterOptions options = new AutoFitterOptions();
//Set auto-fit for merged cells
options.AutoFitMergedCells = true;
//Autofit rows in the sheet(including the merged cells)
_worksheet.AutoFitRows(options);
//Save the Excel file
wb.Save(dataDir + "AutoFitMergedCells.xlsx");
}
/// <summary>
/// 把一个数值写入到Excel
/// </summary>
private void InsertValue()
{
// 比如要在 A1 位置写入 Demo这个值
DetailSheet.AutoFitColumns();
DetailSheet.AutoFitRows();
Cell itemCell = DetailSheet.Cells["B3"];
itemCell.PutValue("lidong");
Cell itemCell2 = DetailSheet.Cells["B4"];
itemCell2.PutValue("111111");
}
public static void Run()
{
// ExStart:1
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiate a new Workbook
Workbook wb = new Workbook();
// Get the first (default) worksheet
Worksheet _worksheet = wb.Worksheets[0];
// Create a range A1:B1
Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2);
// Merge the cells
range.Merge();
// Insert value to the merged cell A1
_worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end";
// Create a style object
Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle();
// Set wrapping text on
style.IsTextWrapped = true;
// Apply the style to the cell
_worksheet.Cells[0, 0].SetStyle(style);
// Create an object for AutoFitterOptions
AutoFitterOptions options = new AutoFitterOptions();
// Set auto-fit for merged cells
options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
// Autofit rows in the sheet(including the merged cells)
_worksheet.AutoFitRows(options);
// Save the Excel file
wb.Save(outputDir + "AutofitRowsforMergedCells.xlsx");
// ExEnd:1
Console.WriteLine("AutofitRowsforMergedCells executed successfully.\r\n");
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string output1Path = dataDir + "Output.out.xlsx";
string output2Path = dataDir + "Output.out.ods";
// Prepare a DataTable with some HTML formatted values
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("Product ID", typeof(Int32));
dataTable.Columns.Add("Product Name", typeof(string));
dataTable.Columns.Add("Units In Stock", typeof(Int32));
DataRow dr = dataTable.NewRow();
dr[0] = 1;
dr[1] = "<i>Aniseed</i> Syrup";
dr[2] = 15;
dataTable.Rows.Add(dr);
dr = dataTable.NewRow();
dr[0] = 2;
dr[1] = "<b>Boston Crab Meat</b>";
dr[2] = 123;
dataTable.Rows.Add(dr);
// Create import options
ImportTableOptions importOptions = new ImportTableOptions();
importOptions.IsFieldNameShown = true;
importOptions.IsHtmlString = true;
// Create workbook
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.ImportData(dataTable, 0, 0, importOptions);
worksheet.AutoFitRows();
worksheet.AutoFitColumns();
workbook.Save(output1Path);
workbook.Save(output2Path);
//ExEnd:1
}
private void ExportExcel(DataTable src)
{
if (src == null)
{
return;
}
string temp_path = Server.MapPath("~/Client/Admin/DataEngery/TongHopNLTTHangNam.xls");
Workbook _Excell = new Workbook();
_Excell.Open(temp_path);
Worksheet _Sheet = _Excell.Worksheets[0];
#region Build Sheet
int rowStart = 1;
int TotalColumn = 0;
int ColIndex = 0;
//Excel header
foreach (DataColumn col in src.Columns)
{
TotalColumn++;
string name = GetHeader(col.ColumnName);
_Sheet.Cells[rowStart, ColIndex].PutValue(name);
ColIndex++;
}
rowStart++;
//content
if (src != null)
{
foreach (DataRow row in src.Rows)
{
for (int i = 0; i < TotalColumn; i++)
{
_Sheet.Cells[rowStart, i].PutValue(row[i].ToString());
}
rowStart++;
}
}
_Sheet.AutoFitRows();
_Excell.Save("TongHopNLTTHangNam.xls", SaveType.OpenInBrowser, FileFormatType.Default, this.Response);
#endregion
}
/// <summary>
///
/// </summary>
public void Output()
{
#region
string path = System.Web.HttpContext.Current.Server.MapPath("~");
path = path.Substring(0, path.LastIndexOf("\\"));
path += @"\temp\temp.xls";
Workbook workbook = new Workbook(path);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
cells.StandardHeight = 22;
this.BindDataAndStyle(cells);
#region 列宽设定
cells.SetColumnWidth(0, 6);
cells.SetColumnWidth(1, 15);
cells.SetColumnWidth(2, 10);
cells.SetColumnWidth(3, 20);
cells.SetColumnWidth(4, 15);
cells.SetColumnWidth(5, 15);
cells.SetColumnWidth(6, 8);
cells.SetColumnWidth(7, 20);
cells.SetColumnWidth(8, 8);
cells.SetColumnWidth(9, 10);
cells.SetColumnWidth(10, 20);
cells.SetColumnWidth(11, 20);
cells.SetColumnWidth(12, 18);
cells.SetColumnWidth(13, 15);
cells.SetColumnWidth(14, 35);
cells.SetColumnWidth(15, 35);
cells.SetColumnWidth(16, 35);
cells.SetColumnWidth(17, 35);
#endregion
worksheet.AutoFitRows();
workbook.Save(System.Web.HttpContext.Current.Response,
filename, ContentDisposition.Attachment,
new XlsSaveOptions(SaveFormat.Excel97To2003));
#endregion
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string InputPath = dataDir + "Book1.xlsx";
// Instantiate a new Workbook
Workbook wb = new Workbook();
// Get the first (default) worksheet
Worksheet _worksheet = wb.Worksheets[0];
// Create a range A1:B1
Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2);
// Merge the cells
range.Merge();
// Insert value to the merged cell A1
_worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end";
// Create a style object
Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle();
// Set wrapping text on
style.IsTextWrapped = true;
// Apply the style to the cell
_worksheet.Cells[0, 0].SetStyle(style);
// Create an object for AutoFitterOptions
AutoFitterOptions options = new AutoFitterOptions();
// Set auto-fit for merged cells
options.AutoFitMergedCells = true;
// Autofit rows in the sheet(including the merged cells)
_worksheet.AutoFitRows(options);
// Save the Excel file
wb.Save(dataDir + "output.xlsx");
// ExEnd:1
}
private static void SetCellStyle(Worksheet worksheet, int row, bool isBold)
{
for (int i = 0; i < 6; i++)
{
worksheet.Cells[row, i].Style.Font.Size = 11;
worksheet.Cells[row, i].Style.Font.Name = "Calibri";
worksheet.Cells[row, i].Style.Font.IsBold = isBold;
worksheet.Cells[row, i].Style.HorizontalAlignment = TextAlignmentType.Left;
worksheet.Cells[row, i].Style.ShrinkToFit = true;
worksheet.Cells[row, i].Style.Borders[BorderType.BottomBorder].Color = Color.Black;
worksheet.Cells[row, i].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
worksheet.Cells[row, i].Style.Borders[BorderType.LeftBorder].Color = Color.Black;
worksheet.Cells[row, i].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
worksheet.Cells[row, i].Style.Borders[BorderType.RightBorder].Color = Color.Black;
worksheet.Cells[row, i].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
worksheet.Cells[row, i].Style.Borders[BorderType.TopBorder].Color = Color.Black;
worksheet.Cells[row, i].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
}
worksheet.AutoFitRows();
worksheet.AutoFitColumns();
}
private static void SetCellStyle(Worksheet worksheet, int row, int column, bool isHeader)
{
var grey = Color.FromArgb(197, 197, 197);
for (int i = 0; i <= 5; i++)
{
worksheet.Cells.SetRowHeight(row, i);
worksheet.Cells[row, column + i].Style.Font.Size = isHeader ? 9 : 11;
worksheet.Cells[row, column + i].Style.Font.Name = "Tahoma";
worksheet.Cells[row, column + i].Style.Font.IsBold = isHeader;
worksheet.Cells[row, column + i].Style.Font.Color = Color.FromArgb(123, 178, 36);
worksheet.Cells[row, column + i].Style.Pattern = BackgroundType.Solid;
worksheet.Cells[row, column + i].Style.BackgroundColor = row % 2 == 0 ? Color.FromArgb(221, 221, 221) : Color.White;
worksheet.Cells[row, column + i].Style.HorizontalAlignment = TextAlignmentType.Center;
worksheet.Cells[row, column + i].Style.VerticalAlignment = TextAlignmentType.Center;
worksheet.Cells[row, column + i].Style.ShrinkToFit = true;
if (i == 0)
{
worksheet.Cells[row, column + i].Style.Borders[BorderType.LeftBorder].Color = grey;
worksheet.Cells[row, column + i].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
}
if (i == 5)
{
worksheet.Cells[row, column + i].Style.Borders[BorderType.RightBorder].Color = grey;
worksheet.Cells[row, column + i].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
worksheet.Cells[row, column + i].Style.Font.IsBold = true;
}
worksheet.Cells[row, column + i].Style.Borders[BorderType.BottomBorder].Color = grey;
worksheet.Cells[row, column + i].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
worksheet.Cells[row, column + i].Style.Borders[BorderType.TopBorder].Color = grey;
worksheet.Cells[row, column + i].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
worksheet.Cells.SetRowHeight(row, 100);
}
worksheet.AutoFitRows();
}
protected void ExportButton_Click(object sender, EventArgs e)
{
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
if (ExportDataSource != null)
{
this.AllowPaging = false;
this.DataSource = ExportDataSource;
this.DataBind();
}
this.RenderBeginTag(hw);
this.HeaderRow.RenderControl(hw);
foreach (GridViewRow row in this.Rows)
{
row.RenderControl(hw);
}
this.FooterRow.RenderControl(hw);
this.RenderEndTag(hw);
string heading = string.IsNullOrEmpty(ExportFileHeading) ? string.Empty : ExportFileHeading;
string pageSource = sw.ToString();
// Check for license and apply if exists
if (File.Exists(LicenseFilePath))
{
License license = new License();
license.SetLicense(LicenseFilePath);
}
using (MemoryStream stream = new MemoryStream(Encoding.ASCII.GetBytes(pageSource)))
{
var loadOptions = new LoadOptions(LoadFormat.Html)
{
ConvertNumericData = false
};
Workbook workbook = new Workbook(stream, loadOptions);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFitColumns();
worksheet.AutoFitRows();
Aspose.Cells.Cells cells = worksheet.Cells;
Range range = worksheet.Cells.MaxDisplayRange;
int tcols = range.ColumnCount;
int trows = range.RowCount;
for (int i = 0; i < trows; i++)
{
for (int j = 0; j < tcols; j++)
{
if (cells[i, j].Type != CellValueType.IsNull)
{
Aspose.Cells.Style style = cells[i, j].GetStyle();
if (i == 0)
{
style.Font.IsBold = true;
}
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Black;
cells[i, j].SetStyle(style);
}
}
}
worksheet.Cells.InsertColumn(0);
worksheet.Cells.InsertRow(0);
worksheet.Cells["B1"].HtmlString = heading;
worksheet.Cells.InsertRow(0);
worksheet.Cells.InsertRow(2);
Aspose.Cells.Style style2 = worksheet.Cells["B2"].GetStyle();
style2.Font.Size = 20;
style2.Borders.SetStyle(CellBorderType.None);
worksheet.Cells["B2"].SetStyle(style2);
ExcelOutputFormat format;
if (Enum.TryParse <ExcelOutputFormat>(ExcelOutputFormat.ToString(), out format))
{
string extension = ExcelOutputFormat.ToString().ToLower();
if (string.IsNullOrEmpty(extension))
{
extension = "xls";
}
string fileNameWithExtension = System.Guid.NewGuid() + "." + extension;
if (!string.IsNullOrEmpty(ExportOutputPathOnServer) && Directory.Exists(ExportOutputPathOnServer))
{
try
{
workbook.Save(ExportOutputPathOnServer + "\\" + fileNameWithExtension);
}
catch (Exception) { }
}
workbook.Save(HttpContext.Current.Response, fileNameWithExtension, ContentDisposition.Inline, GetSaveFormat(ExcelOutputFormat.ToString()));
HttpContext.Current.Response.End();
}
else
{
HttpContext.Current.Response.Write("Invalid export format, must be one of Xlsx, Xlsb, Xls, Txt, Csv, Ods");
}
}
}
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="pKQ">数据集</param>
/// <returns></returns>
internal static string DataTableToExcel(DataTable pKQ, DataTable pDK, string pParams)
{
string pYearMonth = pParams.Substring(0, 4) + "年" + pParams.Substring(5, 2) + "月";
if (pKQ == null || pKQ.Rows.Count <= 0)
{
return("Error:没有考勤数据");
}
if (pDK == null || pDK.Rows.Count == 0)
{
return("Error:没有打卡数据");
}
DateTime now = DateTime.Now;
Workbook workbook;
try
{
//打开模板
string filePath = "doc\\";
string phyPath = HttpContext.Current.Server.MapPath(filePath);
DirectoryInfo di = new DirectoryInfo(phyPath);
string tempPath = di.Parent.Parent.GetDirectories("WebUI")[0].FullName + "\\doc\\Template\\";
string fileName = "考勤记录模板.xlsx";
if (!File.Exists(tempPath + fileName))
{
return("Error:找不到导出模板");
}
workbook = new Workbook(tempPath + fileName);
//考勤表
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Worksheet worksheet2 = workbook.Worksheets[1];
worksheet2.AutoFitRows(4, pKQ.Rows.Count + 4);
Cells cells2 = worksheet2.Cells;
string strTitle = "广州五号停机坪商业经营管理有限公司" + pYearMonth + "考勤表";
Cell cell = cells[0, 0];
cell.PutValue(strTitle);
string strTitle2 = "员工打卡记录" + pYearMonth + "报表";
Cell cell2 = cells2[0, 0];
cell2.PutValue(strTitle2);
int rowNumber = pKQ.Rows.Count;
int columnNumber = pKQ.Columns.Count;
int rowNumber2 = pDK.Rows.Count;
int columnNumber2 = pDK.Columns.Count;
//遍历DataTable行
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
cells[r + 4, c].PutValue(pKQ.Rows[r][c]);
}
}
//处理星期
string strZQ = "考勤周期:" + pYearMonth;
cells2[1, 0].PutValue(strZQ);
string[] strWeek = new string[] { "日", "一", "二", "三", "四", "五", "六" };
DateTime dateRP = DateTime.Parse(pParams);
int FirstDW = (int)dateRP.DayOfWeek;
string[] wks = new string[DateTime.DaysInMonth(dateRP.Year, dateRP.Month)];
for (int i = 0; i < wks.Length; i++)
{
wks[i] = strWeek[(i + FirstDW) % 7];
}
for (int i = 0; i < wks.Length; i++)
{
cells2[2, i + 4].PutValue(wks[i]);
}
for (int r = 0; r < rowNumber2; r++)
{
for (int c = 0; c < columnNumber2; c++)
{
cells2[r + 4, c].PutValue(pDK.Rows[r][c]);
}
}
//保存Excel文件
string savePath = di.Parent.Parent.GetDirectories("WebUI")[0].FullName + "\\doc\\";
fileName = now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
workbook.Save(savePath + fileName, SaveFormat.Xlsx);
return(filePath + fileName);
}
catch (Exception e)
{
return("Error:" + e.Message);
}
finally
{
workbook = null;
}
}
//-------------------------Generate an Excel sheet-------------------------
private byte[] GetExcelGrid(System.Collections.ICollection data)
{
// create a work book for an excel file
Workbook wb = new Workbook();
// specify the excel work sheet
Worksheet sheet = wb.Worksheets[0];
sheet.Cells.ImportCustomObjects(data, 0, 0, null);
// create and customize style
var styleHeader = wb.CreateStyle();
var styleWrapped = wb.CreateStyle();
styleHeader.Font.IsBold = true;
styleHeader.HorizontalAlignment = TextAlignmentType.Center;
styleHeader.Pattern = BackgroundType.Solid;
styleHeader.ForegroundArgbColor = Color.FromArgb(220, 238, 194).ToArgb();
styleWrapped.IsTextWrapped = true;
// get the collection of all excel cells
var cells = sheet.Cells;
//if (addGeneratedInfo) // ACRedit Plus logic, please ignore
//{
// // Set cell with value
// cells["A3"].PutValue("Generated By");
// cells["B3"].PutValue(UserContext.CurrentUser.FirstName.ToString() + " " + UserContext.CurrentUser.LastName.ToString());
// // Set cell with value
// cells["A4"].PutValue("Generated On");
// cells["B4"].PutValue(DateTime.Now.ToShortDateString());
// // Apply pre-defined style
// cells["B3"].SetStyle(styleWrapped);
//}
//if (searchModelheaders != null && searchModelheaders.Any()) // ACRedit Plus logic, please ignore
//{
// // Set cell with value
// cells["A6"].PutValue("Search Criteria");
// cells["B6"].PutValue("Value");
// cells["A6"].SetStyle(styleHeader);
// cells["B6"].SetStyle(styleHeader);
// searchSectionRowCount++; // ACRedit Plus logic, please ignore
// ...
//}
//...
// Delete blank columns
cells.DeleteBlankColumns();
int maxDataColumnIndex = cells.MaxDataColumn;
// merge specific cells
//if (addGeneratedInfo)
// cells.Merge(2, 2, 2, maxDataColumnIndex - 1);
//cells.Merge(rowIndex_searchStart - 1, 0, 1, maxDataColumnIndex + 1);
//cells.Merge(rowIndex_searchStart, colIndex_searchEnd + 1, searchSectionRowCount, maxDataColumnIndex - 1);
//cells.Merge(rowIndex_searchStart + searchSectionRowCount, 0, 1, maxDataColumnIndex + 1);
// set style
for (int rIndex = 0; rIndex <= cells.MaxDataRow; rIndex++)
{
for (int cIndex = 0; cIndex <= cells.MaxDataColumn; cIndex++)
{
var cell = cells[rIndex, cIndex];
if (cell != null)
{
var styleBorderFont = cell.GetStyle();
styleBorderFont.Font.Size = 11;
styleBorderFont.Font.Name = "Calibri";
styleBorderFont.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
styleBorderFont.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
styleBorderFont.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
styleBorderFont.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cell.SetStyle(styleBorderFont);
}
}
}
sheet.AutoFitRows(new AutoFitterOptions()
{
MaxRowHeight = 45
});
sheet.AutoFitColumns();
// save generated excel file to stream
MemoryStream ms = new MemoryStream();
wb.Save(ms, SaveFormat.Xlsx);
ms.Seek(0, SeekOrigin.Begin);
//return ms;
byte[] buffer = new byte[(int)ms.Length];
buffer = ms.ToArray();
return(buffer);
}
public byte[] ExportarPesquisa(AlunoFilter filter)
{
List <AlunoDTO> alunos = alunoRepository.FiltrarAlunos(filter).Select(x => new AlunoDTO(x)).ToList();
#region Criação da tabela
Workbook conteudoExcel = new Workbook
{
FileName = "exportação.xlsx"
};
conteudoExcel.Worksheets.RemoveAt(0);
Worksheet sheet = conteudoExcel.Worksheets.Add("Alunos");
string[] propertyNames =
{
/* 00 */ "Nome",
/* 01 */ "CPF",
/* 02 */ "Telefone",
/* 03 */ "Celular",
/* 04 */ "Matrículas",
/* 05 */ "Turmas",
/* 06 */ "Matrícula",
/* 07 */ "Validade",
/* 08 */ "Situação"
};
Aspose.Cells.Style styleDia = conteudoExcel.CreateStyle();
styleDia.Custom = "dd/mm/yyyy";
#endregion
#region Preenchimento da tabela
var contador = 1;
alunos.ForEach(a =>
{
try
{
var row = sheet.Cells.Rows[contador];
GravarValor(row, a.Nome, 0);
GravarValor(row, a.CPF, 1);
GravarValor(row, a.Telefone, 2);
GravarValor(row, a.Celular, 3);
GravarValor(row, string.Join(", ", a.TurmasAluno.Select(ta => ta.Matricula)), 4);
GravarValor(row, string.Join(", ", a.TurmasAluno.Select(ta => ta.Turma.Codigo)), 5);
GravarValor(row, a.DataMatricula, 6, styleDia);
GravarValor(row, a.DataValidade, 7, styleDia);
GravarValor(row, a.TipoStatusAluno, 8);
contador++;
}
catch (Exception e)
{
log.Error($"Erro exportar pesquisa.", e);
}
});
sheet.ListObjects.Add(0, 0, contador - 1, propertyNames.Length - 1, true);
var header = sheet.Cells.Rows[0];
for (int i = 0; i < propertyNames.Length; i++)
{
header[i].PutValue(propertyNames[i]);
}
sheet.AutoFitColumns();
sheet.AutoFitRows();
#endregion
MemoryStream stream = new MemoryStream();
conteudoExcel.Save(stream, Aspose.Cells.SaveFormat.Xlsx);
stream.Seek(0, SeekOrigin.Begin);
return(stream.ToArray());
void GravarValor(Row row, object dado, int indice, Aspose.Cells.Style style = null)
{
if (dado == null)
{
dado = "";
}
row[indice].PutValue(dado);
if (style != null)
{
row[indice].SetStyle(style);
}
}
}
public void ExportDataTable(Worksheet workSheet, DataTable table)
{
var headerStyle = workSheet.Workbook.Styles["HeaderStyle"];
//workSheet.Cells.StandardHeight = 30;
int MaxRowHeader = 0;
var expInfo = SysvarUtils.GetVarValue(SYSVAR.GRNAME_SYS, SYSVAR.VARNAME_EXPORT_LOGO);
if (expInfo == CONSTANTS.Yes)
{
int pictureIndex = workSheet.Pictures.Add(0, 0, System.Environment.CurrentDirectory + "\\Theme\\Logo.jpg");
Aspose.Cells.Picture picture = workSheet.Pictures[pictureIndex];
picture.Height = 100;
picture.Width = 600;
MaxRowHeader = 6;
}
//write header manual
ExportHeaderInfo = GetExportHeaderParams(ModuleInfo.ModuleID);
if (ExportHeaderInfo.Count > 0)
{
foreach (var headerRow in ExportHeaderInfo)
{
workSheet.Cells.Merge(headerRow.StartRow, headerRow.StartCol, headerRow.RowNum, headerRow.ColNum);
MaxRowHeader = headerRow.MaxRow;
workSheet.Cells[headerRow.StartRow, headerRow.StartCol].PutValue(headerRow.Text);
workSheet.Cells[headerRow.StartRow, headerRow.StartCol].SetStyle(headerStyle);
}
}
else
{
for (var i = 0; i < Headers.Length; i++)
{
workSheet.Cells[MaxRowHeader, i].PutValue(Headers[i]);
workSheet.Cells[MaxRowHeader, i].SetStyle(headerStyle);
}
}
var rowIndex = MaxRowHeader + 1;
foreach (DataRow row in table.Rows)
{
for (var i = 0; i < Columns.Length; i++)
{
var cellValue = row[Columns[i]];
if (ListSources[i] != null)
{
if (ListSources[i].ContainsKey(cellValue))
{
workSheet.Cells[rowIndex, i].PutValue(ListSources[i][cellValue]);
}
}
else
{
//workSheet.Cells[rowIndex, i].PutValue(cellValue);
workSheet.Cells[rowIndex, i].PutValue(cellValue.DecodeAny(Fields[i]));
}
if (rowIndex % 2 == 1)
{
workSheet.Cells[rowIndex, i].SetStyle(OddStyles[i]);
}
else
{
workSheet.Cells[rowIndex, i].SetStyle(EvenStyles[i]);
}
switch (Fields[i].FieldType)
{
case CONSTANTS.FLDTYPEDEC:
if (Fields[i].FieldName != "STT")
{
workSheet.Cells[rowIndex, i].Style.Custom = CONSTANTS.FLDFORMATDEC;
}
break;
case CONSTANTS.FLDTYPEDTE:
workSheet.Cells[rowIndex, i].Style.Custom = Fields[i].FieldFormat;
break;
}
}
rowIndex++;
}
var maxRow = rowIndex++;
string CellAt;
for (var i = 0; i < Columns.Length; i++)
{
string cellname = CellsHelper.ColumnIndexToName(i);
string CellNameStart = cellname + 1;
string CellNameStop = cellname + maxRow.ToString();
if (maxRow % 2 == 1)
{
workSheet.Cells[maxRow, i].SetStyle(OddStyles[i]);
}
else
{
workSheet.Cells[maxRow, i].SetStyle(EvenStyles[i]);
}
//wrap
if (Fields[i].WrapText == CONSTANTS.Yes)
{
for (var j = 0; j <= maxRow; j++)
{
CellAt = cellname + j;
Cell obj = workSheet.Cells[CellAt];
Style style = obj.GetStyle();
style.IsTextWrapped = true;
obj.SetStyle(style);
}
}
// end wrap
//workSheet.Cells[maxRow, 0].PutValue(maxRow);
//GroupSummaries = FieldUtils.GetModuleGroupSummary(ModuleInfo.ModuleID, ModuleInfo.ModuleType);
if (Fields[i].Group_Summary == CONSTANTS.Yes)
{
workSheet.Cells[maxRow, 1].PutValue(CONSTANTS.SUMTEXT);
if (Fields[i].FieldName == "ROA" || Fields[i].FieldName == "ROE")
{
workSheet.Cells[maxRow, i].Formula = "=SUM(" + CellNameStart + ":" + CellNameStop + ")/" + table.Rows.Count;
}
else
{
workSheet.Cells[maxRow, i].Formula = "=SUM(" + CellNameStart + ":" + CellNameStop + ")";
}
workSheet.Cells[maxRow, i].Style.Custom = CONSTANTS.FLDFORMATDEC;
}
}
MaxRowHeader = MaxRowHeader + 1;
int iRows = MaxRowHeader + 1;
int startRowGroup = MaxRowHeader + 1;
int endRowGroup = MaxRowHeader + 1;
//// Group rao lai da
//for (var j = 0; j < Columns.Length; j++)
//{
// iRows = MaxRowHeader + 1; startRowGroup = MaxRowHeader + 1; endRowGroup = MaxRowHeader + 1; cellValuePre = null;
// if (Fields[j].GroupOnSearch == CONSTANTS.Yes)
// {
// foreach (DataRow row in table.Rows)
// {
// string cellValue = row[Columns[j]].ToString();
// if (cellValuePre != cellValue)
// {
// workSheet.Cells.InsertRow(startRowGroup - 1);
// workSheet.Cells[startRowGroup - 1, 1].PutValue(cellValue);
// cellValuePre = cellValue;
// }
// //if (cellValuePre != cellValue)
// //{
// // cellValuePre = cellValue;
// // if (startRowGroup < endRowGroup)
// // {
// // workSheet.Cells.InsertRow(endRowGroup + 1);
// // iRows++;
// // }
// // startRowGroup = iRows;
// // endRowGroup = iRows;
// //}
// //else
// //{
// // endRowGroup = iRows;
// //}
// startRowGroup = iRows + 1;
// iRows++;
// }
// //iRows = MaxRowHeader + 1; startRowGroup = MaxRowHeader + 1; endRowGroup = MaxRowHeader + 1; cellValuePre = null;
// //foreach (DataRow row in table.Rows)
// //{
// // string cellValue = row[Columns[j]].ToString();
// // if (cellValuePre != cellValue)
// // {
// // cellValuePre = cellValue;
// // if (startRowGroup < endRowGroup)
// // {
// // workSheet.Cells.GroupRows(startRowGroup, endRowGroup, false);
// // iRows++;
// // }
// // startRowGroup = iRows;
// // endRowGroup = iRows;
// // }
// // else
// // {
// // endRowGroup = iRows;
// // }
// // iRows++;
// //}
// }
//}
workSheet.Outline.SummaryRowBelow = false;
workSheet.AutoFitRows();
}
private static void ApplyStyleToWorkSheet(ref Workbook excelToExport, ref Worksheet sheet, int rowsCount, int ColumnsCount)
{
sheet.IsGridlinesVisible = false;
sheet.AutoFitRows();
Range objRangeData = sheet.Cells.CreateRange(0, 0, rowsCount, ColumnsCount);
objRangeData.Name = "DataRange";
objRangeData.RowHeight = 15;
Style StyleDataRange = excelToExport.Styles[excelToExport.Styles.Add()];
StyleDataRange.Font.Name = "Arial";
StyleDataRange.Font.Size = 10;
StyleDataRange.Font.Color = System.Drawing.Color.Black;
StyleDataRange.HorizontalAlignment = TextAlignmentType.Left;
StyleDataRange.Borders.SetColor(System.Drawing.Color.FromArgb(221, 221, 221));
StyleDataRange.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
StyleDataRange.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
StyleDataRange.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
StyleDataRange.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
StyleDataRange.ForegroundColor = System.Drawing.Color.FromArgb(255, 255, 255);
StyleDataRange.Pattern = BackgroundType.Solid;
//Define a style flag struct.
StyleFlag flagDataRange = new StyleFlag();
flagDataRange.CellShading = true;
flagDataRange.FontName = true;
flagDataRange.FontSize = true;
flagDataRange.FontColor = true;
flagDataRange.HorizontalAlignment = true;
flagDataRange.Borders = true;
flagDataRange.WrapText = true;
objRangeData.ApplyStyle(StyleDataRange, flagDataRange);
StyleDataRange = null;
objRangeData = null;
//Setting Range And Style For Header Data Row
Range objRangeHeader = sheet.Cells.CreateRange(0, 0, 1, ColumnsCount);
objRangeHeader.Name = "HeaderRange";
objRangeHeader.RowHeight = 25;
Aspose.Cells.Style StyleHeaderFooterRange = excelToExport.Styles[excelToExport.Styles.Add()];
StyleHeaderFooterRange.Font.Name = "Arial";
StyleHeaderFooterRange.Font.Size = 10;
StyleHeaderFooterRange.Font.IsBold = true;
StyleHeaderFooterRange.Font.Color = System.Drawing.Color.Black;
StyleHeaderFooterRange.HorizontalAlignment = TextAlignmentType.Center;
StyleHeaderFooterRange.VerticalAlignment = TextAlignmentType.Center;
StyleHeaderFooterRange.Borders.SetColor(System.Drawing.Color.FromArgb(221, 221, 221));
StyleHeaderFooterRange.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
StyleHeaderFooterRange.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
StyleHeaderFooterRange.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
StyleHeaderFooterRange.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
StyleHeaderFooterRange.IsTextWrapped = true;
StyleHeaderFooterRange.ShrinkToFit = true;
StyleHeaderFooterRange.ForegroundColor = System.Drawing.Color.FromArgb(255, 255, 255);
StyleHeaderFooterRange.Pattern = BackgroundType.Solid;
//Define a style flag struct.
StyleFlag flagHeaderFooterRange = new StyleFlag();
flagHeaderFooterRange.CellShading = true;
flagHeaderFooterRange.FontName = true;
flagHeaderFooterRange.FontSize = true;
flagHeaderFooterRange.FontColor = true;
flagHeaderFooterRange.FontBold = true;
flagHeaderFooterRange.HorizontalAlignment = true;
flagHeaderFooterRange.VerticalAlignment = true;
flagHeaderFooterRange.Borders = true;
flagHeaderFooterRange.ShrinkToFit = true;
flagHeaderFooterRange.WrapText = true;
objRangeHeader.ApplyStyle(StyleHeaderFooterRange, flagHeaderFooterRange);
objRangeHeader = null;
sheet.AutoFitColumns();
}
private static void WriteExcel(System.Data.DataTable dt, string strTitle, string fileFullPath, bool hasHeader, bool isAutoFit)
{
Workbook workbook = new Workbook();
int rows = dt.Rows.Count;
int rowCount = dt.Rows.Count;
int sheetCount = 1; //WorkSheet数量
sheetCount = GetSheetCount(rowCount, rows);
for (int i = 0; i < sheetCount; i++)
{
//获得要插入到当前sheet的数据源。
DataTable pdt = GetSheetTable(dt, i, sheetCount, rows, rowCount);
//新增工作表
workbook.Worksheets.Add();
//获取当前工作表
Worksheet worksheet = workbook.Worksheets[i];
// 输出表头信息 **********************************************************************************
worksheet.Name = Convert.ToString(i + 1);
//如果含有表头，则填写表头列
if (hasHeader)
{
for (int k = 0; k < pdt.Columns.Count; k++)
{
//根据导入数据控制导出数据的格式
if (pdt.Columns[k].DataType == typeof(string))
{
worksheet.Cells.Columns[k].Style.Custom = "@";
}
else if (pdt.Columns[k].DataType == typeof(DateTime))
{
var style = new Style {
Custom = "yyyy-MM-dd"
};
worksheet.Cells[1, k].SetStyle(style);
}
else if (pdt.Columns[k].DataType == typeof(decimal))
{
//worksheet.Cells.Columns[k].Style.Custom = "General";
var style = new Style {
Custom = "####.######", ShrinkToFit = true
};
worksheet.Cells[1, k].SetStyle(style);
}
worksheet.Cells[0, k].PutValue(pdt.Columns[k].Caption.Trim());
}
//如果从第二行开始填写
worksheet.Cells.ImportDataTable(pdt, false, 1, 0, rows, pdt.Columns.Count);
}
else
{
for (int k = 0; k < pdt.Columns.Count; k++)
{
//根据导入数据控制导出数据的格式
if (pdt.Columns[k].DataType == typeof(string))
{
worksheet.Cells.Columns[k].Style.Custom = "@";
}
else if (pdt.Columns[k].DataType == typeof(DateTime))
{
var style = new Style {
Custom = "yyyy-MM-dd"
};
worksheet.Cells[0, k].SetStyle(style);
}
else if (pdt.Columns[k].DataType == typeof(decimal))
{
//worksheet.Cells.Columns[k].Style.Custom = "General";
var style = new Style {
Custom = "####.######", ShrinkToFit = true
};
worksheet.Cells[1, k].SetStyle(style);
}
}
//如果没有表头，第一行则为数据
worksheet.Cells.ImportDataTable(pdt, false, 0, 0, rows, pdt.Columns.Count);
}
if (isAutoFit)
{
worksheet.AutoFitColumns();
worksheet.AutoFitRows(true);
}
if (!string.IsNullOrWhiteSpace(strTitle))
{
string[] arrTitle = strTitle.Split('~');
//定义表格的标题
worksheet.Cells.InsertRow(0);
Range range = worksheet.Cells.CreateRange(worksheet.Cells[0, 0].Name, worksheet.Cells[0, pdt.Columns.Count - 1].Name);
range.RowHeight = 20;
range.Name = "ExcelTitle";
range.Merge();
Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = "宋体";
style.Font.IsBold = true;
style.Font.Size = 14;
style.HorizontalAlignment = TextAlignmentType.Center;
range.Style = style;
range[0, 0].PutValue(arrTitle[0]);
//输入次标题
for (int k = 1; k < arrTitle.Length; k++)
{
worksheet.Cells.InsertRow(k);
Range Range_Title = worksheet.Cells.CreateRange(worksheet.Cells[k, 0].Name, worksheet.Cells[k, pdt.Columns.Count - 1].Name);
Range_Title.RowHeight = 16;
Range_Title.Name = "ExcelTitle";
Range_Title.Merge();
Style style1 = workbook.Styles[workbook.Styles.Add()];
style1.Font.Name = "宋体";
style1.Font.IsBold = true;
style1.Font.Size = 10;
style1.HorizontalAlignment = TextAlignmentType.Center;
Range_Title.Style = style1;
Range_Title[0, 0].PutValue(arrTitle[k]);
}
}
}
if (fileFullPath.ToLower().EndsWith(".xlsx"))
{
workbook.Save(fileFullPath, FileFormatType.Excel2007Xlsx);
}
else
{
workbook.Save(fileFullPath, FileFormatType.Default);
}
}
private void Export(DataTable dt, string tablename)
{
Workbook workbook = new Workbook(); //工作簿
Worksheet worksheet = workbook.Worksheets[0]; //工作表
Cells cells = worksheet.Cells; //单元格集合
//为表名设置样式
Aspose.Cells.Style styleTablename = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleTablename.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleTablename.Font.Name = "宋体"; //文字字体
styleTablename.Font.Size = 22; //文字大小
styleTablename.Font.IsBold = true;
//为标题设置样式
Aspose.Cells.Style styleTitle = workbook.Styles[workbook.Styles.Add()]; //
styleTitle.HorizontalAlignment = TextAlignmentType.Center;
styleTitle.Font.Name = "宋体";
styleTitle.Font.Size = 12;
styleTitle.Font.IsBold = true;
styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//为内容设置样式
Aspose.Cells.Style styleBody = workbook.Styles[workbook.Styles.Add()];
styleBody.HorizontalAlignment = TextAlignmentType.Center;
styleTitle.Font.Name = "宋体";
styleBody.Font.Size = 9;
styleBody.IsTextWrapped = true;
styleBody.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleBody.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleBody.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleBody.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
int colnum = dt.Columns.Count; //表格列数
int rownum = dt.Rows.Count; //表格行数
//生成行1标题行
cells.Merge(0, 0, 1, colnum); //合并单元格
cells[0, 0].PutValue(tablename);
cells[0, 0].SetStyle(styleTablename);
//填充数据
cells.ImportDataTable(dt, true, "A2");
//设置列名的样式
Range rangeTitle = cells.CreateRange(1, 0, 1, colnum);
rangeTitle.ApplyStyle(styleTitle, new StyleFlag()
{
All = true
});
Range rangeBody = cells.CreateRange(2, 0, rownum, colnum);
rangeBody.ApplyStyle(styleBody, new StyleFlag()
{
All = true
});
worksheet.AutoFitColumns();
worksheet.AutoFitRows();
workbook.Save(Response, DateTime.Now.ToString("yyyy-MM-dd") + ".xls", ContentDisposition.Attachment, workbook.SaveOptions);
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="dt">所要导出的数据</param>
/// <param name="tablename">标题</param>
/// <returns></returns>
private void Export(DataTable dt, string tablename)
{
Workbook workbook = new Workbook(); //工作簿
Worksheet worksheet = workbook.Worksheets[0]; //工作表
Cells cells = worksheet.Cells; //单元格集合
//为表名设置样式
Aspose.Cells.Style styleTablename = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleTablename.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleTablename.Font.Name = "宋体"; //文字字体
styleTablename.Font.Size = 22; //文字大小
styleTablename.Font.IsBold = true;
//为标题设置样式
Aspose.Cells.Style styleTitle = workbook.Styles[workbook.Styles.Add()]; //
styleTitle.HorizontalAlignment = TextAlignmentType.Center;
styleTitle.Font.Name = "宋体";
styleTitle.Font.Size = 12;
styleTitle.Font.IsBold = true;
styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//为内容设置样式
Aspose.Cells.Style styleBody = workbook.Styles[workbook.Styles.Add()];
styleBody.HorizontalAlignment = TextAlignmentType.Center;
styleTitle.Font.Name = "宋体";
styleBody.Font.Size = 9;
styleBody.IsTextWrapped = true;
styleBody.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleBody.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleBody.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleBody.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
int colnum = dt.Columns.Count; //表格列数
int rownum = dt.Rows.Count; //表格行数
//生成行1标题行
cells.Merge(0, 0, 1, colnum); //合并单元格
cells[0, 0].PutValue(tablename);
cells[0, 0].SetStyle(styleTablename);
//填充数据
cells.ImportDataTable(dt, true, "A2");
//设置列名的样式
Range rangeTitle = cells.CreateRange(1, 0, 1, colnum);
rangeTitle.ApplyStyle(styleTitle, new StyleFlag()
{
All = true
});
Range rangeBody = cells.CreateRange(2, 0, rownum, colnum);
rangeBody.ApplyStyle(styleBody, new StyleFlag()
{
All = true
});
//Range range = worksheet.Cells.CreateRange(0, 0, 1, 6);
//range.Merge();
//Cell cell = range[0, 0];
//cell.PutValue("测试导出数据");
//Aspose.Cells.Style style = new Aspose.Cells.Style();
//style.Font.Name = "楷体";
//style.VerticalAlignment = TextAlignmentType.Center;
//style.HorizontalAlignment = TextAlignmentType.Center;
//style.Font.Size = 18;
//style.Font.Color = System.Drawing.Color.Blue;
//style.BackgroundColor = System.Drawing.Color.Gray;
//cell.SetStyle(style);
//datatable.Columns.Add("测试1", typeof(string));
//datatable.Columns.Add("测试2", typeof(string));
//datatable.Columns.Add("测试3", typeof(string));
//datatable.Columns.Add("测试4", typeof(string));
//datatable.Columns.Add("测试5", typeof(string));
//datatable.Columns.Add("测试6", typeof(string));
//for (int i = 0; i < 6; i++)
//{
// DataRow dr = datatable.NewRow();
// dr[0] = datatable.Columns[i].ColumnName + "_" + i;
// dr[1] = datatable.Columns[i].ColumnName + "_" + i;
// dr[2] = datatable.Columns[i].ColumnName + "_" + i;
// dr[3] = datatable.Columns[i].ColumnName + "_" + i;
// dr[4] = datatable.Columns[i].ColumnName + "_" + i;
// dr[5] = datatable.Columns[i].ColumnName + "_" + i;
// datatable.Rows.Add(dr);
//}
//worksheet.Cells.ImportDataTable(datatable, true, "A2");
worksheet.AutoFitColumns();
worksheet.AutoFitRows();
workbook.Save(Response, DateTime.Now.ToString("yyyy-MM-dd") + ".xls", ContentDisposition.Attachment, workbook.SaveOptions);
}
private static void exportSummaryDetail(DataTable taskTable, DataTable subTaskTable, Worksheet ws, String[] SelectColumns, String[] SummaryOverviews)
{
string[] taskAttributes = null; //split up selected columns into task and sub task attributes. Task attributes exist in taskTable while subTask attributes exist in subTask table. Summary overview attributers exist in both tables.
string[] subTaskAttributes = null;
DataRow currentRow = null;
int taskRowsPrinted = 0; //need to alternate between printing tasks and sub tasks. Need to know where you stopped in the table, so you can start from that position next time you print.
int subTaskRowsPrinted = 0;
int rowCount = 1; //keep track all rows printed.
int newRowsPrinted = 0; //keep track of new rows printed to be added to the above row counts.
taskAttributes = Array.FindAll <string>(SelectColumns, x => x.StartsWith("Task")); //task attributes all begin with "task". Sub task attributres all begin with "Sub-Task".
subTaskAttributes = Array.FindAll <string>(SelectColumns, x => x.StartsWith("Sub-Task"));
int mergedCellCount = subTaskAttributes.Length > taskAttributes.Length ? subTaskAttributes.Length : taskAttributes.Length; //the number of columns that will be in the table, used for creating cell headers and the like.
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
cellStyle.Font.IsBold = true;
cellStyle.Font.Underline = FontUnderlineType.Single;
cellStyle.IsTextWrapped = true;
cellStyle.Font.Size = cellStyle.Font.Size + 2;
cellStyle.IsTextWrapped = true;
bool autosize = true;; //if true let aspose size the columns, if false manually size columns so that all columns fit in pdf page width.
List <double> columSizeRatio = getColumnSizeRatio(taskTable, subTaskTable, taskAttributes, subTaskAttributes, mergedCellCount, ref autosize); //this list contains the ratio of column sizes, where the ratio is defined as the neccessary size of the column devided by the total size of all columns. This list is null if autosize is set to true.
if (autosize == false)
{
for (int i = 0; i < mergedCellCount; i++) //resize columns so they fit on one page.
{
double size = (double)9.0 * columSizeRatio[i]; //in ladscape mode with 1 inch margins the available page space is about 9 inches.
ws.Cells.SetColumnWidthInch(i, size);
}
}
ws.Cells.Merge(0, 0, 1, mergedCellCount);
ws.Cells["A1"].PutValue("Workload Overview");
ws.Cells["A1"].SetStyle(cellStyle);
ws.AutoFitRow(0, 0, 0);
rowCount += 2;
rowCount += newRowsPrinted;
while (taskRowsPrinted < taskTable.Rows.Count) //the task table is the driver of the loop, because it is not possible to have orphan sub tasks; that is there should never be an instance where a sub task has attributes distinct from all higher level tasks
{
DataRow oldRow = currentRow;
currentRow = taskTable.Rows[taskRowsPrinted]; //get the next row to be printed
newRowsPrinted = printOverviews(currentRow, oldRow, SummaryOverviews, ws, rowCount, mergedCellCount);
rowCount += newRowsPrinted;
newRowsPrinted = printTasks(taskAttributes, taskTable, ws, rowCount, ref taskRowsPrinted, currentRow, SummaryOverviews, mergedCellCount, alternateColor: false);
rowCount += newRowsPrinted;
newRowsPrinted = printTasks(subTaskAttributes, subTaskTable, ws, rowCount, ref subTaskRowsPrinted, currentRow, SummaryOverviews, mergedCellCount, alternateColor: true);
rowCount += newRowsPrinted;
}
if (autosize == true)
{
ws.AutoFitColumns();
}
else
{
ws.AutoFitRows();
}
ws.Name = "Summary Detail";
return;
}
private static void exportSummaryOverviews(DataTable parentTable, DataTable childTable, Worksheet ws, String[] SummaryOverview1 = null, String[] SummaryOverview2 = null)
{
int rowCount = 5; //holds the integer value of the current row in the document to write too.
int childRowsPrinted = 0;
string headerRow = "";
string[] parentColumns = object.ReferenceEquals(SummaryOverview1, null) ? SummaryOverview2 : SummaryOverview1; //it is possible for there to be only one summary level.
bool level1 = !Object.ReferenceEquals(SummaryOverview1, null);
bool level2 = !Object.ReferenceEquals(SummaryOverview2, null);
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
cellStyle.Font.Underline = FontUnderlineType.Single;
cellStyle.Font.IsBold = true;
cellStyle.IsTextWrapped = true;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.Font.Size = cellStyle.Font.Size + 2;
if (level1) //if there is a level 1 summary, add the column names to the header row
{
headerRow = String.Join(",", SummaryOverview1);
}
if (level1 && level2) //if there is both a level one and level two summary, add a \ to deliminate the too.
{
headerRow += " \\ ";
}
if (level2) //if there is a level 2 summary, add the column names to the header row.
{
headerRow += String.Join(",", SummaryOverview2);
}
if (headerRow.Length > 90) //if the header row is really long then you have to manually set the column widths so everthing fits.
{
ws.Cells.SetColumnWidthInch(0, 1);
ws.Cells.SetColumnWidthInch(1, 6);
ws.Cells.SetColumnWidthInch(2, 1);
ws.Cells.SetColumnWidthInch(3, 1);
}
ws.Cells.Merge(0, 0, 1, 4);
ws.Cells["A1"].PutValue("Summary Overview");
ws.Cells["A1"].SetStyle(cellStyle);
ws.AutoFitRow(0, 0, 0);
cellStyle.Font.Size = cellStyle.Font.Size - 2;
ws.Cells.Merge(2, 0, 1, 4);
ws.Cells["A3"].PutValue(headerRow);
ws.Cells["A3"].SetStyle(cellStyle);
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells["A4"].PutValue("Rank");
ws.Cells["A4"].SetStyle(cellStyle);
ws.Cells["B4"].PutValue(headerRow); //print table header row
ws.Cells["B4"].SetStyle(cellStyle);
ws.Cells["C4"].PutValue("Tasks");
ws.Cells["C4"].SetStyle(cellStyle);
ws.Cells["D4"].PutValue("Sub Tasks");
ws.Cells["D4"].SetStyle(cellStyle);
foreach (DataRow parentRow in parentTable.Rows)
{
printParentRows(parentColumns, parentRow, rowCount, ws);
rowCount++;
if (!Object.ReferenceEquals(childTable, null)) //if there is a child table, print the child rows.
{
int newRowsPrinted = 0;
newRowsPrinted = printChildRows(parentColumns, SummaryOverview2, parentRow, childTable, rowCount, childRowsPrinted, ws);
rowCount += newRowsPrinted;
childRowsPrinted += newRowsPrinted;
}
}
if (headerRow.Length > 90)
{
ws.AutoFitRows();
}
else
{
ws.AutoFitColumns();
}
printTotalRow(parentTable, ws, rowCount);
ws.Name = "Summary Overviews";
return;
}
public void ExportVithStyle(IList <HdrTablesCollection> schemas, List <ColumnEntity> tableColumns, Workbook workbook, int columnsOffset = 0, bool generateDiffColumn = false)
{
var schemaAndTablas = schemas.GroupBy(p => p.Schema).ToList();
AsposeHelper heper = new AsposeHelper();
IList <Style> styles = heper.CheckTest(workbook);
for (int ischema = 0; ischema < schemaAndTablas.Count; ischema++)
{
//一个schema
var item = schemaAndTablas[ischema];
string schemaname = item.Key;
var itemlst = schemaAndTablas[ischema].ToList();//所有的表
Worksheet sheetSchema = workbook.Worksheets.FirstOrDefault(p => p.Name == schemaname);
if (sheetSchema == null)
{
sheetSchema = workbook.Worksheets.Add(schemaname);
}
var cells = sheetSchema.Cells;
//架构下所有的表
int rowIndex = 2;
int columnOffset = columnsOffset;
for (int jtable = 0; jtable < itemlst.Count; jtable++)
{
string tableName = itemlst[jtable].TableName;
#region 写入表级别 说明
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].PutValue(tableName);
rowIndex++;
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].PutValue(schemaname);
//占用行
//列
rowIndex++;
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].PutValue("序号");
cells[CellsHelper.CellIndexToName(rowIndex, 1 + columnOffset)].PutValue("列名");
cells[CellsHelper.CellIndexToName(rowIndex, 2 + columnOffset)].PutValue("中文名");
cells[CellsHelper.CellIndexToName(rowIndex, 3 + columnOffset)].PutValue("字段类型");
cells[CellsHelper.CellIndexToName(rowIndex, 4 + columnOffset)].PutValue("允许空");
cells[CellsHelper.CellIndexToName(rowIndex, 5 + columnOffset)].PutValue("外键");
cells[CellsHelper.CellIndexToName(rowIndex, 6 + columnOffset)].PutValue("字典系统");
cells[CellsHelper.CellIndexToName(rowIndex, 7 + columnOffset)].PutValue("字典标准化");
cells[CellsHelper.CellIndexToName(rowIndex, 8 + columnOffset)].PutValue("说明");
if (generateDiffColumn)
{
cells[CellsHelper.CellIndexToName(rowIndex, 9 + columnOffset)].PutValue("列名对比");
cells[CellsHelper.CellIndexToName(rowIndex, 10 + columnOffset)].PutValue("字段类型对比");
cells[CellsHelper.CellIndexToName(rowIndex, 11 + columnOffset)].PutValue("允许空对比");
}
rowIndex++;
#endregion
#region 处理列级别 表对应的列
//写入具体的表列
var tabItemCols = tableColumns.Where(p => p.table_schema == schemaname).Where(p => p.table_name == tableName).OrderBy(p => p.ordinal_position).ToList();
for (int icolumn = 0; icolumn < tabItemCols.Count; icolumn++)
{
var colItem = tabItemCols[icolumn];
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].PutValue(colItem.ordinal_position);
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 1 + columnOffset)].PutValue(colItem.column_name);
cells[CellsHelper.CellIndexToName(rowIndex, 1 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 2 + columnOffset)].PutValue(colItem.chinese_name);
cells[CellsHelper.CellIndexToName(rowIndex, 2 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 3 + columnOffset)].PutValue(colItem.data_typ);
cells[CellsHelper.CellIndexToName(rowIndex, 3 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 4 + columnOffset)].PutValue(colItem.is_nullable);
cells[CellsHelper.CellIndexToName(rowIndex, 4 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 5 + columnOffset)].PutValue(colItem.ref_key);
cells[CellsHelper.CellIndexToName(rowIndex, 5 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 6 + columnOffset)].PutValue(colItem.vale_range);
cells[CellsHelper.CellIndexToName(rowIndex, 6 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 7 + columnOffset)].PutValue(colItem.is_standard);
cells[CellsHelper.CellIndexToName(rowIndex, 7 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 8 + columnOffset)].PutValue(colItem.col_memo);
cells[CellsHelper.CellIndexToName(rowIndex, 8 + columnOffset)].SetStyle(styles[2]);
if (generateDiffColumn)
{
string columnNameIndexName = CellsHelper.CellIndexToName(rowIndex, 1); // 列名
string columnTypeIndexName = CellsHelper.CellIndexToName(rowIndex, 3); // 字段类型
string columnIsNullIndexName = CellsHelper.CellIndexToName(rowIndex, 4); // 允许空
string columnNameIndexName1 = CellsHelper.CellIndexToName(rowIndex, 1 + 9); // 列名
string columnTypeIndexName1 = CellsHelper.CellIndexToName(rowIndex, 3 + 9); // 字段类型
string columnIsNullIndexName1 = CellsHelper.CellIndexToName(rowIndex, 4 + 9); // 允许空
//★☆◊
cells[CellsHelper.CellIndexToName(rowIndex, 9 + columnOffset)].Formula = $"=IF(EXACT({columnNameIndexName}, {columnNameIndexName1}), \"★\", \"☆\")";
cells[CellsHelper.CellIndexToName(rowIndex, 10 + columnOffset)].Formula = $"=IF(EXACT({columnTypeIndexName}, {columnTypeIndexName1}), \"★\", \"☆\")";
cells[CellsHelper.CellIndexToName(rowIndex, 11 + columnOffset)].Formula = $"=IF(EXACT({columnIsNullIndexName}, {columnIsNullIndexName1}), \"★\", \"☆\")";
}
rowIndex++;
}
#endregion
}
sheetSchema.AutoFitColumns();
sheetSchema.AutoFitRows();
}
}
//
private Workbook Object2Workbook(JObject jsonObject, HttpContext context)
{
#region Aspose.Cell引用
Aspose.Cells.License licExcel = new License(); //Aspose.Cells申明
if (File.Exists(context.Server.MapPath("~/bin/Service/_extend/cellLic.lic")))
{
licExcel.SetLicense(context.Server.MapPath("~/bin/Service/_extend/cellLic.lic"));
}
#endregion
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Styles styles = workbook.Styles;
int styleIndex = styles.Add();
Aspose.Cells.Style borderStyle = styles[styleIndex];
borderStyle.Borders.DiagonalStyle = CellBorderType.None;
borderStyle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
Cells cells = sheet.Cells;
//sheet.FreezePanes(1, 1, 1, 0);//冻结第一行
sheet.Name = jsonObject["sheetName"].ToString();//接受前台的Excel工作表名
//为标题设置样式
Style styleTitle = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleTitle.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleTitle.Font.Name = "宋体"; //文字字体
styleTitle.Font.Size = 18; //文字大小
styleTitle.Font.IsBold = true; //粗体
//题头样式
Style styleHeader = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleHeader.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleHeader.Font.Name = "宋体"; //文字字体
styleHeader.Font.Size = 14; //文字大小
styleHeader.Font.IsBold = true; //粗体
styleHeader.IsTextWrapped = true; //单元格内容自动换行
styleHeader.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//内容样式
Style styleContent = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleContent.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleContent.Font.Name = "宋体"; //文字字体
styleContent.Font.Size = 12; //文字大小
styleContent.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
var rowCount = jsonObject["rows"].ToArray().Count(); //表格行数
var columnCount = jsonObject["columns"].ToArray().Count(); //表格列数
//生成行1 标题行
cells.Merge(0, 0, 1, columnCount); //合并单元格
cells[0, 0].PutValue(jsonObject["sheetName"]); //填写内容
cells[0, 0].Style = styleTitle;
cells.SetRowHeight(0, 25);
//生成题头列行
for (int i = 0; i < columnCount; i++)
{
cells[1, i].PutValue(jsonObject["columns"].ToArray()[i]["title"]);
cells[1, i].Style = styleHeader;
cells.SetRowHeight(1, 23);
}
//生成地址列
cells[1, columnCount].PutValue("地址");
cells[1, columnCount].Style = styleHeader;
cells.SetRowHeight(1, 23);
//调用接口进行地址解析
List <AddressConvert.DLngLat> convertAddrList = new List <AddressConvert.DLngLat>(); //存放需要解析地址的轨迹点的坐标
for (int i = 0; i < rowCount; i++)
{
//
AddressConvert.DLngLat dlnglat = new AddressConvert.DLngLat();
dlnglat.Lng = double.Parse(jsonObject["rows"].ToArray()[i]["Long"].ToString());
dlnglat.Lat = double.Parse(jsonObject["rows"].ToArray()[i]["Lati"].ToString());
convertAddrList.Add(dlnglat);
}
//
string[] conArr = CommLibrary.AddressConvert.AddConvertBatch(convertAddrList);
//生成内容行,第三行起始
//生成数据行
for (int i = 0; i < rowCount; i++)
{
for (int k = 0; k < columnCount; k++)
{
var currentColumnName = jsonObject["columns"].ToArray()[k]["field"];
var b = jsonObject["rows"].ToArray()[i][currentColumnName.ToString()];
cells[2 + i, k].PutValue(jsonObject["rows"].ToArray()[i][currentColumnName.ToString()]);
//cells[2 + i, k].PutValue(jsonObject.rows[i][currentColumnName.Value]);
cells[2 + i, k].Style = styleContent;
}
//地址行
cells[2 + i, columnCount].PutValue(conArr[i]);
//cells[2 + i, k].PutValue(jsonObject.rows[i][currentColumnName.Value]);
cells[2 + i, columnCount].Style = styleContent;
cells.SetRowHeight(2 + i, 22);
}
//添加制表日期
cells[2 + rowCount, columnCount].PutValue("制表日期:" + DateTime.Now.ToShortDateString());
sheet.AutoFitColumns(); //让各列自适应宽度
sheet.AutoFitRows(); //让各行自适应宽度
return(workbook);
}
/// <summary>
/// Export Excel from Datatable
/// </summary>
/// <param name="ExportControl"></param>
/// <param name="strTitle"></param>
/// <param name="strSubTitle"></param>
/// <param name="strFileName"></param>
/// <param name="strDestFont"></param>
public static void ExportExcel(DataTable dtExport, string strFileName)
{
Workbook workbook = new Workbook();
Worksheet wSheet = workbook.Worksheets[0];
wSheet.Name = dtExport.TableName;
ImportTableOptions importOptions = new ImportTableOptions();
importOptions.IsFieldNameShown = false;
Range range = null;
Style style = null;
StyleFlag styleFlag = null;
bool bOverwrite = true;
if (System.IO.File.Exists(strFileName))
{
try
{
if (true)
{
System.IO.File.Delete(strFileName);
}
else
{
bOverwrite = false;
}
}
catch (Exception ex)
{
return;
}
}
//DataTable dtExport;
string[] lstColumnsID = null;
string[] lstColumnsName = null;
int iColLength, iRowLength, iNextRow = 0;
int iBold = -1;
int iCongThuc = -1;
int iFore_Color = -1;
int iBack_Color = -1;
int iColor = -1;
string strAddr1, strAddr2;
iColLength = dtExport.Columns.Count;
iRowLength = dtExport.Rows.Count;
lstColumnsID = new string[iColLength];
lstColumnsName = new string[iColLength];
//Điền dữ liệu vào objColNames
for (int i = 0; i < iColLength; i++)
{
lstColumnsID[i] = dtExport.Columns[i].ColumnName;
lstColumnsName[i] = dtExport.Columns[i].ColumnName;
}
#region Create column header
wSheet.Cells.ImportArray(lstColumnsName, iNextRow, 0, false);
strAddr1 = wSheet.Cells[iNextRow, 0].Name;
strAddr2 = wSheet.Cells[iNextRow, iColLength - 1].Name;
//strAddr2 = wSheet.Cells[dtExport.Rows.Count+1, iColLength - 1].Name;
range = wSheet.Cells.CreateRange(strAddr1 + ":" + strAddr2);
style = workbook.Styles[workbook.Styles.Add()];
styleFlag = new StyleFlag();
styleFlag.All = true;
style.IsTextWrapped = true;
style.Number = 49;
//styleFlag.NumberFormat = true;
#region ColumnName Bold and Center.
// style.VerticalAlignment = TextAlignmentType.Center;
//style.HorizontalAlignment = TextAlignmentType.Center;
//style.Font.IsBold = true;
#endregion
//
range.ApplyStyle(style, styleFlag);
//End column header
#endregion
#region Formating the columns before polulating the data
//style = workbook.Styles[workbook.Styles.Add()];
styleFlag = new StyleFlag();
styleFlag.All = true;
iNextRow += 1;
//End Format
#endregion
//workbook.Save(strFileName);
//return;
#region Polulating the data
try
{
//style = workbook.Styles[workbook.Styles.Add()];
strAddr1 = wSheet.Cells[iNextRow, 0].Name;
strAddr2 = wSheet.Cells[dtExport.Rows.Count, iColLength - 1].Name;
range = wSheet.Cells.CreateRange(strAddr1 + ":" + strAddr2);
style.IsTextWrapped = false;
style.Number = 49;
range.ApplyStyle(style, styleFlag);
DataView dvExport = dtExport.DefaultView;
DataTable dtTemp = dvExport.ToTable(false, lstColumnsID);
wSheet.Cells.ImportData(dtTemp, iNextRow, 0, importOptions);
//Creating a range of cells starting from "A1" cell to 3rd column in a row
}
catch (Exception ex)
{
throw new Exception("Có lỗi xảy ra: " + ex.Message);
}
#endregion
wSheet.AutoFitRows();
wSheet.AutoFitColumns();
//Ghi file lên đĩa
if (bOverwrite)
{
try
{
if (System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(strFileName)))
{
workbook.Save(strFileName);
}
}
catch (Exception ex)
{
throw new Exception("Không thể kết xuất được dữ liệu!" + ex.ToString());
}
}
}
