private static int printHeaderRow(Worksheet ws, String[] SelectedColumns, int rowCount, String Worklog, int mergedCellCount, bool alternateColor)
{
int uniA = (int)'A';
int numberRowsPrinted = 0;
String cellName = "A" + rowCount.ToString();
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.IsTextWrapped = true;
cellStyle.Font.IsBold = true;
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.ForegroundColor = Color.Orange;
cellStyle.Pattern = BackgroundType.Solid;
ws.Cells.Merge(rowCount - 1, 0, 1, mergedCellCount);
ws.Cells[cellName].PutValue(Worklog); //print current worklog, where work log is either production, release, or backlog
ws.Cells[cellName].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
numberRowsPrinted++;
cellStyle.ForegroundColor = (alternateColor) ? Color.LightGreen : Color.LightBlue;
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.IsTextWrapped = true;
for (int i = 0; i < SelectedColumns.Length; i++) //print the attribute names
{
string column = (SelectedColumns[i] == "Task #" || SelectedColumns[i] == "Sub-Task #") ? SelectedColumns[i] : SelectedColumns[i].Replace("Sub-Task", "").Replace("Task", "");
cellName = (char)(uniA + i) + (rowCount + numberRowsPrinted).ToString();
ws.Cells[cellName].PutValue(column);
ws.Cells[cellName].SetStyle(cellStyle);
}
numberRowsPrinted++;
return(numberRowsPrinted);
}
/// <summary>
/// 标题样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="pRange"></param>
private static void DrawTitleStyle(Workbook workbook, Range pRange)
{
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Gray;
style.ForegroundColor = c;
style.Pattern = BackgroundType.Solid;
style.Font.Color = Color.White;
style.Font.IsBold = true;
//s.Borders.DiagonalColor = Color.Red;
//s.Borders.DiagonalStyle= CellBorderType.Thick;
//s.Borders.SetColor(Color.Red);
//s.Borders.SetStyle(CellBorderType.Thick);
//s.SetBorder(BorderType.Horizontal | BorderType.Vertical, CellBorderType.Dashed , Color.Red);
//s.SetBorder(BorderType.LeftBorder | BorderType.RightBorder | BorderType.BottomBorder | BorderType.TopBorder, CellBorderType.Thick, Color.Red);
style.HorizontalAlignment = TextAlignmentType.Center;
//s.ShrinkToFit = true;
SetBorder(style);
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(style, styleFlag);
}
//Aspose导出
private void AsposeExport(DataTable dt, string savePath)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
Aspose.Cells.Style styleTitle = workbook.Styles[workbook.Styles.Add()];
styleTitle.Font.IsBold = true;
int colnums = dt.Columns.Count;
int rownums = dt.Rows.Count;
for (int i = 0; i < colnums; i++)
{
cells[0, i].PutValue(dt.Columns[i].ColumnName);
cells[0, i].SetStyle(styleTitle);
cells.SetColumnWidth(i, 18);
}
for (int i = 0; i < rownums; i++)
{
for (int j = 0; j < colnums; j++)
{
cells[1 + i, j].PutValue(dt.Rows[i][j].ToString());
}
}
//Aspose.Cells.Style style = cells["D2"].GetStyle();
//style.Number = 3;
//cells["D2"].SetStyle(style);
workbook.Save(savePath);
}
public static void AddBackground(Cell c, Color color, BackgroundType type)
{
Aspose.Cells.Style style = c.GetStyle();
style.Pattern = type;
style.ForegroundColor = color;
c.SetStyle(style);
}
public void CreateStaticReport()
{
// Create a workbook object.
Workbook workbook = new Workbook();
// Create a worksheet and get the first worksheet.
Worksheet ExcelWorkSheet = workbook.Worksheets[0];
// Obtain the existing Validations collection.
ValidationCollection validations = ExcelWorkSheet.Validations;
// Create a validation object adding to the collection list.
Validation validation = validations[validations.Add()];
// Set the validation type.
validation.Type = ValidationType.Decimal;
// Specify the operator.
validation.Operator = OperatorType.Between;
// Set the lower and upper limits.
validation.Formula1 = Decimal.MinValue.ToString();
validation.Formula2 = Decimal.MaxValue.ToString();
// Set the error message.
validation.ErrorMessage = "Please enter a valid integer or decimal number";
// Specify the validation area of cells.
CellArea area;
area.StartRow = 0;
area.EndRow = 9;
area.StartColumn = 0;
area.EndColumn = 0;
// Add the area.
validation.AreaList.Add(area);
// Set the number formats to 2 decimal places for the validation area.
for (int i = 0; i < 10; i++)
{
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Custom = "0.00";
ExcelWorkSheet.Cells[i, 0].SetStyle(style);
}
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "DecimalNumberValidation.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "DecimalNumberValidation.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
static void printTotalRow(DataTable dt, Worksheet ws, int rowCount)
{
int totalTasks = 0;
int totalSubtasks = 0;
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thick, 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.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.Font.IsBold = true;
cellStyle.IsTextWrapped = true;
foreach (DataRow row in dt.Rows)
{
totalTasks += row.Field <int>("Tasks"); //computer total tasks/subtasks from parent table
totalSubtasks += row.Field <int>("Subtasks");
}
ws.Cells['B' + rowCount.ToString()].PutValue("Total"); //print to worksheet
ws.Cells['B' + rowCount.ToString()].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
cellStyle.Font.IsBold = false;
ws.Cells['A' + rowCount.ToString()].SetStyle(cellStyle);
ws.Cells['C' + rowCount.ToString()].PutValue(totalTasks);
ws.Cells['C' + rowCount.ToString()].SetStyle(cellStyle);
ws.Cells['D' + rowCount.ToString()].PutValue(totalSubtasks);
ws.Cells['D' + rowCount.ToString()].SetStyle(cellStyle);
}
public static void AddAlignment(Cell c)
{
Aspose.Cells.Style style = c.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.IsTextWrapped = true;
c.SetStyle(style);
}
/// <summary>
/// 构造函数
/// </summary>
public ExcelHelper()
{
book = new Workbook();
sheet = book.Worksheets[0];
st = book.Styles[book.Styles.Add()];
st.Borders.SetStyle(CellBorderType.Thin);
st.Borders.DiagonalStyle = CellBorderType.None;
}
private void SetProductsByCategoryStyles(Workbook workbook)
{
//Create a style with some specific formatting attributes
int styleIndex = workbook.Styles.Add();
Aspose.Cells.Style style = workbook.Styles[styleIndex];
style.Font.IsItalic = true;
style.Font.IsBold = true;
style.Font.Size = 16;
style.HorizontalAlignment = TextAlignmentType.Right;
style.Name = "Category";
//Create a style with some specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 16;
style.Font.IsBold = true;
style.HorizontalAlignment = TextAlignmentType.Left;
style.Name = "CategoryName";
//Create a style with some specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 14;
style.Font.IsBold = true;
style.Font.IsItalic = true;
style.HorizontalAlignment = TextAlignmentType.Left;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
style.Name = "ProductName";
//Create a style with some specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 14;
style.Font.IsBold = true;
style.Font.IsItalic = true;
style.HorizontalAlignment = TextAlignmentType.Right;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
style.Name = "UnitsInStock";
//Create a style with some specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.IsBold = true;
style.Font.IsItalic = true;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Name = "ProductsCount";
//Create a style with some specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.HorizontalAlignment = TextAlignmentType.Left;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Name = "CountNumber";
}
public static bool DataTableToExcel2(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 ExportData(string value)
{
try
{
// open and read file
FileStream fstream = new FileStream((Server.MapPath("~/Uploads/AsposeDynamicFormsDataFile.xlsx")), FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.Worksheets["Data"];
DataTable dataTable;
dataTable = worksheet.Cells.Rows.Count <= 0 ? worksheet.Cells.ExportDataTableAsString(0, 0, 1, 1, true) : worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.Rows.Count, 8, true);
//Closing the file stream to free all resources
fstream.Close();
//Instantiate a new Workbook
Workbook book = new Workbook();
//Clear all the worksheets
book.Worksheets.Clear();
//Add a new Sheet "Data";
Worksheet sheet = book.Worksheets.Add("Data");
// import data in to sheet
sheet.Cells.ImportDataTable(dataTable, true, "A1");
// Apply Hearder Row/First Row text to Bold
Aspose.Cells.Style objStyle = workbook.CreateStyle();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
sheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
book.Worksheets[0].AutoFitColumns();
//Create unique file name
string fileName = System.Guid.NewGuid().ToString() + "." + value;
//Save workbook as per export type
book.Save(this.Response, fileName, ContentDisposition.Attachment, GetSaveFormat(value));
Response.Flush();
}
catch (Exception exc)
{
}
}
/// <summary>
/// 导出数据到Excel
/// </summary>
/// <param name="soureChineseData"></param>
/// <param name="sourceEnglishData"></param>
private void ExportDataToExcel(List <LangModel> soureChineseData, List <LangModel> sourceEnglishData)
{
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Style style = new Style();
style.Number = 49;
style.IsTextWrapped = true;
sheet.Cells[0, 0].Value = "Key";
sheet.Cells[0, 1].Value = LanguageType.ZhCHS;
sheet.Cells.ApplyColumnStyle(1, style, new StyleFlag());
sheet.Cells[0, 2].Value = LanguageType.EnUs;
sheet.Cells.ApplyColumnStyle(2, style, new StyleFlag());
sheet.Cells[0, 3].Value = "中文长度<英文长度";
sheet.Cells.ApplyColumnStyle(3, style, new StyleFlag());
int rowIndex = 1;
soureChineseData = soureChineseData.Where(i => !string.IsNullOrWhiteSpace(i.Value)).ToList();
foreach (var langSource in soureChineseData)
{
sheet.Cells[rowIndex, 0].Value = langSource.Key;
sheet.Cells[rowIndex, 1].Value = langSource.Value;
if (sourceEnglishData.Any(i => i.Key == langSource.Key))
{
var enValue = sourceEnglishData.First(i => i.Key == langSource.Key).Value;
sheet.Cells[rowIndex, 2].Value = enValue;
//中英文长度对比
if (TextHelper.CompareTextLength(langSource.Value, enValue))
{
sheet.Cells[rowIndex, 3].Value = "True";
}
}
rowIndex++;
}
//调整列表显示
sheet.FreezePanes(1, 1, 1, 0);
sheet.AutoFitColumns();
sheet.Cells.SetColumnWidth(1, 40);
sheet.Cells.SetColumnWidth(1, 70);
sheet.Cells.SetColumnWidth(2, 70);
SaveFileDialog sfDialog = new SaveFileDialog();
sfDialog.InitialDirectory = @"C:\Users\user\Desktop\";
sfDialog.Filter = "*.Excel文件|*.xlsx";
if (sfDialog.ShowDialog() == true)
{
string filePath = sfDialog.FileName;
wb.Save(filePath);
}
}
protected static void SetMergeAndStyle(Aspose.Cells.Style style, Cells cls, int startRow, int startCol, int rowNumber, int colNumber)
{
cls.Merge(startRow, startCol, rowNumber, colNumber);
for (int i = 0; i < rowNumber; i++)
{
for (int j = 0; j < colNumber; j++)
{
cls[startRow + i, startCol + j].Style = style;
}
}
}
private void printHeader(ref DataTable copydt, Worksheet ws)
{
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#E6E6E6");
for (int i = 0; i < copydt.Columns.Count; i++)
{
ws.Cells[0, i].PutValue(copydt.Columns[i].ColumnName);
ws.Cells[0, i].SetStyle(style);
}
}
private void printParentHeader(Worksheet ws, ref int rowCount, DataColumnCollection columns)
{
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#E6E6E6");
for (int i = 0; i < columns.Count; i++)
{
ws.Cells[rowCount, i].PutValue(columns[i].ColumnName);
ws.Cells[rowCount, i].SetStyle(style);
}
rowCount++;
}
//Quang Huy 2014-02-25
/// <summary>
/// tô nền cho range
/// </summary>
/// <param name="range"></param>
/// <param name="color"></param>
private void SetRangeBgColor(Aspose.Cells.Range range, System.Drawing.Color?color = null)
{
System.Drawing.Color c = color ?? System.Drawing.Color.White;
//Aspose.Cells.Style s = range.Style ?? range[0, 0].GetStyle();
Aspose.Cells.Style s = range[0, 0].GetStyle();
s.BackgroundColor = c;
s.Update();
range.ApplyStyle(s, (new StyleFlag()
{
All = true
}));
}
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);
}
private static int printChildRows(string[] parentColumns, string[] SummaryOverviews, DataRow parentRow, DataTable childTable, int rowCount, int childRowsPrinted, Worksheet ws)
{
//prints rows from the child table, beginning at childRowsPrinted, where the child table attributes are equal to the parent table attributes. Returns the number of rows printed.
//this method gets called for each parent row, and it picks up where it left off last time in the child table.
int newRowsPrinted = 0;
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
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.IsTextWrapped = true;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
for (int i = childRowsPrinted; i < childTable.Rows.Count; i++, newRowsPrinted++)
{
DataRow childRow = childTable.Rows[i];
string childSummaries = "";
string childRanking = "";
int value;
foreach (string column in parentColumns) //check if childrow attributes are equal to the current parent row attributes. If equal print the row, else return.
{
if (parentRow.Field <string>(column) != childRow.Field <string>(column))
{
return(newRowsPrinted); //stop print child rows. Return to this spot in the table to print next time.
}
}
foreach (string column in SummaryOverviews)
{
string rankingColumn = String.Concat(column, " Rank");
childSummaries += childRow.Field <string>(column) + ',';
childRanking += childRow.Field <int>(rankingColumn).ToString() + ',';
}
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
childSummaries = childSummaries.TrimEnd(',');
childRanking = childRanking.TrimEnd(',');
ws.Cells['B' + (rowCount + newRowsPrinted).ToString()].PutValue(" " + childSummaries);
ws.Cells['B' + (rowCount + newRowsPrinted).ToString()].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells['A' + (rowCount + newRowsPrinted).ToString()].PutValue(childRanking);
ws.Cells['A' + (rowCount + newRowsPrinted).ToString()].SetStyle(cellStyle);
value = childRow.Field <Int32>("Tasks");
ws.Cells['C' + (rowCount + newRowsPrinted).ToString()].PutValue(value);
ws.Cells['C' + (rowCount + newRowsPrinted).ToString()].SetStyle(cellStyle);
value = childRow.Field <Int32>("Subtasks");
ws.Cells['D' + (rowCount + newRowsPrinted).ToString()].PutValue(value);
ws.Cells['D' + (rowCount + newRowsPrinted).ToString()].SetStyle(cellStyle);
}
return(newRowsPrinted);
}
/// <summary>
/// 创建标题样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="AlignmentType"></param>
/// <returns></returns>
private Style CreateStyle(Workbook workbook, TextAlignmentType AlignmentType = TextAlignmentType.Center)
{
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Gray;
style.ForegroundColor = c;
style.Pattern = BackgroundType.Solid;
style.Font.Color = Color.White;
style.Font.IsBold = true;
style.HorizontalAlignment = AlignmentType;
SetBorder(style);
return(style);
}
private void printChildHeader(Worksheet ws, ref int rowCount, DataColumnCollection columns)
{
if (object.ReferenceEquals(columns, null) || columns.Count < 1) { return; }
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("lightBlue");
for (int i = 0; i < columns.Count; i++)
{
ws.Cells[rowCount, i + 2].PutValue(columns[i].ColumnName);
ws.Cells[rowCount, i + 2].SetStyle(style);
}
rowCount++;
}
/// <summary>
/// 边框样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
private void DrawBorderAll(Workbook workbook, Range r)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Black;
s.Borders.SetColor(c);
s.Borders.SetStyle(CellBorderType.Thin);
s.Borders.DiagonalStyle = CellBorderType.None;
s.Pattern = BackgroundType.Solid;
StyleFlag styleFlag = new StyleFlag();
styleFlag.Borders = true;
r.ApplyStyle(s, styleFlag);
}
private void UpdateSheet(DataTable dt)
{
Session["AsposeDynamicFormsdataTable"] = dt;
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/Uploads/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.Worksheets["Settings"];
//Closing the file stream to free all resources
fstream.Close();
workbook.Worksheets.RemoveAt("Settings");
worksheet = workbook.Worksheets.Add("Settings");
worksheet.Cells.ImportDataTable(dt, true, "A1");
Aspose.Cells.Style objStyle = workbook.CreateStyle();
objStyle.Font.IsBold = true;
//Bold style flag options
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
//Apply this style to row 1
Row row1 = workbook.Worksheets[0].Cells.Rows[0];
row1.ApplyStyle(objStyle, objStyleFlag);
worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
workbook.Worksheets["Data"].AutoFitColumns();
workbook.Save(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
PopulateGrid();
if (!ProcessButton.Text.Equals("Update"))
{
lbl_Msg.Visible = true;
lbl_Msg.CssClass = "Success";
lbl_Msg.Text = "Field added successfully";
}
ClearFields();
}
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>
/// 必填字段的样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
private void DrawMastFillStyle(Workbook workbook, Range r)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Maroon;
s.ForegroundColor = c;
s.Pattern = BackgroundType.Solid;
s.Font.Color = Color.White;
s.Font.IsBold = true;
s.HorizontalAlignment = TextAlignmentType.Center;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
r.ApplyStyle(s, styleFlag);
}
/// <summary>
/// 标题样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
private void DrawTitleStyle(Workbook workbook, Range r)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Gray;
s.ForegroundColor = c;
s.Pattern = BackgroundType.Solid;
s.Font.Color = Color.White;
s.Font.IsBold = true;
s.HorizontalAlignment = TextAlignmentType.Center;
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
r.ApplyStyle(s, styleFlag);
}
protected void btn_download_excel_Click(object sender, EventArgs e)
{
string sql = string.Format(@"select * from ref_language order by code");
DataTable dt = ConnectSql.GetTab(sql);
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; //新增样式
//标题
style.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style.Font.Name = "Arial"; //文字字体 ,宋体
style.Font.Size = 18; //文字大小
style.Font.IsBold = false; //粗体
cells[0, 0].PutValue("");
cells[0, 1].PutValue("code");
cells[0, 2].PutValue("English");
cells[0, 3].PutValue("Indonesia");
cells[0, 4].PutValue("Chinese");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
int j = i + 2;
cells[j, 0].PutValue("");
cells[j, 1].PutValue(dr["code"]);
cells[j, 2].PutValue(dr["lgg_en"]);
cells[j, 3].PutValue(dr["lgg_id"]);
cells[j, 4].PutValue(dr["lgg_zh"]);
}
string str_date = DateTime.Now.ToString("yyyyMMddHHmmss");
string path0 = string.Format("~/files/language/language_{0}.xlsx", str_date);
string path = HttpContext.Current.Server.MapPath(path0);//POD_RECORD
//workbook.Save(path);
System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流
byte[] bt = ms.ToArray();
workbook.Save(path);
Response.Redirect(path0.Substring(1));
}
//Quang Huy 2014-02-19
/// <summary>
/// Tạo viền cho ô
/// </summary>
/// <param name="cell"></param>
/// <param name="position"></param>
/// <param name="color"></param>
private bool SetCellBorder(Aspose.Cells.Cell cell, BorderPosition position = BorderPosition.Around, System.Drawing.Color?color = null)
{
try
{
System.Drawing.Color c = color ?? System.Drawing.Color.Black;
Aspose.Cells.Style style = cell.GetStyle();
if (((int)position & (int)BorderPosition.Top) == (int)BorderPosition.Top)
{
style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.TopBorder].Color = c;
}
if (((int)position & (int)BorderPosition.Right) == (int)BorderPosition.Right)
{
style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.RightBorder].Color = c;
}
if (((int)position & (int)BorderPosition.Bottom) == (int)BorderPosition.Bottom)
{
style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = c;
}
if (((int)position & (int)BorderPosition.Left) == (int)BorderPosition.Left)
{
style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = c;
}
if (((int)position & (int)BorderPosition.DiagonalDown) == (int)BorderPosition.DiagonalDown)
{
style.Borders[Aspose.Cells.BorderType.DiagonalDown].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.DiagonalDown].Color = c;
}
if ((((int)position & (int)BorderPosition.DiagonalUp) == (int)BorderPosition.DiagonalUp) || (position == BorderPosition.Full))
{
style.Borders[Aspose.Cells.BorderType.DiagonalUp].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.DiagonalUp].Color = c;
}
//Apply the border styles to the cell
cell.SetStyle(style);
return(true);
}
catch { }
return(false);
}
/// <summary>
/// 将DatTable导出Excel，动态创建表头和列
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="SheetName">工作薄sheet名称</param>
/// <returns>Excel文件流</returns>
public byte[] ExportExcel(DataTable dt, string SheetName)
{
//创建Excel工作薄
Workbook workbook = new Workbook();
//创建Sheet
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = SheetName.Trim().Equals("") ? "Sheet1" : SheetName;
worksheet.IsSelected = true;
Cells cells = worksheet.Cells;
//将数据填充到Excel中[yyyy-MM-dd 为日期格式]
cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, false, "yyyy-MM-dd", false);
// 为表头单元格添加样式
Aspose.Cells.Style style = workbook.CreateStyle();
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //设置居中
style.Font.Size = 10; //文字大小
style.IsTextWrapped = true; //自动换行
style.Font.IsBold = true;
style.HorizontalAlignment = TextAlignmentType.Center; //文字居中
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[0, i].SetStyle(style);
worksheet.Cells.SetColumnWidth(i, 15);//列固定宽
//worksheet.AutoFitColumn(i);//列自动宽
}
worksheet.Cells.SetRowHeight(0, 20);
//冻结行和列
worksheet.FreezePanes(1, 0, 1, 0);
//设置当前显示的工作表
workbook.Worksheets.ActiveSheetIndex = 0;
//将填充的数据，转为流
MemoryStream ms = new MemoryStream();
workbook.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx));
byte[] bt = ms.ToArray();
ms.Close();
return(bt);
}
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");
}
private static int printOverviews(DataRow currentOverivews, DataRow oldOverviews, string[] Overviews, Worksheet ws, int rowCount, int mergeCellsCount)
{
int newRowsPrinted = 0;
int index = 0;
//styling and formatting
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.Pattern = BackgroundType.Solid;
cellStyle.Font.IsBold = true;
cellStyle.IsTextWrapped = true;
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
if (object.ReferenceEquals(Overviews, null))
{
return(0); //if there are no summary overviews, they should not be printed.
}
if (!object.ReferenceEquals(currentOverivews, null) && !object.ReferenceEquals(oldOverviews, null))
{
while (index < Overviews.Length) //check for any differences in the overviews. If there is a difference, print the current overviews
{
string columnName = Overviews[index];
if (currentOverivews.Field <string>(columnName) != oldOverviews.Field <string>(columnName))
{
break;
}
index++;
}
}
string overviewRowValue = "";
foreach (string columnName in Overviews)
{
overviewRowValue += " " + currentOverivews.Field <string>(columnName);
}
ws.Cells.Merge(rowCount + newRowsPrinted - 1, 0, 1, mergeCellsCount);
string cellName = 'A' + (rowCount + newRowsPrinted).ToString();
cellStyle.ForegroundColor = Color.Yellow;
ws.Cells[cellName].PutValue(overviewRowValue);
ws.Cells[cellName].SetStyle(cellStyle);
newRowsPrinted++;
return(newRowsPrinted);
}
private void CreateProductsByCategoryHeader(Workbook workbook, Aspose.Cells.Cells cells, ushort startRow, byte startColumn, string categoryName)
{
//Input values and apply the styles to the cells
Aspose.Cells.Style style = workbook.Styles["Category"];
cells[startRow, startColumn].PutValue("Category:");
cells[startRow, startColumn].SetStyle(style);
style = workbook.Styles["CategoryName"];
cells[startRow, (byte)(startColumn + 1)].PutValue(categoryName);
cells[startRow, (byte)(startColumn + 1)].SetStyle(style);
style = workbook.Styles["ProductName"];
cells[startRow + 1, startColumn].PutValue("Product Name");
cells[startRow + 1, startColumn].SetStyle(style);
style = workbook.Styles["UnitsInStock"];
cells[startRow + 1, (byte)(startColumn + 1)].PutValue("Units In Stock:");
cells[startRow + 1, (byte)(startColumn + 1)].SetStyle(style);
}
