public static void Run()
{
// ExStart:UsingGlobalizationSettings
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Loads an existing spreadsheet containing some data
Workbook book = new Workbook(dataDir + "sample.xlsx");
// Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step
book.Settings.GlobalizationSettings = new CustomSettings();
// Accesses the 1st worksheet from the collection which contains data resides in the cell range A2:B9
Worksheet sheet = book.Worksheets[0];
// Adds Subtotal of type Average to the worksheet
sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });
// Calculates Formulas
book.CalculateFormula();
// Auto fits all columns
sheet.AutoFitColumns();
// Saves the workbook on disc
book.Save(dataDir + "output_out.xlsx");
// ExEnd:UsingGlobalizationSettings
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
var export_html = @"<html>
<body>
<table>
<tr>
<td>
<div>This is some Text.</div>
<div>
<div>
<span>This is some more Text</span>
</div>
<div>
<span>[email protected]</span>
</div>
<div>
<span>1234567890</span>
</div>
<div>
<span>ABC DEF</span>
</div>
</div>
<div>Generated On May 30, 2016 02:33 PM <br />Time Call Received from Jan 01, 2016 to May 30, 2016</div>
</td>
<td>";
export_html = export_html + " <img src=\"" + sourceDir + "sampleDivTagsLayout_ASpose_logo_100x100.png\"" + @" />
</td>
</tr>
</table>
</body>
</html>";
MemoryStream ms = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(export_html));
// Specify HTML load options, support div tag layouts
Aspose.Cells.HTMLLoadOptions loadOptions = new HTMLLoadOptions(LoadFormat.Html);
loadOptions.SupportDivTag = true;
// Create workbook object from the html using load options
Workbook wb = new Workbook(ms, loadOptions);
// Auto fit rows and columns of first worksheet
Worksheet ws = wb.Worksheets[0];
ws.AutoFitRows();
ws.AutoFitColumns();
// Save the workbook in xlsx format
wb.Save(outputDir + "outputDivTagsLayout.xlsx", Aspose.Cells.SaveFormat.Xlsx);
Console.WriteLine("DivTagsLayout executed successfully.");
}
//public static DataTable GetExcelNewData(string FilePath)
//{
// Workbook workbook = new Workbook();
// workbook.Open(FilePath);
// Cells cells;
// cells = workbook.Worksheets[0].Cells;
// int Columns = cells.MaxDataColumn;
// int Rows = cells.MaxDataRow;
// DataTable dtnew = new DataTable();
// for (int i = 0; i < Columns + 1; i++)
// {
// dtnew.Columns.Add(new DataColumn("Columns" + i.ToString(), typeof(string)));
// }
// for (int k = 1; k < Rows + 1; k++)
// {
// DataRow dr = dtnew.NewRow();
// for (int j = 0; j < Columns + 1; j++)
// {
// string s = cells[k, j].StringValue.Trim();
// dr[j] = s;
// }
// dtnew.Rows.Add(dr);
// }
// return dtnew;
//}
public static void SaveDataTable(Dictionary <string, DataTable> dic, string filename)
{
Workbook wb = new Workbook();
foreach (var item in dic)
{
string WorksheetName = item.Key;
DataTable dt = item.Value;
Worksheet ws = wb.Worksheets.Add(WorksheetName);
Aspose.Cells.Cell cell = null;
int Rows = dt.Rows.Count;
int Columns = dt.Columns.Count;
for (int i = 0; i < Rows; i++)
{
for (int j = 0; j < Columns; j++)
{
string Value = dt.Rows[i][j].ToString();
cell = ws.Cells[i, j];
cell.PutValue(Value);
}
}
ws.AutoFitRows(0, Rows);
ws.AutoFitColumns(0, Columns);
}
wb.Save(filename);
}
public DataGridViewExport(DataGridView dgv)
{
_workbook = new Workbook();
_workbook.Worksheets.Clear();
_worksheet = _workbook.Worksheets[_workbook.Worksheets.Add()];
_worksheet.Name = "Sheet1";
_colIndexes = new List<int>();
int sheetRowIndex = 0;
int sheetColIndex = 0;
foreach (DataGridViewColumn col in dgv.Columns)
{
if (col.Visible == false) continue;
_colIndexes.Add(col.Index);
_worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue(col.HeaderText);
}
foreach (DataGridViewRow row in dgv.Rows)
{
sheetRowIndex++;
sheetColIndex = 0;
foreach (int colIndex in _colIndexes)
_worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue("" + row.Cells[colIndex].Value);
}
_worksheet.AutoFitColumns();
}
/// <summary>
/// 将DataTable输出为字节数组
/// </summary>
/// <param name="dt">表格数据</param>
/// <returns>Byte数组</returns>
public static byte[] DataTableToExcelBytes(DataTable dt)
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
int Colnum = dt.Columns.Count; //表格列数
int Rownum = dt.Rows.Count; //表格行数
//生成行 列名行
for (int i = 0; i < Colnum; i++)
{
cells[0, i].PutValue(dt.Columns[i].ColumnName);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[1 + i, k].PutValue(dt.Rows[i][k].ToString());
}
}
//自动行高，列宽
sheet.AutoFitColumns();
sheet.AutoFitRows();
//将DataTable写入内存流
var ms = new MemoryStream();
book.Save(ms, SaveFormat.Excel97To2003);
return(ms.ToArray());
}
public static void ClassCleanup()
{
ws.AutoFitColumns();
var fileName = @"C:\CellTests.xlsx";
wb.Save(fileName);
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Loads an existing spreadsheet containing some data
Workbook book = new Workbook(sourceDir + "sampleCustomLabelsSubtotals.xlsx");
// Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step
book.Settings.GlobalizationSettings = new CustomSettings();
// Accesses the 1st worksheet from the collection which contains data resides in the cell range A2:B9
Worksheet sheet = book.Worksheets[0];
// Adds Subtotal of type Average to the worksheet
sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });
// Calculates Formulas
book.CalculateFormula();
// Auto fits all columns
sheet.AutoFitColumns();
// Saves the workbook on disc
book.Save(outputDir + "outputCustomLabelsSubtotals.xlsx");
Console.WriteLine("CustomLabelsSubtotals executed successfully.");
}
public static Boolean DatatableToExcel(DataTable dt, string outFileName)
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Boolean yn = false;
try
{
//sheet.Name = sheetName;
//AddTitle(title, dt.Columns.Count);
//AddHeader(dt);
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
}
}
sheet.AutoFitColumns();
//sheet.AutoFitRows();
book.Save(outFileName);
yn = true;
return(yn);
}
catch (Exception)
{
return(yn);
// throw e;
}
}
/// <summary>
/// 转换成DataTable导出Excel
/// </summary>
private MemoryStream DataTableToExcel(DataTable table, out string mag)
{
mag = string.Empty;
try {
Workbook workbook = new Workbook(); //*工作簿*
Worksheet worksheet = workbook.Worksheets[0]; //*页*
Cells cells = worksheet.Cells; //*单元格*
int _colnums = table.Columns.Count; //*数据总列数*
int _rows = table.Rows.Count; //*数据总行数*
//**生成列头**
for (int i = 0; i < _colnums; i++)
{
cells[0, i].PutValue(table.Columns[i].ColumnName);
}
//**生成行数据**
for (int i = 0; i < _rows; i++)
{
for (int k = 0; k < _colnums; k++)
{
cells[1 + i, k].PutValue(table.Rows[i][k]);//*添加数据*
}
}
worksheet.AutoFitColumns();
return(workbook.SaveToStream());
}
catch (Exception ex) {
mag = ex.Message;
return(null);
}
}
private void exportExcel(DataTable dt)
{
DataTable copydt = dt.Copy();
formatParent(ref copydt);
String strName = "Master Grid - AOR Workload Type";
Workbook wb = new Workbook(FileFormatType.Xlsx);
MemoryStream ms = new MemoryStream();
Worksheet ws = wb.Worksheets[0];
int rowCount = 0;
printParentHeader(ws, ref rowCount, copydt.Columns);
foreach (DataRow parentRow in copydt.Rows)
{
if (parentRow.Field <int>("AORWorkTypeID") != 0)
{
printParent(parentRow, ws, ref rowCount, copydt);
rowCount++;
//printChildRows(parentRow, ws, ref rowCount);
//rowCount++;
}
}
ws.Cells.DeleteColumn(0);
ws.AutoFitColumns();
wb.Save(ms, SaveFormat.Xlsx);
Response.BufferOutput = true;
Response.ContentType = "application/xlsx";
Response.AddHeader("content-disposition", "attachment; filename=" + strName + ".xlsx");
Response.BinaryWrite(ms.ToArray());
Response.End();
}
private bool ExportExcel(DataTable dt)
{
bool success = false;
string errorMsg = string.Empty;
try
{
string name = "SR Import";
Workbook wb = new Workbook(FileFormatType.Xlsx);
Worksheet ws = wb.Worksheets[0];
ws.Cells.ImportDataTable(dt, true, 0, 0, false, false);
ws.AutoFitColumns();
MemoryStream ms = new MemoryStream();
wb.Save(ms, SaveFormat.Xlsx);
Response.ContentType = "application/xlsx";
Response.AddHeader("content-disposition", "attachment; filename=" + name + ".xlsx");
Response.BinaryWrite(ms.ToArray());
Response.End();
success = true;
}
catch (Exception ex)
{
success = false;
errorMsg += Environment.NewLine + ex.Message;
}
return(success);
}
public FileResult Download(string username)
{
List <Game> games = db.Games.Where(x => x.Player1 == username || x.Player2 == username).ToList();
Game game = games.OrderBy(x => x.Id).Last();
List <FifthGame> fifthGames = db.FifthGames.Where(x => x.Player1 == username || x.Player2 == username).ToList();
FifthGame fifthGame = fifthGames.OrderBy(x => x.Id).Last();
GamingUser user = db.Users.First(x => x.Username == username);
GamingUser firstPlayer = db.Users.First(x => x.Username == game.Player1);
GamingUser secondPlayer = db.Users.First(x => x.Username == game.Player2);
using (MemoryStream ms = new MemoryStream())
{
string pathFile = HttpContext.Server.MapPath(@"~/Content/ExcelTemplate.xlsx");
Workbook workbook = new Workbook(pathFile);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFitColumns();
worksheet.Name = "Statistika";
FillTableForGame(game, firstPlayer, secondPlayer, worksheet);
FillTableForFifthGame(fifthGame, user, worksheet);
FillSurvey(user, worksheet);
FillTasks(user, worksheet);
RemoveSuperfluousErrorWarnings(worksheet);
string dateTime = "" + DateTime.Now.Hour + ":" + DateTime.Now.Minute + "_" + DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year;
string fileName = "Statistika_" + dateTime + ".xlsx";
workbook.Save(ms, SaveFormat.Xlsx);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms.ToArray(), System.Net.Mime.MediaTypeNames.Application.Octet, fileName));
}
}
/// <summary>
/// 读取数据库数据导出excel文件
/// </summary>
/// <param name="tabel"></param>
public static void Export(DataTable tabel)
{
var data = tabel;
Workbook outExcel = new Workbook(FileFormatType.Xlsx);
Worksheet sheet = outExcel.Worksheets[0];
sheet.Name = "Sheet1";
Cells cells = sheet.Cells;
cells.ImportDataTable(data, true, 0, 0, data.Rows.Count, data.Columns.Count, true, "yyyy-MM-dd");
sheet.AutoFitColumns();
//for (int i = 1; i < cells.Rows.Count; i++)
//{
// var style = cells[i, 1].GetStyle();
// var send_id = Convert.ToInt64(cells[i, 0].Value).ToString();
// style.Number = 2;
// cells[i, 0].Value = (object)send_id;
//}
var folder = Path.Combine(AppContext.BaseDirectory, "temp");
if (!System.IO.Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
var path = Path.Combine(folder, "merchant.xlsx");
outExcel.Save(path, SaveFormat.Xlsx);
}
public DataGridViewExport(DataGridView dgv)
{
_workbook = new Workbook();
_workbook.Worksheets.Clear();
_worksheet = _workbook.Worksheets[_workbook.Worksheets.Add()];
_worksheet.Name = "Sheet1";
_colIndexes = new List <int>();
int sheetRowIndex = 0;
int sheetColIndex = 0;
foreach (DataGridViewColumn col in dgv.Columns)
{
if (col.Visible == false)
{
continue;
}
_colIndexes.Add(col.Index);
_worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue(col.HeaderText);
}
foreach (DataGridViewRow row in dgv.Rows)
{
sheetRowIndex++;
sheetColIndex = 0;
foreach (int colIndex in _colIndexes)
{
_worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue("" + row.Cells[colIndex].Value);
}
}
_worksheet.AutoFitColumns();
}
//导出------------下一篇会用到这个方法
public Boolean DatatableToExcel(DataTable dt)
{
Boolean yn = false;
try
{
//sheet.Name = sheetName;
//AddTitle(title, dt.Columns.Count);
//AddHeader(dt);
AddBody(dt);
sheet.AutoFitColumns();
//sheet.AutoFitRows();
book.Save(outFileName);
yn = true;
return(yn);
}
catch (Exception e)
{
return(yn);
// throw e;
}
}
public static void Test()
{
try
{
Workbook book = new Workbook();
Worksheet cellSheet = book.Worksheets[0];
Cells cells = cellSheet.Cells;
Console.WriteLine("开始查询");
DataTable dt = GetDataTable();
Console.WriteLine("开始生成Excel");
cells.ImportDataTable(dt, false, 0, 0);
cellSheet.AutoFitColumns();
Console.WriteLine("保存Excel");
book.Save(@"D:\Demo2016\Console\Console\Excel\test.xlsx");
Console.WriteLine("保存成功");
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.ReadLine();
}
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// ExStart:1
// Sample Html containing redundant spaces after <br> tag
string html = "<html> <body> <table> <tr> <td> <br> This is sample data <br> This is sample data<br> This is sample data</td> </tr> </table> </body> </html>";
// Convert Html to byte array
byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(html);
// Set Html load options and keep precision true
HtmlLoadOptions loadOptions = new Aspose.Cells.HtmlLoadOptions(LoadFormat.Html);
loadOptions.DeleteRedundantSpaces = true;
// Convert byte array into stream
MemoryStream stream = new MemoryStream(byteArray);
// Create workbook from stream with Html load options
Workbook workbook = new Workbook(stream, loadOptions);
// Access first worksheet
Worksheet sheet = workbook.Worksheets[0];
// Auto fit the sheet columns
sheet.AutoFitColumns();
// Save the workbook
workbook.Save(outputDir + "outputDeleteRedundantSpacesWhileImportingFromHtml.xlsx", SaveFormat.Xlsx);
// ExEnd:1
Console.WriteLine("DeleteRedundantSpacesWhileImportingFromHtml executed successfully.\r\n");
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// ExStart:1
// Sample Html containing large number with digits greater than 15
string html = "<html><body><p>1234567890123456</p></body></html>";
// Convert Html to byte array
byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(html);
// Set Html load options and keep precision true
HtmlLoadOptions loadOptions = new Aspose.Cells.HtmlLoadOptions(LoadFormat.Html);
loadOptions.KeepPrecision = true;
// Convert byte array into stream
MemoryStream stream = new MemoryStream(byteArray);
// Create workbook from stream with Html load options
Workbook workbook = new Workbook(stream, loadOptions);
// Access first worksheet
Worksheet sheet = workbook.Worksheets[0];
// Auto fit the sheet columns
sheet.AutoFitColumns();
// Save the workbook
workbook.Save(outputDir + "outputAvoidExponentialNotationWhileImportingFromHtml.xlsx", SaveFormat.Xlsx);
// ExEnd:1
Console.WriteLine("AvoidExponentialNotationWhileImportingFromHtml executed successfully.\r\n");
}
/// <summary>
/// DataTable导出为Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
/// <param name="title"></param>
/// <returns></returns>
public static bool DataTableToExcel(DataTable dt, string filePath, string title = "")
{
bool result = false;
try
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName);
}
for (int i = 1; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.Cells[i, j].PutValue(dt.Rows[i][j].ToString());
}
}
sheet.AutoFitColumns();
sheet.AutoFitRows();
book.Save(filePath);
result = true;
}
catch (Exception ex)
{
result = false;
}
return(result);
}
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);
}
//Create a new Workbook.
Workbook workbook = new Workbook();
//Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
//Get the worksheet cells collection.
Cells cells = sheet.Cells;
//Input a value.
cells["B3"].PutValue("Employee:");
//Set it bold.
cells["B3"].GetStyle().Font.IsBold = true;
//Input some values that denote the input range
//for the combo box.
cells["A2"].PutValue("Emp001");
cells["A3"].PutValue("Emp002");
cells["A4"].PutValue("Emp003");
cells["A5"].PutValue("Emp004");
cells["A6"].PutValue("Emp005");
cells["A7"].PutValue("Emp006");
//Add a new combo box.
Aspose.Cells.Drawing.ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 2, 0, 22, 100);
//Set the linked cell;
comboBox.LinkedCell = "A1";
//Set the input range.
comboBox.InputRange = "A2:A7";
//Set no. of list lines displayed in the combo
//box's list portion.
comboBox.DropDownLines = 5;
//Set the combo box with 3-D shading.
comboBox.Shadow = true;
//AutoFit Columns
sheet.AutoFitColumns();
//Saves the file.
workbook.Save(dataDir + "book1.xls");
}
private void BuildTable()
{
//Print Title
WriteCell(0, 0, 1, _jTable.ColumTemplate.Count, _arg.TableName, _titleStyle);
//Print Date
foreach (var tuple in _arg.SubTitle)
{
WriteCell(1, tuple.Item1, 1, 1, tuple.Item2, null);
}
for (int i = 0; i < _jTable.ColumTemplate.Count; i++)
{
var style = _cells[1, i].GetStyle();
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
_cells[1, i].SetStyle(style);
}
//Draw Table Headers
var rowPointer = 2;
var colPointer = 0;
if (_jTable.ExtraHeaders != null)
{
foreach (var jsonExtraColumn in _jTable.ExtraHeaders)
{
WriteCell(rowPointer, colPointer, 1, jsonExtraColumn.ColSpan, jsonExtraColumn.Name, _headerStyle);
colPointer += jsonExtraColumn.ColSpan;
}
rowPointer++;
}
colPointer = 0;
foreach (var column in _jTable.ColumTemplate)
{
WriteCell(rowPointer, colPointer++, 1, 1, column.Name, _headerStyle);
}
rowPointer++;
//Draw Table Body
foreach (var row in _jTable.RowData)
{
colPointer = 0;
foreach (var column in _jTable.ColumTemplate)
{
var valueText = row.ContainsKey(column.ColumnName) ? row[column.ColumnName].Replace(" ", " ") : "";
decimal v;
var cStyle = colPointer == 0 ? _firstColumnStyle : _textStyle;
if (decimal.TryParse(valueText, out v))
{
WriteCell(rowPointer, colPointer++, 1, 1, v, cStyle);
}
else
{
WriteCell(rowPointer, colPointer++, 1, 1, valueText, cStyle);
}
}
rowPointer++;
}
//Draw Source
WriteCell(rowPointer, 0, 1, 1, _arg.Source, _signitureStyle);
_worksheet.AutoFitColumns();
_worksheet.IsGridlinesVisible = false;
}
public Worksheet Render(IFrmSummaryReport model, Worksheet worksheet)
{
worksheet.Cells.StandardHeight = 15;
GenerateSummaryHeader(worksheet, model.HeaderData, 0, 0);
GenerateSummaryTable(worksheet, model, 8);
worksheet.AutoFitColumns(0, 1);
return(worksheet);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
var export_html = @"
<html>
<body>
<table>
<tr>
<td>
<div>This is some Text.</div>
<div>
<div>
<span>This is some more Text</span>
</div>
<div>
<span>[email protected]</span>
</div>
<div>
<span>1234567890</span>
</div>
<div>
<span>ABC DEF</span>
</div>
</div>
<div>Generated On May 30, 2016 02:33 PM <br />Time Call Received from Jan 01, 2016 to May 30, 2016</div>
</td>
<td>";
export_html = export_html + " <img src=" + dataDir + "ASpose_logo_100x100.png" + @" />
</td>
</tr>
</table>
</body>
</html>";
using (MemoryStream ms = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(export_html)))
{
// Specify HTML load options, support div tag layouts
Aspose.Cells.HTMLLoadOptions loadOptions = new HTMLLoadOptions(LoadFormat.Html);
loadOptions.SupportDivTag = true;
// Create workbook object from the html using load options
Workbook wb = new Workbook(ms, loadOptions);
// Auto fit rows and columns of first worksheet
Worksheet ws = wb.Worksheets[0];
ws.AutoFitRows();
ws.AutoFitColumns();
// Save the workbook in xlsx format
wb.Save(dataDir + "DivTagsLayout_out.xlsx", Aspose.Cells.SaveFormat.Xlsx);
}
// ExEnd:1
}
/// <summary>
/// 导出数据表到多个Sheet
/// </summary>
/// <param name="fileName">导出文件的名称</param>
/// <param name="dicDataTables">数据表是个字典，Key表示Sheet的名称，Value是具体的数据表</param>
public static void ExportFromDataTable(string fileName, Dictionary <string, DataTable> dicDataTables)
{
Workbook workbook = new Workbook();
workbook.Worksheets.Clear();
foreach (KeyValuePair <string, DataTable> keyValuePair in dicDataTables)
{
workbook.Worksheets.Add(keyValuePair.Key);
Worksheet workSheet = workbook.Worksheets[keyValuePair.Key];
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;
DataTable dt = keyValuePair.Value;
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell cell = row[j];
cell.Value = dt.Columns[j].ColumnName;
cell.SetStyle(titleStyle);
}
workSheet.Cells.ImportDataTable(dt, 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 k = 1; k <= cells.MaxDataRow; k++)
{
for (int m = 0; m <= cells.MaxDataColumn; m++)
{
Cell cell = cells.GetCell(k, m);
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();
}
/// <summary>
/// 数据导出到Excel（支持多表导出多个Sheet页）
/// </summary>
/// <param name="dataset">数据集</param>
/// <param name="filePath">保存文件夹路径</param>
/// <param name="fileName">保存文件名称</param>
/// <param name="saveFileType">保存文件类型（.xls;.xlsx）</param>
/// <returns>文件物理路径</returns>
public static string SaveDataSetToExcel(DataSet dataset, 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();
foreach (DataTable dt in dataset.Tables)
{
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);
}
private void btnExport_Click(object sender, EventArgs e)
{
if (dgv.Rows.Count <= 0)
{
return;
}
SaveFileDialog sd = new SaveFileDialog();
sd.FileName = "匯出訊息";
sd.Filter = "Excel檔案(*.xls)|*.xls";
if (sd.ShowDialog() != DialogResult.OK)
{
return;
}
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.Name = "錯誤訊息";
int colIndex = 0;
sheet.Cells[0, colIndex++].PutValue(chEntity.HeaderText);
sheet.Cells[0, colIndex++].PutValue("錯誤訊息");
int rowIndex = 1;
foreach (DataGridViewRow row in dgv.Rows)
{
if (row.IsNewRow)
{
continue;
}
sheet.Cells[rowIndex, 0].PutValue("" + row.Cells[chEntity.Index].Value);
sheet.Cells[rowIndex, 1].PutValue("" + row.Cells[chError.Index].Value);
rowIndex++;
}
sheet.AutoFitColumns();
try
{
book.Save(sd.FileName, FileFormatType.Excel2003);
if (MsgBox.Show("匯出完成，是否立刻開啟？", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(sd.FileName);
}
}
catch (Exception ex)
{
MsgBox.Show("匯出失敗。" + ex.Message);
}
}
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 void CreateXlsHeaderMedalCharecteristic(int row, Worksheet worksheet)
{
SetCellStyle(worksheet, row, true);
worksheet.Cells[row, 0].PutValue("Customer Characteristic");
worksheet.Cells[row, 1].PutValue("Weight Used");
worksheet.Cells[row, 2].PutValue("Points Obtained");
worksheet.AutoFitRows();
worksheet.AutoFitColumns();
}
/// <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);
}
}
/// <summary>
/// 匯出到 Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
if (listView.Items.Count <= 0)
{
return;
}
saveFileDialog1.FileName = string.Format("{0}學年度{1}學期課程平時評量成績輸入狀況", intSchoolYear.Value, (intSemester.Value == 1) ? "上" : "下");
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
Workbook book = new Workbook();
book.Worksheets.Clear();
Worksheet ws = book.Worksheets[book.Worksheets.Add()];
ws.Name = string.Format("{0}學年度 {1}學期", intSchoolYear.Value, (intSemester.Value == 1) ? "上" : "下");
#region 加入 Header
int row = 0;
ws.Cells[row, chCourseName.Index - 1].PutValue(chCourseName.Text);
ws.Cells[row, chTeacher.Index - 1].PutValue(chTeacher.Text);
ws.Cells[row, chScore.Index - 1].PutValue(chScore.Text);
ws.Cells[row, chEffort.Index - 1].PutValue(chEffort.Text);
ws.Cells[row, chText.Index - 1].PutValue(chText.Text);
#endregion
listView.SuspendLayout();
foreach (ListViewItem item in listView.Items)
{
row++;
ws.Cells[row, chCourseName.Index - 1].PutValue(item.SubItems[chCourseName.Index].Text);
ws.Cells[row, chTeacher.Index - 1].PutValue(item.SubItems[chTeacher.Index].Text);
ws.Cells[row, chScore.Index - 1].PutValue(item.SubItems[chScore.Index].Text);
ws.Cells[row, chEffort.Index - 1].PutValue(item.SubItems[chEffort.Index].Text);
if (Option.CheckText)
{
ws.Cells[row, chText.Index - 1].PutValue(item.SubItems[chText.Index].Text);
}
}
listView.ResumeLayout();
ws.AutoFitColumns();
try
{
book.Save(saveFileDialog1.FileName + ".xls", FileFormatType.Excel2003);
FISCA.Presentation.Controls.MsgBox.Show("匯出完成。");
}
catch (Exception ex)
{
FISCA.Presentation.Controls.MsgBox.Show("匯出失敗。" + ex.Message);
}
}
}
/// <summary>
/// 按下「匯出到 Excel」時觸發
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
if (listView.Items.Count <= 0)
{
return;
}
saveFileDialog1.FileName = string.Format("{0}學年度{1}學期{2}課程成績輸入狀況", intSchoolYear.Value, (intSemester.Value == 1) ? "上" : "下", ((KeyValuePair <string, string>)cboExam.SelectedItem).Value);
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
Workbook book = new Workbook();
book.Worksheets.Clear();
Worksheet ws = book.Worksheets[book.Worksheets.Add()];
ws.Name = string.Format("{0}學年度 {1}學期 {2}", intSchoolYear.Value, (intSemester.Value == 1) ? "上" : "下", ((KeyValuePair <string, string>)cboExam.SelectedItem).Value);
#region 加入 Header
int row = 0;
ws.Cells[row, chCourseName.Index].PutValue(chCourseName.Text);
ws.Cells[row, chTeacher.Index].PutValue(chTeacher.Text);
ws.Cells[row, chScore.Index].PutValue(chScore.Text);
ws.Cells[row, chEffort.Index].PutValue(chEffort.Text);
ws.Cells[row, chText.Index].PutValue(chText.Text);
#endregion
#region 加入每一筆課程輸入狀況
listView.SuspendLayout();
foreach (CourseListViewItem item in listView.Items)
{
row++;
ws.Cells[row, chCourseName.Index].PutValue(item.Text);
ws.Cells[row, chTeacher.Index].PutValue(item.SubItems[chTeacher.Index].Text);
ws.Cells[row, chScore.Index].PutValue(item.SubItems[chScore.Index].Text);
ws.Cells[row, chEffort.Index].PutValue(item.SubItems[chEffort.Index].Text);
ws.Cells[row, chText.Index].PutValue(item.SubItems[chText.Index].Text);
}
listView.ResumeLayout();
#endregion
ws.AutoFitColumns();
try
{
book.Save(saveFileDialog1.FileName, FileFormatType.Excel2003);
Framework.MsgBox.Show("匯出完成。");
}
catch (Exception ex)
{
Framework.MsgBox.Show("匯出失敗。" + ex.Message);
}
}
}
private void ImportTwoDimensionArray(Worksheet sheet)
{
//Get the cells collection in the worksheet
Cells cells = sheet.Cells;
//Put a string value into a cell
sheet.Cells["A1"].PutValue("Import a two-dimension object Array");
//Get Style Object
Aspose.Cells.Style style = cells["A1"].GetStyle();
//the font text is set to bold
style.Font.IsBold = true;
//Apply style to the cell
cells["A1"].SetStyle(style);
//Create a multi-dimensional array and fill some values
object[,] objs = new object[2, 3];
objs[0, 0] = "Product ID";
objs[0, 1] = 1;
objs[0, 2] = 2;
objs[1, 0] = "Product Name";
objs[1, 1] = "Aniseed Syrup";
objs[1, 2] = "Boston Crab Meat";
//Import the multi-dimensional array to the sheet cells
sheet.Cells.ImportTwoDimensionArray(objs, 1, 0);
//Autofit the sheet cells
sheet.AutoFitColumns();
}
private void ImportObjectArray(Worksheet sheet)
{
//Get the cells collection in the worksheet
Cells cells = sheet.Cells;
//Put a string value into a cell
sheet.Cells["A1"].PutValue("Import an object Array");
//Get Style Object
Aspose.Cells.Style style = cells["A1"].GetStyle();
//the font text is set to bold
style.Font.IsBold = true;
//Apply style to the cell
cells["A1"].SetStyle(style);
//Create an object array and fill it with some values
object[] obj = { "Tom", "John", "kelly", 1, 2, 2.8, 5.16, true, false };
//Import the object array to the sheet cells
sheet.Cells.ImportObjectArray(obj, 1, 0, false);
//Autofit all the columns in the sheet
sheet.AutoFitColumns();
}
private void ImportFromDataReader(Worksheet sheet)
{
//Get the cells collection in the worksheet
Cells cells = sheet.Cells;
//Put the string value into a cell
sheet.Cells["A1"].PutValue("Import from DataReader");
//Get Style Object
Aspose.Cells.Style style = cells["A1"].GetStyle();
//the font text is set to bold
style.Font.IsBold = true;
//Apply style to the cell
cells["A1"].SetStyle(style);
string path = Server.MapPath("~");
path = path.Substring(0, path.LastIndexOf("\\")) + "\\Database\\Northwind.mdb";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path;
string sql = "SELECT Country,EmployeeID,FirstName,LastName FROM Employees ORDER BY Country,EmployeeID";
//Define connection scope
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
//Create command object
OleDbCommand command = new OleDbCommand(sql, conn);
//Open connection
conn.Open();
//Create and fill data reader object
OleDbDataReader reader;
reader = command.ExecuteReader();
//Import the datareader object to the sheet cells
sheet.Cells.ImportDataReader(reader, true, 1, 0, false);
//sheet.Cells.ImportFromDataReader(reader, true, 1, 0, false);
// Always call Close when done reading.
reader.Close();
}
//Autofit all the columns in the sheet
sheet.AutoFitColumns();
}
private void ImportDataView(Worksheet sheet)
{
//Get the cells collection in the worksheet
Cells cells = sheet.Cells;
//Put a string value into a cell
sheet.Cells["A1"].PutValue("Import a DataView");
//Get Style Object
Aspose.Cells.Style style = cells["A1"].GetStyle();
//the font text is set to bold
style.Font.IsBold = true;
//Apply style to the cell
cells["A1"].SetStyle(style);
//Create a datatable and add three columns to it
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));
//Add the first record to it
DataRow dr = dataTable.NewRow();
dr[0] = 1;
dr[1] = "Aniseed Syrup";
dr[2] = 15;
dataTable.Rows.Add(dr);
//Add the second record to it
dr = dataTable.NewRow();
dr[0] = 2;
dr[1] = "Boston Crab Meat";
dr[2] = 123;
dataTable.Rows.Add(dr);
//Import the dataview to the sheet cells
sheet.Cells.ImportDataView(dataTable.DefaultView, true, 1, 0, false);
//Autofit all the columns in the sheet
sheet.AutoFitColumns();
}
