public void SaveCopyAs(string Ps_FileName)
{
try
{
Mwb_Book.SaveCopyAs(Ps_FileName);
}
catch (Exception)
{
throw;
}
}
public static void Export1(DataTable dt, string filepath)
{
String strFileName = "";
strFileName = filepath;
// Server File Path Where you want to save excel file.
ExcelApp.Application myExcel = new ExcelApp.Application();
//Create a New file
ExcelApp._Workbook mybook = myExcel.Workbooks.Add(System.Reflection.Missing.Value);
//Open the exist file
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath,
// Type.Missing, Type.Missing, Type.Missing,
// Type.Missing,Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing,Type.Missing, Type.Missing);
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath);
myExcel.Visible = false;
try
{
mybook.Activate();
ExcelApp._Worksheet mysheet = (ExcelApp._Worksheet)mybook.ActiveSheet;
int colIndex = 0;///********////
int rowIndex = 0;
//foreach (DataColumn dcol in dt.Columns)
//{
// colIndex = colIndex + 1;
// myExcel.Cells[1, colIndex] = dcol.ColumnName;
//}
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
myExcel.Cells[rowIndex + 1, colIndex] = dcol.ColumnName;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).WrapText = true;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Font.Bold = true;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Font.Size = 10;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
ExcelApp.Borders borders = mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Borders;
borders[ExcelApp.XlBordersIndex.xlEdgeLeft].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeTop].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeBottom].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeRight].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders.Color = 0;
borders[ExcelApp.XlBordersIndex.xlInsideVertical].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlInsideHorizontal].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlDiagonalUp].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlDiagonalDown].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders = null;
// mysheet.Columns.WrapText = mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]);
//mysheet.get_Range(myExcel.Cells[1, colIndex], myExcel.Cells[1, colIndex]).
}
ExcelApp.Style style1 = myExcel.ActiveWorkbook.Styles.Add("Content", Type.Missing);
//style1.Borders.Color = Color.Black;
style1.Font.Name = "Verdana";
// style1.WrapText = true;
style1.Font.Size = 10;
style1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
style1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);
foreach (DataRow drow in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
//string str = drow[3].ToString();
//if ((!string.IsNullOrEmpty(drow[3].ToString())))
//{
// decimal sum = Convert.ToDecimal(str);
// if (sum <= 0)
// {
// break;
// }
//}
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
myExcel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
}
// //if (sum != 0)
// //{
// int col = colIndex;
// for (int i = 0; i <= col; i++)
// {
// ExcelApp.Range rng = (ExcelApp.Range)mysheet.Cells[rowIndex + 1, col];
// // mysheet.Columns.WrapText = rng;
// // rng.WrapText = true;
// rng.Style = style1;
// ExcelApp.Borders borders = mysheet.get_Range(myExcel.Cells[rowIndex + 1, col], myExcel.Cells[rowIndex + 1, col]).Borders;
// borders[ExcelApp.XlBordersIndex.xlEdgeBottom].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
// }
// //}
//}
}
mysheet.Columns.AutoFit();
//For Saving excel file on Server
mybook.SaveCopyAs(strFileName);
}
catch (Exception wzx)
{
MessageBox.Show(wzx.Message);
}
finally
{
mybook.Close(false, false, System.Reflection.Missing.Value);
myExcel.Quit();
GC.Collect();
}
}
public void CrearExcel(System.Data.DataTable dt, string path)
{
object misvalue = System.Reflection.Missing.Value;
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
bool codescreenUpdateState = oXL.ScreenUpdating;
bool statusBarState = oXL.DisplayStatusBar;
bool eventsState = oXL.EnableEvents;
string pathDefinitivo = path + ".xlsx";
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oWB.SaveCopyAs(pathDefinitivo);
//oWB.SaveAs(pathDefinitivo, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
// false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB = oXL.Workbooks.Open(pathDefinitivo, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oXL.Calculation = XlCalculation.xlCalculationManual;
oXL.ScreenUpdating = false;
oXL.DisplayStatusBar = false;
oXL.Calculation = XlCalculation.xlCalculationManual;
oXL.EnableEvents = false;
oXL.Visible = false;
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//Cargar header
oSheet.Cells[1, 1] = "Empresa: ";
oSheet.Cells[1, 2] = "SURFACTAN S.A.";
oSheet.Cells[1, 11] = "Fecha: ";
oSheet.Cells[1, 12] = DateTime.Now.ToShortDateString();
oSheet.Cells[2, 7] = "Listado de Muestras";
oSheet.get_Range("L1", "L1").EntireColumn.NumberFormat = "mm/dd/yyyy";
oSheet.get_Range("A1", "L2").Font.Bold = true;
oSheet.get_Range("A1", "L1").Cells.Font.Size = 10;
oSheet.get_Range("A1", "L2").Cells.Font.Name = "Times New Roman";
oSheet.get_Range("G2", "G2").Cells.Font.Size = 16;
oSheet.get_Range("G2", "G2").HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
oSheet.get_Range("G2", "G2").Cells.Font.Size = 16;
oSheet.get_Range("A1", "L2").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("G2", "G2").Borders.LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//Agrego los headers
oSheet.Cells[4, 1] = "Fecha";
oSheet.Cells[4, 2] = "Codigo";
oSheet.Cells[4, 3] = "Cantidad";
oSheet.Cells[4, 4] = "Nombre para el Cliente";
oSheet.Cells[4, 6] = "Cliente";
oSheet.Cells[4, 8] = "Observaciones";
oSheet.Cells[4, 10] = "Lote";
oSheet.Cells[4, 11] = "Cantidad";
oSheet.Cells[4, 12] = "Observaciones";
oSheet.get_Range("A4", "L4").Cells.Font.Size = 8;
oSheet.get_Range("A4", "L4").Cells.Font.Name = "Times New Roman";
oSheet.get_Range("A4", "L4").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A4", "L4").HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
int rango = 6;
for (int i = 0; i < dt.Rows.Count; i++)
{
string[,] valores = new string[dt.Rows.Count, 12];
DataRow dr = dt.Rows[i];
valores[0, 0] = dr["Fecha"].ToString();
valores[0, 1] = dr["Codigo"].ToString();
valores[0, 2] = dr["Cantidad"].ToString();
valores[0, 3] = dr["DescriCliente"].ToString();
valores[0, 4] = "";
valores[0, 5] = dr["Razon"].ToString();
valores[0, 6] = "";
valores[0, 7] = dr["Observaciones"].ToString();
valores[0, 8] = "";
valores[0, 9] = dr["Lote2"].ToString();
valores[0, 10] = dr["Cantidad2"].ToString();
valores[0, 11] = dr["Observaciones2"].ToString();
oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Value2 = valores;
oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Cells.Font.Size = 8;
oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Cells.Font.Name = "Times New Roman";
rango += 1;
}
oXL.Visible = false;
oXL.UserControl = false;
oWB.Save();
oWB.Close();
oXL.ScreenUpdating = codescreenUpdateState;
oXL.DisplayStatusBar = statusBarState;
oXL.EnableEvents = eventsState;
MessageBox.Show("El archivo se genero con exito", "Generacion de Excel",
MessageBoxButtons.OK, MessageBoxIcon.None);
}
catch (Exception err)
{
MessageBox.Show(err.Message, "Error al generar archivo",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 另存为xls文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void Save(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath)
{
CurBook.SaveCopyAs(strFilePath);
}
private void saveTOcsv(object sender, EventArgs e)
{
button1_Click(sender, e);
// 開啟一個新的應用程式
Excel.Application app = new Excel.Application();
// 讓Excel文件可見
app.Visible = true;
// 停用警告訊息
app.DisplayAlerts = false;
// 加入新的活頁簿
app.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wbk = app.Workbooks[1];
// 設定活頁簿焦點
wbk.Activate();
try
{
// 引用第一個工作表
wst = (Excel._Worksheet)wbk.Worksheets[1];
// 命名工作表的名稱
wst.Name = db.Station_0001.mday;
// 設定工作表焦點
wst.Activate();
app.Cells[1, 1] = "ubike retrieve data";
// 設定metainfo
app.Cells[1, 1] = "站點";
app.Cells[2, 1] = "資料更新時間";
app.Cells[3, 1] = "目前車輛數";
app.Cells[4, 1] = "空位數量";
app.Cells[5, 1] = "總停車格";
// 設定第1列顏色
wrange = wst.Range[wst.Cells[1, 1], wst.Cells[5, 1]];
wrange.Select();
wrange.Font.Color = ColorTranslator.ToOle(Color.Blue); //word color
wrange.Interior.Color = ColorTranslator.ToOle(Color.White); //back color
// 設定第2列資料
for (int c = 2; c < db.Stations.Count; c++)
{
app.Cells[1, c] = db.Stations[c - 2].sno; //站點
app.Cells[2, c] = db.Stations[c - 2].mday; //資料更新時間
app.Cells[3, c] = db.Stations[c - 2].sbi; //目前車數
app.Cells[4, c] = db.Stations[c - 2].bemp; //空格數
app.Cells[5, c] = db.Stations[c - 2].tot; //總停車數
}
try
{
//另存活頁簿
wbk.SaveAs(savePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbk.SaveCopyAs(db.Station_0001.mday);
Console.WriteLine("儲存文件於 " + Environment.NewLine + savePath);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
}
catch (Exception ex)
{
Console.WriteLine("產生報表時出錯！" + Environment.NewLine + ex.Message);
}
//關閉活頁簿
wbk.Close(false, Type.Missing, Type.Missing);
//關閉Excel
app.Quit();
//釋放Excel資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wbk = null;
wst = null;
wrange = null;
app = null;
GC.Collect();
}
private void XMLsave2excel(object sender, EventArgs e)
{
// 開啟一個新的應用程式
Excel.Application app = new Excel.Application();
// 讓Excel文件可見
app.Visible = true;
// 停用警告訊息
app.DisplayAlerts = false;
// 加入新的活頁簿
app.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wbk = app.Workbooks[1];
// 設定活頁簿焦點
wbk.Activate();
try
{
// 引用第一個工作表
wst = (Excel._Worksheet)wbk.Worksheets[1];
// 命名工作表的名稱
wst.Name = "kk citybike";
// 設定工作表焦點
wst.Activate();
app.Cells[1, 1] = "city bike retrieve data";
// 設定metainfo
app.Cells[1, 1] = "站點";
app.Cells[2, 1] = "目前車輛數";
app.Cells[3, 1] = "空位數量";
app.Cells[4, 1] = "總停車格";
app.Cells[5, 1] = "lat,lon";
// 設定第1列顏色
wrange = wst.Range[wst.Cells[1, 1], wst.Cells[5, 1]];
wrange.Select();
wrange.Font.Color = ColorTranslator.ToOle(Color.Blue); //word color
wrange.Interior.Color = ColorTranslator.ToOle(Color.White); //back color
// 設定第2列資料
for (int c = 2; c < bs.cbs.Count + 2; c++)
{
app.Cells[1, c] = c - 1; //站點
app.Cells[2, c] = bs.cbs[c - 2].StationNums1; //資料更新時間
app.Cells[3, c] = Convert.ToInt32(bs.cbs[c - 2].StationNums2); //目前車數
app.Cells[4, c] = Convert.ToInt32(bs.cbs[c - 2].StationNums1) + Convert.ToInt32(bs.cbs[c - 2].StationNums2); //空格數
app.Cells[5, c] = bs.cbs[c - 2].StationLat + "," + bs.cbs[c - 2].StationLon; //總停車數
}
DateTime thisDay = DateTime.Today;
savePath = "C:\\Users\\user\\Desktop\\rawdata\\" + "kk";
try
{
//另存活頁簿
wbk.SaveAs(savePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbk.SaveCopyAs("kk");
Console.WriteLine("儲存文件於 " + Environment.NewLine + savePath);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
}
catch (Exception ex)
{
Console.WriteLine("產生報表時出錯！" + Environment.NewLine + ex.Message);
}
//關閉活頁簿
wbk.Close(false, Type.Missing, Type.Missing);
//關閉Excel
app.Quit();
//釋放Excel資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wbk = null;
wst = null;
wrange = null;
app = null;
GC.Collect();
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void Save(Excel._Workbook CurBook, string strFilePath)
{
CurBook.SaveCopyAs(strFilePath);
}