想說這個應該更好用。但最近的工作內容一直沒去碰到Excel。
所幸，有了一個需求，是要用excel的，而且問了網管，
網管說基本上office都是2010了。於是放心大膽的用下去。
在那之前先寫個測試來實做。
說明基本上都在程式裡面了。
/// <summary>
/// 建立新檔
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
if (!System.IO.Directory.Exists("xlsx"))
{
System.IO.Directory.CreateDirectory("xlsx");
}
string strFilePath = AppDomain.CurrentDomain.BaseDirectory + "xlsx\\" +DateTime.Now.ToString("yyyy-MM-dd-hhmm") +".xlsx";
ExcelPackage ep = new ExcelPackage();
//加入一個Sheet
ep.Workbook.Worksheets.Add("MySheet");
//取得剛剛加入的Sheet(實體Sheet就叫MySheet)
ExcelWorksheet sheet1 = ep.Workbook.Worksheets["MySheet"];//取得Sheet1
//寫入資料、設定格式
cellConfig(ref sheet1);
//建立檔案串流
FileStream OutputStream = new FileStream(strFilePath, FileMode.Create,FileAccess.Write, FileShare.ReadWrite);
//把剛剛的Excel物件真實存進檔案裡
ep.SaveAs(OutputStream);
//關閉串流
OutputStream.Close();
msg("Create complete");
}
/// <summary>
/// 開檔
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
//開檔
string filePath = AppDomain.CurrentDomain.BaseDirectory + "\\testData.xlsx";
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
//載入Excel檔案
using (ExcelPackage ep = new ExcelPackage(fs))
{
ExcelWorksheet sheet = ep.Workbook.Worksheets[1];//取得Sheet1
int startRowNumber = sheet.Dimension.Start.Row;//起始列編號，從1算起
int endRowNumber = sheet.Dimension.End.Row;//結束列編號，從1算起
int startColumn = sheet.Dimension.Start.Column;//開始欄編號，從1算起
int endColumn = sheet.Dimension.End.Column;//結束欄編號，從1算起 // BMK 複製sheet
ep.Workbook.Worksheets.Copy(sheet.Name, sheet.Name + "I");
ExcelWorksheet sheet2 = ep.Workbook.Worksheets.Add("Test2"); //新增sheet2
// BMK 複製Cells 範圍
sheet.Select("A1:C3"); //等同於 sheet.Select(sheet.Cells[1,1,3,3])
sheet.SelectedRange.Copy(sheet2.Cells["B2:C4"]);
sheet.SelectedRange.Clear(); //有clear：剪下，沒有則變成複製
// BMK 刪除A1 Column
sheet.DeleteColumn(1);
// BMK 在A1的前面插入3個col
sheet.InsertColumn(1, 3);
bool isHeader = true;
if (isHeader)//有包含標題
{
startRowNumber += 1;
}
////寫入標題文字
sheet.Cells[1, 1].Value = "第1欄";
sheet.Cells[1, 2].Value = "第2欄";
for (int currentRow = startRowNumber; currentRow <= endRowNumber; currentRow++)
{
ExcelRange range = sheet.Cells[currentRow, startColumn, currentRow, endColumn];//抓出目前的Excel列
if (range.Any(c => !string.IsNullOrEmpty(c.Text)) == false)//這是一個完全空白列(使用者用Delete鍵刪除動作)
{
continue;//略過此列
}
//讀值
string cellText = sheet.Cells[currentRow, 1].Text;//讀取格式化過後的文字(讀取使用者看到的文字)
string cellValue = Convert.ToString(sheet.Cells[currentRow, 1].Value); //讀取格式化過後的原始內容
//寫值
sheet.Cells[currentRow, 1].Value = cellText + "test";
}
//建立檔案
using (FileStream createStream = new FileStream(filePath, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
ep.SaveAs(createStream);//存檔
}//end using
msg("修改完成");
}//end using
}//end using
}
/// <summary>
/// 檔案匯出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("sn");
dt.Columns.Add("volt1");
dt.Columns.Add("volt2");
dt.Columns.Add("volt3");
dt.Columns.Add("volt4");
dt.Columns.Add("volt5");
dt.Columns.Add("volt6");
for (int i = 0; i < 10; i++)
{
Random rand1 = new Random(i);
dt.Rows.Add(new object[] {i,rand1.Next(),rand1.Next(),rand1.Next(),rand1.Next(),rand1.Next(),rand1.Next() });
}
MemoryStream stream = new MemoryStream();
using (var ep = new ExcelPackage(stream))
{
var worksheet = ep.Workbook.Worksheets.Add("Worksheet Name");
//datatable 使用 LoadFromDatatable,collection 可使用 LoadFromCollection
worksheet.Cells["A3"].LoadFromDataTable(dt,true);
ep.Save();
string filePath = AppDomain.CurrentDomain.BaseDirectory + "xlsx\\" +
DateTime.Now.ToString("yyyy-MM-dd-hhmm") + ".xlsx";
using (FileStream myFile = File.Open(filePath, FileMode.OpenOrCreate))
{
stream.WriteTo(myFile);
}
}
dataGridView1.DataSource = dt;
}
/// <summary>
/// 欄位格式屬性
/// </summary>
/// <param name="sheet1"></param>
private void cellConfig(ref ExcelWorksheet sheet1)
{
sheet1.Cells[1, 1].Value = "欄位1";//加入標頭
sheet1.Cells[1, 2].Value = "欄位2";//同上
sheet1.Cells["C1"].Value = "欄位3";
// BMK 黑大的擴充方法，設定字體、背景、位置
sheet1.Cells[1, 9, 1, 12].Value = "欄位N";
sheet1.Cells[1, 9, 1, 12].SetQuickStyle(Color.Yellow, Color.Green, ExcelHorizontalAlignment.Center);
// BMK 合併儲存格
sheet1.Cells[2, 1, 3, 3].Merge = true; //從A2[2,1]:C3[3,3] 合併
sheet1.Cells[2, 1].Value = "Merge Col";
// BMK 文字置中
sheet1.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //合併的欄位，以開頭算
// BMK 文字cell置中
sheet1.Cells[2,1,3,3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// BMK 自動調整寬度
sheet1.Cells[4, 4].Value = "Hello,EPPLUS.This is first used.i wish it's good "; //有可能因為上面的欄位而沒有動作
sheet1.Column(4).AutoFit();
// BMK 算col數量
int startColumn = sheet1.Dimension.Start.Column;
int endColumn = sheet1.Dimension.End.Column;
sheet1.Cells[4, 1].Value = "Start Column:" + startColumn;
sheet1.Cells[4, 2].Value = "End Column:" + endColumn;
// BMK 換行
sheet1.Cells[5, 1].Value = "Line\nNo";
sheet1.Cells[5, 1].Style.WrapText = true;
// BMK Excel的註解
var command = sheet1.Cells[5, 1];
command.AddComment("Line Number", "Daimom");
// BMK 框線
sheet1.Cells[5, 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet1.Cells[5, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet1.Cells[5, 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
sheet1.Cells[5, 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
//BMK 範圍內的框線
sheet1.Cells[2, 1, 3, 3].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet1.Cells[2, 1, 3, 3].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet1.Cells[2, 1, 3, 3].Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet1.Cells[2, 1, 3, 3].Style.Border.Left.Style = ExcelBorderStyle.Thin;
// BMK 背景顏色
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B7DEE8");
sheet1.Cells[5, 1].Style.Fill.PatternType = ExcelFillStyle.Solid; //設定背景的實線
sheet1.Cells[5, 1].Style.Fill.BackgroundColor.SetColor(colFromHex);
sheet1.Cells[5, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet1.Cells[5, 2].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(170, 170, 170));
sheet1.Cells["C1:F1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet1.Cells["C1:F1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
sheet1.Cells["A6:B6"].Style.Fill.PatternType = ExcelFillStyle.LightTrellis; //背景虛線
// BMK 字型設定
sheet1.Cells[1, 1, 1, endColumn].Style.Font.SetFromFont(new Font("Arial", 8, FontStyle.Italic | FontStyle.Bold));
sheet1.Cells[1, 1, 1, endColumn].Style.Font.SetFromFont(new Font("Arial", 8)); //這個要先打
sheet1.Cells[1, 1, 1, endColumn].Style.Font.Bold = true;
// BMK 手動設定寬度
sheet1.Column(1).Width += 10;
}
private void button2_Click(object sender, EventArgs e)
{
System.Diagnostics.Process.Start("explorer.exe", AppDomain.CurrentDomain.BaseDirectory + "xlsx\\");
}
private void msg(string str)
{
textBox1.Text += Environment.NewLine + " >> " + str;
textBox1.SelectionStart = textBox1.Text.Length;
textBox1.ScrollToCaret();
}
上面的程式是在windowsForm裡面寫的，
所以匯出檔案的部份跟web版的不一樣。
MVC的匯出檔案，請參考下面
public async Task<ActionResult> PostParAsync(){
ExcelPackage ep = new ExcelPackage();
ep.Workbook.Worksheets.Add("歸檔資料");
ExcelWorksheet sheet1 = ep.Workbook.Worksheets[1];
string DownloadName = DateTime.Now.ToString(@"yyyy-MM-dd hhmmss") + ".xlsx";
//塞入資料
//......
MemoryStream memory = new MemoryStream();
ep.SaveAs(memory);
memory.Position = 0;
var fsr = new FileStreamResult(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
fsr.FileDownloadName = DownloadName;
return fsr;
}
沒有留言:
張貼留言