public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create a Workbook.
// Open the excel file.
Workbook wbk = new Aspose.Cells.Workbook(dataDir + "mergingcells.xls");
// Create a Worksheet and get the first sheet.
Worksheet worksheet = wbk.Worksheets[0];
// Create a Cells object ot fetch all the cells.
Cells cells = worksheet.Cells;
// Unmerge the cells.
cells.UnMerge(5, 2, 2, 3);
// Save the file.
wbk.Save(dataDir + "unmergingcells.out.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Define memory stream object
System.IO.MemoryStream objImage;
// Define web client object
System.Net.WebClient objwebClient;
// Define a string which will hold the web image url
string sURL = "http:// Www.aspose.com/Images/aspose-logo.jpg";
try
{
// Instantiate the web client object
objwebClient = new System.Net.WebClient();
// Now, extract data into memory stream downloading the image data into the array of bytes
objImage = new System.IO.MemoryStream(objwebClient.DownloadData(sURL));
// Create a new workbook
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
// Get the first worksheet in the book
Aspose.Cells.Worksheet sheet = wb.Worksheets[0];
// Get the first worksheet pictures collection
Aspose.Cells.Drawing.PictureCollection pictures = sheet.Pictures;
// Insert the picture from the stream to B2 cell
pictures.Add(1, 1, objImage);
// Save the excel file
wb.Save(dataDir+ "webimagebook.out.xlsx");
}
catch (Exception ex)
{
// Write the error message on the console
Console.WriteLine(ex.Message);
}
// ExEnd:1
}
static void WriteExcelFile (Dictionary<string, List<List<string>>> data, string fileName)
{
Console.WriteLine ("Escribiendo: " + fileName);
using (Workbook book = new Aspose.Cells.Workbook ()) {
foreach (var key in data.Keys) {
var sheet = book.Worksheets.Add (key);
var rows = data [key];
for (int i = 0; i < rows.Count; i++) {
for (int j = 0; j < rows [i].Count; j++) {
sheet.Cells.Rows [i] [j].Value = rows [i] [j];
if (i == 0) {
var style = sheet.Cells.Rows [i] [j].GetStyle ();
style.Font.IsBold = true;
sheet.Cells.Rows [i] [j].SetStyle (style);
}
}
}
}
book.Save (fileName);
}
}
private void process()
{
excel.Workbook workbook = new excel.Workbook(this.ucFilesAndButtons1.TxbTemplateFileName.Text);
excel.Worksheet sheet = workbook.Worksheets[0];
int count = sheet.TextBoxes.Count();
for (int i = 0; i < count; i++)
{
excel.Drawing.TextBox t = sheet.TextBoxes[i];
if (t.Text == "二维码")
{
t.HasLine = false;
t.Text = "";
int x = sheet.Pictures.Add(5, 5, this.ucFilesAndButtons1.TxbImageFilePath.Text);
excel.Drawing.Picture picture = sheet.Pictures[x];
picture.LeftToCorner = t.LeftToCorner;
picture.TopToCorner = t.TopToCorner;
picture.Width = t.Width;
picture.Height = t.Height;
break;
}
}
//excel.Drawing.TextBox txb = sheet.TextBoxes[0];
//txb.HasLine = false;
//txb.Text = "123";
//int x = sheet.Pictures.Add(5, 5, this.ucFilesAndButtons1.TxbImageFilePath.Text);
//excel.Drawing.Picture picture = sheet.Pictures[x];
//picture.LeftToCorner = txb.LeftToCorner;
//picture.TopToCorner = txb.TopToCorner;
//picture.Width = txb.Width;
//picture.Height = txb.Height;
workbook.Save(this.ucFilesAndButtons1.TxbExportFileName.Text);
workbook.Save(this.ucFilesAndButtons1.TxbPdf.Text);
MessageBox.Show("ok");
}
/// <summary>
/// 添加演示版水印到excel文件
/// </summary>
/// <param name="sourceExcelFile"></param>
public static void AddWaterMark(string sourceExcelFile)
{
//string content = Cal2.Wpf.ModuleBase.ACalProductInfo.Company == ModuleBase.CompanyName.Additel ? "Demo" : "演示版";
string content = "销售演示版";
try
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(sourceExcelFile);
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
int firstRowIndex = 0;
int rowMaxIndex = workbook.Worksheets[i].Cells.MaxDataRow;
int row = firstRowIndex + 1;
while (row < rowMaxIndex + firstRowIndex - 10)
{
AddWaterMarkInSheet(workbook.Worksheets[i], MsoPresetTextEffect.TextEffect2,
content, "", 50, false, true
, row, 0, 1, 0, 100, 500);
row += 30;
}
}
workbook.Save(sourceExcelFile);
}
catch (Exception ex)
{
}
}
public static void Run()
{
string dataDir = RunExamples.GetDataDir_Charts();
//ExStart:SetChartDataFromWorkBook
Presentation pres = new Presentation(dataDir + "Test.pptx");
IChart chart = pres.Slides[0].Shapes.AddChart(ChartType.Pie, 50, 50, 500, 400);
chart.ChartData.ChartDataWorkbook.Clear(0);
Workbook workbook = null;
try
{
workbook = new Aspose.Cells.Workbook("a1.xlsx");
}
catch (Exception ex)
{
Console.Write(ex);
}
MemoryStream mem = new MemoryStream();
workbook.Save(mem, Aspose.Cells.SaveFormat.Xlsx);
chart.ChartData.WriteWorkbookStream(mem);
chart.ChartData.SetRange("Sheet1!$A$1:$B$9");
IChartSeries series = chart.ChartData.Series[0];
series.ParentSeriesGroup.IsColorVaried = true;
pres.Save(dataDir + "response2.pptx", Aspose.Slides.Export.SaveFormat.Pptx);
//ExEnd:SetChartDataFromWorkBook
}
private void write(List <List <CellEntity> > content)
{
try
{
String result = txtSavePath.Text.Trim();
Aspose.Cells.Workbook excel = new Aspose.Cells.Workbook();
Worksheet sheet = excel.Worksheets[0];
for (int rowIndex = 0; rowIndex < content.Count; rowIndex++)
{
List <CellEntity> row = content[rowIndex];
for (int colIndex = 0; colIndex < row.Count; colIndex++)
{
//Cell cell = sheet.Cells[currentRowIndex, colIndex];
// sheet.Cells.
CellEntity cellEntity = row[colIndex];
//sheet.Cells[currentRowIndex, colIndex].SetStyle(cellEntity.Style);
sheet.Cells[rowIndex, colIndex].Copy(cellEntity.Cell);
sheet.Cells[rowIndex, colIndex].PutValue(cellEntity.Cell.Value);
//sheet.Cells[currentRowIndex].Formula
//sheet.Cells[currentRowIndex, colIndex] = row[colIndex];
//sheet.Cells[currentRowIndex, colIndex].PutValue(row[colIndex]);
}
}
sheet.AutoFitColumns();
excel.Save(result, FileFormatType.Excel2007Xlsx);
MessageBox.Show("success");
}
catch (Exception)
{
throw;
}
}
public Form1()
{
InitializeComponent();
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
//合併欄位
wb.Worksheets[0].Cells.Merge(0, 0, 1, 30);
SaveFileDialog sd = new System.Windows.Forms.SaveFileDialog();
sd.Title = "另存新檔";
sd.FileName = "日常生活表現總表.xls";
sd.Filter = "Excel檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*";
if (sd.ShowDialog() == DialogResult.OK)
{
try
{
wb.Save(sd.FileName, FileFormatType.Excel2003);
System.Diagnostics.Process.Start(sd.FileName);
}
catch
{
MessageBox.Show("指定路徑無法存取。", "建立檔案失敗", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
}
static void WriteExcelFile(Dictionary <string, List <List <string> > > data, string fileName)
{
Console.WriteLine("Escribiendo: " + fileName);
using (Workbook book = new Aspose.Cells.Workbook()) {
foreach (var key in data.Keys)
{
var sheet = book.Worksheets.Add(key);
var rows = data [key];
for (int i = 0; i < rows.Count; i++)
{
for (int j = 0; j < rows [i].Count; j++)
{
sheet.Cells.Rows [i] [j].Value = rows [i] [j];
if (i == 0)
{
var style = sheet.Cells.Rows [i] [j].GetStyle();
style.Font.IsBold = true;
sheet.Cells.Rows [i] [j].SetStyle(style);
}
}
}
}
book.Save(fileName);
}
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
try
{
//Get the template excel file path.
string designerFile = dataDir + "SampleInput.xlsx";
//Specify the pdf file path.
string pdfFile = dataDir + "Output.pdf";
//Create a new Workbook.
//Open the template excel file which you have to
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(designerFile);
//Save the pdf file.
wb.Save(pdfFile, SaveFormat.Pdf);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
public DeleteExcelColumnResponse Process(string keyWordName, int rowIndex)
{
var result = new DeleteExcelColumnResponse();
if (HttpContext.Current.Request.Files.Count == 0)
{
return(result);
}
var file = HttpContext.Current.Request.Files[0];
var excel = new Aspose.Cells.Workbook(file.InputStream);
var sheet = excel.Worksheets[0];
for (int i = 0; i < sheet.Cells.MaxDataColumn + 100; i++)
{
if (ExcelHelper.GetCellValue(sheet, rowIndex, i) == keyWordName)
{
sheet.Cells.DeleteColumn(i, false);
i--;
}
}
var directory = HttpContext.Current.Request.PhysicalApplicationPath;
var relativePath = string.Concat("\\Files\\FinancialExcel\\", Guid.NewGuid().ToString("n"), ".xlsx");
var filePath = string.Concat(directory, relativePath);
var directoryPath = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
}
excel.Save(filePath, SaveFormat.Xlsx);
result.success = true;
result.FilePath = relativePath;
return(result);
}
private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.FileName = "常模轉換訊息";
saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
DataTable dt = new DataTable();
dt.Columns.Add("班級");
dt.Columns.Add("座號");
dt.Columns.Add("姓名");
dt.Columns.Add("訊息");
foreach (FitInfo each in _list)
{
DataRow row = dt.NewRow();
row["班級"] = each._class;
row["座號"] = each._seatno;
row["姓名"] = each._name;
row["訊息"] = each._info;
dt.Rows.Add(row);
}
wb.Worksheets[0].Cells.ImportDataTable(dt, true, "A1");
wb.Save(saveFileDialog1.FileName, FileFormatType.Xlsx);
System.Diagnostics.Process.Start(saveFileDialog1.FileName);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
try
{
//Get the template excel file path.
string designerFile = dataDir + "SampleInput.xlsx";
//Specify the pdf file path.
string pdfFile = dataDir + "Output.out.pdf";
//Create a new Workbook.
//Open the template excel file which you have to
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(designerFile);
//Save the pdf file.
wb.Save(pdfFile, SaveFormat.Pdf);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
/// <summary>
/// 转pdf
/// </summary>
/// <param name="inputPath">word路径</param>
/// <param name="pdfPath">pdf路径</param>
/// <returns></returns>
public static bool ConverToPdf(string inputPath, string pdfPath)
{
try
{
FileInfo f1 = new FileInfo(inputPath);
string ext1 = f1.Extension.ToLower().Substring(1);
switch (ext1)
{
case "xls":
case "xlsx":
Aspose.Cells.Workbook book1 = new Aspose.Cells.Workbook(inputPath);
book1.Save(pdfPath, Aspose.Cells.SaveFormat.Pdf);
break;
case "doc":
case "docx":
Aspose.Words.Document doc = new Aspose.Words.Document(inputPath);
doc.Save(pdfPath);
break;
default:
break;
}
}
catch (Exception ex1)
{
return(false);
}
return(true);
}
/// <summary>
/// 保存到文件
/// </summary>
/// <param name="stream">流</param>
public void Save(Stream stream)
{
var xls = new Aspose.Cells.Workbook();
var sheet = xls.Worksheets[0];
var style = this.GetHeaderStyle();
for (var i = 0; i < this.fields.Count; i++)
{
var header = sheet.Cells[0, i];
header.SetStyle(style, true);
header.PutValue(this.fields[i].Name);
}
var row = 0;
foreach (var model in this.Models)
{
row = row + 1;
for (var i = 0; i < this.fields.Count; i++)
{
var fieldValue = this.fields[i].GetValue(model);
sheet.Cells[row, i].PutValue(fieldValue);
}
}
sheet.AutoFitColumns();
xls.Save(stream, Aspose.Cells.SaveFormat.Excel97To2003);
}
/// <summary>
///DataSet导出到Excel文件
/// </summary>
/// <param name="ds"></param>
/// <param name="fileName"></param>
public static void ExportToExcel(DataSet ds, string fileName)
{
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook();
book.Worksheets.Clear();
int tbCount = ds.Tables.Count;
if (tbCount > 0)
{
for (int i = 0; i < tbCount; i++)
{
string sheetName = ds.Tables[i].TableName;
book.Worksheets.Add(sheetName);
Aspose.Cells.Worksheet sheet;
sheet = book.Worksheets[i];
sheet.Name = sheetName;
sheet.IsGridlinesVisible = true;
//AddBody(ds.Tables[i]);
for (int r = 0; r < ds.Tables[i].Rows.Count; r++)
{
for (int c = 0; c < ds.Tables[i].Columns.Count; c++)
{
sheet.Cells[0, c].PutValue(ds.Tables[i].Columns[c].ColumnName);//列标题
sheet.Cells[r + 1, c].PutValue(ds.Tables[i].Rows[r][c].ToString());
}
}
sheet.AutoFitColumns();
sheet.AutoFitRows();
}
}
book.Save(fileName, Aspose.Cells.FileFormatType.Excel2007Xlsx);
}
private void btnOptDesign_Click(object sender, EventArgs e)
{
try
{
for (int i = 0; i < Config.designer_opt_configs.Count; i++)
{
Output_designer_config optRequest = Config.designer_opt_configs[i];
Excel.Workbook src_book = new Excel.Workbook(optRequest.src_path);
Excel.Worksheet src_sheet = src_book.Worksheets[optRequest.src_sheet];
SheetHeader src_sheet_header = new SheetHeader();
src_sheet_header.readHeader(src_sheet, optRequest.src_head_row);
src_sheet_header.readDataWithIndex(optRequest.src_pm_key, optRequest.src_row_beg);
Excel.Workbook tar_book = new Excel.Workbook(optRequest.tar_path);
Excel.Worksheet tar_sheet = tar_book.Worksheets[optRequest.tar_sheet];
SheetHeader tar_sheet_header = new SheetHeader();
tar_sheet_header.readHeader(tar_sheet, optRequest.tar_head_row);
tar_sheet_header.readDataWithIndex(optRequest.tar_pm_key, optRequest.tar_row_beg);
ExcelTableConvert.convert(src_sheet_header, tar_sheet_header, optRequest.src_cols, optRequest.tar_cols);
tar_book.Save(optRequest.tar_path);
}
}
catch (Exception ex) { Debug.Error(ex.ToString()); }
Debug.Info("导出完成");
}
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);
try
{
//Get the template excel file path.
string designerFile = dataDir + "SampleInput.xlsx";
//Specify the pdf file path.
string pdfFile = dataDir + "Output.out.pdf";
//Create a new Workbook.
//Open the template excel file which you have to
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(designerFile);
//Save the pdf file.
wb.Save(pdfFile, SaveFormat.Pdf);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}
//ExEnd:1
}
/// <summary>
/// 自己定义的
/// </summary>
public static bool Ecu_Id_DataTableToExcel(DataTable datatable, string filepath, out string error)
{
//string file = Application.StartupPath + "\\13id\\" +"id.xls";
//filepath = file;
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
cells.SetColumnWidth(4, 40);
cells.SetColumnWidth(3, 40);
Aspose.Cells.Style style = new Style();
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;
StyleFlag a = new StyleFlag();
a.WrapText = true;
style.IsTextWrapped = true; //文本换行 这里很重要的；有助于格式的的显示内容
cells.ApplyStyle(style, a);
cells.ImportDataTable(datatable, true, "A5");
workbook.Save(filepath);
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
return(true);
}
public void OutputMemoExcel(string v_excelPath)
{
Excel.Workbook book = new Excel.Workbook(v_excelPath);
Excel.Worksheet sheet = book.Worksheets["CString"];
ExcelSheetObject eso = new ExcelSheetObject(sheet, "CString");
eso.init_header();
int row = 3;
foreach (string words in _words)
{
eso.set_vali("Id", row, row - 2);
eso.set_vals("Key", row, words);
if (_data.ContainsKey(words))
{
Dictionary <string, string> language = _data[words];
foreach (string lanHeader in language.Keys)
{
if (lanHeader != "Id")
{
eso.set_vals(lanHeader, row, language[lanHeader]);
}
}
}
row = row + 1;
}
book.Save(v_excelPath);
}
/// <summary>
/// 生成Excel文件流
/// </summary>
/// <param name="titles"></param>
/// <param name="fields"></param>
/// <param name="list"></param>
/// <param name="saveFormat"></param>
/// <param name="isPropertyNameShown"></param>
/// <param name="firstRow"></param>
/// <param name="firstColumn"></param>
/// <param name="insertRows"></param>
/// <param name="dateFormatString"></param>
/// <param name="convertStringToNumber"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static byte[] GenerateExcelFileStream(string[] titles, string[] fields, ICollection list
, SaveFormat saveFormat, bool isPropertyNameShown, int firstRow, int firstColumn
, bool insertRows, string dateFormatString, bool convertStringToNumber, string sheetName = "Sheet1")
{
AsposeXls.Workbook workbook = new AsposeXls.Workbook();
AsposeXls.Worksheet sheet = workbook.Worksheets[0];
sheet.Name = sheetName;
sheet.FreezePanes(1, 1, 1, 0); //冻结第一行
for (int i = 0; i < titles.Length; i++)
{
sheet.Cells[0, i].PutValue(titles[i]);
}
sheet.Cells.ImportCustomObjects(list, fields, isPropertyNameShown, firstRow, firstColumn,
list.Count, insertRows, dateFormatString, convertStringToNumber);
sheet.AutoFitColumns();//让各列自适应宽度，这个很有用
using (MemoryStream stream = new MemoryStream())
{
workbook.Save(stream, saveFormat);
return(stream.ToArray());
}
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
try
{
// Get the template excel file path.
string designerFile = dataDir + "SampleInput.xlsx";
// Specify the pdf file path.
string pdfFile = dataDir + "Output.out.pdf";
// Open the template excel file
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(designerFile);
// Save the pdf file.
wb.Save(pdfFile, SaveFormat.Pdf);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}
// ExEnd:1
}
/// <summary>
///DataTable导出到Excel文件
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
public static void ExportToExcel(DataTable dt, string fileName, bool isTextWrapped = false)
{
Aspose.Cells.Worksheet sheet;
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook();
sheet = book.Worksheets[0];
sheet.IsGridlinesVisible = true;
//AddBody(dt);
if (dt.Rows.Count == 0)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
sheet.Cells[0, c].PutValue(dt.Columns[c].ColumnName);//列标题
}
}
else
{
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
sheet.Cells[0, c].PutValue(dt.Columns[c].ColumnName);//列标题
sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
if (isTextWrapped && sheet.Cells[r + 1, c].Style != null)
{
sheet.Cells[r + 1, c].Style.IsTextWrapped = true;
}
}
}
}
sheet.AutoFitColumns();
sheet.AutoFitRows();
book.Save(fileName, Aspose.Cells.FileFormatType.Excel2007Xlsx);
}
/// <summary>
/// 把DataTabel转换成Excel文件
/// </summary>
/// <param name="datatable">DataTable对象</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//int i = cells.GetColumnWidth(1);
cells.SetColumnWidth(0, 5);
cells.SetColumnWidth(1, 16);
cells.SetColumnWidth(2, 10);
cells.SetColumnWidth(3, 15);
cells.SetColumnWidth(4, 10);
cells.SetColumnWidth(5, 10);
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入图片数据-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
}
workbook.Save(filepath);
return(true);
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
}
protected Stream GetPreviewImagesExcelStream(Content content, IEnumerable <SNCR.Image> previewImages, RestrictionType?restrictionType = null)
{
CheckLicense(LicenseProvider.Cells);
try
{
var ms = new MemoryStream();
var oldExcel = content.Name.ToLower().EndsWith(".xls");
var fileFormat = oldExcel ? AsposeCells.FileFormatType.Excel97To2003 : AsposeCells.FileFormatType.Xlsx;
var saveFormat = oldExcel ? AsposeCells.SaveFormat.Excel97To2003 : AsposeCells.SaveFormat.Xlsx;
var document = new AsposeCells.Workbook(fileFormat);
var index = 1;
var imageOptions = new PreviewImageOptions()
{
RestrictionType = restrictionType
};
foreach (var previewImage in previewImages.Where(previewImage => previewImage != null))
{
using (var imgStream = GetRestrictedImage(previewImage, imageOptions))
{
var image = System.Drawing.Image.FromStream(imgStream);
var imageForDocument = ResizeImage(image, Math.Min(image.Width, PREVIEW_EXCEL_WIDTH), Math.Min(image.Height, PREVIEW_EXCEL_HEIGHT));
if (imageForDocument != null)
{
using (var imageStream = new MemoryStream())
{
imageForDocument.Save(imageStream, Common.PREVIEWIMAGEFORMAT);
try
{
var ws = index == 1 ? document.Worksheets[0] : document.Worksheets.Add("Sheet" + index);
ws.Pictures.Add(0, 0, imageStream);
}
catch (IndexOutOfRangeException ex)
{
SnLog.WriteException(ex, "Error during document generation. Path: " + previewImage.Path);
break;
}
}
}
}
index++;
}
document.Save(ms, saveFormat);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
catch (Exception ex)
{
SnLog.WriteException(ex);
}
return(null);
}
///<Summary>
/// ConvertXlsToHtml method to convert XLS file to HTML
///</Summary>
public Response ConvertXlsToHtml(string fileName, string folderName, string userEmail)
{
return(ProcessTask(fileName, folderName, ".html", true, false, delegate(string inFilePath, string outPath, string zipOutFolder)
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(inFilePath);
workbook.Save(outPath, Aspose.Cells.SaveFormat.Html);
}));
}
private void Button_Click(object sender, RoutedEventArgs e)
{
// load file to be converted
var workbook = new Aspose.Cells.Workbook("D:\\C# Practice\\exltojson\\myExcel.xlsx");
// save in different formats
workbook.Save("output.pdf", Aspose.Cells.SaveFormat.Pdf);
}
public static void EncryptExcelFile(string sourceExcelPath, string destinateExcelPath, string password)
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(sourceExcelPath);
workbook.SetEncryptionOptions(Aspose.Cells.EncryptionType.XOR, 40);
workbook.SetEncryptionOptions(Aspose.Cells.EncryptionType.StrongCryptographicProvider, 128);
workbook.Settings.Password = password;
workbook.Save(destinateExcelPath);
}
///<Summary>
/// ConvertXlsToOds method to convert XLS file to ods
///</Summary>
public Response ConvertXlsToOds(string fileName, string folderName)
{
return(ProcessTask(fileName, folderName, ".ods", false, false, delegate(string inFilePath, string outPath, string zipOutFolder)
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(inFilePath);
workbook.Save(outPath, Aspose.Cells.SaveFormat.ODS);
}));
}
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);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(Server.MapPath("./") + "Template\\mb.xls");
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
cells[4, 1].PutValue("50H工具箱");
XlsSaveOptions saveOptions = new XlsSaveOptions();
workbook.Save(Response, "result.xls", ContentDisposition.Inline, saveOptions);
}
public void CreateStaticReport()
{
//Define memory stream object
System.IO.MemoryStream objImage;
//Define web client object
System.Net.WebClient objwebClient;
//Define a string which will hold the web image url
string sURL = "http://www.xlsoft.com/jp/products/aspose/images/Aspose_Cells-Product-Box.jpg";
//Instantiate the web client object
objwebClient = new System.Net.WebClient();
//Now, extract data into memory stream downloading the image data into the array of bytes
objImage = new System.IO.MemoryStream(objwebClient.DownloadData(sURL));
//Create a new workbook
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
//Get the first worksheet in the book
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
//Get the first worksheet pictures collection
Aspose.Cells.Drawing.PictureCollection pictures = sheet.Pictures;
//Insert the picture from the stream to B2 cell
pictures.Add(1, 1, objImage);
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "AddingImageFromWeb.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "AddingImageFromWeb.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
/// <summary>
/// 把DataTabel转换成Excel文件
/// </summary>
/// <param name="datatable">DataTable对象</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入图片数据-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (Exception ex)
{
error = error + " DataTableToExcel: " + ex.Message;
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
}
}
workbook.Save(filepath);
return(true);
}
catch (Exception ex)
{
error = error + " DataTableToExcel: " + ex.Message;
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
return(false);
}
}
public static void AutoFitExcel(string sourceExcelPath, string destinateExcelPath)
{
FileStream fstream = new FileStream(sourceExcelPath, FileMode.Open);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fstream);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFitRows();
worksheet.AutoFitColumns();
workbook.Save(destinateExcelPath);
fstream.Close();
}
/// <summary>
/// Will get the bytes represented by an aspose excel workbook.
/// </summary>
public static byte[] GetBytes(this E.Workbook book)
{
using (var stream = new MemoryStream())
using (var reader = new BinaryReader(stream))
{
book.Save(stream, E.SaveFormat.Xlsx);
stream.Position = 0;
return(reader.ReadBytes((int)stream.Length));
}
}
//Word報表
public static void WordReport_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
object[] result = (object[])e.Result;
string reportName = (string)result[0];
string path = (string)result[1];
Aspose.Words.Document doc = (Aspose.Words.Document)result[2];
string path2 = (string)result[3];
bool PrintStudetnList = (bool)result[4];
Aspose.Cells.Workbook wb = (Aspose.Cells.Workbook)result[5];
string Message = "" + result[6];
if (File.Exists(path2))
{
int i = 1;
while (true)
{
string newPath = Path.GetDirectoryName(path2) + "\\" + Path.GetFileNameWithoutExtension(path2) + (i++) + Path.GetExtension(path2);
if (!File.Exists(newPath))
{
path2 = newPath;
break;
}
}
}
if (PrintStudetnList)
{
MemoryStream memoryStream = new MemoryStream();
doc.Save(memoryStream, Aspose.Words.SaveFormat.Doc);
ePaperCloud ePaperCloud = new ePaperCloud();
ePaperCloud.upload_ePaper(Convert.ToInt32(School.DefaultSchoolYear), Convert.ToInt32(School.DefaultSemester)
, reportName, "", memoryStream, ePaperCloud.ViewerType.Student, ePaperCloud.FormatType.Docx, Message);
wb.Save(path2);
FISCA.Presentation.MotherForm.SetStatusBarMessage(reportName + "產生完成");
System.Diagnostics.Process.Start(path2);
}
else
{
int schoolYear = Convert.ToInt32(School.DefaultSchoolYear);
int semester = Convert.ToInt32(School.DefaultSemester);
MemoryStream memoryStream = new MemoryStream();
doc.Save(memoryStream, Aspose.Words.SaveFormat.Doc);
ePaperCloud ePaperCloud = new ePaperCloud();
ePaperCloud.upload_ePaper(schoolYear, semester, reportName, "", memoryStream, ePaperCloud.ViewerType.Student, ePaperCloud.FormatType.Docx, Message);
FISCA.Presentation.MotherForm.SetStatusBarMessage(reportName + "產生完成");
}
}
/// <summary>
/// [Aspose] Save to stream
/// </summary>
/// <param name="workBook"></param>
/// <returns></returns>
public Stream SaveToStream(AsposeCells.Workbook workBook)
{
Stream stream = new MemoryStream();
//Save theo định dạng của excel (office97-2003/2007/2010)
AsposeCells.SaveFormat saveFormat = AsposeCells.SaveFormat.Xlsx;
// Save
saveFormat = AsposeCells.SaveFormat.Xlsx;
//Save the Excel file.
workBook.Save(stream, saveFormat);
stream.Seek(0, SeekOrigin.Begin);
return(stream);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Create a Workbook.
//Open the excel file.
Workbook wbk = new Aspose.Cells.Workbook(dataDir + "mergingcells.xls");
//Create a Worksheet and get the first sheet.
Worksheet worksheet = wbk.Worksheets[0];
//Create a Cells object ot fetch all the cells.
Cells cells = worksheet.Cells;
//Unmerge the cells.
cells.UnMerge(5, 2, 2, 3);
//Save the file.
wbk.Save(dataDir + "unmergingcells.xls");
}
public static void XuatDuLieuRaExcel(int iRowPara,
int iColumnPara,
string strSubHeaderPara,
System.Data.DataTable tblBangDuLieuPara,
string strTemplateNamePara)
{
//Đường dẫn file template
string strSourceFilePri = string.Format("{0}{1}{2}",
System.Windows.Forms.Application.StartupPath,
PATH_TEMPLATES, strTemplateNamePara);
SaveFileDialog saveFileDialogPri = new SaveFileDialog();
saveFileDialogPri.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
saveFileDialogPri.FilterIndex = 1;
if (saveFileDialogPri.ShowDialog() == DialogResult.OK)
{
FileStream streamTemp = new FileStream(strSourceFilePri, FileMode.Open);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(streamTemp);
workbook.Worksheets.Add();
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
//Set cell store subHeader
Aspose.Cells.Cells cellHeader = worksheet.Cells;
//cellHeader.Merge(2, 0, 1, tblBangDuLieuPara.Columns.Count);
worksheet.Cells["A3"].PutValue(strSubHeaderPara);
//worksheet.IsGridlinesVisible = false;
worksheet.Cells.ImportDataTable(tblBangDuLieuPara, false, iRowPara, iColumnPara, tblBangDuLieuPara.Rows.Count, tblBangDuLieuPara.Columns.Count);
//Formatting for cells store database
for (int i = 0; i < tblBangDuLieuPara.Rows.Count; i++)
{
for (int j = 0; j < tblBangDuLieuPara.Columns.Count; j++)
{
Aspose.Cells.Cell cell = worksheet.Cells[iRowPara + i, j];
workbook.Styles.Add();
Aspose.Cells.Style style = cell.GetStyle();
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Silver;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Silver;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Silver;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Silver;
cell.SetStyle(style);
}
}
//worksheet.AutoFitColumns();
//Save excel file
workbook.Save(saveFileDialogPri.FileName, FileFormatType.Default);
MessageBox.Show(WorkingContext.LangManager.GetString("frmRestSheet_ExportExcel_Messa"),
WorkingContext.LangManager.GetString("Message"),
MessageBoxButtons.OK, MessageBoxIcon.Information);
streamTemp.Close();
if (File.Exists(saveFileDialogPri.FileName))
Process.Start(saveFileDialogPri.FileName);
}
}
public static bool ListsToExcelFile(string filepath, IList[] lists, out string error)
{
error = "";
//----------Aspose变量初始化----------------
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//-------------输入数据-------------
int nRow = 0;
sheet.Pictures.Clear();
cells.Clear();
foreach (IList list in lists)
{
for (int i = 0; i <= list.Count - 1; i++)
{
try
{
System.Console.WriteLine(i.ToString() + " " + list[i].GetType());
if (list[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//插入图片数据
System.Drawing.Image image = (System.Drawing.Image)list[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(list[i]);
}
}
catch (System.Exception e)
{
error = error + e.Message;
}
}
nRow++;
}
//-------------保存-------------
workbook.Save(filepath);
return true;
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//�qResources��TemplateŪ�X��
template.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//�n���ͪ�excel��
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//�������j�X��row
int next = 23;
//�������X��col
int col = 14;
//���row�ƥ�
int dataRow = 16;
//����
int index = 0;
//�d���d��
Range tempRange = template.Worksheets[0].Cells.CreateRange(0,23,false);
//�`�@�X�����ʬ���
int count = 0;
int totalRec = source.SelectNodes("�M��/���ʬ���").Count;
foreach (XmlNode list in source.SelectNodes("�M��"))
{
//���ͲM��Ĥ@��
ws.Cells.CreateRange(index, next, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / dataRow) + 1;
//�g�J�N��
ws.Cells[index, 11].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText + "-" + list.SelectSingleNode("@��O�N��").InnerText);
//�g�J�զW�B�Ǧ~�סB�Ǵ��B��O�B�~��
ws.Cells[index + 2, 1].PutValue(source.SelectSingleNode("@�ǮզW��").InnerText);
ws.Cells[index + 2, 5].PutValue(source.SelectSingleNode("@�Ǧ~��").InnerText + " �Ǧ~�� �� " + source.SelectSingleNode("@�Ǵ�").InnerText + " �Ǵ�");
ws.Cells[index + 2, 8].PutValue(list.SelectSingleNode("@��O").InnerText);
ws.Cells[index + 2, 12].PutValue(list.SelectSingleNode("@�~��").InnerText + "�~��");
//�g�J���
int recCount = 0;
int dataIndex = index + 6;
for (; currentPage <= totalPage; currentPage++)
{
//�ƻs����
if (currentPage + 1 <= totalPage)
{
ws.Cells.CreateRange(index + next, next, false).Copy(tempRange);
}
//��J���
for (int i = 0; i < dataRow && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("���ʬ���")[recCount];
if(rec.SelectSingleNode("@�s�Ǹ�")!=null)
if(string.IsNullOrEmpty(rec.SelectSingleNode("@�s�Ǹ�").InnerText))
if(rec.SelectSingleNode("@�Ǹ�")!=null)
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�Ǹ�").InnerText);
else
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�s�Ǹ�").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@�m�W").InnerText);
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@�����Ҹ�").InnerText.ToString());
ws.Cells[dataIndex, 3].PutValue(rec.SelectSingleNode("@�ʧO�N��").InnerText);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@�ʧO").InnerText);
ws.Cells[dataIndex, 5].PutValue(rec.SelectSingleNode("@�X�ͦ~���").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�Ǯ�").InnerText);
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�Ǹ�").InnerText + "\n" + rec.SelectSingleNode("@��J�e�ǥ��_��O").InnerText);
ws.Cells[dataIndex, 8].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@��J�e�ǥ��_�Ƭd���").InnerText) + "\n" + rec.SelectSingleNode("@��J�e�ǥ��_�Ƭd�帹").InnerText);
ws.Cells[dataIndex, 9].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�~��").InnerText);
ws.Cells[dataIndex, 10].PutValue(rec.SelectSingleNode("@���ʥN��").InnerText);
ws.Cells[dataIndex, 11].PutValue(rec.SelectSingleNode("@��]�Ψƶ�").InnerText);
ws.Cells[dataIndex, 12].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@���ʤ��").InnerText));
//ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@�Ƶ�").InnerText);
if(rec.SelectSingleNode("@�S������N�X")!=null)
ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@�S������N�X").InnerText);
dataIndex++;
count++;
//��J�e�ǥ��_�Ǯ�="�|������" ��J�e�ǥ��_�Ǹ�="010101" ��J�e�ǥ��_��O="��T��" ��J�e�ǥ��_�Ƭd���="90/09/09" ��J�e�ǥ��_�Ƭd�帹="�Ф��T�r��09200909090��" ��J�e�ǥ��_�~��="�@�W"
}
//�p��X�p
if (currentPage == totalPage)
{
ws.Cells.CreateRange(dataIndex, 0, 1, 2).Merge();
ws.Cells[dataIndex, 0].PutValue("�X�p " + list.ChildNodes.Count.ToString() + " �W");
}
//����
ws.Cells[index + next -1, 10].PutValue("�� " + currentPage + " ���A�@ " + totalPage + " ��");
ws.HPageBreaks.Add(index + next, col);
//���ޫ��V�U�@��
index += next;
dataIndex = index + 6;
//�^���i��
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
#region ��J��,�q�l�榡
Worksheet TemplateWb = wb.Worksheets["�q�l�榡�d��"];
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
DyWb.Name = "�q�l�榡";
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// �榡�ഫ
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// �Ƨ� (�� �Z�O�B�~�šB��O�N�X�B�Ǹ�)
_data =(from data in _data orderby data.ClassType,data.GradeYear,data.DeptCode,data.StudentNumber select data).ToList ();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
DyWb_index++;
//�C�W�[�@��,�ƻs�@��
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//�Z�O
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//��O�N�X
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
//�Ǹ�
if(string.IsNullOrEmpty(rec.NewStudNumber))
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
else
DyWb.Cells[DyWb_index, 3].PutValue(rec.NewStudNumber);
//�m�W
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//�����Ҧr��
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//��1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//�ʧO�N�X
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//�X�ͤ��
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//�S������N�X
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//�~��
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//���ʭ�]�N�X
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//��J���
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// ��J�����O
DyWb.Cells[DyWb_index, 13].PutValue(rec.TransferStatus);
//��Ƭd���
DyWb.Cells[DyWb_index, 14].PutValue(rec.PreviousSchoolLastADDate);
//��Ƭd��r(*)
DyWb.Cells[DyWb_index, 15].PutValue(rec.PreviousSchoolLastADDoc);
//��Ƭd�帹(*)
DyWb.Cells[DyWb_index, 16].PutValue(rec.PreviousSchoolLastADNum);
//��ǮեN�X(*)
DyWb.Cells[DyWb_index, 17].PutValue(rec.PreviousSchoolCode);
//���O�N�X
DyWb.Cells[DyWb_index, 18].PutValue(rec.PreviousDeptCode);
//��Ǹ�
DyWb.Cells[DyWb_index, 19].PutValue(rec.PreviousStudentNumber);
// ���䴩�µ��c�~�ŻP�Ǵ��O�Τ�r�r��@�W�A�ҥH�o�˼g
//��~��
DyWb.Cells[DyWb_index, 20].PutValue(Getyear(rec.PreviousGradeYear));
//��Ǵ�
DyWb.Cells[DyWb_index, 21].PutValue(Getsemester(rec.PreviousSemester));
//�Ƶ�����
DyWb.Cells[DyWb_index, 22].PutValue(rec.Comment);
}
//foreach (XmlElement Record in source.SelectNodes("�M��/���ʬ���"))
//{
// DyWb_index++;
// //�C�W�[�@��,�ƻs�@��
// DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
// //�Z�O
// DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("�Z�O"));
// //��O�N�X
// DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("��O�N��"));
// //�Ǹ�
// DyWb.Cells[DyWb_index, 2].PutValue(Record.GetAttribute("�s�Ǹ�"));
// //�m�W
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("�m�W"));
// //�����Ҧr��
// DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("�����Ҹ�"));
// //��1
// DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("��1"));
// //�ʧO�N�X
// DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("�ʧO�N��"));
// //�X�ͤ��
// DyWb.Cells[DyWb_index, 7].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("�X�ͦ~���")));
// //�S������N�X
// DyWb.Cells[DyWb_index, 8].PutValue(Record.GetAttribute("�S������N�X"));
// //�~��
// DyWb.Cells[DyWb_index, 9].PutValue((Record.ParentNode as XmlElement).GetAttribute("�~��"));
// //���ʭ�]�N�X
// DyWb.Cells[DyWb_index, 10].PutValue(Record.GetAttribute("���ʥN��"));
// //��J���
// DyWb.Cells[DyWb_index, 11].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("���ʤ��")));
// //��Ƭd���
// DyWb.Cells[DyWb_index, 12].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("��J�e�ǥ��_�Ƭd���")));
// //��Ƭd��r(*)
// DyWb.Cells[DyWb_index, 13].PutValue(GetNumAndSrt1(Record.GetAttribute("��J�e�ǥ��_�Ƭd�帹")));
// //��Ƭd�帹(*)
// DyWb.Cells[DyWb_index, 14].PutValue(GetNumAndSrt2(Record.GetAttribute("��J�e�ǥ��_�Ƭd�帹")));
// //��ǮեN�X(*)
// DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("��J�e�ǥ��_�Ǯ�"));
// //���O�N�X
// DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("��J�e�ǥ��_��O"));
// //��Ǹ�
// DyWb.Cells[DyWb_index, 17].PutValue(Record.GetAttribute("��J�e�ǥ��_�Ǹ�"));
// //��~��
// DyWb.Cells[DyWb_index, 18].PutValue(Getyear(Record.GetAttribute("��J�e�ǥ��_�~��")));
// //��Ǵ�
// DyWb.Cells[DyWb_index, 19].PutValue(Getsemester(Record.GetAttribute("��J�e�ǥ��_�~��")));
// //�Ƶ�����
// DyWb.Cells[DyWb_index, 20].PutValue(Record.GetAttribute("�Ƶ�"));
//}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("�q�l�榡�d��");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//�x�s
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region �إ� Excel
//�q Resources �N���y���ʦW�UtemplateŪ�X��
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentListTemplate), FileFormatType.Excel2003);
//���� excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentListTemplate), FileFormatType.Excel2003);
#endregion
#region �ƻs�˦�-�w�]�˦��B��e
//�]�w�w�]�˦�
wb.DefaultStyle = template.DefaultStyle;
//�ƻs�˪����e18�� Column(��e)
for (int m = 0; m < 18; m++)
{
/*
* �ƻs template���Ĥ@�� Sheet���� m�� Column
* �� wb���Ĥ@�� Sheet���� m�� Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region ��l�ܼ�
/******************************
* rowi ��J�Ǯո�ƥ�
* rowj ��J�ǥ�ƥ�
* num �p��M�����
* numcount �p��C���M�歶��
* j �p��Ҳ��ͲM�歶��
* x �P�_�ӼƬO�_��20�Q�ƥ�
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("�M��").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("�M��/���ʬ���").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("�M��"))
{
int i = 0;
#region ��X����`�ƤΧP�_
//��X����`�Ƥ�K����i��
int count = list.SelectNodes("���ʬ���").Count;
//�P�_�ӼƬO�_��20�Q��
if (count % 20 == 0)
{
x = true;
}
#endregion
#region ���ʬ���
//�Nxml��ƶ�J��excel
foreach (XmlNode st in list.SelectNodes("���ʬ���"))
{
recCount++;
if (i % 20 == 0)
{
#region �ƻs�˦�-�氪�B�d��
//�ƻs�˪����e287�� Row(�氪)
//for (int m = 0; m < 28; m++)
//{
// /*
// * �ƻs template���Ĥ@�� Sheet����m�� Row
// * �� wb���Ĥ@�� Sheet����(j * 28) + m�� Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* �ƻsStyle(�]�t�x�s��X�֪���T)
* ����CreateRange()����n�ƻs��Range("A1", "R28")
* �A��CopyStyle�ƻs�t�@��Range�����榡
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t,28,false).Copy(range);
#endregion
#region ��J�Ǯո��
//�N�Ǯո�ƶ�J�A�����m��
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@��O�N��").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@�ǮզW��").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@�Ǧ~��").InnerText)+" �Ǧ~�� �� "+Convert.ToInt32(source.SelectSingleNode("@�Ǵ�").InnerText)+" �Ǵ�");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@��O").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@�~��").InnerText);
#endregion
if (j > 0)
{
//���J����(�b j * 28 �� (j * 28) +1 �����AR��S����)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region ��ܭ���
//��ܭ���
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("��" + numcount + "���A�@" + Math.Ceiling((double)count / 20) + "��");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("��" + numcount + "���A�@" + (Math.Ceiling((double)count / 20) + 1) + "��");
}
numcount++;
#endregion
}
#region ��J�ǥ��
//�N�ǥ�ƶ�J�A�����m��
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@�Ǹ�").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@�m�W").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@�����Ҹ�").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@�Ƭd���").InnerText) + "\n" + st.SelectSingleNode("@�Ƭd�帹").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@���ʥN��").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@��]�Ψƶ�").InnerText);
if (st.SelectSingleNode("@�s�Ǹ�").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@���ʤ��").InnerText));
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@�s�Ǹ�").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@���ʤ��").InnerText));
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@�Ƶ�").InnerText);
if(st.SelectSingleNode("@�S������N�X")!=null)
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@�S������N�X").InnerText);
#endregion
i++;
rowj++;
//�^���i��
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region �Y�ӼƬ�20���ơA�B�z��@����
if (x == true)
{
#region �ƻs�˦�-�氪�B�d��
//�ƻs�˪��e28�� Row(�氪)
//for (int m = 0; m < 28; m++)
//{
// /*
// * �ƻs template���Ĥ@�� Sheet����m�� Row
// * �� wb���Ĥ@�� Sheet����(j * 28) + m�� Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* �ƻsStyle(�]�t�x�s��X�֪���T)
* ����CreateRange()����n�ƻs��Range("A1", "R28")
* �A��CopyStyle�ƻs�t�@��Range�����榡
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region ��J�Ǯո��
//�N�Ǯո�ƶ�J�A�����m��
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@��O�N��").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@�ǮզW��").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@�Ǧ~��").InnerText) + " �Ǧ~�� �� " + Convert.ToInt32(source.SelectSingleNode("@�Ǵ�").InnerText) + " �Ǵ�");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@��O").InnerText);
#endregion
if (j > 0)
{
//���J����(�bi��i+1�����AO��P����)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region ��ܭ���
//��ܭ���
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("��" + numcount + "���A�@" + (Math.Ceiling((double)count / 20) + 1) + "��");
numcount++;
#endregion
}
#endregion
#region �έp�H��
//��J�έp�H��
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("�X �p " + count.ToString() + " �W");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region �]�w�ܼ�
//�վ�s�M��Ҩϥ��ܼ�
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "�q�l�榡";
int mdws_index = 0;
foreach (XmlElement record in source.SelectNodes("�M��/���ʬ���"))
{
mdws_index++;
// �����~�Ǧ~��
mdws.Cells[mdws_index, 0].PutValue(record.GetAttribute("�����~�Ǧ~��"));
mdws.Cells[mdws_index, 1].PutValue(record.GetAttribute("�Z�O"));
mdws.Cells[mdws_index, 2].PutValue((record.ParentNode as XmlElement).GetAttribute("��O�N��"));
mdws.Cells[mdws_index, 3].PutValue("");
mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("�Ǹ�"));
mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("�m�W"));
mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("�����Ҹ�"));
mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("��1"));
mdws.Cells[mdws_index, 8].PutValue(record.GetAttribute("�ʧO�N��"));
mdws.Cells[mdws_index, 9].PutValue((BL.Util.ConvertDate1(record.GetAttribute("�X�ͦ~���"))));
mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("�S������N�X")); //�쬰�����������
mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("���ʥN��"));
mdws.Cells[mdws_index, 12].PutValue(BL.Util.ConvertDate1(record.GetAttribute("���ʤ��")));
mdws.Cells[mdws_index, 13].PutValue(BL.Util.ConvertDate1(record.GetAttribute("�Ƭd���")));
mdws.Cells[mdws_index, 14].PutValue(BL.Util.GetDocNo_Doc(record.GetAttribute("�Ƭd�帹")));
mdws.Cells[mdws_index, 15].PutValue(BL.Util.GetDocNo_No(record.GetAttribute("�Ƭd�帹")));
mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("�Ƶ�"));
}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.ActiveSheetIndex = 0;
//�x�s Excel
wb.Save(location, FileFormatType.Excel2003);
}
//列印
private void btnPrint_Click(object sender, EventArgs e)
{
//0. 使用者選擇檔名
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "另存新檔";
sfd.FileName = "座位表_" + this.layout.ClassRoomName + "_" + this.expandablePanel1.TitleText + ".xls";
sfd.Filter = "Excel 2003 相容檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*";
DialogResult dr = sfd.ShowDialog();
if (dr != System.Windows.Forms.DialogResult.OK)
return;
//1. Open Excel File
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
// 讀取樣版檔
if (this.layout.GetUDTLayout().ExcelTemplate =="B")
wb.Open(new MemoryStream(EMBACore.Properties.Resources.座位表_100B));
else
wb.Open(new MemoryStream(EMBACore.Properties.Resources.座位表_100A));
// 讀取樣版工作表
Worksheet templateSheet = wb.Worksheets[0];
// 複製樣版
int instanceSheetIndex = wb.Worksheets.AddCopy("範本");
Worksheet instanceSheet = wb.Worksheets[instanceSheetIndex];
instanceSheet.Name = this.expandablePanel1.TitleText;
//填入學生座位
foreach (string studID in this.stud_coords.Keys)
{
//取得學生資料
if (this.students.ContainsKey(studID))
{
SeatTableStudent stud = this.students[studID];
//取得學生位置座標
CellCoordinate coord = this.stud_coords[studID];
//將座標轉換為 Excel 格子位置
int offsetX = coord.X;
int offsetY = (this.layout.YCount - coord.Y -1) * 2 + 1 ;
//放置照片
if (stud.Photo != null)
{
MemoryStream ms = new MemoryStream();
stud.Photo.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
Cell cell = instanceSheet.Cells[offsetY, offsetX];
instanceSheet.Pictures.Add(cell.Row, cell.Column, cell.Row + 1, cell.Column + 1, ms);
}
//寫入姓名
instanceSheet.Cells[offsetY+1, offsetX].PutValue(stud.Name);
}
} //end of foreach loop
try
{
wb.Worksheets.RemoveAt("範本");
wb.Save(sfd.FileName);
if (System.IO.File.Exists(sfd.FileName))
System.Diagnostics.Process.Start(sfd.FileName);
}
catch (Exception ex)
{
Util.ShowMsg(ex.Message,"注意");
}
}
private static bool SavePdfToStream(string inputFile, MemoryStream fileStream, MemoryStream documentStream, ref bool isLandscape)
{
var extension = Path.GetExtension(inputFile);
if (string.IsNullOrEmpty(extension))
throw new ArgumentException(inputFile);
try
{
fileStream.Position = 0;
switch (extension.ToUpperInvariant())
{
case ".DOC":
case ".DOCX":
case ".RTF":
case ".DOT":
case ".DOTX":
var doc = new Aspose.Words.Document(fileStream);
if (doc.PageCount > 0)
{
var pageInfo = doc.GetPageInfo(0);
isLandscape = pageInfo.WidthInPoints > pageInfo.HeightInPoints;
}
doc.Save(documentStream, Aspose.Words.SaveFormat.Pdf);
break;
case ".XLS":
case ".XLSX":
var workbook = new Aspose.Cells.Workbook(fileStream);
for (var i = 0; i < workbook.Worksheets.Count; i++)
{
if (!workbook.Worksheets[i].IsVisible) continue;
isLandscape = workbook.Worksheets[i].PageSetup.Orientation == Aspose.Cells.PageOrientationType.Landscape;
break;
}
workbook.Save(documentStream, Aspose.Cells.SaveFormat.Pdf);
break;
//Microsoft Visio
case ".VSD":
case ".VSS":
case ".VST":
case ".VSX":
case ".VTX":
case ".VDW":
case ".VDX":
var vsdDiagram = new Aspose.Diagram.Diagram(fileStream);
if (vsdDiagram.Pages.Count > 0)
isLandscape = vsdDiagram.Pages[0].PageSheet.PrintProps.PrintPageOrientation.Value == Aspose.Diagram.PrintPageOrientationValue.Landscape;
vsdDiagram.Save(documentStream, Aspose.Diagram.SaveFileFormat.PDF);
break;
//Microsoft Project
case ".MPP":
var project = new Aspose.Tasks.Project(fileStream);
project.Save(documentStream, Aspose.Tasks.Saving.SaveFileFormat.PDF);
isLandscape = true;
break;
//PowerPoint
case ".PPT":
case ".PPS":
case ".POT":
using (var pres = new Aspose.Slides.Presentation(fileStream))
{
isLandscape = pres.SlideSize.Size.Width > pres.SlideSize.Size.Height;
pres.Save(documentStream, Aspose.Slides.Export.SaveFormat.Pdf);
}
break;
case ".PPTX":
case ".PPSX":
case ".POTX":
//case ".XPS":
using (var presEx = new Aspose.Slides.Presentation(fileStream))
{
isLandscape = presEx.SlideSize.Orientation == Aspose.Slides.SlideOrienation.Landscape;
presEx.Save(documentStream, Aspose.Slides.Export.SaveFormat.Pdf);
}
break;
case ".PDF":
{
using (var pdf = new Aspose.Pdf.Document(fileStream))
{
var page = pdf.Pages.OfType<Aspose.Pdf.Page>().FirstOrDefault();
if (page != null && page.MediaBox != null)
{
isLandscape = page.MediaBox.Width > page.MediaBox.Height;
}
}
fileStream.Seek(0, SeekOrigin.Begin);
fileStream.CopyTo(documentStream);
}
break;
}
}
finally
{
fileStream.Close();
}
return true;
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將新生名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.EnrollmentListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.EnrollmentListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
// 所在地代碼對照
Dictionary<string, string> gLocationCodeDict = new Dictionary<string, string>();
foreach (XElement elm in BL.Get.JHSchoolList().Elements("學校"))
{
string code=elm.Attribute("所在地代碼").Value;
if(!gLocationCodeDict.ContainsKey(code))
gLocationCodeDict.Add(code,elm.Attribute("所在地").Value);
}
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前27個 Row(欄高)
//for (int m = 0; m < 27; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 27) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 27) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R27")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 27, false);
int t = j * 27;
wb.Worksheets[0].Cells.CreateRange(t, 27, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 27 跟 (j * 27) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 27, 18);
rowj += 7;
}
else
{
rowj = 5;
}
rowi += 27;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 5].PutValue(st.SelectSingleNode("@身分證號").InnerText);
try
{
wb.Worksheets[0].Cells[rowj, 6].PutValue(Convert.ToInt32(st.SelectSingleNode("@性別代號").InnerText));
}
catch (Exception)
{}
wb.Worksheets[0].Cells[rowj, 7].PutValue(st.SelectSingleNode("@性別").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(st.SelectSingleNode("@出生年月日").InnerText);
string stra1 = "", stra2 = "";
if (st.SelectSingleNode("@畢業國中所在縣市代號") != null)
stra1 = st.SelectSingleNode("@畢業國中所在縣市代號").InnerText;
if (st.SelectSingleNode("@入學資格代號") != null)
stra2 = st.SelectSingleNode("@入學資格代號").InnerText;
wb.Worksheets[0].Cells[rowj, 11].PutValue(stra1 + "\n" + stra2);
string data = "", data1 = "";
string uCode = "";
if (st.SelectSingleNode("@異動代號") == null)
{
if (st.SelectSingleNode("@異動代碼") != null)
uCode = st.SelectSingleNode("@異動代碼").InnerText;
}
else
uCode = st.SelectSingleNode("@異動代號").InnerText;
if (uCode == "001")
data1 = "畢業";
if (uCode == "003")
data1 = "結業";
if (uCode == "004")
data1 = "修滿";
if(st.SelectSingleNode("@畢業國中所在縣市代號")!=null)
if (!string.IsNullOrEmpty(st.SelectSingleNode("@畢業國中所在縣市代號").InnerText))
{
string code=st.SelectSingleNode("@畢業國中所在縣市代號").InnerText;
if (gLocationCodeDict.ContainsKey(code))
{
data = gLocationCodeDict[code];
}
}
wb.Worksheets[0].Cells[rowj, 12].PutValue(data+st.SelectSingleNode("@畢業國中").InnerText+data1);
//wb.Worksheets[0].Cells[rowj, 14].PutValue(st.SelectSingleNode("@備註").InnerText);
if(st.SelectSingleNode("@特殊身份代碼")!=null)
wb.Worksheets[0].Cells[rowj, 14].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前27個 Row(欄高)
//for (int m = 0; m < 27; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 27) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 27) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R27")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 27, false);
int t= j * 27;
wb.Worksheets[0].Cells.CreateRange(t, 27, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 27, 18);
rowj += 7;
}
rowi += 27;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 ");
wb.Worksheets[0].Cells[rowj, 3].PutValue(count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 27, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (27 * j) - 2;
rowi = (27 * j);
x = false;
#endregion
}
#region 97中辦格式
Worksheet mingdao = wb.Worksheets["電子格式99"];
Worksheet mdws = wb.Worksheets[wb.Worksheets.Add()];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
{
mdws_index++;
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
mdws.Cells[mdws_index, 0].PutValue(record.GetAttribute("班別"));
mdws.Cells[mdws_index, 1].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
mdws.Cells[mdws_index, 2].PutValue("");
mdws.Cells[mdws_index, 3].PutValue(record.GetAttribute("學號"));
mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("姓名"));
mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("身分證號"));
mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("註1"));
mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("性別代號"));
mdws.Cells[mdws_index, 8].PutValue(GetBirthdateWithoutSlash(record.GetAttribute("出生年月日")));
mdws.Cells[mdws_index, 9].PutValue(record.GetAttribute("特殊身份代碼")); //原為抓取註備欄位值
mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("入學資格代號"));
//mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("畢業國中所在縣市代號"));
string GradeSchoolCode = record.GetAttribute("畢業國中代碼");
//if (GradeSchoolCode.Length > 3)
// mdws.Cells[mdws_index, 12].PutValue(GradeSchoolCode.Substring(2, 1));
// mdws.Cells[mdws_index, 13].PutValue(record.GetAttribute("畢業國中"));
mdws.Cells[mdws_index, 11].PutValue(GradeSchoolCode);
mdws.Cells[mdws_index, 12].PutValue(record.GetAttribute("入學資格證明文件"));
mdws.Cells[mdws_index, 13].PutValue(record.GetAttribute("備註"));
}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
//mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.RemoveAt("電子格式99");
wb.Worksheets.ActiveSheetIndex = 0;
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將學籍異動名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlElement st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + " 學年度 第 " + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + " 學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@年級").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
string updatecode = st.SelectSingleNode("@異動代號").InnerText;
////將學生資料填入適當的位置內
//if (NewStudentNumberCodes.Contains(updatecode))
//{
// string strNum = "";
// if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
// strNum = st.SelectSingleNode("@新學號").InnerText;
// else
// {
// string sid = "";
// if (!string.IsNullOrEmpty(st.SelectSingleNode("@學生編號").InnerText))
// sid = st.SelectSingleNode("@學生編號").InnerText;
// List<string> ids = new List<string>();
// ids.Add(sid);
// SHSchool.Data.SHStudent.RemoveByIDs(ids);
// SHSchool.Data.SHStudentRecord stud = SHSchool.Data.SHStudent.SelectByID(sid);
// strNum = stud.StudentNumber;
// }
// wb.Worksheets[0].Cells[rowj, 1].PutValue(strNum);
//}
//else
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
//wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText + (string.IsNullOrEmpty(st.GetAttribute("更正後資料")) ? "" : "\n" + st.GetAttribute("更正後資料")));
string UpdateData = "";
if (st.SelectSingleNode("@新資料")!=null)
{
// 更正學號填到另一格
if (updatecode != "401")
UpdateData = st.SelectSingleNode("@新資料").InnerText;
}
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText+"\n"+UpdateData);
string strUpdateDate = Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText);
//假設有異動學生學號的類別才出現新學號字樣
if (st.SelectSingleNode("@新學號")!=null)
if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
{
int newNo;
if (int.TryParse(st.SelectSingleNode("@新學號").InnerText, out newNo))
strUpdateDate = newNo + "\n" + strUpdateDate;
}
else
{
// 更正學號
if (updatecode == "401")
{
if (st.SelectSingleNode("@新資料") != null)
if (!string.IsNullOrEmpty(st.SelectSingleNode("@新資料").InnerText))
strUpdateDate = st.SelectSingleNode("@新資料").InnerText + "\n" + strUpdateDate;
}
}
wb.Worksheets[0].Cells[rowj, 13].PutValue(strUpdateDate);
if(st.SelectSingleNode("@特殊身份代碼") !=null )
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
// 因2010年格式不同小修改
#region 學籍異動電子格式
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
//名稱
DyWb.Name = "電子格式";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
//拷貝range_H
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
// 遇到特殊異動代碼要處理
List<string> spcCode = new List<string>();
spcCode.Add("211");
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, GYear(data.GradeYear), data.DeptCode, data.UpdateCode select data).ToList();
foreach(GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data )
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//身分證字號
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//註1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//出生日期
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//特殊身份代碼
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//年級
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//異動原因代碼
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//異動日期
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// 異動順序
DyWb.Cells[DyWb_index, 13].PutValue(rec.Order);
//備查日期
DyWb.Cells[DyWb_index, 14].PutValue(rec.LastADDate);
//備查文字
DyWb.Cells[DyWb_index, 15].PutValue(rec.LastADDoc);
//備查文號
DyWb.Cells[DyWb_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
strUpdateData = rec.NewData;
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
DyWb.Cells[DyWb_index, 17].PutValue(strUpdateData);
// 註2
DyWb.Cells[DyWb_index, 18].PutValue(rec.Comment2);
//備註說明
DyWb.Cells[DyWb_index, 19].PutValue(rec.Comment);
// 2011 新承辦單位修正，轉科讀取新學號
if (NewStudentNumberCodes.Contains(rec.UpdateCode))
{
List<string> ids = new List<string>();
ids.Add(rec.StudentID);
SHSchool.Data.SHStudent.RemoveByIDs(ids);
SHSchool.Data.SHStudentRecord studRec = SHSchool.Data.SHStudent.SelectByID(rec.StudentID);
if(studRec !=null)
DyWb.Cells[DyWb_index, 3].PutValue(studRec.StudentNumber);
DyWb.Cells[DyWb_index, 17].PutValue("");
}
}
//foreach (XmlElement Record in source.SelectNodes("清單/異動紀錄"))
//{
// DyWb_index++;
// //每增加一行,複製一次
// DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
// //班別
// DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("班別"));
// //科別代碼
// DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("科別代號"));
// // 2 放上傳類別，請使用者自填
// //學號
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("學號"));
// //姓名
// DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("姓名"));
// //身分證字號
// DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身分證號"));
// //註1
// DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("註1"));
// //性別代碼
// DyWb.Cells[DyWb_index, 7].PutValue(Record.GetAttribute("性別代號"));
// //出生日期
// DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("出生年月日")));
// //特殊身份代碼
// DyWb.Cells[DyWb_index, 9].PutValue(Record.GetAttribute("特殊身份代碼")); //原為抓取備註欄位
// //年級
// DyWb.Cells[DyWb_index, 10].PutValue((Record.ParentNode as XmlElement).GetAttribute("年級"));
// //異動原因代碼
// DyWb.Cells[DyWb_index, 11].PutValue(Record.GetAttribute("異動代號"));
// //異動日期
// DyWb.Cells[DyWb_index, 12].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("異動日期")));
// //原備查日期
// DyWb.Cells[DyWb_index, 13].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("備查日期")));
// //原備查文字
// DyWb.Cells[DyWb_index, 14].PutValue(GetNumAndSrt1(Record.GetAttribute("備查文號")));
// //原備查文號
// DyWb.Cells[DyWb_index, 15].PutValue(GetNumAndSrt2(Record.GetAttribute("備查文號")));
// // 捨棄
// ////舊班別
// //DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("舊班別"));
// ////舊科別代碼
// //DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("舊科別代碼"));
// //更正後資料
// string strUpdateData = string.Empty;
// //若是更正後資料有值則填入更正後資料
// if (!string.IsNullOrEmpty(Record.GetAttribute("更正後資料")))
// strUpdateData = Record.GetAttribute("更正後資料");
// //若是新學號中有值則填入新學號
// //判斷strUpdateData是否已有值，若是已有值則加入斷行符號
// if (!string.IsNullOrEmpty(Record.GetAttribute("新學號")))
// strUpdateData += string.IsNullOrEmpty(strUpdateData) ? Record.GetAttribute("新學號") : "\n" + Record.GetAttribute("新學號");
// DyWb.Cells[DyWb_index, 16].PutValue(strUpdateData);
// //備註說明
// DyWb.Cells[DyWb_index, 17].PutValue(Record.GetAttribute("備註"));
// // 2011 新承辦單位修正
// if(spcCode.Contains(Record.GetAttribute("異動代號")))
// {
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("新學號"));
// DyWb.Cells[DyWb_index, 16].PutValue("");
// }
//}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入图片数据-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
}
workbook.Save(filepath);
return true;
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
}
}
public static bool ExportCSV(DataTable dt, string path)
{
bool succeed = false;
if (dt != null)
{
try
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
colIndex++;
}
rowIndex++;
}
//0-byte array
byte[] workbookData = new byte[0];
//Text save options. You can use any type of separator
TxtSaveOptions opts = new TxtSaveOptions();
opts.Separator = '\t';
opts.Encoding = System.Text.Encoding.Default;
//Copy each worksheet data in text format inside workbook data array
for (int idx = 0; idx < workbook.Worksheets.Count; idx++)
{
//Save the active worksheet into text format
MemoryStream ms = new MemoryStream();
workbook.Worksheets.ActiveSheetIndex = idx;
workbook.Save(ms, opts);
//Save the worksheet data into sheet data array
ms.Position = 0;
byte[] sheetData = ms.ToArray();
//Combine this worksheet data into workbook data array
byte[] combinedArray = new byte[workbookData.Length + sheetData.Length];
Array.Copy(workbookData, 0, combinedArray, 0, workbookData.Length);
Array.Copy(sheetData, 0, combinedArray, workbookData.Length, sheetData.Length);
workbookData = combinedArray;
}
//cellSheet.AutoFitColumns();
path = Path.GetFullPath(path);
//Save entire workbook data into file
File.WriteAllBytes(path, workbookData);
//workbook.Save(path,SaveFormat.CSV);
succeed = true;
}
catch (Exception ex)
{
succeed = false;
}
}
return succeed;
}
public static bool ExportExcel(DataTable dt, string path)
{
bool succeed = false;
if (dt != null)
{
try
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
//为单元格添加样式
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
//style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
//cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true;
//cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
//cellSheet.Cells[rowIndex, colIndex].Style = style;
colIndex++;
}
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
path = Path.GetFullPath(path);
workbook.Save(path);
succeed = true;
}
catch (Exception ex)
{
succeed = false;
}
}
return succeed;
}
protected override void Build(System.Xml.XmlElement source, string location)
{
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(Properties.Resources.GraduatingStudentListTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.GraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//頁面間隔幾個row
int next = 24;
//索引
int index = 0;
//範本範圍
Range tempRange = template.Worksheets[0].Cells.CreateRange(0,24,false);
//總共幾筆異動紀錄
int count = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
foreach (XmlNode list in source.SelectNodes("清單"))
{
//產生清單第一頁
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, next, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "畢業名冊")
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□畢業", "■畢業"));
else
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□結業", "■結業"));
////寫入代號
//ws.Cells[index,6].PutValue("代碼："+source.SelectSingleNode("@學校代號").InnerText+"-"+list.SelectSingleNode("@科別代號").InnerText);
////寫入校名、學年度、學期、科別
//ws.Cells[index+2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
//ws.Cells[index+2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
//ws.Cells[index+2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//寫入資料
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//寫入代號
ws.Cells[index, 6].PutValue("代碼：" + source.SelectSingleNode("@學校代號").InnerText + "-" + list.SelectSingleNode("@科別代號").InnerText);
//寫入校名、學年度、學期、科別
ws.Cells[index + 2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//複製頁面
if (currentPage+1 <= totalPage)
{
ws.Cells.CreateRange(index + next, next, false).Copy(tempRange);
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "畢業名冊")
ws.Cells[index + next, 0].PutValue(ws.Cells[index + next, 0].StringValue.Replace("□畢業", "■畢業"));
else
ws.Cells[index + next, 0].PutValue(ws.Cells[index + next, 0].StringValue.Replace("□結業", "■結業"));
}
//填入資料
for (int i = 0; i < 18 && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("異動紀錄")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@學號").InnerText + "\n" + rec.SelectSingleNode("@姓名").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@性別代號").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@性別").InnerText);
string ssn = "";
if(rec.SelectSingleNode("@身分證號")!=null)
ssn=rec.SelectSingleNode("@身分證號").InnerText;
if (ssn == "")
if(rec.SelectSingleNode("@身份證號")!=null)
ssn = rec.SelectSingleNode("@身份證號").InnerText;
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@生日").InnerText) + "\n" + ssn);
if(rec.SelectSingleNode("@最後異動代號")!=null )
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@備查日期").InnerText) + "\n" +rec.SelectSingleNode("@備查文號").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@畢業證書字號").InnerText);
//ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@備註").InnerText);
if(rec.SelectSingleNode("@特殊身份代碼")!=null)
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@特殊身份代碼").InnerText);
dataIndex++;
count++;
}
//計算合計
if (currentPage == totalPage)
{
ws.Cells[dataIndex, 0].PutValue("合計");
ws.Cells[dataIndex, 1].PutValue(list.ChildNodes.Count.ToString());
//ws.Cells[index + 22, 0].PutValue("合計");
//ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//分頁
ws.Cells[index + 23, 6].PutValue("第 " + currentPage + " 頁，共 " + totalPage + " 頁");
ws.HPageBreaks.Add(index+24, 8);
//索引指向下一頁
index += next;
dataIndex = index + 5;
//回報進度
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
#region 畢業異動,電子格式
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
//名稱
DyWb.Name = "電子格式";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
//拷貝range_H
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
foreach(XmlElement Record in source.SelectNodes("清單/異動紀錄"))
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index,1,false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("班別"));
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("科別代號"));
// 上傳類別
//學號
DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("學號"));
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("姓名"));
//身分證字號
if(Record.GetAttribute("身分證號")=="")
DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身份證號"));
else
DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身分證號"));
//註1
DyWb.Cells[DyWb_index,6].PutValue(Record.GetAttribute("註1"));
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(Record.GetAttribute("性別代號"));
//出生日期
if(!string.IsNullOrEmpty(Record.GetAttribute("生日")))
DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("生日")));
else
DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("生日1")));
// 特殊身分代碼
DyWb.Cells[DyWb_index, 9].PutValue(Record.GetAttribute("特殊身分代碼"));
// 年級
DyWb.Cells[DyWb_index, 10].PutValue(Record.GetAttribute("年級"));
// 學籍異動代碼
DyWb.Cells[DyWb_index, 11].PutValue(Record.GetAttribute("最後異動代號"));
//學籍異動文字
DyWb.Cells[DyWb_index, 12].PutValue(Util.GetDocNo_Doc(Record.GetAttribute("備查文號")));
//學籍異動文號
DyWb.Cells[DyWb_index, 13].PutValue(Util.GetDocNo_No(Record.GetAttribute("備查文號")));
// 學籍異動核准日期
DyWb.Cells[DyWb_index, 14].PutValue(Util.ConvertDate1(Record.GetAttribute("備查日期")));
//畢業證書字號
DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("畢業證書字號"));
//備註說明
DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("備註"));
}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將延修生學籍名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28,false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) +"學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
if (st.SelectSingleNode("@新學號").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@新學號").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
if(st.SelectSingleNode("@特殊身份代碼")!=null )
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0,28,false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// 應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//異動日期
mdws.Cells[mdws_index, 12].PutValue(rec.UpdateDate);
// 異動順序
mdws.Cells[mdws_index, 13].PutValue(rec.Order);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//備查文字
mdws.Cells[mdws_index, 15].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
strUpdateData = rec.NewData;
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
mdws.Cells[mdws_index, 17].PutValue(strUpdateData);
// 註2
mdws.Cells[mdws_index, 18].PutValue(rec.Comment2);
//備註說明
mdws.Cells[mdws_index, 19].PutValue(rec.Comment);
}
//foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
//{
// mdws_index++;
// mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// // 學年度
// string schoolYear = "";
// if (!string.IsNullOrEmpty(record.GetAttribute("學生編號")))
// {
// SHSchool.Data.SHLeaveInfoRecord scl = SHSchool.Data.SHLeaveInfo.SelectByStudentID(record.GetAttribute("學生編號"));
// if (scl.SchoolYear.HasValue)
// schoolYear = scl.SchoolYear.Value.ToString();
// }
// mdws.Cells[mdws_index, 0].PutValue(schoolYear);
// mdws.Cells[mdws_index, 1].PutValue(record.GetAttribute("班別"));
// mdws.Cells[mdws_index, 2].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
// mdws.Cells[mdws_index, 3].PutValue("");
// mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("學號"));
// mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("姓名"));
// mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("身分證號"));
// mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("註1"));
// mdws.Cells[mdws_index, 8].PutValue(record.GetAttribute("性別代號"));
// mdws.Cells[mdws_index, 9].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("出生年月日"))));
// mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("特殊身份代碼")); //原為抓取註備欄位值
// mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("異動代號"));
// mdws.Cells[mdws_index, 12].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("異動日期"))));
// mdws.Cells[mdws_index, 13].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("備查日期"))));
// mdws.Cells[mdws_index, 14].PutValue(GetADDoc(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 15].PutValue(GetADNo(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("新學號"));
// mdws.Cells[mdws_index, 17].PutValue(record.GetAttribute("備註"));
//}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
private void btnUnMerge_Click(object sender, System.EventArgs e)
{
//Create a Workbook.
string path = System.Web.HttpContext.Current.Server.MapPath("~");
path = path.Substring(0, path.LastIndexOf("\\"));
path += @"\designer\Workbooks\MergeCells.xls";
Aspose.Cells.Workbook wbk = new Aspose.Cells.Workbook(path);
//Create a Worksheet and get the first sheet.
Aspose.Cells.Worksheet worksheet = wbk.Worksheets[0];
//Create a Cells object ot fetch all the cells.
Aspose.Cells.Cells cells = worksheet.Cells;
//Unmerge the cells.
cells.UnMerge(5, 2, 2, 3);
//Save the excel file
wbk.Save(HttpContext.Current.Response, "UnMergeCells.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
// End response to avoid unneeded html after xls
Response.End();
}
/// <summary>
/// DataTableToExcel
/// DataTabel转换成Excel文件
///
/// </summary>
/// <param name="datatable">DataTable</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径<</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public static bool DataTableInsertToExcel(DataTable datatable, ArrayList colNameList, string fromfile, out string error, int beginRow, int beginColumn)
{
error = "";
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(fromfile);
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//-------------插入数据-------------
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
cells.InsertRow(beginRow);
for (int i = 0; i < colNameList.Count; i++)
{
string colName = colNameList[i].ToString();
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (colName == datatable.Columns[j].ColumnName)
{
object temp = row[datatable.Columns[j].ColumnName];
cells[beginRow, beginColumn + i].PutValue(row[datatable.Columns[j].ColumnName]);
break;
}
}
}
}
catch (System.Exception e)
{
error = error + " DataTableInsertToExcel: " + e.Message;
}
}
//-------------保存-------------
workbook.Save(fromfile);
return true;
}
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].Style = 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;
}
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//�qResources��TemplateŪ�X��
template.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
//�n���ͪ�excel��
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//�������j�X��row
int next = 24;
//����
int index = 0;
//�d���d��
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 24, false);
//�`�@�X�����ʬ���
int count = 0;
int totalRec = source.SelectNodes("�M��/���ʬ���").Count;
// ���o�W�U���s���̫Ყ�ʥN�X���
Dictionary<string,string> LastCodeDict = new Dictionary<string,string>();
foreach (XmlNode list in source.SelectNodes("�M��"))
{
//���ͲM��Ĥ@��
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, 24, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//�g�J�W�U���O
if (source.SelectSingleNode("@���O").InnerText == "���ץͲ��~�W�U")
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("�����~", "�����~"));
else
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("�����~", "�����~"));
//�g�J�N��
ws.Cells[index, 6].PutValue("�N�X�G" + source.SelectSingleNode("@�ǮեN��").InnerText + "-" + list.SelectSingleNode("@��O�N��").InnerText);
//�g�J�զW�B�Ǧ~�סB�Ǵ��B��O
ws.Cells[index + 2, 0].PutValue("�զW�G" + source.SelectSingleNode("@�ǮզW��").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@�Ǧ~��").InnerText + "�Ǧ~�� ��" + source.SelectSingleNode("@�Ǵ�").InnerText + "�Ǵ�");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@��O").InnerText);
//�g�J���
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//�ƻs����
if (currentPage + 1 <= totalPage)
{
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(ws.Cells, row + index, row + index + next);
//}
ws.Cells.CreateRange(index + next, 24, false).Copy(tempRange);
}
//��J���
for (int i = 0; i < 18 && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("���ʬ���")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�Ǹ�").InnerText + "\n" + rec.SelectSingleNode("@�m�W").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@�ʧO�N��").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@�ʧO").InnerText);
string ssn = rec.SelectSingleNode("@�����Ҹ�").InnerText;
if (ssn == "")
ssn = rec.SelectSingleNode("@�����Ҹ�").InnerText;
if(!LastCodeDict.ContainsKey(ssn))
LastCodeDict.Add(ssn,rec.SelectSingleNode("@�̫Ყ�ʥN��").InnerText.ToString());
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@�ͤ�").InnerText) + "\n" + ssn);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@�̫Ყ�ʥN��").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@�Ƭd���").InnerText) + "\n" + rec.SelectSingleNode("@�Ƭd�帹").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@���~�ҮѦr��").InnerText);
//ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@�Ƶ�").InnerText);
if(rec.SelectSingleNode("@�S������N�X")!=null)
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@�S������N�X").InnerText);
dataIndex++;
count++;
}
//�p��X�p
if (currentPage == totalPage)
{
ws.Cells[index + 22, 0].PutValue("�X�p");
ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//����
ws.Cells[index + 23, 6].PutValue("�� " + currentPage + " ���A�@ " + totalPage + " ��");
ws.HPageBreaks.Add(index + 24, 8);
//���ޫ��V�U�@��
index += next;
dataIndex = index + 5;
//�^���i��
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "�q�l�榡";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// �榡�ഫ
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// �Ƨ� (�� �Z�O�B�~�šB��O�N�X�B���ʥN�X)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//�C�W�[�@��,�ƻs�@��
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
//�����~�Ǧ~��
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//�Z�O
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//��O�N�X
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 ��W�����O�A�ШϥΪ̦۶�
//�Ǹ�
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//�m�W
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//�����Ҧr��
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//��1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//�ʧO�N�X
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//�X�ͤ��
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//�S������N�X
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//���ʭ�]�N�X
if(LastCodeDict.ContainsKey(rec.IDNumber))
mdws.Cells[mdws_index, 11].PutValue(LastCodeDict[rec.IDNumber]);
else
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//�Ƭd��r
mdws.Cells[mdws_index, 12].PutValue(rec.LastADDoc);
//�Ƭd�帹
mdws.Cells[mdws_index, 13].PutValue(rec.LastADNum);
//�Ƭd���
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//���~�ҮѦr��
mdws.Cells[mdws_index, 15].PutValue(rec.GraduateCertificateNumber);
//�Ƶ�����
mdws.Cells[mdws_index, 16].PutValue(rec.Comment);
}
//�x�s
wb.Save(location, FileFormatType.Excel2003);
}
private void btnMerge_Click(object sender, System.EventArgs e)
{
//Create a Workbook.
Aspose.Cells.Workbook wbk = new Aspose.Cells.Workbook();
//Create a Worksheet and get the first sheet.
Aspose.Cells.Worksheet worksheet = wbk.Worksheets[0];
//Create a Cells object ot fetch all the cells.
Aspose.Cells.Cells cells = worksheet.Cells;
//Merge some Cells (C6:E7) into a single C6 Cell.
cells.Merge(5, 2, 2, 3);
//Input data into C6 Cell.
worksheet.Cells[5, 2].PutValue("This is my value");
//Create a Style object to fetch the Style of C6 Cell.
Aspose.Cells.Style style = worksheet.Cells[5, 2].GetStyle();
//Create a Font object
Aspose.Cells.Font font = style.Font;
//Set the name.
font.Name = "Times New Roman";
//Set the font size.
font.Size = 18;
//Set the font color
font.Color = Color.Blue;
//Bold the text
font.IsBold = true;
//Make it italic
font.IsItalic = true;
//Set the backgrond color of C6 Cell to Red
style.ForegroundColor = Color.Red;
style.Pattern = BackgroundType.Solid;
//Apply the Style to C6 Cell.
cells[5, 2].SetStyle(style);
//Save the excel file
wbk.Save(HttpContext.Current.Response,"MergeCells.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
// End response to avoid unneeded html after xls
Response.End();
}